Cannot select where ip=inet_pton($ip)

Question

I have a unique column in database which is named ip

IP addresses are stored in this column as BINARY(16) (with no collation) after converting them using the PHP function

$store_ip = inet_pton($ip);

When I try to insert the same IP twice it works fine and fails because it is unique,

But when I try to select the IP it doesn't work and always returns FALSE (not found)

<?php

try {
    $ip = inet_pton($_SERVER['REMOTE_ADDR']);
    $stmt = $db->prepare("SELECT * FROM `votes` WHERE ip=?");
    $stmt->execute([$ip]);
    $get = $stmt->fetch();

    if( ! $get){
        echo 'Not found';
    }else{
        echo 'Found';
    }

    // close connection
    $get = null;
    $stmt = null;

} catch (PDOException $e) {
    error_log($e->getMessage());
}

The part where I insert the IP:

<?php

if( ! filter_var($ip, FILTER_VALIDATE_IP)){
        return FALSE;
}

$ip = inet_pton($_SERVER['REMOTE_ADDR']);

try {
    $stmt = $db->prepare("INSERT INTO votes(ip, answer) VALUES(?,?)");
    $stmt->execute([$ip, $answer]);
    $stmt = null;
} catch (PDOException $e) {
    return FALSE;
}

Answer 1

First the fix, which is quite simple: If you want to store both, IPv4 and IPv6 addresses, you should use VARBINARY(16) instead of BINARY(16).

Now to the problem: Why doesn't it work as expected with BINARY(16)?

Consider we have a table ips with only one column ip BINARY(16) PRIMARY KEY. We store the default local IPv4 address with

$stmt = $db->prepare("INSERT INTO ips(ip) VALUES(?)");
$stmt->execute([inet_pton('127.0.0.1')]);

and find the following value in the database:

0x7F000001000000000000000000000000

As you see - It's a 4 byte binary value (0x7F000001) right-padded with zeros to fit the 16 byte fixed-length column.

When you now try to find it with

$stmt = $db->prepare("SELECT * FROM ips WHERE ip = ?");
$stmt->execute([inet_pton('127.0.0.1')]);

the following happens: PHP sends the value 0x7F000001 as parameter which is then compared with the stored value 0x7F000001000000000000000000000000. But since two binary values of different length are never equal, the WHERE condition will always return FALSE. You can try it with

SELECT 0x00 = 0x0000

which will return 0 (FALSE).

Note: The behavior is different for fixed length non binary strings (CHAR(N)).

We could use explicit casting as a workaround:

$stmt = $db->prepare("SELECT * FROM ips WHERE ip = CAST(? as BINARY(16))");
$stmt->execute([inet_pton('127.0.0.1')]);

and it will find the row. But if we look at what we get

var_dump(inet_ntop($stmt->fetch(PDO::FETCH_OBJ)->ip));

we will see

string(8) "7f00:1::"

But that is not (really) what we have tried to store. And when we now try to store 7f00:1::, we will get a duplicate key error, though we have never stored any IPv6 address yet.

So once again: Use VARBINARY(16), and you can keep your code untouched. You will even save some storage, if you store many IPv4 addresses.

Answer 2

I will not answer why your code didn't work as expected because I don't know exactly. Thanks for @Paul Spiegel great answer, he explained why.

In this answer I just suggest you use the MySQL built-in functions instead of PHP.

This is how I handle IPs in my applications and I'm having no trouble until now with this model.

I store IPs in varbinary(16) column, and doing the conversions using MySQL built-in functions

1. inet6_aton for converting IP strings to binary

2. inet6_ntoa for converting binary to IP strings

So replace this code

//query 1 
$ip = inet_pton($_SERVER['REMOTE_ADDR']);
$stmt = $db->prepare("SELECT * FROM `votes` WHERE ip=?");
$stmt->execute([$ip]);

with this one

//query 2 
$stmt = $db->prepare("SELECT * FROM `votes` WHERE ip=INET6_ATON(?)");
$stmt->execute([$_SERVER['REMOTE_ADDR']]);

Needless to say DON'T do it like this (query 3)-

//query 3 
$stmt = $db->prepare("SELECT * FROM `votes` WHERE INET6_NTOA(ip)= ?");
$stmt->execute([$_SERVER['REMOTE_ADDR']]);

(because the Database will hate you for making it do the conversion for every IP record in the table)

From my short experience, I found that whenever I have the chance to let the database do something instead of the application layer(PHP), let the database do it immediately. Make MySQL fat and PHP skinny =) , like the fat model and skinny controller say.

When You make most of your work inside the database, that will make your database can work better independent from the PHP code (it doesn't need it), which makes your database more portable.

For example if you wanted to turn your system from an on-cloud web based one that uses PHP, to an on-premise one that uses .net languages, the .net developers will love you for making them code less because most of the work is already written and done by MySQL.

Another example, your application succeed and you are now having more clients that want your application, you will just get them another server and install MySQL only on it, and because most of the work is done by the database, your application scaled easier than installing a complete web server and do the scaling also for the PHP.

Answer 3

Instead of struggling over escaping BINARY, let's avoid that.

INSERT INTO ips (ip) VALUES(INET6_ATON(?))

and

SELECT INET6_NTOA(ip) FROM ips WHERE ...;

That way, you are working only with human-readable strings.

Notes:

• Skip the use of inet_pton() in PHP, since the conversion is now being done in MySQL.
• The INET6... functions do not exist in old versions of MySQL.
• Yes, do use VARBINARY(16), and be sure to check whether IPv4 strings (like "1.2.3.4") will work.