MYSQL - SELECT IP v4/v6, inet_pton & bin2hex

Question

I'm having bit of a trouble selecting correct values from my mysql sql server.

The ip can be ipv6 and v4.

Table: User{

...

ip binary(16)

}

$ip = '192.168.10.115';
$ip = bin2hex(inet_pton($ip)); // Returns c0a80a73
$result = $this->db->select("SELECT * FROM User WHERE HEX(ip) = $ip");
// $result empty because in db its stored as:
// HEX(ip) = C0A80A73000000000000000000000000

How can I get a viable match to the * 00000 * ?

If input was a ipv6 match this would be ok, but ip v4 not.

If you're storing the variable as a hex number, change the column to be numeric... `HEX()` will work on a numeric input. — nickb, Feb 10 '12 at 23:04

Dmitry Savy · Answer 1 · 2017-03-14T19:06:53.237

1

UPDATE:

The MySQL 5.6.3 and higher have support for IPv6 addresses - see the following: "INET6_ATON(expr)"

The data type is VARBINARY(16) instead of BINARY(16) as was suggested by earlier comments here. The only reason for this is that the MySQL functions work for both IPv6 and IPv4 addresses. BINARY(16) is fine for storing only IPv6 addresses and saves one byte. VARBINARY(16) should be used when handling both IPv6 and IPv4 addresses.

edited Mar 14 '17 at 19:06

answered Feb 10 '12 at 23:05

Dmitry Savy

1,067
8
22

unfortunately not for IPv6 – Jon Gilbert Apr 03 '13 at 07:34
1

If you want to deal with both ipv4 and ipv6 in the same field, and if you are using Mysql 5.6 or higher, you can use varbinary(16) and the functions inet6_aton and inet6_ntoa. This is a better example of why you should use MySQL functions – Dmitry Savy Jul 01 '13 at 18:28

score 1 · Accepted Answer · answered Feb 10 '12 at 23:45

Can you use VARBINARY instead of just BINARY?

From the MySQL Manual on Binary/Varbinary:

If the value retrieved must be the same as the value specified for storage with no padding, it might be preferable to use VARBINARY or one of the BLOB data types instead.

MYSQL - SELECT IP v4/v6, inet_pton & bin2hex

2 Answers2

Linked