I am trying to learn the best way to write queries. I also understand the importance of being consistent. Until now, I have randomly used single quotes, double quotes, and backticks without any real thought.
Example:
$query = 'INSERT INTO table (id, col1, col2) VALUES (NULL, val1, val2)';
Also, in the above example, consider that table
, col1
, val1
, etc. may be variables.
What is the standard for this? What do you do?
I've been reading answers to similar questions on here for about 20 minutes, but it seems like there is no definitive answer to this question.
Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double. MySQL also expects DATE and DATETIME literal values to be single-quoted as strings like '2001-01-01 00:00:00' .
Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes generally aren't used in SQL, but that can vary from database to database. Stick to using single quotes.
Backticks ( ` ) are used to indicate database, table, and column names. Unless you're using reserved or conflicting words for table and database names, you'll not need to use them.
Metacharacters are characters in a system (command interpreter, file system, or database management system, for example) that have special meanings. Single and double quotes, for example, are used as string delimiters in SQL queries. They are used at both the start and end of a string.
Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.
Single quotes should be used for string values like in the VALUES()
list. Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double.
MySQL also expects DATE
and DATETIME
literal values to be single-quoted as strings like '2001-01-01 00:00:00'
. Consult the Date and Time Literals documentation for more details, in particular alternatives to using the hyphen -
as a segment delimiter in date strings.
So using your example, I would double-quote the PHP string and use single quotes on the values 'val1', 'val2'
. NULL
is a MySQL keyword, and a special (non)-value, and is therefore unquoted.
None of these table or column identifiers are reserved words or make use of characters requiring quoting, but I've quoted them anyway with backticks (more on this later...).
Functions native to the RDBMS (for example, NOW()
in MySQL) should not be quoted, although their arguments are subject to the same string or identifier quoting rules already mentioned.
Backtick (`) table & column ───────┬─────┬──┬──┬──┬────┬──┬────┬──┬────┬──┬───────┐ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ $query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`, `updated`) VALUES (NULL, 'val1', 'val2', '2001-01-01', NOW())"; ↑↑↑↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑↑↑↑↑ Unquoted keyword ─────┴┴┴┘ │ │ │ │ │ │ │││││ Single-quoted (') strings ───────────┴────┴──┴────┘ │ │ │││││ Single-quoted (') DATE ───────────────────────────┴──────────┘ │││││ Unquoted function ─────────────────────────────────────────┴┴┴┴┘
The quoting patterns for variables do not change, although if you intend to interpolate the variables directly in a string, it must be double-quoted in PHP. Just make sure that you have properly escaped the variables for use in SQL. (It is recommended to use an API supporting prepared statements instead, as protection against SQL injection).
// Same thing with some variable replacements // Here, a variable table name $table is backtick-quoted, and variables // in the VALUES list are single-quoted $query = "INSERT INTO `$table` (`id`, `col1`, `col2`, `date`) VALUES (NULL, '$val1', '$val2', '$date')";
When working with prepared statements, consult the documentation to determine whether or not the statement's placeholders must be quoted. The most popular APIs available in PHP, PDO and MySQLi, expect unquoted placeholders, as do most prepared statement APIs in other languages:
// PDO example with named parameters, unquoted $query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (:id, :col1, :col2, :date)"; // MySQLi example with ? parameters, unquoted $query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (?, ?, ?, ?)";
According to MySQL documentation, you do not need to quote (backtick) identifiers using the following character set:
ASCII:
[0-9,a-z,A-Z$_]
(basic Latin letters, digits 0-9, dollar, underscore)
You can use characters beyond that set as table or column identifiers, including whitespace for example, but then you must quote (backtick) them.
Also, although numbers are valid characters for identifiers, identifiers cannot consist solely of numbers. If they do they must be wrapped in backticks.
There are two types of quotes in MySQL:
'
for enclosing string literals`
for enclosing identifiers such as table and column namesAnd then there is "
which is a special case. It could be used for one of above-mentioned purposes at a time depending on MySQL server's sql_mode
:
"
character can be used to enclose string literals just like '
ANSI_QUOTES
mode the "
character can be used to enclose identifiers just like `
SELECT "column" FROM table WHERE foo = "bar"
The query will select the string literal "column"
where column foo
is equal to string "bar"
The query will select the column column
where column foo
is equal to column bar
"
so that your code becomes independent of SQL modesIf you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With