When to use single quotes double quotes and backticks in MySQL

Question

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

User · Accepted Answer

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.

User · Answer

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

User · Answer

There are two types of quotes in MySQL     for enclosing string literals   for enclosing identifiers such as table and column names  And then there is  quot  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   By default the  quot  character can be used to enclose string literals just like   In ANSI QUOTES mode the  quot  character can be used to enclose identifiers just like    The following query will produce different results  or errors  depending on SQL mode  SELECT  quot column quot  FROM table WHERE foo    quot bar quot   ANSI QUOTES disabled The query will select the string literal  quot column quot  where column foo is equal to string  quot bar quot  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  quot  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

User · Answer

SQL servers and MySQL  PostgreySQL  Oracle don t understand double quotes     Thus your query should be free from double quotes    and should only use single quotes      Back-trip    is optional to use in SQL and is used for table name  db name and column names   If you are trying to write query in your back-end to call MySQL then you can use double quote    or single quotes    to assign query to a variable like   let query    select id  name from accounts     Or let query    select id  name from accounts     If ther s a where statement in your query and or trying to insert a value and or an update of value which is string use single quote    for these values like   let querySelect    select id  name from accounts where name    John    let queryUpdate    update accounts set name    John  where id   8   let queryInsert    insert into accounts name  values  John        Please not that double quotes are only to be used in assigning string to our variable not in the query   All these below will generate error  let querySelect    select id  name from accounts where name    John    let queryUpdate    update accounts set name    John  where id   8   let queryInsert    insert into accounts name  values  John        As MySQL or any SQL doesn t understand double quotes     these all will generate error    If you want to stay out of this confusion when to use double quotes    and single quotes     would recommend to stick with single quotes    this will include backslash   like   let query    select is  name from accounts where name     John       Problem with double    or single    quotes arise when we had to assign some value dynamic and perform some string concatenation like   let query    select id  name from accounts where name       fName         lName    This will generate error as it must be like name    John Smith  for SQL   However our statement made it like name   John Smith    In order to resolve such errors use let query    select id  name from accounts where name        fName         lName           Or using backslash    let query    select id  name from accounts where name         fName         lName           If need further clearance do follow quotes in JavaScript

User · Answer

Single quotes should be used for string values like in the VALUES   list   Backticks are generally used to indicate an identifier and as well be safe from accidentally using the reserved keywords   In combination of PHP and MySQL  double quotes and single quotes make your query writing time so much easier

User · Answer

Besides all of the  well-explained  answers  there hasn t been the following mentioned and I visit this Q amp A quite often   In a nutshell  MySQL thinks you want to do math on its own table column and interprets hyphens such as  e-mail  as e minus mail      Disclaimer  So I thought I would add this as an  FYI  type of answer for those who are completely new to working with databases and who may not understand the technical terms described already

User · Answer

The string literals in MySQL and PHP are the same      A string is a sequence of bytes or characters  enclosed within either   single quote           or double quote           characters    So if your string contains single quotes  then you could use double quotes to quote the string  or if it contains double quotes  then you could use single quotes to quote the string  But if your string contains both single quotes and double quotes  you need to escape the one that used to quote the string   Mostly  we use single quotes for an SQL string value  so we need to use double quotes for a PHP string    query    INSERT INTO table  id  col1  col2  VALUES  NULL   val1    val2       And you could use a variable in PHP s double-quoted string    query    INSERT INTO table  id  col1  col2  VALUES  NULL    val1     val2       But if  val1 or  val2 contains single quotes  that will make your SQL be wrong  So you need to escape it before it is used in sql  that is what mysql real escape string is for   Although a prepared statement is better

User · Answer

If table cols and values are variables then there are two ways   With double quotes    the complete query    query    INSERT INTO  table name  id   col1   col2                   VALUES  NULL    val1     val2       Or    query    INSERT INTO    table name    id     col1       col2                   VALUES  NULL      val1         val2         With single quotes       query    INSERT INTO    table name    id     col1       col2                 VALUES  NULL     val1       val2        Use back ticks    when a column value name is similar to a MySQL reserved keyword   Note  If you are denoting a column name with a table name then use back ticks like this    table name    column name     lt -- Note  exclude    from back ticks

