Convert CGI REMOTE_ADDR to network byte order for use with MySql INET_NTOA
I'm trying to log requests for a script and I want to store the client's IP Address in an int(4)
column, using INET_NTOA()
to return it in a readable function.
However, in my Perl CGI script, $ENV{REMOTE_ADDR}
contains an IP in host byte order which, when entered into the database with the INET_ATON()
function, is converted incorrectly so when I pull it back out, I have a completely different address.
#!usr/bin/perl -wT
use strict;
use CGI;
use DBI;
my $dbh = DBI->connect("DBI:mysql:$db:$server", "$user", "$password");
my $addr = $ENV{REMOTE_ADDR};
my $insert = $dbh->prepare(qq[insert into access_log (ipaddress)
开发者_C百科 values (INET_ATON('$addr'))]);
$insert->execute or die "sql insert error";
my $retrieve = $dbh->prepare(qq[select INET_NTOA(ipaddress) as IP_Address
from access_log]);
$retrieve->execute or die "sql select error";
my $newIP = $retrieve->fetchrow_array;
Ideally, this script should return the value of $addr
but instead I get something completely different. How can I format $addr
before putting it into the database?
Thanks for your help.
I've discovered that no change to the $ENV{REMOTE_ADDR} is needed.
By using a an int(4) unsigned for my ip_address column, inet_aton() and inet_ntoa() work correctly. With a signed integer,
"values corresponding to IP addresses for which the first octet is greater than 127 cannot be stored correctly" so the data gets distorted in the insert stage.
http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-ntoa
Odd. The ENV var should be the normal dotted-quad notation, not a raw integer. Shouldn't it be $ENV{'REMOTE_ADDR'}
anyways?
Note that your INT column should be unsigned. inet_ntoa() does not like negative numbers, and half the IP address space will show up as negative if you store it in signed int fields.
精彩评论