Check if IP is in subnet

问题:

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?

回答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");

回答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                 |
+------------------------+----------------------------+

回答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")

回答4:

I have a solution for when the addresses are stored as strings. So if you wish to make that part of your algorithm, you can use my solution here.

It is a little tricky, especially for IPv6. In IPv6 there can optionally be compressed segments, like 1::1 which is equivalent to 1:0:0:0:0:0:0:1, which is the main reason it is tricky.

The IPAddress Java library will produce mysql SQL to search for addresses in a given subnet. The link describes this problem in more detail. Disclaimer: I am the project manager.

The basic algorithm is to take the network section of the subnet address, then take each variant of that section (for example the two strings above are variants of the full address 1::1), then count the number of segment separators, then do a mysql substring on the address being matched, but also count the total separators in the address being matched.

Here is sample code:

public static void main(String[] args) throws IPAddressStringException {
    System.out.println(getSearchSQL("columnX", "1.2.3.4/16"));
    System.out.println(getSearchSQL("columnX", "1:2:3:4:5:6:7:8/64"));
    System.out.println(getSearchSQL("columnX", "1::8/64"));
}

static String getSearchSQL(String expression, String ipAddressStr) throws IPAddressStringException {
    IPAddressString ipAddressString = new IPAddressString(ipAddressStr);
    IPAddress ipAddress = ipAddressString.toAddress();
    IPAddressSection networkPrefix = ipAddress.getNetworkSection(ipAddress.getNetworkPrefixLength(), false);
    StringBuilder sql = new StringBuilder("Select rows from table where ");
    networkPrefix.getStartsWithSQLClause(sql, expression);
    return sql.toString();
}

Ouptut:

Select rows from table where (substring_index(columnX,'.',2) = '1.2')
Select rows from table where (substring_index(columnX,':',4) = '1:2:3:4')
Select rows from table where ((substring_index(columnX,':',4) = '1:0:0:0') OR ((substring_index(columnX,':',2) = '1:') AND (LENGTH (columnX) - LENGTH(REPLACE(columnX, ':', '')) <= 5)))