Compare range of ip addresses with start and end ip address in MySQL

Question

I have a MySQL table where I store IP ranges. It is setup in the way that I have the start address stored as a long, and the end address (and an id and some other data). Now I have users adding ranges by inputting a start and end ip address, and I would like to check if the new range is not already (partially) in the database.

I know I can do a between query, but that doesn't seem to work with 2 different columns, and I also cannot figure out how to pass a range to compare it. Doing it in a loop in PHP is a possibility, but would with a range of e.g. 132.0.0.0-199.0.0.0 be quite a big amount of queries..

Answer 1

What about something like:

SELECT * FROM addresses WHERE
    (start < $new_start AND end > $new_start) OR
    (start > $new_end AND end < $new_end);

That should give you all the existing addresses which will overlap the new address.

Answer 2

Both start_address and end_address are longs? Then why not just convert the IP you're looking for to a long and do WHERE start_address <= $myip AND end_address >= $myip?

For a range, just do it three times in the where clause

WHERE (start_address <= $startAddress AND end_address >= $startAddress) 
   OR (start_address <= $endAddress AND end_address >= $endAddress) 
   OR (start_address >= $startAddress AND end_address <= $endAddress)

The first grouping finds ranges that encompass the start address. The second one finds ranges that encompass the end address. That means that there's still a possibility that the range that was inputted is a superset of a range in the db. That's what the third one checks for.

That should return you all the ranges which intersect your inputted range...

Answer 3

When you say you have the addresses stored as a long, I'm assuming you mean you've got them stored such that (say) 10.1.2.3 will be stored as 0x0a010203. In that case, to find if an address is already present, you can do:

SELECT ...
FROM ipranges
WHERE (<NEWADDR> >= startaddr)
    AND (<NEWADDR> <= endaddr)

and then if you get any rows back, the address is already in the table. (replace <NEWADDR> with the new address, of course!)

As for checking overlapping rows, that's only slightly more complicated:

SELECT ...
FROM ipranges
WHERE NOT ((<NEWENDADDR> < startaddr) OR (<NEWSTARTADDR> > endaddr))

i.e. the new range doesn't overlap the old range provided that either it starts after it, or ends before it.

Answer 4

How about storing the IP addres ranges as decimal that way you can do simple checks using the BETWEEN.

4294967295 - 4294967040 -> FFFFFF00 - FFFFFFFF -> 255.255.255.0 - 255.255.255.255