MySQL selecting IPs via CIDR
Quick little snippet here for selecting IPs from a database based off a CIDR subnet. First off a table structure with some test data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> desc tmp; +-------+------------------+ | Field | Type | +-------+------------------+ | ip1 | int(12) unsigned | | ip2 | varchar(15) | +-------+------------------+ mysql> select * from tmp; +------------+-----------------+ | ip1 | ip2 | +------------+-----------------+ | 16909060 | 1.2.3.4 | | 167904004 | 10.2.3.4 | | 2915086080 | 173.192.175.0 | | 2915086100 | 173.192.175.20 | | 2915086335 | 173.192.175.255 | +------------+-----------------+ |
Now let’s say we want all IPs from the subnet 173.192.175.16/28, using a simple 173.192.175.% would provide false results since you don’t want the whole /24.
If your IP is stored as an unsigned int (good for you) than you can use this snippet to search for matching IPs:
1 | select inet_ntoa(ip1) as ip1,ip2 from tmp where (ip1 & power(2, 32) - power(2, (32 - 28))) = inet_aton('173.192.175.16'); |
If your IP is stored as a varchar (for whatever reason), the only difference is a inet_aton() around the IP field.
1 | select inet_ntoa(ip1) as ip1,ip2 from tmp where (inet_aton(ip2) & power(2, 32) - power(2, (32 - 28))) = inet_aton('173.192.175.16'); |
No matter which one you use, the result will be:
1 2 3 4 5 | +----------------+----------------+ | ip1 | ip2 | +----------------+----------------+ | 173.192.175.20 | 173.192.175.20 | +----------------+----------------+ |