3

IP addresses are being stored using inet_pton() from a PHP script. They are being stored in a varbinary(16) field in the database. However I now need to query and return the IP addresses stored with only MySQL, however this seems to be a bit of a mission!

I've tried using MySQL's INET_NTOA() but it just returns 0.0.0.0 for some reason. Anyone have any ideas?

Ashley
  • 1,459
  • 2
  • 12
  • 25
  • possible duplicate of [How to convert IPv6 from binary for storage in MySQL](http://stackoverflow.com/questions/1120371/how-to-convert-ipv6-from-binary-for-storage-in-mysql) – Alma Do Jun 17 '14 at 14:26
  • @AlmaDo I've read that one - it didn't fix my issue. As per my question, I tried INET_NTOA() and it didn't work. – Ashley Jun 17 '14 at 14:28
  • It won't because that is for IPv4. You need IPv6 conversion. That is what linked question is about – Alma Do Jun 17 '14 at 14:29
  • What MySQL version do you have? As of 5.6.3 there is [INET6_NTOA](http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-ntoa) – VMai Jun 17 '14 at 14:35
  • I'm on 5.5 at the moment, so those functions are not available. Guessing there's no way without installing those functions linked above? – Ashley Jun 17 '14 at 14:43

1 Answers1

6

Just came across this problem myself. As the comments suggest, the correct solution is to use INET6_NTOA if you're on MySQL 5.6.3+. It handles IPv4 and IPv6 addresses, but the format for IPv4 is different to that produced by INET_ATON (which only handles IPv4). The latter uses the same format as the PHP function ip2long. Here's a reference table of equivalent functions:

PHP        | MySQL
-----------|-------------
ip2long    | INET_ATON
long2ip    | INET_NTOA
inet_pton  | INET6_ATON
inet_ntop  | INET6_NTOA

Having said all that, it does appear to be possible to convert INET6_* addresses using the INET_* functions. As noted in this question you can convert from an IP string to binary format using this:

UNHEX(HEX(CAST(INET_ATON(ipaddress) AS UNSIGNED)))
-- same as
INET6_ATON(ipaddress)

With a bit of testing I figured out how to do the reverse:

INET_NTOA(CONV(HEX(ipaddress), 16, 10))
-- same as
INET6_NTOA(ipaddress)

Hopefully this is useful to someone.

DisgruntledGoat
  • 70,219
  • 68
  • 205
  • 290