奇怪的mysqli未知的错误？ [重复]

Question

This question already has an answer here:

• When to use single quotes, double quotes, and backticks in MySQL 12 answers

can someone see the differences between this

$query = "INSERT INTO users ('username', 'password', 'email', 'firstname', 'lastname', 'user_level') VALUES ('$username', '$password', '$email', '$firstname', '$lastname', '1')";

and this

$query_2 = "INSERT INTO users (`username`, `password`, `email`, `firstname`, `lastname`, `user_level`) VALUES ('$username', '$password', '$email', '$firstname', '$lastname', '1')";

only the second one works but the first one doesn't but it look exactly the same to me.

</div>

Answer 1

The difference between the two are simple.

One is using regular quotes ' around the column names that are mostly used to identify string literal values, as opposed to backticks which are used for escaping table/column names.

Read up on string literals and identifier qualifiers:

• http://dev.mysql.com/doc/en/string-literals.html
• http://dev.mysql.com/doc/en/identifier-qualifiers.html

Those are things you need to know the difference for, when dealing with databases.

Backticks are mostly used whenever a table/column name contains a space, a hyphen, or is a MySQL reserved word.

Sidenote: If none of your column names contains spaces, hyphens or isn't a reserved word, you can just omit the ticks altogether.

Reference:

• https://dev.mysql.com/doc/refman/5.5/en/keywords.html

Using this example would fail.

SELECT column_name FROM my-table

would produce 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 '-

What is happening here is that, my-table would be interpreted as my MINUS table and MySQL thinks you want to do a mathematical problem.

This would be the same scenario for your INSERT query.

• The INSERT would produce the following error:

Fatal error: Uncaught exception 'mysqli_sql_exception' with message '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'

Whereas:

SELECT `column 1` FROM `my-table`

with backticks would be valid and won't produce syntax errors.

The first line of code would have produced syntax errors.

"weird mysqli unknown mistake?"

• It's only "weird" when things are unclear/unknown.

Important note about using quotes or a space in SELECT:

SELECT user name FROM table

would produce the following:

Unknown column 'user' in 'field list'

while SELECT 'username' FROM table WHERE username='".$username."'

would not produce an error, but strangely enough give you an entirely different result than expected.

In regards to the values you are presently using, may be leaving you open to an SQL injection.

If you haven't properly escaped those values, use a prepared statement.

References:

• How can I prevent SQL injection in PHP?
• http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php
• http://php.net/pdo.prepared-statements

Error checking.

Depending on the MySQL API used to connect with which is unknown, checking for errors would have thrown you something about it.

Use the respective method:

• http://php.net/manual/en/function.mysql-error.php (MySQL_ API) - Deprecated
• http://php.net/manual/en/mysqli.error.php (MySQLi_ API)
• http://php.net/manual/en/pdo.error-handling.php (PDO API)

An mysqli_ example:

mysqli_query(...) or die(mysqli_error($connection));