PHP/mysqli:使用 mysqli_stmt_bind_param() 插入 IP 地址
我有一个数据库表,其中包含一个无符号整数字段来存储访问者的 IP 地址:
`user_ip` INT(10) UNSIGNED DEFAULT NULL,
这是尝试存储 IP 地址的 PHP 代码片段:
$ipaddr = $_SERVER['REMOTE_ADDR'];
if ($stmt = mysqli_prepare($dbconn, 'INSERT INTO visitors(user_email, user_ip) VALUES (?,?)'))
{
$remote_ip = "INET_ATON('$ipaddr')";
mysqli_stmt_bind_param($stmt, 'ss', $email, $remote_ip);
if (mysqli_stmt_execute($stmt) === FALSE) return FALSE;
$rows_affected = mysqli_stmt_affected_rows($stmt);
mysqli_stmt_close($stmt);
}
INSERT 操作成功,但 user_ip 字段包含空值。 我还尝试将 mysqli_stmt_bind_param() (在上面的示例中设置为字符串) 中的参数类型更改为整数,即 mysqli_bind_param(... 'si',...) - 但要没有用。
我还尝试使用以下代码代替 mysql 的 INET_ATON() SQL 函数:
function IP_ATON($ipaddr)
{
$trio = intval(substr($ipaddr,0,3));
return ($trio>127) ? ((ip2long($ipaddr) & 0x7FFFFFFF) + 0x80000000) : ip2long($ipaddr);
}
它仍然不起作用 - 'user_ip' 字段仍然设置为 null。我尝试将 $ip_addr 变量作为整数和mysqli_bind_param() 中的字符串 - 无济于事。
看来问题出在参数化插入上。
以下“旧式”代码可以正常工作:
mysqli_query(..., "INSERT INTO visitors(user_email, user_ip) VALUES ('$email',INET_ATON('$ipaddr'))");
我在这里做错了什么?
提前致谢!
I have a database table which contains an unsigned integer field to store the visitor's IP address:
`user_ip` INT(10) UNSIGNED DEFAULT NULL,
Here's the snippet of PHP code which tries to store the IP address:
$ipaddr = $_SERVER['REMOTE_ADDR'];
if ($stmt = mysqli_prepare($dbconn, 'INSERT INTO visitors(user_email, user_ip) VALUES (?,?)'))
{
$remote_ip = "INET_ATON('$ipaddr')";
mysqli_stmt_bind_param($stmt, 'ss', $email, $remote_ip);
if (mysqli_stmt_execute($stmt) === FALSE) return FALSE;
$rows_affected = mysqli_stmt_affected_rows($stmt);
mysqli_stmt_close($stmt);
}
The INSERT operation succeeds, however the user_ip field contains a null value.
I have also tried changing the parameter type in mysqli_stmt_bind_param() (which was set to string in the above example) to integer, i.e. mysqli_bind_param(... 'si',...) - but to no avail.
I've also tried using the following bit of code instead of mysql's INET_ATON() SQL function:
function IP_ATON($ipaddr)
{
$trio = intval(substr($ipaddr,0,3));
return ($trio>127) ? ((ip2long($ipaddr) & 0x7FFFFFFF) + 0x80000000) : ip2long($ipaddr);
}
It still doesn't work - the 'user_ip' field is still set to null. I've tried passing the $ip_addr variable as both integer & string in mysqli_bind_param() - to no avail.
It seems the problem lies with the parameterized insert.
The following "old-style" code works without any problem:
mysqli_query(..., "INSERT INTO visitors(user_email, user_ip) VALUES ('$email',INET_ATON('$ipaddr'))");
What am I doing wrong here?
Thanks in advance!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我认为它应该看起来像这样:
注意第二秒的变化?以及哪些参数被发送到 mysqli_stmt_bind_param。
由于传递到 mysqli_stmt_bind_param 的参数应该是要插入的实际值,而不是任何函数。
I think it should look like this:
Note the change by the second ? and what parameters are sent into mysqli_stmt_bind_param.
Since the parameters you pass into mysqli_stmt_bind_param should be the actual values to be inserted, and not any functions.
绑定 SQL 查询参数只能用于值,不能用于表达式或函数调用。因此,当您执行此操作时:
它绑定字符串“INET_ATON('$ipaddr的值')”,而不是使用该名称的 SQL 函数的结果。当将字符串值插入到整数列时,MySQL 会采用任何前导数字的数值,在这种情况下没有前导数字。因此插入值 0。
我建议您使用 PHP 的内置函数
ip2long()
。虽然
$remote_ip
现在是一个整数,但我读到 mysqli 在绑定无符号整数时遇到问题,因此您应该在绑定时使用 's' 作为类型。回复您的评论:您是对的,因为 PHP 不支持无符号整数,如果您的 IP 地址为 128.0.0.0 或更大,
ip2long()
将返回负数。您可以使用 sprintf() 转换为无符号整数的字符串表示形式:Bound SQL query parameters can be used only for a value, not an expression or a function call. So when you do this:
It binds the string "INET_ATON('value of $ipaddr')", not the result of a SQL function by that name. When a string value is inserted to an integer column, MySQL takes the numeric value of any leading digits, and in this case there are no leading digits. So the value 0 is inserted.
I would recommend you use PHP's built-in function
ip2long()
.Although
$remote_ip
is now an integer, I've read that mysqli has trouble binding unsigned integers, so you should just use 's' as the type when you bind.Re your comment: You're right, because PHP doesn't support unsigned integers,
ip2long()
will return a negative number if your IP address is 128.0.0.0 or greater. You can convert to a string representation of the unsigned integer withsprintf()
: