douluo6626
2010-05-26 21:30 阅读 109
已采纳

PHP / mysqli:使用mysqli_stmt_bind_param()插入IP地址

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!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    douduandiao1368 douduandiao1368 2010-05-26 21:33

    I think it should look like this:

    $ipaddr = $_SERVER['REMOTE_ADDR'];
    if ($stmt = mysqli_prepare($dbconn, 'INSERT INTO visitors(user_email, user_ip) VALUES (?, INET_ATON(?))'))
    {
        mysqli_stmt_bind_param($stmt, 'ss', $email, $ipaddr);
        if (mysqli_stmt_execute($stmt) === FALSE) return FALSE;
        $rows_affected = mysqli_stmt_affected_rows($stmt);
        mysqli_stmt_close($stmt);
    }
    

    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.

    点赞 评论 复制链接分享
  • duanmaifu3428 duanmaifu3428 2010-05-26 21:37

    Bound SQL query parameters can be used only for a value, not an expression or a function call. So when you do this:

    $remote_ip = "INET_ATON('$ipaddr')";
    

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

    $remote_ip = ip2long($ipaddr);
    mysqli_stmt_bind_param($stmt, 'ss', $email, $remote_ip);
    

    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 with sprintf():

    <?php
    
    $addr = '192.168.1.1';
    
    $ip = ip2long($addr);
    var_dump($ip);
    // int(-1062731519)
    
    $ip = sprintf("%u", ip2long($addr));
    var_dump($ip);
    // string(10) "3232235777"
    
    var_dump(long2ip($ip));
    // string(11) "192.168.1.1"
    
    点赞 评论 复制链接分享

相关推荐