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