Storing IPv4 and IPv6 IP addresses in a uniform way in MySQL

Basically, we store the IP addresses in a packed form in a VARBINARY(16) field and then query these IP's in this form. This way we can treat IPv4 and IPv6 addresses similarly in the database.

There is something to be said for dealing with this binary format in an indirect way by using a helper class. This prevents hardcoding of functions like inet_pton() which might be strewn about in your code. Then, when something changes in IP Country, you might have to search and replace these instances when they no longer work the way they should or are no longer compatible with their database counterpart.

By using this wrapper you can change the implementation should the need arise without having to dive into your codebase to figure out where it is used.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

<?php
class Helper_Ip
{
    // @see http://php.net/manual/en/function.inet-ntop.php#117398
    // PHPs equivalent of MySQLs INET6_ATON(), because inet_ntop() isn't the same apparently
    /**
    * Convert a MySQL binary v4 (4-byte) or v6 (16-byte) IP address to a printable string.
    * @param string $ip A binary string containing an IP address, as returned from MySQL's INET6_ATON function
    * @return false if not valid.
    */
    protected static function inet6_ntop($ip) {
        $l = strlen($ip);
        if ($l == 4 or $l == 16) {
            return inet_ntop(pack('A' . $l, $ip));
        }
        return false;
    }

    // unpacked --> packed
    public static function pack($ip) {
        // already compatible with MySQLs function INET6_NTOA() for unpacking
        return inet_pton($ip);
    }

    // packed --> unpacked
    public static function unpack($packedIp) {
        $return = self::inet6_ntop($packedIp);
        if ($return === false) {
            throw new Exception('[Helper_Ip] not a valid packed IP');
        }
        return $return;
    }
}
?>

If we want to display this binary data in some human readable form there are a couple of options.

• On the MySQL side
  • use HEX(), to convert the binary value to its hexadecimal representation
  • use INET6_NTOA(), to convert the binary value back to the original IP address
• On the PHP side
  • use bin2hex(), which converts binary data into its hexadecimal representation
  • use the static Helper_Ip::unpack() method, to convert the binary value back to the original IP address

Remember to also always use the appropriate escaping functions for whatever context you are working in.