1

I am storing IP addresses in a table in their packed binary form (4 bytes for IPv4 and 16 bytes for IPv6). My question is, is there a way to search for IP addresses via SQL?

I used php (inet_pton) to convert them to binary. I wasn't aware of any MySQL function available for this. My dilemma comes from when searching for part of an IP address. Say if the IP address is 192.168.1.101 and the user searches for 101.

Since inet_pton wouldn't work for a string/integer like 101, is there an alternative for searching for packed ip addresses? I'm open to suggestions.

Devon Bessemer
  • 34,461
  • 9
  • 69
  • 95
  • Searching for an IP address by its suffix doesn't make sense to me — there is no relationship between `192.168.1.101` and `1.2.3.101`, for instance. What are you trying to do? –  Apr 20 '14 at 23:34
  • @duskwuff It is a table of servers with their IP addresses. A user may not wish to enter the entire IP address and just narrow the results by the a part of the IP address. For example 192. or .101 or whatever they choose to do. – Devon Bessemer Apr 20 '14 at 23:38
  • That's unlikely to ever be a useful way to search, though. IP addresses are organized by prefixes; your search should work on this basis. –  Apr 20 '14 at 23:39
  • @duskwuff, yes, and subnets... Think about a IPv6 address. If they want to narrow all the IP addresses from a /64. It would definitely be useful. – Devon Bessemer Apr 20 '14 at 23:50

2 Answers2

1

If you are allowing your users to enter any valid substring for searching there is the INET6_NTOA function which will convert a packed ipv4 or v 6address into a binary string which you could then do string comparisons on. This exists in version 5.7 of mysql.

John Garrard
  • 167
  • 4
  • This looks to be useful for my needs. I'm contemplating whether I should be storing the IP addresses in a textual representation rather than binary after going through this. The ~12-20 bytes I save per record are pretty miniscule compared to the cost of processing when doing lookups. – Devon Bessemer Apr 20 '14 at 23:45
0

I know that this question has been idle for some time now, but just in case someone get here trying to solve a similar issue, please have a look at my answer here: php search on a partial IP address stored in mysql as unsigned int . Note that the binary mask approach is applicable to IPv6 addresses, and is much more efficient (both in terms of memory requirement and lookup time) than varchar columns.

Community
  • 1
  • 1
James
  • 4,211
  • 1
  • 18
  • 34