Categories
mysql quotes sql

When to use single quotes, double quotes, and backticks in MySQL

740

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.

1

  • 20

    Note that this is a very MySQL specific question. SQL in general (i.e. ISO/ANSI SQL) has a different set of quotes: double quotes are for delimited identifiers, e.g. "tablename", and single quotes are for literals, e.g. 'this is a some text'. Back-ticks are never used in standard SQL. (If you need to include a double quote in an identifier, type it twice as "odd""tablename". Similarly, double single quotes in literals, like 'Conan O''Brien'.)

    – jarlh

    Nov 25, 2016 at 9:02


717

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         ─────────────────────────────────────────┴┴┴┴┘    

Variable interpolation

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')";

Prepared statements

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 (?, ?, ?, ?)";

Characters requring backtick quoting in identifiers:

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.

13

  • 52

    but single quotes are more widely accepted by other RDBMS” – using single quotes for string literals is defined (and required) by the SQL standard

    Jul 14, 2014 at 8:57

  • 5

    this isn’t true: “MySQL also expects DATE and DATETIME literal values to be single-quoted as strings like ‘2001-01-01 00:00:00′”

    Apr 12, 2016 at 14:59

  • 4

    @evilReiko MySQL docs don’t seem to address alias quoting clearly. It will accept single, double, or backtick for aliases but that may be affected by different ANSI SQL modes. I’m not sure what the SQL spec requires for alias quotes — Personal preference: for consistency I quote them the same as column identifiers – that is, I either backtick them if needed, or leave them unquoted if not. I don’t use single or double quotes on aliases.

    Aug 16, 2017 at 15:36

  • 3

    @GuneyOzsan Yes, very vulnerable. Never ever use a variable for a table name unless it has been validated against a list of acceptable table names – create an array of permissible names and check the variable matches something in the list to make it safe to use. Otherwise you cannot safely escape a table name variable for use.

    Oct 27, 2018 at 1:55

  • 2

    Michael all sorted now thanks. Might I suggest editing the chart to add bound variables. It is absolutely gorgeous and if another poor soul came along checking on :whatever that would be even more lovely. ATB Steve

    – BeNice

    Dec 6, 2019 at 11:31

150

There are two types of quotes in MySQL:

  1. ' for enclosing string literals
  2. ` for enclosing identifiers such as table and column names

And 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:

  1. By default the " character can be used to enclose string literals just like '
  2. In ANSI_QUOTES mode the " character can be used to enclose identifiers just like `

The following query will produce different results (or errors) depending on SQL mode:

SELECT "column" FROM table WHERE foo = "bar"

ANSI_QUOTES disabled

The query will select the string literal "column" where column foo is equal to string "bar"

ANSI_QUOTES enabled

The query will select the column column where column foo is equal to column bar

When to use what

  • I suggest that you avoid using " so that your code becomes independent of SQL modes
  • Always quote identifiers since it is a good practice (quite a few questions on SO discuss this)

2

  • Or alternatively, run your MySQL in ANSI_QUOTES mode to make your SQL skillset more portable, and use ' for strings, " for identifiers and do not use backticks. Adhering to standards is always good

    Mar 17, 2021 at 17:03


  • For others on how to enable ANSI_QUOTES in a session level: SET SESSION sql_mode = 'ANSI_QUOTES'; or SET SESSION sql_mode = 'ANSI'; . You can then SELECT @@SESSION.sql_mode; to check the value. If you want to restore to the default, the default for MySQL 5.7 is : SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

    – Rick

    Jan 14 at 9:59


37

(There are good answers above regarding the SQL nature of your question, but this may also be relevant if you are new to PHP.)

Perhaps it is important to mention that PHP handles single and double quoted strings differently…

Single-quoted strings are ‘literals’ and are pretty much WYSIWYG strings. Double-quoted strings are interpreted by PHP for possible variable-substitution (backticks in PHP are not exactly strings; they execute a command in the shell and return the result).

Examples:

$foo = "bar";
echo 'there is a $foo'; // There is a $foo
echo "there is a $foo"; // There is a bar
echo `ls -l`; // ... a directory list