3

What is the best way to store in a MySQL database multiple IP types like:
- Single IP (123.123.123.123)
- IP Ranges (123.123.123.1 - 123.123.123.121)
- IP blocks (123.123.123.1/20 )
- IP Classes (123.123.123.* or 123.123..)

I was thinking to transform all ranges/blocks/classes into single IP's and store them with ip2long for a faster search into the table, but this will result in a 1 million+ database also i need from time to time to reduce/enlarge the classes or to change/delete the IP blocks.

This database will be accessed every time someone access my website (so it needs to be quick). Any ideas?

Lajos Veres
  • 13,595
  • 7
  • 43
  • 56
dracosu
  • 177
  • 1
  • 1
  • 8
  • just as long as you know ip!= person, and any ip blocking can be circumvented. –  Oct 22 '13 at 22:31
  • Three of the types you've described here - ranges, blocks, and classes - are all different ways of describing the same thing. –  Oct 22 '13 at 22:45
  • @Dagon I know this, i don't want to use this list to block certain persons. – dracosu Oct 22 '13 at 22:58
  • @duskwuff Yes, you are right all could be translated in IP ranges very quickly but i receive the Ip's in this types so when someone will send me a modification will be a little bit hard to reverse the information from Ip range to IP block then modify/delete it and add the new information. – dracosu Oct 22 '13 at 23:02

3 Answers3

3

This tutorial can help: http://daipratt.co.uk/mysql-store-ip-address/

The most efficient say of saving IPv4 addresses is with an INT field (not VARCHAR as you might expect). You convert them using PHP's ip2long and back using either MySQL's INET_NTOA function or PHP's longtoip.

Source: IP address storing in MySQL database using PHP

Community
  • 1
  • 1
Lajos Veres
  • 13,595
  • 7
  • 43
  • 56
  • Thank's for your answer. But i've specified that i will transform the IPs in numbers (with ip2long php function). All i need is an idea to put together Ip classes/blocks/and single IPs and search/modify/delete them quickly. – dracosu Oct 22 '13 at 22:54
2

Something like:

CREATE TABLE ip_ranges (
  id         INT UNSIGNED AUTO_INCREMENT,
  ip_start   INT UNSIGNED NOT NULL,
  ip_end     INT UNSIGNED DEFAULT NULL,
  ip_subnet  TINYINT UNSIGNED DEFAULT NULL,
  ip_class   ENUM('A', 'B', 'C') DEFAULT NULL
)

Where ip_start is required and all other fields can be NULL [single IP rule] or one other can be set:

  • ip_end is the end part of a range [192.168.1.10 to 192.168.1.15] specification
  • ip_subnet is the subnet mask [192.168.1.0/22]
  • ip_class is the network class, although these could logically just be stored as /8, /16, or /24 subnets.

Ignoring ip_class completely...

SELECT *
FROM ip_ranges
WHERE
  $ipaddr BETWEEN ip_start AND ip_end
  OR
  $ipaddr BETWEEN
    (ip_start &~ (POW(2,32-ip_subnet)-1)) AND
    (ip_start |  (POW(2,32-ip_subnet)-1))

Should select any rules that apply to $ipaddr. [but I haven't tested it]

Sammitch
  • 30,782
  • 7
  • 50
  • 77
  • 1
    The concept of a network "class" has been obsolete for over 20 years - RFC 1518 and 1519, which defined CIDR, were released in 1993. Please forget that "classes" of networks ever existed. –  Oct 22 '13 at 23:43
  • @duskwuff yeah. I imagine once CIDR happened people were all like "oh jeez, how dumb was all that 'classful' bullshit?" – Sammitch Oct 22 '13 at 23:44
0

You can store all types of addresses as LineString(Point(-1, first_ip_iplong), Point(+1, last_ip_iplong)), add index to this field and enjoy instant search ( with MBRIntersects ) with almost any data or records count.

More information can be found here

Note, that this practice will tightly couple your code within mysql.

Skpd
  • 670
  • 3
  • 17
  • IP addresses are not spatial coordinates. Using GIS types to index them is a terrible idea. –  Oct 22 '13 at 22:43
  • Do you know any other way to get instant search over IP ranges? – Skpd Oct 22 '13 at 22:47
  • Uhh, `ip2long()` and then basic math. – Sammitch Oct 22 '13 at 23:01
  • 1
    +1 IP addresses absolutely are spatial coordinates (IP "address space" lol) ... the boxes (subnets) containing them are always a power of 2 in size, and finding the bounding box containing an IP address given the "corners" of the box is a brilliant use of R-Trees, far better than anything you can accomplish with B-Trees... and you get an R-Tree with a MySQL spatial index. – Michael - sqlbot Oct 22 '13 at 23:43
  • 1
    No, that's still a terrible idea. IP addresses are linear, not spatial. An address 12.34.56.78 has no relationship with 99.34.56.78, for instance. –  Oct 22 '13 at 23:44
  • 1
    Okay, fine, points on a linestring, that's still "spatial." When you're looking up an IP address, you're typically looking at a collection of address ranges (low/high pairs, subnets). An R-Tree is an exceptionally fast way to find them. If you weren't interested in subnets or ranges then, of course this approach would be pointless... but don't knock ot until you try it. – Michael - sqlbot Oct 23 '13 at 00:02