Whilst I was making the IP range function for my crawler catcher, I stumbled upon the problem of signed and unsigned integers for ip address (ip2long, INET_ATON) and back again (long2ip, INET_NTOA) using PHP and MySQL respectively. This caused me a problem when I used ip2long to sort ip addresses by order, and received negative numbers (-16843010) this gave me some funny results when using php's sort() function. I was getting 1 IP out of a range appearing 3/4 numbers out of place.

Upon futher inspection I found out that php uses signed integers (positive and negative numbers). This messes with the array sort functon. After I changed to unsigned integers, my ip ranges worked perfectly.

MySQL also won't produce an IP from a negative integer, and just returns false:

I've provided some demonstrations, and a php function to produce an unsigned long from an IP address.

MySQL - unsigned integers

mysql> SELECT INET_ATON("1.1.1.1");
+----------------------+
| INET_ATON("1.1.1.1") |
+----------------------+
|             16843009 |
+----------------------+
1 row IN SET (0.02 sec)
 
mysql> SELECT INET_ATON("254.254.254.254");
+------------------------------+
| INET_ATON("254.254.254.254") |
+------------------------------+
|                   4278124286 |
+------------------------------+
1 row IN SET (0.00 sec)
 
mysql> SELECT INET_NTOA(4278124286);
+-----------------------+
| INET_NTOA(4278124286) |
+-----------------------+
| 254.254.254.254       |
+-----------------------+
1 row IN SET (0.07 sec)

MYSQL - signed integers

mysql> SELECT INET_NTOA(-16843010);
+----------------------+
| INET_NTOA(-16843010) |
+----------------------+
| NULL                 |
+----------------------+
1 row IN SET (0.04 sec)
 
mysql> SELECT INET_NTOA(16843010);
+---------------------+
| INET_NTOA(16843010) |
+---------------------+
| 1.1.1.2             |
+---------------------+
1 row IN SET (0.00 sec)

PHP Functions

ip2long("254.254.254.254"); // -16843010
 
ip2long("1.1.1.1"); // 16843009
 
sprintf("%u", ip2long("1.1.1.1")); // 16843009
 
sprintf("%u", ip2long("254.254.254.254")); // 4278124286

unsigned ip2long function

/**
* unsigned ip 2 long
* @author Mark Willis
* @version 1.0 - August 2009
*
* @param string $ip - ip address
* @return string
*/
function my_ip2long($ip) {
return sprintf("%u", ip2long($ip));
}
 
//usage
echo my_ip2long("254.254.254.254");

We also need to be able to get proper ips from unsigned ints. However PHP's "long2ip" allows for unsigned integers to produce the correct value.

long2ip(4278124286); // 254.254.254.254

On a side note - On ip 255.255.255.255 MySQL returns an integer and PHP returns false. Although some may say that IP is not a valid ip address:

255.255.255.255 is the broadcast address for the entire class system it used for experimenting with new protocols.

MySQL

mysql> SELECT INET_ATON("255.255.255.255");
+------------------------------+
| INET_ATON("255.255.255.255") |
+------------------------------+
|                   4294967295 |
+------------------------------+
1 row IN SET (0.00 sec)

PHP

ip2long("255.255.255.255"); // -1

Mark Willis