-2

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?

DarkBee
  • 16,592
  • 6
  • 46
  • 58
Veeza
  • 63
  • 5
  • postgresql supports a CIDR & INET data types, just fyi. Dunno about mysql or sqlite. – alzee Aug 07 '16 at 14:39
  • 1
    I removed the incompatible database tags. Please tag the question with the database you are using, or explicitly explain why it is was originally tagged with both MySQL and SQLite. – Gordon Linoff Aug 07 '16 at 14:40
  • The fastest way would be some in-memory (shared between processes) solution with logic tailored to this specific usage. Likely a huge overkill (and unnecessary work) for you. Be HONEST about your requirements. – Karoly Horvath Aug 07 '16 at 15:36

2 Answers2

0

(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 use INET_ATON() when storing and INET_NTOA() when fetching.

For IPv6, use BINARY(16) with INET6_ATON() and INET6_NTOA().

Community
  • 1
  • 1
Rick James
  • 135,179
  • 13
  • 127
  • 222
0

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)

symcbean
  • 47,736
  • 6
  • 59
  • 94