IP addresses get stored as 0 when converting with PHP ip2long function

Question

The newsletter_ip field in MySQL is set as an UNSIGNED INT (10). I've also tried INET_ATON to format the data, but my results always look like this.

Here is part of my processing code:

//Retrieve data from user and create variables

$ip_orig = $_SERVER['REMOTE_ADDR'];

$ip = ip2long($ip_orig);

//Place into database

$sql = "INSERT INTO newsletter(newsletter_email, newsletter_ip, newsletter_date, newsletter_time) VALUES('".$email."', '".$ip."', '".$date."', '".$time."')";

I've also tried this snippet prior to the ip2long formatting, to no avail:

if (!empty($_SERVER['HTTP_CLIENT_IP'])){
    $ip=$_SERVER['HTTP_CLIENT_IP'];
}elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
    $ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
}else{
    $ip=$_SERVER['REMOTE_ADDR'];
}

Any help would be much appreciated, thanks!

Try using a varchar(15) instead of a string for an IP address. — andrewsi, Jun 26 '12 at 15:28
You [cannot trust X-Forwarded-For](http://stackoverflow.com/a/3003233/238978)! — Emil Vikström, Jun 26 '12 at 15:30
I believe you may be sending a value that is out of range. Either use BIGINT or VARCHAR to store as a string. I would also consider IPv6 addresses. — Kermit, Jun 26 '12 at 15:32
Neither `varchar(15)` nor `unsigned int(10)` are going to help you with IPv6 addresses. Do you have the option of using a [database that can store IP addresses natively](http://www.postgresql.org/docs/9.1/static/datatype-net-types.html)? — MattH, Jun 26 '12 at 15:34
This question addresses the problem as well http://stackoverflow.com/questions/1108918/how-to-store-an-ip-in-mysql — ply, Jun 26 '12 at 15:40

score 1 · Answer 1 · answered Jun 26 '12 at 15:35

for the root cause of your problem, ip2long gives a signed int as the PHP manual states:

Note:

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.

And you store it as an unsigned int, thats the reason why you see ony zeros. For a standard and clean solution handling IPv6 others have already given the solution.

score 0 · Answer 2 · answered Jun 26 '12 at 15:31

0

Since an IP address has several dots in it, or it may be a IPv6 address, I would suggest you set the newsletter_ip field as a VARCHAR

Right now your IP addresses may be showing up as 0 because they are not actually integers.

answered Jun 26 '12 at 15:31

RutZap

381
1
6

Mike Mackintosh · Answer 3 · 2012-06-26T15:49:27.330

You are storing the long as a string, not as a long.

You are also better off making the conversion in MySQL itself:

$sql = "INSERT INTO newsletter(newsletter_email, newsletter_ip, newsletter_date, newsletter_time) VALUES ('".$email."', INET_ATON('".$ip."'), '".$date."', '".$time."')";

To retreive the IP address, do:

SELECT INET_NTOA(newsletter_ip) as newsletter_ip FROM newsletter;

INET_ATON = Address to number

INET_NTOA = Number to Address

score 0 · Answer 4 · answered Jun 26 '12 at 15:32

0

Does printing out the ip before storing give you the correct value? If not, check if a proxy exists which removes this information from the request or renames it.

Anyways, there is no gain in storing the ip as long. There should not be a notable loss in performance if you use a string for this field which is much more straight forward and easier to handle. And while you're at it, leave enough space for IPv6 ;-)

answered Jun 26 '12 at 15:32

Roben

840
9
19

Proxies can't remove `$_SERVER['REMOTE_ADDR']`. It's server side. Apache knows all. – Marcus Adams Jun 26 '12 at 16:43
If it is a local proxy in front of the web server on the same machine, it would always set 127.0.0.1, for example. – Roben Jun 26 '12 at 21:45
you'd know about this proxy running on the same machine, but anyway, 127.0.0.1 is still an IP address. – Marcus Adams Jun 27 '12 at 13:15

score 0 · Answer 5 · answered Jun 26 '12 at 15:33

How about letting MySQL translate the IP using INET_ATON on the server side ?

//Retrieve data from user and create variables

$ip_orig = $_SERVER['REMOTE_ADDR'];

//Place into database

$sql = "INSERT INTO newsletter(newsletter_email, newsletter_ip, newsletter_date, newsletter_time) VALUES('".$email."', INET_ATON('".$ip."'), '".$date."', '".$time."')";

score 0 · Answer 6 · answered Jun 26 '12 at 15:35

Use varchar(15) as min... varchar(45) to support newer IPv6 addresses, also here is my IP function:

function ip()
{
    if (!empty($_SERVER['HTTP_CLIENT_IP']))
    {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
    } elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))
    {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
    }
    else
    {
        $ip = $_SERVER['REMOTE_ADDR'];
    }
    return $ip;
}

IP addresses get stored as 0 when converting with PHP ip2long function

6 Answers6

Linked