使用预准备语句保存远程IP

Question

I'm trying to save remote user IP in database. For the purpose I've created column as int(10) UNSIGNED.

When I try like this everything work but the IP is saved like 127.0.0.1

$ipaddr = $_SERVER['REMOTE_ADDR'];
$stmt = $pdo->prepare('INSERT INTO comment (body, author, email, date, url, ip) VALUES (:body, :author, :email, NOW(), :url, :ipaddr)');

$stmt->execute(array(
    ':body' => $_POST['k3jZGEeX1k1oo'],
    ':author' => $_POST['k3k0xKicU0eHQ'],
    ':email' => $email,
    'url' => $url,
    'ip' => $ipaddr
));

When I try to use INET_ATON nothing goes in database.

$ipaddr = $_SERVER['REMOTE_ADDR'];
$stmt = $pdo->prepare('INSERT INTO comment (body, author, email, date, url, ip) VALUES (:body, :author, :email, NOW(), :url, :INET_ATON(ipaddr))');

$stmt->execute(array(
        ':body' => $_POST['k3jZGEeX1k1oo'],
        ':author' => $_POST['k3k0xKicU0eHQ'],
        ':email' => $email,
        'url' => $url,
        'ip' => $ipaddr
));

Note the :INET_ATON(ipaddr). How can I do this with prepared statements?

Answer 1

As @Fred-ii- stated you should check errors. I also can recommend this solution and build in php function ip2long. In your case will be

$ipaddr = $_SERVER['REMOTE_ADDR'];
$ip = sprintf("%u", ip2long($ipaddr));

$stmt = $pdo->prepare('INSERT INTO comment (body, author, email, date, url, ip) VALUES (:body, :author, :email, NOW(), :url, :ip)');

$stmt->execute(array(
    ':body' => $_POST['k3jZGEeX1k1oo'],
    ':author' => $_POST['k3k0xKicU0eHQ'],
    ':email' => $email,
    ':url' => $url,
    ':ip' => $ip
));

To select and see actual IP you can then use INET_NTOA(again from the @Fred-ii- comment) in your query.