How to Convert Ipv6 from Binary for Storage in MySQL

How to convert IPv6 from binary for storage in MySQL

Here are the functions I now use to convert IP addresses from and to DECIMAL(39,0) format. They are named inet_ptod and inet_dtop for "presentation-to-decimal" and "decimal-to-presentation". It needs IPv6 and bcmath support in PHP.

/**
 * Convert an IP address from presentation to decimal(39,0) format suitable for storage in MySQL
 *
 * @param string $ip_address An IP address in IPv4, IPv6 or decimal notation
 * @return string The IP address in decimal notation
 */
function inet_ptod($ip_address)
{
    // IPv4 address
    if (strpos($ip_address, ':') === false && strpos($ip_address, '.') !== false) {
        $ip_address = '::' . $ip_address;
    }

    // IPv6 address
    if (strpos($ip_address, ':') !== false) {
        $network = inet_pton($ip_address);
        $parts = unpack('N*', $network);

        foreach ($parts as &$part) {
            if ($part < 0) {
                $part = bcadd((string) $part, '4294967296');
            }

            if (!is_string($part)) {
                $part = (string) $part;
            }
        }

        $decimal = $parts[4];
        $decimal = bcadd($decimal, bcmul($parts[3], '4294967296'));
        $decimal = bcadd($decimal, bcmul($parts[2], '18446744073709551616'));
        $decimal = bcadd($decimal, bcmul($parts[1], '79228162514264337593543950336'));

        return $decimal;
    }

    // Decimal address
    return $ip_address;
}

/**
 * Convert an IP address from decimal format to presentation format
 *
 * @param string $decimal An IP address in IPv4, IPv6 or decimal notation
 * @return string The IP address in presentation format
 */
function inet_dtop($decimal)
{
    // IPv4 or IPv6 format
    if (strpos($decimal, ':') !== false || strpos($decimal, '.') !== false) {
        return $decimal;
    }

    // Decimal format
    $parts = array();
    $parts[1] = bcdiv($decimal, '79228162514264337593543950336', 0);
    $decimal = bcsub($decimal, bcmul($parts[1], '79228162514264337593543950336'));
    $parts[2] = bcdiv($decimal, '18446744073709551616', 0);
    $decimal = bcsub($decimal, bcmul($parts[2], '18446744073709551616'));
    $parts[3] = bcdiv($decimal, '4294967296', 0);
    $decimal = bcsub($decimal, bcmul($parts[3], '4294967296'));
    $parts[4] = $decimal;

    foreach ($parts as &$part) {
        if (bccomp($part, '2147483647') == 1) {
            $part = bcsub($part, '4294967296');
        }

        $part = (int) $part;
    }

    $network = pack('N4', $parts[1], $parts[2], $parts[3], $parts[4]);
    $ip_address = inet_ntop($network);

    // Turn IPv6 to IPv4 if it's IPv4
    if (preg_match('/^::\d+.\d+.\d+.\d+$/', $ip_address)) {
        return substr($ip_address, 2);
    }

    return $ip_address;
}

Storing IPv6 Addresses in MySQL

How about:

BINARY(16)

That should be effective enough.

Currently there is no function to convert textual IPv6 addresses from/to binary in the MySQL server, as noted in that bug report. You either need to do it in your application or possibly make a UDF (User-Defined Function) in the MySQL server to do that.

UPDATE:

MySQL 5.6.3 has support for IPv6 addresses, see the following: "INET6_ATON(expr)".

The data type is VARBINARY(16) instead of BINARY(16) as I suggested earlier. 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.

An implementation for older versions of MySQL and MariaDB, see the following: "EXTENDING MYSQL 5 WITH IPV6 FUNCTIONS".

How to store IPv6-compatible address in a relational database

