Check if IP is in subnet

Question

I have a table A with IP addresses (ipNumeric) stored as unsigned ints and a table B with subnets (subnetNumeric):

INET_NTOA(ipNumeric) = 192.168.0.1
INET_NTOA(subnetNumeric) = 192.168.0.0

I'd like to check if this IP is a member of a subnet.

The subnets are Class A, B and C.

Is this possible to do in reasonable time in MySQL on the fly or should the subnet ranges be precomputed?

Solution 1

Sure, it's doable. The idea is that we calculate the subnet mask by setting the most significant bits to 1, as many as dictated by the subnet class. For a class C, that would be

SELECT -1 << 8;

Then, AND the subnet mask with the IP address you have; if the IP is inside the subnet, the result should be equal to the subnet address -- standard networking stuff. So we end up with:

SELECT (-1 << 8) & INET_ATON("192.168.0.1") = INET_ATON("192.168.0.0");

Update: Yes, it is necessary to know the network class or the subnet mask (which is equivalent information). Consider how could you handle the case where the subnet is X.Y.0.0 if you did not have this information. Is this X.Y.0.0/16 or X.Y.0.0/8 where the third octet just happens to be 0? No way to know.

If you do know the subnet mask, then the query can be written as

SELECT (-1 << (33 - INSTR(BIN(INET_ATON("255.255.255.0")), "0"))) &
       INET_ATON("192.168.0.1") = INET_ATON("192.168.0.0");

Solution 2

First create tables to support testing.

CREATE TABLE a (ipNumeric INT UNSIGNED);
INSERT INTO a (ipNumeric) VALUES(INET_ATON("172.16.40.101"));
INSERT INTO a (ipNumeric) VALUES(INET_ATON("192.168.1.12"));
INSERT INTO a (ipNumeric) VALUES(INET_ATON("10.1.5.51"));
INSERT INTO a (ipNumeric) VALUES(INET_ATON("10.7.1.78"));      
CREATE TABLE b (subnetNumeric INT UNSIGNED);
INSERT INTO b (subnetNumeric) VALUES (INET_ATON("192.168.0.0"));
INSERT INTO b (subnetNumeric) VALUES (INET_ATON("10.1.0.0"));
INSERT INTO b (subnetNumeric) VALUES (INET_ATON("172.16.0.0"));

Now do a select finding matches between the tables based on address & mask = subnet. Here we assume class B subnets (255.255.0.0). No need to bit-shift as we can use the INET_ATON() function.

SELECT INET_NTOA(a.ipNumeric),INET_NTOA(b.subnetNumeric) FROM a,b 
       WHERE (a.ipNumeric & INET_ATON("255.255.0.0")) = b.subnetNumeric;

+------------------------+----------------------------+
| INET_NTOA(a.ipNumeric) | INET_NTOA(b.subnetNumeric) |
+------------------------+----------------------------+
| 192.168.1.12           | 192.168.0.0                |
| 10.1.5.51              | 10.1.0.0                   |
| 172.16.40.101          | 172.16.0.0                 |
+------------------------+----------------------------+

Solution 3

This is the MySQL function we use.

DELIMITER $$
DROP FUNCTION IF EXISTS `ip_in_subnet_mask`$$
CREATE DEFINER=`root`@`%` FUNCTION `ip_in_subnet_mask`(ip VARCHAR(20), subnet VARCHAR(20), netmask VARCHAR(20)) RETURNS TINYINT(1)
    DETERMINISTIC
BEGIN
    RETURN (INET_ATON(ip) & INET_ATON(netmask)) = INET_ATON(subnet);
END$$
DELIMITER ;

e.g. Find all the rows where t.ip is in the range 192.168.0.x where 0<=x<=255

 SELECT *
 FROM t
 WHERE
    ip_in_subnet_mask(t.ip, "192.168.0.0", "255.255.255.0")