php - Best way to store many ip ranges?
541
I have 3,000 rows of IP ranges in this format:
5.9.0.0 5.9.255.255 Hetzner http://www.hetzner.de/
5.10.64.0 5.10.127.255 SoftLayer http://www.softlayer.com/
5.34.182.0 5.34.183.255 UA Servers https://itldc.com/
If a user visits my site I want to lookup if their IP is in this IP ranges and block them if that is the case. What is the fastest way to lookup their IP with PHP? The IP ranges are static and will never change.
I already thought of creating a .dat file to store the IPs is this a good approach?
Answer
Solution:
(Previously there was a link to how to do the task in PHP. This question seems to be about MySQL.)
If you are doing IPv4 only, then store into datatype
INT UNSIGNED, and useINET_ATON()when storing andINET_NTOA()when fetching.For IPv6, use
BINARY(16)withINET6_ATON()andINET6_NTOA().Answer
Solution:
Converting the dotted quad to an integer value is only the start of a solution.
You can't dereference values in an array or from a database using a normal index. Consider the case where you want to find a record, say 125.54.123.8 in your ranges. If your ranges are keyed by the start address, then you can only exclude records where the start adress is greater than your search value. If it is keyed on the end of the range, you can only exclude values where the end address is less than your search value. You still have to examine approximately half your data to find a match (or lack thereof).
You could use databuckets or nested arrays but these are somewhat kludgy solutions.
A further consderation is, if your are using a php datastructure to manage the ranges is the time it will take to load and parse the dataset before you can begin searching it. From experience, this overhead kicks in with a dataset of approx 100-200 rows compared with fetching a record from mysql.
The solution is to use mysql's geospatial capabilities to map the ranges in a one dimensional space - I get lookup times under 1ms on modest hardware searching the whole of the internet's assigned ranges (using the datasets from RIPE, APNIC etc).
The process is described here
Note: ip2long() returns a signed 32bit integer value in PHP while MySQL's inet_aton,() is unsigned (i.e. you can'tmix and match the 2 functions)