36

I want to store IP addresses in my database, but I also need to use them throughout my application. I read about using INET_ATON() and INET_NTOA() in my MySQL queries to get a 32-bit unsigned integer out of an IP address, which is exactly what I want as it will make searching through the database faster than using char(15).

The thing is, I can't find a function that does the same sort of thing in PHP. The only thing I came across is:

http://php.net/manual/en/function.ip2long.php

So I tested it:

$ip = $_SERVER['REMOTE_ADDR'];
echo ip2long($ip);

And it outputs nothing. In the example they gave it seems to work, but then again I'm not exactly sure if ip2long() does the same thing as INET_ATON().

Does someone know a PHP function that will do this? Or even a completely new solution to storing an IP address in a database?

Thanks.

2
  • Forgot to add, I'm working on localhost, so maybe that's why ip2long() isn't returning anything?
    – blerh
    Commented May 2, 2010 at 17:46
  • The IPv4 of localhost is still 127.0.0.1, it should work unless you're using IPv6 - check my answer.
    – Alix Axel
    Commented May 2, 2010 at 18:40

6 Answers 6

39

There is an important distinction between ip2long, long2ip and the MySQL functions.

PHP's ip2long and long2ip deal with signed integers.

See http://php.net/manual/en/function.ip2long.php

"Because PHP's integer type is signed, and many IP addresses will result in negative integers on 32-bit architectures, you need to use the '%u' formatter of sprintf() or printf() to get the string representation of the unsigned IP address."

MySQL's INET_ATON() and INET_NTOA() deal with unsigned integers

See http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton

"To store values generated by INET_ATON(), use an INT UNSIGNED column rather than INT, which is signed. If you use a signed column, values corresponding to IP addresses for which the first octet is greater than 127 cannot be stored correctly."

Here are some functions you can use to work between the two.

If you inserted into the MySQL database, an IP using INET_ATON(), you can convert it back in PHP using the following:

long2ip(sprintf("%d", $ip_address));

And you can convert it to save it in the database from PHP using this:

sprintf("%u", ip2long($ip_address));

(Also important, don't type-cast the $ip_address to int as this might cause problems by wrapping the number if it's bigger than MAX_INT. If you must cast it, cast it to a long or float)

2
  • Works like a charm. Thanks a lot for the tip. I was never aware of that. Commented Sep 20, 2013 at 10:24
  • 3
    ip2long on a 64-bit system will give an unsigned result. "%u" will still do the right thing in that case, though.
    – Brilliand
    Commented Jan 28, 2014 at 17:25
38

The ip2long() and long2ip() functions should work just fine.

Note : you should use those for IPv4 addresses -- make sure that, in your case, $_SERVER['REMOTE_ADDR'] actually contains a valid IPv4 address (and not some IPv6-stuff).


Trying on a google IP address :

var_dump(ip2long('209.85.227.147'));
var_dump(long2ip(3512066963));

I get the following output :

int(3512066963)
string(14) "209.85.227.147" 
19

You should not have to deal with that inside PHP, that's what MySQL native funcions are for. See this example:

create table iptable (
    ip int(32) unsigned not null,
    comment varchar(32) not null
);

insert into iptable (ip, comment) values (inet_aton('10.0.0.3'), 'This is 10.0.0.3');

select * from iptable;

+-----------+------------------+
| ip        | comment          |
+-----------+------------------+
| 167772163 | This is 10.0.0.3 |
+-----------+------------------+

select inet_ntoa(ip) as ip, comment from iptable;

+----------+------------------+
| ip       | comment          |
+----------+------------------+
| 10.0.0.3 | This is 10.0.0.3 |
+----------+------------------+

If you want to deal with both ipv4 and ipv6 in the same field, and you are using Mysql 5.6 or higher, you can use varbinary(16) and the functions inet6_aton and inet6_ntoa. This is a better example of why you should use MySQL functions and not deal with binary data inside PHP:

create table iptable2 (
    ip varbinary(16) not null,
    comment varchar(32) not null
);

insert into iptable2 (ip, comment) values
    (inet6_aton('192.168.1.254'), 'This is router 192.168.1.254'),
    (inet6_aton('::1'), 'This is ipv6 localhost ::1'),
    (inet6_aton('FE80:0000:0000:0000:0202:B3FF:FE1E:8329'), 'This is some large ipv6 example')
;

select * from iptable2;
+------------------+---------------------------------+
| ip               | comment                         |
+------------------+---------------------------------+
| +¿?¦             | This is router 192.168.1.254    |
|                ? | This is ipv6 localhost ::1      |
| ¦Ç      ??¦ ¦?â) | This is some large ipv6 example |
+------------------+---------------------------------+

select inet6_ntoa(ip) as ip, comment from iptable2;
+--------------------------+---------------------------------+
| ip                       | comment                         |
+--------------------------+---------------------------------+
| 192.168.1.254            | This is router 192.168.1.254    |
| ::1                      | This is ipv6 localhost ::1      |
| fe80::202:b3ff:fe1e:8329 | This is some large ipv6 example |
+--------------------------+---------------------------------+

You can see that by doing this, you can actually avoid having to evaluate ipv6 addresses in different formats, since MySQL converts them to binary and back to their simpliest expression.

I know this question has more than 2 years already, but I want to let this information be useful for others that come across.

HTH

Francisco Zarabozo

2
  • 2
    What if you have to insert them with a prepared statement? You can't bind a parameter to mysql functions, INET_ATON(?), so you have to use php
    – the_nuts
    Commented Jul 7, 2016 at 10:41
  • 2
    @the_nuts What are you talking about? Yes, you can. Try it for yourself. Commented Jul 7, 2016 at 15:04
14

For IPv4 and IPv6 support use inet_pton() and inet_ntop(), these are availiable since PHP 5.1+ and mimic exactly the equivalent MySQL functions.

Otherwise just use ip2long() and long2ip().

2
  • i have not been able to mimic this.
    – Artistan
    Commented Feb 26, 2015 at 13:31
  • inet_ntop( $ip ) was exactly what I needed to convert a varbinary value (from MYSQL) to a readable IP address within PHP. Thx.
    – Avatar
    Commented Sep 23, 2020 at 9:42
3

Here PHP alternative functions (simple copy/paste in your program) -

function inet_aton($ip)
{
    $ip = trim($ip);
    if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)) return 0;
    return sprintf("%u", ip2long($ip));  
}


function inet_ntoa($num)
{
    $num = trim($num);
    if ($num == "0") return "0.0.0.0";
    return long2ip(-(4294967295 - ($num - 1))); 
}
1

ip2long is equivalent to inet_aton().

ip2long only works with IPv4. I suspect your system is using IPv6 for loopback. Try to print REMOTE_ADDR.

2
  • 1
    Ah I can see why it isn't working now, $_SERVER['REMOTE_ADDR'] returns: ::1. Probably because I'm on localhost. Is there a way to prevent this? Or must I do a check to see if it's returning ::1?
    – blerh
    Commented May 2, 2010 at 17:52
  • 1
    ::1 is the IPv6 for loopback. You need to disable IPv6. For Linux, follow this instruction: blog.taragana.com/index.php/archive/…
    – ZZ Coder
    Commented May 2, 2010 at 18:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.