What is the problem with this php script? [duplicate]

This question already has answers here: How can I write SQL for a table that shares the same name as a protected keyword in MySql? [duplicate] (3 answers) Closed 9 years ago.

I keep getting error 1064 in this line:

$sq开发者_JS百科lquery = "INSERT INTO user 
               (username, password, email, key) 
             VALUES 
                ('".$_POST["username"]."','".$_POST["password"]."','".$_POST["email"]."','".$activation."')";`

---

key is a reserved word which you're using in your query, this must be escaped with backticks. Reserved word error is 1064.

You should also consider learning some security theory particularly with regards to using unescaped values in a query (straight from a user).

The below code is both secure and fixed:

$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);
$email = mysql_real_escape_string($_POST['email']);
$sqlquery = "INSERT INTO `user` (`username`, `password`, `email`, `key`) VALUES ('{$username}','{$password}','{$email}','{$activation}')";

A simple rule when it comes to queries (well, anything) is to never trust user input. By using mysql_real_escape_string you're escaping the variables so that they're safe for insertion into the database. Without it, you could allow the user to run any query that they wanted to.

For future reference, here is a complete list of MySQL Reserved Words.

---

MySQL error 1064 generally means a SQL syntax error. Take a look at your SQL statement to make sure it's valid.

A good way to debug those kinds of errors is to print out the SQL, then try to execute it manually in MySQL.

---

Do you still get errors if you use this instead:

$query = sprintf("INSERT INTO user 
                    (username, password, email, `key`) 
                  VALUES 
                    ('%s','%s','%s','%s')",
                  mysql_real_escape_string($_POST["username"]),
                  mysql_real_escape_string($_POST["password"]),
                  mysql_real_escape_string($_POST["email"]),
                  mysql_real_escape_string($_POST["activation"]));

$result = mysql_query($query);

KEY is a MySQL reserved word -- it needs to be enclosed in backticks to escape its use in queries. Backticks are not necessary if not using reserved words...

---

Try surrounding each variable $var with mysql_real_escape_string(), such as

instead of $_POST["password"] use mysql_real_escape_string($_POST["password"])!

---

Taking user-defined values directly from the HTTP Request and concatenating them into an SQL query is B-A-D, and likely the source of your syntax error. Make sure you escape all values.