Trying to store IP Address in Database. SQL Errors

Question

Here is my code I'm trying to use to store the ip address of the client. I'm trying to store the ip address to an unsigned int, by trying to do something seen in this question: Which MySQL datatype to use for an IP address?

Here is my code:

$client_ip = $_SERVER['REMOTE_ADDR'];

$ip_insert = "SELECT INET_ATON('$client_ip')";

$sql = "INSERT INTO rtv_sign_cispa (name_cispa,ip_cispa,email_cispa,message_cispa) VALUES ('$name','$ip_insert','$email','$message')";

There are other things in that statement being stored but just ignore those ones, they work, it's the ip address that isn't.

Error:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '192.168.2.1')','email@email.com','Hmmmm')' at line 1

The ip address after near does in fact show the correct IP, I just switched it to a local ip for security reasons of course. email@email.com and Hmmm is from a form that is filled out to activate this so don't really mind that.

Does anyone know what I'm doing wrong? I'm kinda new to SQL.

Have you tried printing out the SQL that your code is generating, and pasting that straight into the database to see what happens? — andrewsi, May 08 '12 at 18:33
@SLaks where is it? I'm a beginner so I really have not known what to look for when trying to make it secure. — ComputerLocus, May 08 '12 at 18:41

Luigi Siri · Accepted Answer · 2012-05-08T18:41:58.353

You are doing this:

$sql = "INSERT INTO rtv_sign_cispa (name_cispa,ip_cispa,email_cispa,message_cispa) VALUES ('$name',SELECT INET_ATON('$client_ip'),'$email','$message')";

Because you are setting:

$ip_insert = "SELECT INET_ATON('$client_ip')";

and it must be:

$ip_insert = "INET_ATON('$client_ip')";

This is the way to do it:

$sql = "INSERT INTO rtv_sign_cispa (name_cispa,ip_cispa,email_cispa,message_cispa) VALUES ('$name',INET_ATON('$client_ip'),'$email','$message')";

score 2 · Answer 2 · answered May 08 '12 at 18:37

$ip_insert = "SELECT INET_ATON('$client_ip')";

is only a string, you must query this string to the database and then fetch the result or

$sql = "INSERT INTO rtv_sign_cispa (name_cispa,ip_cispa,email_cispa,message_cispa) VALUES ('$name',INET_ATON('$client_ip'),'$email','$message')";

score 1 · Answer 3 · answered May 08 '12 at 18:40

1

I think you want:

$ip_insert = ip2long($client_ip);
$sql = "INSERT INTO rtv_sign_cispa (name_cispa,ip_cispa,email_cispa,message_cispa) VALUES ('$name','$ip_insert','$email','$message')";

But you would be better using:

$sth = $dbh->prepare("INSERT INTO rtv_sign_cispa (name_cispa,ip_cispa,email_cispa,message_cispa) VALUES (?,?,?,?)");
$sth->bindParam(1, $name, PDO::PARAM_STR);
$sth->bindParam(2, $ip_insert, PDO::PARAM_INT);
$sth->bindParam(3, $email, PDO::PARAM_STR);
$sth->bindParam(4, $message, PDO::PARAM_STR);
$sth->execute();

answered May 08 '12 at 18:40

Julian

1,522
11
26

Could you explain why it is better to do the way you have shown? – ComputerLocus May 08 '12 at 18:42
You get more checking of the types of your arguments and if you want to put several records in it becomes much more efficient to prepare once and bind/execute many times. – Julian May 08 '12 at 19:14

score 0 · Answer 4 · answered May 08 '12 at 18:36

What version of mysql are you running? Try running the inet_aton('192.168.1.1') command on your sql command line see if it works. It should output something like this:

mysql> select inet_aton('192.168.1.1');
+--------------------------+
| inet_aton('192.168.1.1') |
+--------------------------+
|               3232235777 |
+--------------------------+
1 row in set (0.00 sec)

It seems like your inet_aton() function isn't working, that's why you see the IP address in your sql error

score 0 · Answer 5 · answered May 08 '12 at 18:39

0

Use INET_ATON instead of "SELECT INET_ATON" (because this will return a record from DB).

answered May 08 '12 at 18:39

Oscar Jara

14,129
10
62
94

score 0 · Answer 6 · answered May 08 '12 at 18:45

You seem to have a extra ) in your sql statement it is difficult to say why it is in there. Seems like you conversion to unsigned int is going wrong. You can also drop the single quotes around the $ip_insert as you are inserting a number and that error show you are inserting a string.

The '192.168.2.1')' does not look right as you are trying to insert a number not a string.. You should log the entire statement to see what exactly is being executed. Google logging SQL statement mysql and set up the logging. Run the query check logs for it then try and debug that.

It appears to be a simple syntax error. However I could be very wrong as I have not touched PHP for more than 7 years.

Trying to store IP Address in Database. SQL Errors

6 Answers6