how to search for the ip addresses in cidr notation

Question

I need to search for the entries in the mysql database with following ipaddress “192.168.0.1/20 ”

Note: ip addresses stored are varchars (ex. 192.168.0.3,192.168.0.4)

Address:   192.168.0.1          11000000.10101000.0000 0000.00000001
Netmask:   255.255.240.0 = 20   11111111.11111111.1111 0000.00000000
Wildcard:  0.0.15.255           00000000.00000000.0000 1111.11111111
=>
Network:   192.168.0.0/20       11000000.10101000.0000 0000.00000000
HostMin:   192.168.0.1          11000000.10101000.0000 0000.00000001
HostMax:   192.168.15.254       11000000.10101000.0000 1111.11111110
Broadcast: 192.168.15.255       11000000.10101000.0000 1111.11111111
Hosts/Net: 4094                  Class C, Private Internet

my solution is to find to the network address from given cidr notation (replace 0 with "*") and search for 192.168.*.* entries in database as they are stored in strings. It works but I am not sure whether this is correct

can any point out the problem or more optimal solution than this?

Answer 1

The following works if you have single IP address per VARCHAR field.

Correct way to match IP addresses against their network using netmask is through INET_ATON()/INET_NTOA().

There are several steps, though. First use the following to convert your length to subnetmask:

SET @l=20;
SELECT INET_NTOA(0xffffffff >> (32-@l) << (32-@l));

That's just to convey the idea, to make this useful you could do the following:

SELECT 0xffffffff >> (32-@l) << (32-@l) INTO @mask;

For the actual matching of the address against your network address you can use INET_ functions with bitwise operators, for example:

SET @l=20;
SET @nw='192.168.0.0';
SELECT 0xffffffff >> (32-@l) << (32-@l) INTO @mask;
SELECT * FROM yourtable 
 WHERE (INET_ATON(addrfield) & @mask) = INET_ATON(@nw);

Of course you may roll this into a single statement like:

SELECT * FROM yourtable 
     WHERE (INET_ATON(addrfield) & (0xffffffff >> (32-@l) << (32-@l))) = INET_ATON('192.168.0.0');