MySQL Syntax Error; "check the manual that corresponds to your MySQL server version.."

Question

I've been looking all over the internet for a solution to the following 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 'primary, username, password, password2) VALUES (null, 'hello', 'hello', 'hello')' at line 1"

I have no idea what is going on.. I know you will ask what my code is so here:

$con = mysql_connect("localhost","root","*****");
    if (!$con)
      {
      die('Server overload, please try again' . mysql_error());
      }

    mysql_select_db("users", $con);

    $sql = "INSERT INTO details (primary, username, password, password2) VALUES (null, '$_POST[username]', '$_POST[password]', '$_POST[password2]')";

    if (!mysql_query($sql,$con))
      {
      die('Error: Server overload, try again' . mysql_error());
      }
    echo "You have signed up successfully!";

    mysql_close($con);

I've been trying to figure it out for around 4/5 hours now and have had no success.
Thanks,
Lawrence

Answer 1

Primary is a reserved word. What is the table definition?

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

Answer 2

I'd rename that first column to something else: "primary" is a reserved word in MySQL:

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

Answer 3

primary is a reserved keyword, in SQL, which means that you should either :

• rename that column -- would be a good idea, to avoid that kind od situation
• or use backticks arround that name

Here what the query would look like in the second case :

INSERT INTO details (`primary`, `username`, `password`, `password2`)
VALUES (null, 'hello', 'hello', 'hello')

Note : and you should escape your values, using mysql_real_escape_string, to avoid SQL Injections !

Answer 4

Try not to name your tables or columns with relitively common names like primary and details.

While they may not be reserved words in the flavor of SQL you are currently using, you never know when you might be supporting other types (Postgres, Oracle, etc.).

You can also use this handy-dandy reserved word checker.

Followup Question:
I would like to know who wrote the error statement you are getting, which essentially says RTFM? Hilarious. I'm going to use that in my next try catch. :)