Tutorial :SQL syntax error



Question:

I cannot find the error in the following sql:

$query = "INSERT INTO users('username', 'password', 'key', 'email', 'rank',   'ip','active') VALUES ('$username','$password','$random','$email','1','$ip',  '0')";  

For some reason I keep getting the error

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', 'password', 'key', 'email', 'rank', 'ip', 'active') VALUES ('wx','79' at line 1


Solution:1

transform

('username', 'password', 'key', 'email', 'rank', 'ip', 'active')    

to

(`username`, `password`, `key`, `email`, `rank`, `ip`, `active`)    

In MySQL, field names should either be un-quoted or backticked (enclosed in back-ticks or back quotes).

In MS SQL Server, field names should either be unquoted or enclosed in [square brackets].

Other SQL DBMS mostly follow the SQL standard, and field names should either be unquoted or enclosed in "double quotes", and are then called 'delimited identifiers'. Sometimes, you have to turn on delimited identifier handling (which is itself non-standard behaviour).


Solution:2

Lose the quotes around the column names.


Solution:3

Don't put the column names in single quotes:

$query = "INSERT INTO users(username, password, key, email, rank, ip, active)             VALUES ('$username','$password','$random','$email','1','$ip','0')";  

We'll ignore the SQL injection problems for now. :-)


Solution:4

"right syntax to use near ''user..." is a good hint. You should drop the ' ' around the column names.


Solution:5

In addition to removing the apostrophes around the names as already has been mentioned, you may need to specify some of the names as identifiers if they are reserved keywords in your database provider.

For SQL Server you would use brackets to specify an identifier: [password], however from your error message it seems as you are using MySQL, which uses backticks (`) instead of brackets. (As this forum uses backticks for code blocks I haven't been able to write an example, but I think that you get it anyway.)

Depending on the data types in your table you may also have to remove apostrophes around some values. If for example rank is a numeric field, there should be no apostrophes around the number 1 in the values.


Solution:6

Judging from where the error occurred, you're not escaping the data before sending it to the database, and the third character in the password was an apostrophe (').

Depending on which MySQL API you're using, you'll want one of the following functions: mysql_real_escape_string, mysqli_real_escape_string, or pdo_quote to escape each variable before passing it to the database.

Alternately, if you're using MySQLi or PDO, use prepared statements with bind parameters which does this for you.


Solution:7

Get rid of the single quotes around your field names. The error message is giving you a hint to your problem.

check the manual that corresponds to your MySQL server version for the right syntax to use near ''username', 'password', 'key', ...


Solution:8

INSERT INTO users('username', ...

As pointed out by Itay, there is no need for single quotes here. If you want/need to use reserved words as names, you have to delimit them in something else, either:

  • `backticks`, the default MySQL way, or
  • "doublequotes", the standard ANSI SQL way supported by other databases.

It's not a bad idea to set the sql_mode of your connection (or the server default, if that wouldn't interfere with other applications) to allow ANSI_QUOTES, if you would like to use cross-DBMS-compatible code here.

('$username', ...

Oops! Unless you processed $username to escape it previously, you've just invited a lovely SQL injection security hole into your applications. See mysql_real_escape_string or, better, use parameterised queries to avoid this.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »