无法选择ip = inet_pton($ ip)的位置 [英] Cannot select where ip=inet_pton($ip)

问题描述

我在数据库中有一个唯一的列，名为ip

I have a unique column in database which is named ip

使用PHP函数转换IP地址后，IP地址将以BINARY(16)的形式存储在此列中(不带排序规则)

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);

当我尝试两次插入相同的IP时，它可以正常工作，并且因为它是唯一的而失败，

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

但是，当我尝试选择IP时不起作用，并且总是返回FALSE(未找到)

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());
}

我插入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;
}

推荐答案

首先解决此问题，这很简单: 如果您要同时存储IPv4和IPv6地址， 您应该使用VARBINARY(16)而不是BINARY(16).

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).

现在出现问题:为什么BINARY(16)无法正常工作?

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

考虑一下，我们有一个只有一个列ip BINARY(16) PRIMARY KEY的表ips. 我们将默认的本地IPv4地址存储为

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

如您所见-这是一个4字节的二进制值(0x7F000001) 右填充零以适合16字节固定长度的列.

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

当您现在尝试使用

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

发生以下情况: PHP发送值0x7F000001作为参数，然后将其进行比较 与存储的值0x7F000001000000000000000000000000. 但是由于两个不同长度的二进制值永远不会相等， WHERE条件将始终返回FALSE. 您可以尝试使用

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

，它将返回0(FALSE).

which will return 0 (FALSE).

注意:对于固定长度的非二进制字符串(CHAR(N))，其行为是不同的.

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));

我们将会看到

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

但这不是(确实)我们尝试存储的内容. 当我们现在尝试存储7f00:1::时， 我们将收到一个重复键错误， 尽管我们尚未存储任何IPv6地址.

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.

因此再次:使用VARBINARY(16)，您可以使代码保持不变. 如果您存储许多IPv4地址，甚至可以节省一些存储空间.

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.

这篇关于无法选择ip = inet_pton($ ip)的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！