Search code examples
mysqlbinaryipv6ipv4

IP Address Binary to Human Readable

I have IP addresses stored in a DB using a command like this:

cast(INET6_ATON(trim(:ipbinary)) as binary(16)))

my column is:

varbinary(16)

I've tried using the mysql function INET6_NTOA to convert it back to the IPv4 format but haven't had any luck with that.

The IP I need is:

66.249.64.90

The DB value is:

42f9405a000000000000000000000000

The INET6_NTOA gives me:

42f9:405a::

and INET6_NTOA(UNHEX( gives me a NULL response. I'm using PHP as my scripting language so if there is a function there as well I also could use that.

Here's my full query:

SELECT delete_ip, INET6_NTOA(ip_binary), ip_binary FROM `stats`

and here's the response:

phpmyadmin output

Thanks.

(I can't just use delete_ip because as the name implies the column is going to be dropped.)

Solution

  • It's not converting back to IPv4 human readable format, it's converting to IPv6 because the argument to INET6_NTOA (the binary value) is 16 bytes.

    The function is seeing it as a representation of an IPv6 address, not an IPv4 address, which is only four bytes.

    I think the issue can be traced back to the first line of SQL in the question, the cast to BINARY(16). Which is returning a fixed length of 16 bytes. Starting with the four bytes returned for the IPv4 address, and then padded on the right with zeros up to a length of 16 bytes.

    What happens if we remove the cast to fixed length, and allow the result of INET6_ATON function to be just four bytes?

    What happens when the value stored in the database is just four bytes?

    What if we correct the contents of the stats table, to change that 16 byte binary value (representation of an IPv6 address) to a four byte binary representation of an IPv4 adddress

    UPDATE `stats` 
   SET ip_binary = INET6_ATON('66.249.64.90')
 WHERE ip_binary = CAST(INET6_ATON('66.249.64.90') AS BINARY(16))

    --or-- 

    UPDATE `stats` 
   SET ip_binary = X'42f9405a' + 0 
 WHERE ip_binary = X'42f9405a000000000000000000000000' + 0

    Followup

    Question says... "storing IP addresses in database [column] using [expression] like this: 

     cast(INET6_ATON(trim(:ipbinary)) as binary(16)))

    We don't need to use CAST. And we don't need to use CONVERT, HEX/UNHEX or SUBSTR. Convert IPv4 and IPv6 addresses with the same expression:

      INSERT ... ip_binary ... VALUES ( ... , INET6_ATON( :ip_string ) , ...

    And convert them back to strings like this: 

     SELECT ... , INET6_NTOA( ip_binary ) AS ip_string , ...

    The rigmarole with CAST, CONVERT, SUBSTR, HEX/UNHEX is confusing, and is causing things not to work.

    To correct values that are already stored in the database, we need a way to distinguish which of the 16-byte binary representations are actually IPv4 addresses, that should have been stored as 4 bytes.

    If ip_delete contains the string representations, we can re-convert to the binary representation.

     UPDATE `stats`
    SET ip_binary = INET6_ATON( ip_delete )

    Demonstration

    CREATE TABLE `addr` (ip_string VARCHAR(45), ip_binary VARBINARY(16)) ;

INSERT INTO `addr` VALUES ( '66.249.64.90'         , INET6_ATON( '66.249.64.90'         ));
INSERT INTO `addr` VALUES ( '127.0.0.1'            , INET6_ATON( '127.0.0.1'            ));
INSERT INTO `addr` VALUES ( '192.168.1.1'          , INET6_ATON( '192.168.0.1'          ));
INSERT INTO `addr` VALUES ( '2001:4860:4860::8888' , INET6_ATON( '2001:4860:4860::8888' ));

SELECT ip_string, HEX(ip_binary), INET6_NTOA(ip_binary) FROM `addr` ;

ip_string             HEX(ip_binary)                    INET6_NTOA(ip_binary)
--------------------  --------------------------------  -----------------------
66.249.64.90          42F9405A                          66.249.64.90
127.0.0.1             7F000001                          127.0.0.1
192.168.1.1           C0A80001                          192.168.0.1
2001:4860:4860::8888  20014860486000000000000000008888  2001:4860:4860::8888