Checking if IP is in a range in MYSQL

Question

I have a simple table with IP ranges like this (they are saved as varchar, but I can change that if needed):

1.0.0.0/24
1.0.1.0/24
1.0.2.0/23
1.0.4.0/22
...etc

How can I select the row where the IP address given falls into that range?

Something like:

SELECT range FROM ipranges WHERE '195.124.199.201' IN range

Edit: the suggested duplicate answer MySQL check if an IP-address is in range? does not work for me, since it assumes the range is specified as two IP addresses, but my notation uses a slash and I do not know how to convert between the two of them.

Answer 1

You need to use INET_ATON() to convert IPv4 address format to the unsigned integer equivalent.

Start by calculating the starting address and the ending address based on the CIDR notation.

mysql> SELECT INET_ATON(SUBSTRING_INDEX(range, '/', 1)) AS start_address, 
  POWER(2, 32-SUBSTRING_INDEX(range, '/', -1))-1 AS num_addresses
  FROM ipranges;
+---------------+---------------+
| start_address | num_addresses |
+---------------+---------------+
|      16777216 |           255 |
+---------------+---------------+

That result is when I tested this query against '1.0.0.0/24'.

Then you can check if the INET_ATON() of the IP address you're interested in falls in the range, because it's simply an integer range.

SELECT range FROM (
    SELECT range, 
      INET_ATON(SUBSTRING_INDEX(range, '/', 1)) AS start_address, 
      POWER(2, 32-SUBSTRING_INDEX(range, '/', -1))-1 AS num_addresses
    FROM ipranges) AS t
WHERE INET_ATON('195.124.199.201') BETWEEN start_address AND start_address+num_addresses;

I'm afraid with all these expressions, it's not possible for the SQL query to be optimized with indexes. If you need this to run with high performance against a large data set of IP ranges, you need to think of a way to store the data differently so you can index it.

But if you only have a small number of IP ranges, even an unindexable query may nevertheless be fast enough.