Select CIDR that is in range of IP

So I have an IP like 45.76.255.14, and I have a table with rows of CIDR stored as a single varchar, how would I select CIDRs that are in the range of that IP address. For example 45.76.255.14/31

So in theory: select CIDR where in range of IP

Solution

Storing IP addresses in dotted quad notation in a VARCHAR is not the most optimal way of storing them, since dotted-quad is a human friendly representation of a 32 bit unsigned integer that doesn't lend itself to database indexing. But sometimes it's fundamentally more convenient, and at small scale, the fact that queries require a table scan isn't usually a problem.

MySQL Stored Functions are a good way of encapsulating relatively complex logic behind a simple function that can be referenced in a query, potentially leading to easier-to-understand queries and reducing copy/paste errors.

So, here's a stored function I wrote called find_ip4_in_cidr4(). It works somewhat similarly to the built-in function FIND_IN_SET() -- you give it a value and you give it a "set" (CIDR spec) and it returns a value to indicate whether the value is in the set.

First, an illustration of the function in action:

If the address is inside the block, return the prefix length. Why return the prefix length? Non-zero integers are "true," so we could just return 1, but if you want to sort the matching results to find the shortest or longest of multiple matching prefixes, you can ORDER BY the return value of the function.

mysql> SELECT find_ip4_in_cidr4('203.0.113.123','203.0.113.0/24');
+-----------------------------------------------------+
| find_ip4_in_cidr4('203.0.113.123','203.0.113.0/24') |
+-----------------------------------------------------+
|                                                  24 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT find_ip4_in_cidr4('192.168.100.1','192.168.0.0/16');
+-----------------------------------------------------+
| find_ip4_in_cidr4('192.168.100.1','192.168.0.0/16') |
+-----------------------------------------------------+
|                                                  16 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Not in the block? That returns 0 (false).

mysql> SELECT find_ip4_in_cidr4('192.168.100.1','203.0.113.0/24');
+-----------------------------------------------------+
| find_ip4_in_cidr4('192.168.100.1','203.0.113.0/24') |
+-----------------------------------------------------+
|                                                   0 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT find_ip4_in_cidr4('192.168.100.1','192.168.0.0/24');
+-----------------------------------------------------+
| find_ip4_in_cidr4('192.168.100.1','192.168.0.0/24') |
+-----------------------------------------------------+
|                                                   0 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

There's a special case for the all-zeroes address, we return -1 (still "true", but preserves the sort order):

mysql> SELECT find_ip4_in_cidr4('192.168.100.1','0.0.0.0/0');
+------------------------------------------------+
| find_ip4_in_cidr4('192.168.100.1','0.0.0.0/0') |
+------------------------------------------------+
|                                             -1 |
+------------------------------------------------+
1 row in set (0.00 sec)

Nonsense arguments return null:

mysql> SELECT find_ip4_in_cidr4('234.467.891.0','192.168.0.0/24');
+-----------------------------------------------------+
| find_ip4_in_cidr4('234.467.891.0','192.168.0.0/24') |
+-----------------------------------------------------+
|                                                NULL |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Now, teh codez:

DELIMITER $$

DROP FUNCTION IF EXISTS `find_ip4_in_cidr4` $$
CREATE DEFINER=`mezzell`@`%` FUNCTION `find_ip4_in_cidr4`(
  _address VARCHAR(15), 
  _block VARCHAR(18)
) RETURNS TINYINT
DETERMINISTIC /* for a given input, this function always returns the same output */
CONTAINS SQL /* the function does not read from or write to tables */
BEGIN

-- given an IPv4 address and a cidr spec,
-- return -1 for a valid address inside 0.0.0.0/0
-- return prefix length if the address is within the block,
-- return 0 if the address is outside the block,
-- otherwise return null

DECLARE _ip_aton INT UNSIGNED DEFAULT INET_ATON(_address);
DECLARE _cidr_aton INT UNSIGNED DEFAULT INET_ATON(SUBSTRING_INDEX(_block,'/',1));
DECLARE _prefix TINYINT UNSIGNED DEFAULT SUBSTRING_INDEX(_block,'/',-1);
DECLARE _bitmask INT UNSIGNED DEFAULT (0xFFFFFFFF << (32 - _prefix)) & 0xFFFFFFFF;

RETURN CASE /* the first match, not "best" match is used in a CASE expression */
  WHEN _ip_aton IS NULL OR _cidr_aton IS NULL OR /* sanity checks */
       _prefix  IS NULL OR _bitmask IS NULL OR
       _prefix NOT BETWEEN 0 AND 32 OR
       (_prefix = 0 AND _cidr_aton != 0) THEN NULL
  WHEN _cidr_aton = 0 AND _bitmask = 0 THEN -1
  WHEN _ip_aton & _bitmask = _cidr_aton & _bitmask THEN _prefix /* here's the only actual test needed */
  ELSE 0 END;

END $$
DELIMITER ;

An issue that is not specific to stored functions, but rather applies to most functions on most RDBMS platforms is that when a column is used as an argument to a function in WHERE, the server can't "look backwards" through the function to use an index to optimize the query.