3

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

Shady
  • 1,701
  • 2
  • 13
  • 14
  • 3
    Is there an `'` in your username variable?? `print_r($UserName)`. Then, take go meet [Bobby tables](http://bobby-tables.com/), and read up on [prepared statements](http://php.net/manual/en/book.pdo.php) – PenguinCoder May 18 '12 at 18:41
  • 1
    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 May 18 '12 at 18:41

6 Answers6

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.

etm124
  • 2,100
  • 4
  • 41
  • 77
  • oh , so when dealing with varchars, there's no need to put quotes? – Shady May 18 '12 at 18:41
  • 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. – etm124 May 18 '12 at 18:42
  • 1
    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. – jeffjenx May 18 '12 at 18:44
  • aha, well I used your code and I get exactly the same error :( – Shady May 18 '12 at 18:47
  • also used the curly braces and still same thing – Shady May 18 '12 at 18:47
  • Can you echo out `$sql` and then post the query. – etm124 May 18 '12 at 18:47
  • You are right i got double quotes around username afer i echoed it , so i removed the quotes only around username and seems it working good now :) , I actually thought i tried that, thanks alot :) – Shady May 18 '12 at 18:54
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)

ametren
  • 2,186
  • 15
  • 19
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());

Cito
  • 1,659
  • 3
  • 22
  • 49
  • I saw that extra quote , and i thought like: umm, this quote shouldn't be here, but my username is like this: $UserName='username'; – Shady May 18 '12 at 18:43
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.

KittyKris
  • 41
  • 5
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?

Xpleria
  • 5,472
  • 5
  • 52
  • 66
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.

woozy
  • 148
  • 7