Menu
I have hundreds of thousands of IP's and want to identify which ones are within a certain range. Ranges:
64.233.160.0 / 8192
66.102.0.0 / 4096
66.249.64.0 / 8192
72.14.192.0 / 16384
74.125.0.0 / 65536
209.85.128.0 / 32768
216.239.32.0 / 8192
So I converted these ranges to the following:
64.233.160.0 - 64.233.192.0
66.102.0.0 - 66.102.16.0
66.249.64.0 - 66.249.96.0
72.14.192.0 - 72.15.0.0
74.125.0.0 - 74.126.0.0
209.85.128.0 - 209.86.0.0
216.239.32.0 - 216.239.64.0
So now I want to query if an IP address is within any of these ranges. SQL isn't going to understand the octets so I don't know what to do.
Could use some Hex2Dec/Dec2Hex conversions?
I figure this should be something that has been done before, I'm sure I'm not the first person to try and identify particular ip's in a list using an ip range.
I will be doing look ups on multiple IP addresses so some might be 20.0.1.123 and another might be 124.123.123.1 ie the format of the octets won't be the same
312
IP addresses are expressed as a set of four numbers — an example address might be 192.158.1.38. Each number in the set can range from 0 to 255. So, the full IP addressing range goes from 0.0.0.0 to 255.255.255.255.
Click IP Address Manager > IP Addresses > Manage Subnets & IP Addresses. In the network tree pane on the left, click the subnet to which you want to add your new IP address range. Click Add IP Range. Enter the starting IP address and the ending IP address of your IP address range.
The SQL BETWEEN condition allows you to easily test if an expression is within a range of values (inclusive). The values can be text, date, or numbers. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
We can store an IP address with the help of INT unsigned. While using INSERT, include INET_ATON() and with SELECT, include INET_NTOA(). IP address is in dotted format.
You can us the IP functions for this:
PARSE_IP('64.233.160.0') returns 1089052672
and then you can wrap them in a BETWEEN state.
103
IP numbers are really just integers. What you have done here is that you have saved them as human-readable strings. You will need to convert them back to their original integer representation so that you can state a query with normal BETWEEN.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
