Storing IP address in MySQL database (IPv4 AND IPv6)

php mysql ip ipv6 ipv4
Andy picture Andy · May 14, 2012 · Viewed 18k times · Source

Ok, now I'm aware that similar questions have probably been asked a million times but I'm a real novice at this and I'd really appreciate your help here.

Basically, I want to store the visitors IP address in a MySQL for later retrieval and verification. I firstly need to know what type of field I need to use to store the IP address in. I would also like to make the system compatible with IPv6 addresses if possible.

Thanks in advance

Answer

aleroot picture aleroot · May 14, 2012

To store an IPv4 you can use an INT UNSIGNED, while for a IPv6 you need a decimal(39,0), to store an ip in the table you can use the function INET_ATON:

INSERT INTO table (ipcol) VALUES (INET_ATON('192.168.0.10'));

and retrieve it back with the function INET_NTOA:

SELECT INET_NTOA(ipcol) AS ip FROM table;

This answered existing before MySQL IPv6 support; users should be made aware that MySQL now natively supports IPv6: https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html

Related questions

Store IPv6 in database

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 …

Read More
IP address storing in MySQL database using PHP

what is the right field type for IP address in mysql? and what is the right way of storing it using PHP?

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More