What's wrong with this query?

Question

Someone help me:

$query = "INSERT INTO tbl_users(user, password, password_def, userid, level
          , regdate, lastdate, email) VALUES('$username', sha1('$password')
          , sha1('$password'), '$userid', '0', NOW(), NOW(), '$email');";

$userid is a ramdon md5 id.

It gives me this error:

posttokenError: Account not created 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 '\'esck21\', sha1(\'password\'), sha1(\'password\'), 
    \'14bd25cbe111c2975232b33ee8c2' at line 1

I think I'm gonna have a heart attack. Thanks.

Answer 1

You need to backtick (`) the password field:

$query = "INSERT INTO `tbl_users` (`user`, `password`, `password_def`, `userid`
              , `level`, `regdate`, `lastdate`, `email`) 
          VALUES('$username', 'sha1($password)', 'sha1($password)', '$userid'
              , '0', NOW(), NOW(), '$email');";

You should always backtick your fields, tables and databases.

One more thing: pay attention to SQL Injections, use mysql_real_escape_string().

---

After some discussion I'm conviced your problem lies with your quoting usage, check zombat answer.

Answer 2

Judging by the error message, it looks like you might be calling some kind of escaping function on the entire query, such as addslashes($query) or mysql_real_escape_string($query). This will escape every quote in the query, when really what you want to do is only escape the quotes that are inside your variables.

If this is the case, then you want to be doing something like this instead:

$query = "INSERT INTO tbl_users(user, password, password_def, userid, level,
regdate,lastdate, email) VALUES('".mysql_real_escape_string($username)."',
sha1('".mysql_real_escape_string($password)."'), 
sha1('".mysql_real_escape_string($password)."'), 
'".mysql_real_escape_string($userid)."', '0', 
NOW(), NOW(), '".mysql_real_escape_string($email)."')";

That will properly escape your data without erroneously escaping the rest of the query. Once you've done this, do not run $query as a whole string through other forms of escaping.