mysql error with insert command

Question

So , am creating a password change table

When some 1 changes pass , i insert his username, newpass and the confirmation code in PassChange table, (so i send him a confirmation e-mail after) the idea is simple and here's the code i use

 $insertResult=mysql_query("INSERT INTO TempChangePass (UserName, NewPass, ConfirmationCode) VALUES ('$UserName', '$newPass', '$code')") or die (mysql_error());

though i get 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 'username'', '4da59df8d4007807e7230e0881fbf774', '16585482')' at line 1

NOTE: All the columns format in the table is set to varchar.

The connection to mysql database is fine, the table name is currect.

This problem is driving me crazy , i just can't figure out where the problem is, if anyone here can help me will be very thankful :)

and thanks in advance.

EDIT: I actually got it solved, and just for people who visit this post by searching for solutions, if you got similar problem with your sql command, try echo it, and see how exactly the string is moved to the database :-) , happy coding everyone.

And sorry if I wasted any of your time :) am just very new to php & mysql :D

Is there an ' in your username variable?? print_r($UserName). Then, take go meet Bobby tables, and read up on prepared statements — PenguinCoder
– PenguinCoder, Commented May 18, 2012 at 18:41
Try (temporarily) changing your code so that you assemble the query first, then dump the entire query out so you can see what's happening. i.e. $q = "INSERT INTO TempChangePass (UserName, NewPass, ConfirmationCode) VALUES ('$UserName', '$newPass', '$code')"; then var_dump( $q ) so you can see the exact query you are generating. That should let you see where the problem is. — Jazz
– Jazz, Commented May 18, 2012 at 18:41

etm124 · Accepted Answer · 2012-05-18 18:45:32Z

1

Remove the single quotes around your variables. PHP is interpreting them as strings.

 $insertResult=mysql_query("INSERT INTO TempChangePass (UserName, NewPass, ConfirmationCode) VALUES ('" . $UserName. "', '" . $newPass. "', '" . $code . "')") or die (mysql_error());

Additionally, you might want to do something like this:

$sql = "INSERT INTO TempChangePass (UserName, NewPass, ConfirmationCode) VALUES ('" . $UserName. "', '" . $newPass. "', '" . $code . "')";

echo $sql;

Take that echo, and try to manually run it.

edited May 18, 2012 at 18:45

answered May 18, 2012 at 18:40

etm124

2,1404 gold badges41 silver badges78 bronze badges

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

7 Comments

Shady Over a year ago

oh , so when dealing with varchars, there's no need to put quotes?

etm124 Over a year ago

You still want to wrap single quotes around them, but make sure you are passing the variable, not the actual word: '$username'. If you look at the line of code I provided, you'll see I am still using your single quotes before and after each variable.

jeffjenx Over a year ago

Alternatively, you can wrap your variables with curly braces {$Username} in order to better distinguish the variable from the rest of the text. Sometimes, it's a little easier to read this way.

Shady Over a year ago

aha, well I used your code and I get exactly the same error :(

Shady Over a year ago

also used the curly braces and still same thing

|

Cito · Accepted Answer · 2012-05-18 18:40:47Z

1

Looks something like sql inyection. I'm quite sure your $username is $username = "username'". Look at the single quote. So the query became:

$insertResult=mysql_query("INSERT INTO TempChangePass (UserName, NewPass, ConfirmationCode) VALUES ('username*''*, '4da59df8d4007807e7230e0881fbf774', '16585482')") or die (mysql_error());

answered May 18, 2012 at 18:40

Cito

1,7093 gold badges22 silver badges49 bronze badges

1 Comment

Shady Over a year ago

I saw that extra quote , and i thought like: umm, this quote shouldn't be here, but my username is like this: $UserName='username';

KittyKris · Accepted Answer · 2012-05-18 18:44:31Z

1

Did you try to do the Query one column by one ?

i mean :

INSERT INTO TempChangePass (UserName) values ( '$UserName' );

then add it up ?

Works for me mostly when I get errors ;)

Just an idea.

answered May 18, 2012 at 18:44

KittyKris

415 bronze badges

Comments

Community · Accepted Answer · 2023-11-17 20:38:18Z

1

It looks like you have single quotes in your actual username -- you're actually passing in 'username' instead of just username. Try removing those, see if it will work after that.

The recommended way to deal with this issue (and prevent SQL injection) is to use prepared statements, however if you really want to, you could probably do this inline using mysql_real_escape_string($UserName) (reference)

edited Nov 17, 2023 at 20:38

CommunityBot

11 silver badge

answered May 18, 2012 at 18:40

ametren

2,25616 silver badges20 bronze badges

Comments

woozy · Accepted Answer · 2012-05-18 18:56:03Z

1

Try this:

$insertResult=mysql_query("INSERT INTO TempChangePass(UserName, NewPass, ConfirmationCode) VALUES('$UserName', '$newPass', '$code')") or die (mysql_error());

You have some extra spaces in your SQL.

answered May 18, 2012 at 18:56

woozy

1481 silver badge7 bronze badges

Comments

Xpleria · Accepted Answer · 2017-11-15 09:10:47Z

1

try using a sanitizing script before you make the query.

use

mysql_real_escape_string()

EDIT

You should now use the MySQLi version

mysqli_real_escape_string()

or OOP method

mysqli::real_escape_string()

Why use MySQLi instead of MySQL?

edited Nov 15, 2017 at 9:10

answered May 18, 2012 at 18:51

Xpleria

5,9625 gold badges55 silver badges67 bronze badges

Collectives™ on Stack Overflow

mysql error with insert command

6 Answers 6

7 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

7 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related