Store IPv6 in database

Question

What's the best practise to store IP's with PHP in MySQL database? There's a function called ip2long - but this is just for IPv4. But what about IPv6?

I know a php function that is for IPv6 IP's, but it doesn't work on Windows with PHP < Version 5.3

Check out this question: http://stackoverflow.com/questions/1120371/how-to-convert-ipv6-from-binary-for-storage-in-mysql — Pekka, Jan 12 '10 at 14:39
For what it's worth a decade on, MariaDB 10.5 now supports an [`INET6` data type](https://mariadb.com/kb/en/inet6/). — O. Jones, Feb 23 '21 at 15:19

score 12 · Answer 1 · edited May 23 '17 at 12:32

knittl was closer, instead of binary(16) use varbinary(16) as user196009 answered in a related question. It works for me. How?

Storing IP:

<?php
  $query = "insert into stats(vis_ip, id_stat) values('" . inet_pton('66.102.7.104') . "', '1')"; // google's IP address
  // using a PDO wrapper. http://www.phpclasses.org/package/5206-PHP-Execute-database-queries-from-parameters-using-PDO.html
  include_once 'db.php';
  $c = new DB();
  $visit = $c->getResults($query); // stored as binary
?>

Retrieving IP:

<?php
  $query = "SELECT `vis_ip` FROM `stats` WHERE `id_stat`=1";
  // PDO wrapper
  include_once 'db.php';
  $c = new DB();
  $stats = $c->getRow($query);
  echo inet_ntop($stats->vis_ip); // outputs 66.102.7.104
?>

It should work with IPv6 addresses (I have an IPv4 connection). I'm not an expert so I don't know yet if varbinary length is correct, but how I said, it works for me.

In order to check if 'IPv6 Support' is enabled in your PHP version/host:

<?php
  phpinfo(INFO_GENERAL); // http://php.net/manual/es/function.phpinfo.php
?>

Instead of using PHP, you can now use MySQL's [INET6_ATON](http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton) to convert the string to its numeric representation for MySQL >= 5.6. — Mike, Jun 21 '14 at 17:20

score 9 · Accepted Answer · answered Jan 14 '10 at 08:00

9

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.

answered Jan 14 '10 at 08:00

Sjoerd

74,049
16
131
175

4

[45 characters](http://stackoverflow.com/questions/166132/maximum-length-of-the-textual-representation-of-an-ipv6-address). – Sjoerd Oct 10 '12 at 07:16

score 9 · Answer 3 · answered May 02 '10 at 20:38

9

there's the php function inet_pton, it will turn an ip address string into its binary representation (for both ipv4 and ipv6). you can then store it as binary(16) in your mysql database.

to get a human readable address again, use inet_ntop

answered May 02 '10 at 20:38

knittl

246,190
53
318
364

score 2 · Answer 4 · answered Apr 24 '16 at 13:29

2

Note that MySQL (5.6) now support IPv6 addresses, see INET6_ATON().

answered Apr 24 '16 at 13:29

Sebastien Horin

10,803
4
52
54

score 0 · Answer 5 · answered Jan 12 '10 at 14:41

0

On second comment at ip2long function manual you have a function called ip2long6 for IPv6 (and there are more below).

answered Jan 12 '10 at 14:41

hsz

148,279
62
259
315

be aware you need gmp-lib for that function – Thom Wiggers Jan 12 '10 at 16:55

score 0 · Answer 6 · answered Jan 12 '10 at 16:53

0

You could just store it as a string in a CHAR I suppose

answered Jan 12 '10 at 16:53

Thom Wiggers

6,938
1
39
65

Store IPv6 in database

6 Answers6

Linked