Searching for an IP address stored in binary via php mysql

Question

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.

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? — user149341
– user149341, Commented Apr 20, 2014 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
– Devon Bessemer, Commented Apr 20, 2014 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. — user149341
– user149341, Commented Apr 20, 2014 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
– Devon Bessemer, Commented Apr 20, 2014 at 23:50

John Garrard · Accepted Answer · 2014-04-20 23:42:29Z

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.

answered Apr 20, 2014 at 23:42

John Garrard

1674 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Devon Bessemer Over a year ago

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.

Community · Accepted Answer · 2017-05-23 10:29:14Z

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.

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Oct 24, 2014 at 20:30

James

4,7611 gold badge24 silver badges35 bronze badges

Collectives™ on Stack Overflow

Searching for an IP address stored in binary via php mysql

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related