MySQL Insert error

Question

Ok, when trying to insert into the database I'm getting this 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 '@email.com, UT, 84505, NOW(), 69.169.186.192)' at line 1"

I can't figure out the problem. Here is the code for my insert statement.

$insert_query = sprintf("INSERT INTO contacts (first_name, last_name, email, state, zip, date, ip) VALUES (%s, %s, %s, %s, %s, NOW(), %s)",
                        $fname,
                        $lname,
                        $email,
                        $state,
                        $zip,
                        $ip);


$result = mysql_query($insert_query, $connection) or die(mysql_error());

My table has the following structure:

    id int(11)                              
    first_name  varchar(100)                                 
    last_name   varchar(100)                             
    email   varchar(100)                                 
    state   varchar(3)                               
    zip int(10)                             
    date    datetime                                
    ip  varchar(255)

Mat · Accepted Answer · 2011-03-30 05:58:32Z

2

You need to quote all the string-type columns in the insert statement. Replace %s with '%s' in the sprintf format.

Please read about SQL Injection if you haven't done so already.

answered Mar 30, 2011 at 5:58

Mat

208k41 gold badges407 silver badges423 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mitchell Over a year ago

Beautiful. That did the trick. Earlier in my code I'm using mysql_real_escape_string() on each of the variables. Is this not the correct way to do it?

Mitchell Over a year ago

Ok. Thank you very much for your help. I'm new to this whole programming thing. I'll read up more on SQL injection so I don't make this mistake again and keep my sites safe! I appreciate the quick responses from everyone.

Gaurav Porwal · Accepted Answer · 2011-03-30 06:04:30Z

0

This may help you..

$insert_query = "INSERT INTO contacts set first_name = '$fname', last_name = '$lname', email = '$email', state = '$state', zip = '$zip', date = ". time() .", ip = '$ip')";


$result = mysql_query($insert_query, $connection) or die(mysql_error());

if you want to check query

echo $insert_query;

answered Mar 30, 2011 at 6:04

Gaurav Porwal

5134 silver badges6 bronze badges

Comments

Neville Kuyt · Accepted Answer · 2011-03-30 06:06:07Z

0

It would help if you could echo out the $insert_query, but it looks like you're not putting quotes around the parameters that are varchars.

$insert_query = sprintf("INSERT INTO contacts (first_name, last_name, email, state, zip, date, ip) VALUES ('%s', '%s', '%s', '%s', '%s', NOW(), '%s')",
                        $fname,
                        $lname,
                        $email,
                        $state,
                        $zip,
                        $ip);

By the way, you have an extra column in your insert - NOW doesn't appear related to a column. I'm assuming ZIP is a varchar column, not a number, by the way.

edited Mar 30, 2011 at 6:06

answered Mar 30, 2011 at 5:58

Neville Kuyt

29.6k2 gold badges40 silver badges52 bronze badges

3 Comments

Mitchell Over a year ago

This is the echo of $insert_query INSERT INTO contacts (first_name, last_name, email, state, zip, date, ip) VALUES (you, me, [email protected], AL, 12345, NOW(), 69.169.186.192)

Neville Kuyt Over a year ago

Yeah, you need to do what Mat suggests. Will edit my answer to reflect.

Mitchell Over a year ago

Yes it is a varchar, you are right. And that fixed the error I was getting. Thanks for the answer.

Collectives™ on Stack Overflow

MySQL Insert error

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related