SQL query selecting public IP address

Question

I am on a postgresql 8.3 database. I am trying to figure out where the mistake is in the query below. I am trying to design a query to only select source_ips and destination_ips that are private addresses.

For some reason one of the addresses that is grabbed in the query below is the address 208.117.252.39 which is not a private address.

Is there something wrong with the logic in the query below that would make it select public IP addresses as well?

 select source_ip, destination_ip
from ip_table
where
    (
    inet '10/8' >> source_ip
    or inet '192.168/16' >> source_ip
    or source_ip >= inet '172.16/16' and source_ip < inet '172.32/16'
     )
    and  inet '10/8' >> destination_ip
    or  inet '192.168/16' >> destination_ip

Just for clarity's sake, try replacing "source_ip >= inet '172.16/16' and source_ip < inet '172.32/16'" with "inet '172.16/12' >> source_ip". That private range is represented as a /12 CIDR and does not need to be handled specially. — hoxworth, Mar 29 '13 at 00:09

score 2 · Answer 1 · answered Mar 29 '13 at 00:17

Since the and operation has priority over or you are missing a parenthesis. Your query is equivalent to:

select source_ip, destination_ip
from ip_table
where
    (
        (
        inet '10/8' >> source_ip
        or inet '192.168/16' >> source_ip
        or source_ip >= inet '172.16/16' and source_ip < inet '172.32/16'
        )
        and  inet '10/8' >> destination_ip
    )
    or  inet '192.168/16' >> destination_ip

The correct version:

select source_ip, destination_ip
from ip_table
where
    (
        inet '10/8' >> source_ip
        or inet '192.168/16' >> source_ip
        or source_ip >= inet '172.16/16' and source_ip < inet '172.32/16'
    )
    and
    (
        inet '10/8' >> destination_ip
        or  inet '192.168/16' >> destination_ip
    )

score 1 · Accepted Answer · answered Mar 29 '13 at 00:16

You need to appropriately group your final conditions. Right now, the last "or" ignores all of the source_ip conditions.

Structure the query as such:

select source_ip, destination_ip
from ip_table
where
(
  inet '10/8' >> source_ip
    or inet '192.168/16' >> source_ip
    or inet '172.16/12' >> source_ip
 )
and  (
  inet '10/8' >> destination_ip
    or  inet '192.168/16' >> destination_ip
    or inet '172.16/12' >> destination_ip
);

Note the destination clause is grouped together.

SQL query selecting public IP address

2 Answers2