User · Answer

In combination of PHP and MySQL  double quotes and single quotes make your query-writing time so much easier     query    INSERT INTO  table    id    col1    col2   VALUES  NULL    val1     val2       Now  suppose you are using a direct post variable into the MySQL query then  use it this way    query    INSERT INTO  table    id    name    email   VALUES        POST  id              POST  name              POST  email            This is the best practice for using PHP variables into MySQL

User · Answer

It is sometimes useful to not use quotes    because this can highlight issues in the code generating the query    For example  Where x and y are should always be integers    SELECT   FROM table WHERE x  AND y 0 Is a SQL syntax error    a little lazy but can be useful

User · Answer

In MySQL  these symbols are used to delimit a query         and          or   are used for enclosing string-like values  26-01-2014 00 00 00  or  26-01-2014 00 00 00    These symbols are only for strings  not aggregate functions like now  sum  or max     is used for enclosing table or column names  e g  select  column name  from  table name  where id  2    and   simply enclose parts of a query e g    select  column name  from  table name  where  id  2  and gender  male   or name  rakesh

User · Answer

There has been many helpful answers here  generally culminating into two points    BACKTICKS    are used around identifier names  SINGLE QUOTES    are used around values     AND as  MichaelBerkowski said     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    There is a case though where an identifier can neither be a reserved keyword or contain whitespace or characters beyond limited set but necessarily require backticks around them   EXAMPLE  123E10 is a valid identifier name but also a valid INTEGER literal    Without going into detail how you would get such an identifier name   Suppose I want to create a temporary table named 123456e6   No ERROR on backticks   DB  XXX  gt  create temporary table  123456e6    id  char  8    Query OK  0 rows affected  0 03 sec    ERROR when not using backticks   DB  XXX  gt  create temporary table 123451e6   id  char  8    ERROR 1064  42000   You have an error in your SQL syntax  check the manual that corresponds to your MariaDB server version for the right syntax to use near  123451e6   id  char  8    at line 1   However  123451a6 is a perfectly fine identifier name  without back ticks    DB  XXX  gt  create temporary table 123451a6   id  char  8    Query OK  0 rows affected  0 03 sec    This is completely because 1234156e6 is also an exponential number

User · Answer

Backticks are generally used to indicate an identifier and as well be safe from accidentally using the Reserved Keywords    For example   Use  database     Here the backticks will help the server to understand that the database is in fact the name of the database  not the database identifier   Same can be done for the table names and field names  This is a very good habit if you wrap your database identifier with backticks      Check this answer to understand more about backticks      Now about Double quotes  amp  Single Quotes  Michael has already mentioned that    But  to define a value you have to use either single or double quotes  Lets see another example   INSERT INTO  tablename    id   title   VALUES   NULL  title1     Here I have deliberately forgotten to wrap the title1 with quotes  Now the server will take the title1 as a column name  i e  an identifier   So  to indicate that it s a value you have to use either double or single quotes   INSERT INTO  tablename    id   title   VALUES   NULL   title1        Now  in combination with PHP  double quotes and single quotes make your query writing time much easier  Let s see a modified version of the query in your question    query    INSERT INTO  table    id    col1    col2   VALUES  NULL    val1     val2       Now  using double quotes in the PHP  you will make the variables  val1  and  val2 to use their values thus creating a perfectly valid query  Like   val1    my value 1    val2    my value 2    query    INSERT INTO  table    id    col1    col2   VALUES  NULL    val1     val2       will make  INSERT INTO  table    id    col1    col2   VALUES  NULL   my value 1    my value 2

[mysql] When to use single quotes, double quotes, and backticks in MySQL

Variable interpolation

Prepared statements

Characters requring backtick quoting in identifiers:

Examples related to mysql

Examples related to sql

Examples related to quotes