Storing IPv6 Addresses in MySQL

No one has posted a full working answer (and lots of examples use the Windows ::1 which can be very misleading for live (or "production") environments) any where (at least that I can find) so here is:

  1. The format to store with.
  2. Example INSERT query using a reasonably complex IPv6 IP address.
  3. Example SELECT query that you will be able to echo the IPv6 IP address back to the client.
  4. Troubleshooting to ensure you haven't missed any legacy code.

I changed all the column names to ipv6 to reflect that they properly support IPv6 (and that allows you to keep the old column ip intact). It is possible to store the ip column in the ipv6 column and then just DROP the ip column once you're certain the conversion has worked; when I actually have time I'll add that to this post.

IPv6 Data Type

As has been mentioned VARBINARY 16 is the desirable way to go until AMD blesses us with 128 bit CPUs and the databases are updated to support 128 bit integers (did I say that correctly?). IPv6 is 128 bit, not 64 bit.

CREATE TABLE `example`
(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`ipv6` VARBINARY(16) NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_unicode_520_ci'
ENGINE=InnoDB;

IPv6 INSERT Query

We obviously need to store an IPv6 IP address before we can SELECT it; here is the PHP / SQL code:

$ipv6 = mysqli_real_escape_string($db,'FE80:0000:0000:0000:0202:B3FF:FE1E:8329');
$query1 = "INSERT INTO example (ipv6) VALUES (INET6_ATON('$ipv6'));";

IPv6 SELECT Query

The PHP / SQL code:

$ipv6 = mysqli_real_escape_string($db,'FE80:0000:0000:0000:0202:B3FF:FE1E:8329');
$query2 = "SELECT INET6_NTOA(ipv6) AS ipv6 FROM example WHERE ipv6=INET6_ATON('$ipv6');";

This will return fe80::202:b3ff:fe1e:8329; no, not the full IPv6 (which is FE80:0000:0000:0000:0202:B3FF:FE1E:8329), it's a condensed / shorthand version. There is code to make it the formal full-length version but this is to save myself and others time because this Q/A is the one that keeps coming up.

Important: just because some IPv6 addresses look like they'd fit in to bigint does not imply two minutes later someone with a larger IPv6 address won't stop by and wreak havoc.

Hopefully this will save some folks from the insanity of opening another two dozen tabs. When I have time in the future I'll add the extra PHP code that extends the condensed IPv6 to the full formal format.

Troubleshooting

If for some reason storing and/or retrieving IPv6 addresses is not working for you then grab yourself a copy of Advanced Find and Replace (works faster in Wine than Linux's native grep); use this predominantly for finding, not replacing. Ensure that your code is consistent everywhere in your software.

  • All $ip variables must be converted to $ipv6 so you know you've got that bit covered.
  • Do not forget to remove the ending ) for the next four steps:
  • Search for all instances of PHP inet_pton( functions and remove them.
  • Search for all instances of PHP inet_ntop( functions and remove them.
  • Search for all instances of SQL INET_ATON( functions and remove them.
  • Search for all instances of SQL INET_NTOA( functions and remove them.
  • Search for all instances of $ipv6 and ensure that all IP-IN-TO-SQL instances use INET6_ATON('$ipv6') and that all instances where IP-FROM-SQL use INET6_NTOA(ipv6) AS ipv6.
  • Search for all instances of $row1['ip'] and replace them with $row1['ipv6'].
  • Ensure that all instances of $ipv6 = use the following code (with your database object reference changed): $ipv6 = (isset($_SERVER['REMOTE_ADDR']) && strlen($_SERVER['REMOTE_ADDR']) > 0) ? mysqli_real_escape_string($db,$_SERVER['REMOTE_ADDR']) : mysqli_real_escape_string($db,getenv('REMOTE_ADDR'));.
  • Ensure that your tests use freshly tested IP addresses instead of potentially botched versions if you are aware that there was something wrong before you started debugging.

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".

Tags:

Mysql

Sql

Recent Posts