Storing IP address in MySQL database (IPv4 AND IPv6)

Question

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

What are your main use cases for the addresses? Are you going to query them? Split them up and query according to some subnetting rules? Or are they stored just for display? — Emil Vikström, May 14 '12 at 19:37
Try this: http://stackoverflow.com/questions/2049681/store-ipv6-in-database — Mark, May 14 '12 at 19:39
Well its for a confirmation link. I want to make sure the confirmation link was visited from the same IP address that it was generated on. So I'm going to query them] — Andy, May 14 '12 at 19:39

score 12 · Accepted Answer · edited Jul 09 '17 at 20:17

12

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

edited Jul 09 '17 at 20:17

John

1
13
98
177

answered May 14 '12 at 19:42

aleroot

71,077
30
176
213

Thanks but I thought I just read that `INET_ATON()` didn't support IPv6. And even if it did, how do I get the IPv6 address using PHP? – Andy May 14 '12 at 19:46
Fair enough, thanks. Now, how do I go about finding the users IP address in PHP? – Andy May 14 '12 at 19:55
Take a look here : http://roshanbh.com.np/2007/12/getting-real-ip-address-in-php.html ;-) – aleroot May 14 '12 at 20:00
Yeah well, that wad a given for me. It can't really get an IPv6 address it one doesn't exist... :-) – Andy May 14 '12 at 20:06
This won't work with IPv6 because an unsigned int is not big enough. You need a varbinary(16), as inhan's answer says. – Synchro Jan 10 '19 at 17:02

score 6 · Answer 2 · answered May 14 '12 at 19:40

6

I use VARBINARY(16) for the data type and use the MySQL function INET_ATON() to insert the IP number (which I later read using the reverse function, INET_NTOA().

answered May 14 '12 at 19:40

inhan

7,394
2
24
35

1

So, will VARBINARY(16) support both IPv4 and IPv6? And how do I find the visitors' IP address using PHP? – Andy May 14 '12 at 19:48
1

You have to use INET6_NTOA and INTE6_ATON, but this is not available in MySQL until version 5.6.3 – Goran Jurić Aug 26 '13 at 16:26

score 1 · Answer 3 · answered May 14 '12 at 19:38

There is no integral type in MySQL big enough to store an IPv6 address. The most compact way to store it is as something like BINARY(16). If you just need to store and retrieve addresses and you don't need to perform logical operations on them (e.g. netmask operations to query for which IP addresses come under a covering prefix) then that will be enough. If you need to do logical or bit operations, you will need to be fancier: you will need to store IPv6 addresses in two separate 64-bit integer columns.

No, I don't need to do anything more fancy (just retrieving/comparing them in a query). So all I need to know now is how I can obtain the user's IP address using PHP? — Andy, May 14 '12 at 19:52

score 0 · Answer 4 · edited Dec 24 '19 at 12:16

You are right but in MySQL 5.7 it is possible to store IPV6 in decimal format. also you can convert to ipv6 to decimal and vice versa:

MySQL 5.0 IPV4

select INET_ATON('192.168.0.1') returns int value: 3232235521

MySQL 5.7 IPV6

select cast(conv(substr(HEX(INET6_ATON('0:0:0:0:0:FFFF:C0A8:0001')), 1, 16), 16, 10) as decimal(65))*18446744073709551616 + 
       cast(conv(substr(HEX(INET6_ATON('0:0:0:0:0:FFFF:C0A8:0001')), 17, 16), 16, 10) as decimal(65))

returns bigint value: 281473913978881

score -1 · Answer 5 · edited Jul 16 '12 at 12:28

-1

int(10) unsigned

http://lists.mysql.com/cluster/2781

edited Jul 16 '12 at 12:28

Wouter Dorgelo

11,770
11
62
80

answered May 14 '12 at 19:37

mittmemo

2,062
3
20
27

Storing IP address in MySQL database (IPv4 AND IPv6)

5 Answers5

Linked