Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Store IPv6 in database

Tags:

php

mysql

ip

ipv6

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

like image 671
Poru Avatar asked Jan 12 '10 14:01

Poru


People also ask

Can I store IP address in database?

We can store an IP address with the help of INT unsigned. While using INSERT, include INET_ATON() and with SELECT, include INET_NTOA(). IP address is in dotted format.

Will IPv6 ever be exhausted?

Will IPv6 addresses run out eventually? In practical terms, no. There are 2^128 or 340 trillion, trillion, trillion IPv6 addresses, which is more than 100 times the number of atoms on the surface of the Earth. This will be more than sufficient to support trillions of Internet devices for the forseeable future.

How many bits do we need to store an IPv6 address?

IPv6 uses 128-bit (2128) addresses, allowing 3.4 x 1038 unique IP addresses. This is equal to 340 trillion trillion trillion IP addresses. IPv6 is written in hexadecimal notation, separated into 8 groups of 16 bits by the colons, thus (8 x 16 = 128) bits in total.

How do I add IPv6 to my server?

In Settings go to Network & Internet and click the Properties button for the interface you wish to configure. Click the Edit button under IP settings, change the configuration type to Manual, enable IPv6, and populate your settings.


2 Answers

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
?> 
like image 93
quantme Avatar answered Sep 20 '22 08:09

quantme


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.

like image 26
Sjoerd Avatar answered Sep 20 '22 08:09

Sjoerd