I'm not sure which is the right answer for MySQL given that it doesn't yet support IPv6 address formats natively (although whilst "WL#798: MySQL IPv6 support" suggests that it was going to be in MySQL v6.0, current documentation doesn't back that up).

However of those you've proposed I'd suggest going for 2 * BIGINT, but make sure they're UNSIGNED. There's a sort of a natural split at the /64 address boundary in IPv6 (since a /64 is the smallest netblock size) which would align nicely with that.

IP Address Binary to Human Readable

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

Best way to store an IPv6 in UNKNOWN DB?

Like your research shows there are benefits and problems with storing IP addresses as strings in canonical form, binary strings or as integers. Maybe there is a middle ground to store IP addresses in a database.

How about storing them as strings, but expanded to the full maximum length. That way you can still compare them (==, >, <, etc) but they are also still readable and you don't need special input and output encoding of special characters.

An example of how you could do this:

function expand_ip_address($addr_str) {
  /* First convert to binary, which also does syntax checking */
  $addr_bin = @inet_pton($addr_str);
  if ($addr_bin === FALSE) {
    return FALSE;
  }

  /* Then differentiate between IPv4 and IPv6 */
  if (strlen($addr_bin) == 4) {
    /* IPv4: print each byte as 3 digits and add dots between them */
    return implode('.', array_map(
      create_function('$byte', 'return sprintf("%03d", ord($byte));'),
      str_split($addr_bin)
    ));
  } else {
    /* IPv6: print as hex and add colons between each group of 4 hex digits */
    return implode(':', str_split(bin2hex($addr_bin), 4));
  }
}

And some examples:

/* Test IPv4 */
var_dump(expand_ip_address('192.0.2.55'));

/* Test IPv6 */
var_dump(expand_ip_address('2001:db8::abc'));

/* Test invalid */
var_dump(expand_ip_address('192:0:2:55'));

Which produce:

string(15) "192.000.002.055"
string(39) "2001:0db8:0000:0000:0000:0000:0000:0abc"
bool(false)

Size for storing IPv4, IPv6 addresses as a string

Assuming textual representation in a string :

• 15 characters for IPv4 (xxx.xxx.xxx.xxx format, 12+3
  separators)
• 45 characters for IPv6

Those are the maximum length of the string.

Alternatives to storing as string:

• IPv4 is 32-bits, so a MySQL data type that can hold 4 bytes will do, using INT UNSIGNED is common along with INET_ATON and INET_NTOA to handle the conversion from address to number, and from number to address

SELECT INET_ATON('209.207.224.40');
        -> 3520061480

SELECT INET_NTOA(3520061480);
        -> '209.207.224.40'

• For IPv6, unfortunately MySQL does not have a data type that is 16 bytes, however one can put the IPv6 into a canonical form, then separate them into 2 BIGINT (8 bytes), this however will use two fields.

Store IPv6 in database

The dotted-decimal IPv4 address can be converted to an integer, with a maximum size of 32 bits. IPv6 addresses are 128 bits. Since 128 bits do not fit in a PHP int, this will be a pain to work with in PHP.

If you just want to connect and use IPv6 addresses, save yourself the trouble and save them as text. If you want to apply netmasks and calculate subnets, then you need to convert them.

Convert LONGTEXT records storing IPv4/6 addresses to INT(10) UNSIGNED and BINARY(16)

I solved this myself after creating the two columns ipv4 and ipv6 described above and the solution is more efficient than Ulrich's approach.

Query:

UPDATE user_activity
SET ipv4 = CASE
WHEN event_meta LIKE "%.%" THEN INET_ATON(event_meta)
ELSE NULL
END,
ipv6 = CASE
WHEN event_meta LIKE "%:%" THEN INET6_ATON(event_meta)
ELSE NULL
END;

This took 1.93s, or about 35,000 rows per second. I used the LIKE operator with wildcards . and :. Regex matching might make this fast for other readers with many more rows than my 69,000 at present, but this isn't always supported for some MySQL versions.

---

Related Topics