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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post 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
Last edited by batfastad on Thu Nov 12, 2009 2:10 pm, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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));
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post by batfastad »

Hi VladSun
Just tried that code and got 18446744073709551615 out again!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

What code???
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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!
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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 :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post by batfastad »

Sweet, cheers :lol:
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
Post Reply