How to Properly Quote Identifiers and Values in MySQL?

MySQL Identifier Reference Specification

In MySQL, the use of single quotes, double quotes, and backticks in identifiers and values ​​depends on their purpose.

Backticks

Backticks are used to enclose table and column identifiers. Backticks are required when the following situations occur:

• Identifiers are MySQL reserved keywords.
• Identifiers contain spaces or special characters (for example, -, ).
• Identifiers consist only of numbers.

Single quotes

Single quotes are used for string values. This includes values ​​passed to functions. For example:

<code class="language-sql">INSERT INTO table (id, col1, col2) VALUES (NULL, 'val1', 'val2');
SELECT NOW() FROM table WHERE col1 = 'val1';</code>

Double quotes

Double quotes can also be used for string values, but single quotes are more commonly used and are more compatible with other relational database management systems.

Without quotation marks

MySQL keywords and functions should not be quoted.

Variable

When inserting variables directly into a string in PHP, use double quotes and make sure the variable is properly escaped to prevent SQL injection.

Prepared statements

However, when using prepared statements in PHP, placeholders for parameters should not be quoted.

Characters that require backtick

MySQL allows identifiers using the following character sets without backticks:

• ASCII: [0-9,a-z,A-Z,$_] (basic Latin letters, numbers 0-9, dollar sign, underscore)

Identifiers that use characters outside of this character set must be enclosed in backticks.

The above is the detailed content of How to Properly Quote Identifiers and Values in MySQL?. For more information, please follow other related articles on the PHP Chinese website!