One of the most common problems I come across is users not being able to resolve a basic SQL syntax error thrown in MySQL.
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 typically follows is syntax nearest to the error. The most common reasons for this error to be thrown is the following:
- Use of reserved words
- Use of backticks, single quotes and double quotes
Use of reserved words
Reserved words are words that have special meaning in MySQL. They are typically system related identifiers such as tables, columns, DML/DDL, and functions.
To properly quote reserved words for use in syntax, backticks are used to encase the word. For example, the reserved word desc
, would look like `desc`
.
While the use of reserved words is permitted, it’s always best to try to use more descriptive words. A messaging table could contain columns such as from
, to
, and date
. All these words are reserved, and when not quoted properly, will throw a syntax error. Therefore, it’s best to make these columns more descriptive. An alternative could consist of sender
, receiver
, sent
.
The complete list of reserved words as of MySQL 5.5.32 can be found in the documentation.
Use of backticks, single quotes and double quotes
Backticks and single quotes have a special meaning in MySQL. The reason I mention double quotes here is when used in string concatenation in PHP.
When referencing identifiers such as column or table names, backticks should be used to encase the word. While they are not necessary, it is better practice to consistently use them to avoid the potential occurrence of a reserved word. This also holds true when an identifier contains special characters.
Let’s take the following syntax examples:
INSERT INTO messages (id, from, to, date, subject, message) VALUES (1, 'Bob', 'Jane', '2013-06-29', 'Hi', 'How are you?') |
This example contains the from
and to
words in the column list, which are reserved. The values being inserted are escaped properly using single quotes. The properly quoted column list would look like:
INSERT INTO messages (`id`, `from`, `to`, `date`, `subject`, `message`) VALUES ... |
In the following example, we also use a column alias:
SELECT id AS user, name FROM users |
While id
, name
, and users
are not reserved words, they are identifiers and should be quoted. The column alias can either be quoted using backticks or single quotes.
SELECT `id` AS `user`, `name` FROM `users` |
A more comprehensive explanation on quoted and unquoted identifiers can be found in the documentation.
The use of double quotes is sometimes found when trying to perform string concatenation in PHP:
$stmt = "SELECT `col1`, `col2` FROM `table` WHERE `id` = '" . $userId . "'"; |
This type of string concatenation is poor practice as it attempts to provide cleaner (and perhaps safer) syntax, when it’s really setting the user up for confusion with quotes. This type of concatenation should be avoided altogether. The mysqli
and PDO
libraries support prepared statements. Most of the time, prepared statements make for cleaner code. More importantly, they are there to prevent SQL injection.