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 back ticks without any real thought.

Example:

$query = 'INSERT INTO table (id, col1, col2) VALUES (NULL, val1, val2)';

Also, in the above example, consider that "table," "col[n]," and "val[n]" 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 upvote
  flag
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

11 Answers 11

up vote 428 down vote accepted

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.

27 upvote
  flag
"but single quotes are more widely accepted by other RDBMS" - using single quotes for string literals is defined (and required) by the SQL standard – a_horse_with_no_name
upvote
  flag
@a_horse_with_no_name almost no one use ANSI MySQL ('|' for string concat - really?) – Good Person
1 upvote
  flag
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'" – Kick_the_BUCKET
upvote
  flag
@Kick_the_BUCKET Do you have an alternative suggestion? Leaving them unquoted results in them being interpreted as integer arithmetic. It is true that different delimiters than - may be used, but quoting is necessary unless no delimiters are present dev.mysql.com/doc/refman/5.7/en/date-and-time-literals.html – Michael Berkowski
upvote
  flag
upvote
  flag
What about column alias and table alias, as in select col1 as c from my_table, for the alias "c", is it better to wrap it with single quote or backtick? – evilReiko
1 upvote
  flag
@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. – Michael Berkowski
upvote
  flag
@MichaelBerkowski You need to quote aliases if they are reserved keywords. The SQL standard is that string literals and dates are wrapped in single quotes. Objects/entities are wrapped in double quotes. MySQL is a weird (albeit pervasive) exception with the use of backticks ``, and SQL Server is kind of a weird (albeit pervasive) exception with the use of square brackets []. In both cases, it is possible to use double quotes and I personally feel like that is a good idea because it enhances portability and readability across different SQL dialects. – Craig

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 & 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')

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` <-- Note: exclude . from back ticks.

(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

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.)

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)

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.

upvote
  flag
Therefore double quotes are flexible but can't be used as identifiers. – rhavendc
upvote
  flag
Please never ever directly use unescaped user input in your query! – jankal
upvote
  flag
@jankal It's just example.I specified that if you are using direct user input then n then........... – vipul sorathiya
upvote
  flag
@vipulsorathiya Please specify in your answer that POST variables should be escaped. You're now pointing at using them direct in your query. Bad for beginners who try this... – RFLdev

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.

Basically in Mysql, There are these kinds of identifier are used in query ` ," ,' and () .

  1. " or ' use for enclosing the string like values "26-01-2014 00:00:00" or '26-01-2014 00:00:00' . These identifier use only for string not aggregate function like now() or sum ,max etc.

  2. ` use for enclosing table or table column e.g. select column_name from table_name where id='2'

  3. () are use only for just enclose parts of query e.g. select column_name from table_name where (id='2' and gender='male') or name='rakesh' .

There has been many helpful answers here, generally culminating into two points.

  1. BACKTICKS(`) are used around identifier names.
  2. 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]> create temporary table `123456e6` (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

ERROR when not using backticks.

DB [XXX]> 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 backticks).

DB [XXX]> 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.

Besides all of the (well-explained) answers, there hasn't been the following mentioned and I visit this Q&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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.