Page 1 of 1

[SOLVED]Convert IPv4 to number and back for storage in MySQL

Posted: Thu Nov 12, 2009 8:19 am
by batfastad
Hi everyone
Up until now I've been storing IP addresses in my database as a string (VARCHAR) just in the format 192.168.78.49 or whatever
However after a bit of reading it seems it's better to be storing them as integers

So I've done a bit of digging and came up with the following functions to convert to/from a dotted quad

Code: Select all

// GET REAL IP ADDRESS
function get_real_ip() {
    if( !empty($_SERVER['HTTP_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_CLIENT_IP']; // share internet
    } elseif( !empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR']; // pass from proxy
    } else {
        $ip = $_SERVER['REMOTE_ADDR'];
    }
    return $ip;
}
 
$ip = get_real_ip();
echo $ip_numeric = sprintf('%u', ip2long($ip)).'<br />';
echo $ip_string = long2ip($ip_numeric);
1) Are those functions going to be correct for all IP address ranges?
I've tried manually with values of 0.0.0.0 and 255.255.255.255 and the conversion seems to work.

2) My conversion of 255.255.255.255 returns 18446744073709551615
How would I go about storing a number like this in MySQL? Guessing INT won't work since its out of the range... so will UNSIGNED BIGINT (http://dev.mysql.com/doc/refman/5.0/en/ ... types.html) do the job?

3) I'm guessing its no co-incidence that 255.255.255.255 returns 18446744073709551615 and the max value of UNSIGNED BIGINT is also 18446744073709551615

4) I was alerted to what Nick Critten says in the comments on this page http://www.justin-cook.com/wp/2006/11/2 ... and-vbnet/
I tried this

Code: Select all

echo $ip_numeric = floatval(sprintf("%u",ip2long('$ip')));
echo $ip_string = long2ip($ip_numeric);
And the conversion wasn't lossless. Should I just use my original functions above?

I just want to make sure I get this right before I start ploughing loads of IP addresses into a DB

Cheers, B

Re: Convert IPv4 to number and back for storage in MySQL

Posted: Thu Nov 12, 2009 8:38 am
by VladSun
Huh?!? You must have a mistake somewhere ...
255.255.255.255 is 4294967295

http://en.wikipedia.org/wiki/IPv4
IPv4 uses 32-bit (four-byte) addresses, which limits the address space to 4,294,967,296 (2^32) possible unique addresses.

Code: Select all

$ip = '255.255.255.255';
echo sprintf("%u",ip2long($ip));
echo long2ip(ip2long($ip));

Re: Convert IPv4 to number and back for storage in MySQL

Posted: Thu Nov 12, 2009 9:36 am
by batfastad
Hi VladSun
Just tried that code and got 18446744073709551615 out again!

Re: Convert IPv4 to number and back for storage in MySQL

Posted: Thu Nov 12, 2009 9:40 am
by VladSun
What code???

Re: Convert IPv4 to number and back for storage in MySQL

Posted: Thu Nov 12, 2009 9:46 am
by VladSun
18.446.744.073.709.551.615 - even in this form, this number is a way bigger than 255.255.255.255, although it has more combinations permitted in every triad (999 >> 255!)
So, simply it can't be true!

Re: Convert IPv4 to number and back for storage in MySQL

Posted: Thu Nov 12, 2009 9:46 am
by batfastad
VladSun wrote:What code???
This code you posted

Code: Select all

$ip = '255.255.255.255';
echo sprintf("%u",ip2long($ip));
echo long2ip(ip2long($ip));
I'm running this on a 64bit server if that makes any difference to this calculation.
Cheers, B

Re: Convert IPv4 to number and back for storage in MySQL

Posted: Thu Nov 12, 2009 9:51 am
by VladSun
Blah!

255.255.255.255 => FF.FF.FF.FF => FFFFFFFF => 4 bytes => 32 bits
2^32 = 4294967296!

Maybe it's a PHP 64bit bug ...

http://bugs.php.net/bug.php?id=47365

Re: Convert IPv4 to number and back for storage in MySQL

Posted: Thu Nov 12, 2009 10:08 am
by batfastad
Ah nuts. I'm running Ubuntu server 8.04 with php 5.2.4

So in theory though using this code should losslessly convert between the 2 correctly

Code: Select all

echo sprintf("%u",ip2long($ip));
echo long2ip(ip2long($ip))
And I store it in my DB as regular INT UNSIGNED?
Because the sprintf("%u" bit is converting to unsigned integer for me. Without that PHP defaults to signed integers. Is that correct?

Re: Convert IPv4 to number and back for storage in MySQL

Posted: Thu Nov 12, 2009 1:14 pm
by VladSun
batfastad wrote:So in theory though using this code should losslessly convert between the 2 correctly

Code: Select all

echo sprintf("%u",ip2long($ip));
echo long2ip(ip2long($ip))
And I store it in my DB as regular INT UNSIGNED?
Yes.
batfastad wrote:Because the sprintf("%u" bit is converting to unsigned integer for me. Without that PHP defaults to signed integers. Is that correct?
Yes :)

Re: Convert IPv4 to number and back for storage in MySQL

Posted: Thu Nov 12, 2009 2:10 pm
by batfastad
Sweet, cheers :lol:

Re: [SOLVED]Convert IPv4 to number and back for storage in MySQL

Posted: Thu Nov 12, 2009 5:20 pm
by VladSun