PHP and mySQL single quote or double quote?

Question

I don't see any difference using single quote or double quotes. When or why should I use the first instead of the latter?

I've read some where that for SQL I should use singles - why?

Answer 1

You should use the combination of single quotes and mysql_real_escape_string or move into PDO.

The mysql_real_escape_string will take care of cleanning any unclean variable you want to store on your database and the single quotes will prevent any execution inside it.

Here is an example:

$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($password));

PDO's example:

$dbh = new PDO('mysql:dbname=your_database;host=your_host', $user, $password);
$stmt = $dbh->prepare('SELECT * FROM users WHERE user = :username AND password = :password');
$stmt->execute(array('username' => $user, 'password' => $password)); 

If you are not properly cleanning the data you are about to store you can suffer from SQL Injection.

For example a normal query

SELECT * FROM users WHERE user = 'test'

injected query:

SELECT * FROM users WHERE user = 'anything' OR 'x'='x'

another example:

SELECT * FROM users WHERE user = ' '; DROP TABLE users

More about SQL injection attack: SQL injection prevention, MySQL Injection

Why you should be using php's PDO for database access

Answer 2

If your MySQL server is running with the --ansi flag, then "double" quotes will stop to work. SQL strings must be enclosed in 'single' quotes.

Or more specifically, double quotes actually behave like backticks. They can be used for column names:

SELECT "colum&name", `x&y` FROM "fictional-query"
 WHERE strcolumn = 'text' OR "column&name" = 'string'

The very same would however fail if mysqld is not running in the ansi compliant mode. Only then are double quotes equivalent to single quotes for strings. That's why they should be avoided.

Answer 3

Use whichever is easier or more convenient. If they are all the same to you, then single quote is more portable, at least theory.

If the query is being generated from—for example—PHP, then single quotes are more convenient writing SQL queries since double quotes allow a PHP expression to substitute variables.

$query = "SELECT * FROM $sometable WHERE somefield = '1'";

According to PHP rules, single quotes (had they been used) prevent the expansion of $sometable.

As for theoretical portability, I've used Oracle, MySQL, MS Sql server, SQL Lite, and some home grown flavors, and never had a problem with either quote character.

Answer 4

Single quotes are more restrictive in PHP. For example, single quotes will not allow variables to be replaced inside of the string.

$myval = "world";
echo "Hello $myval";   // produces "Hello world"
echo 'Hello $myval';   // produces "Hello $myval"

Also, you would think that, since single-quotes are more strict, they would be more efficient too, but that's not the case. PHP string manipulation performance is a funny (and documented) thing as seen here.

Answer 5

Strings enclosed in double quotes are the most commonly used in PHP scripts because they offer the most flexibility. This is because both variables and escape sequences will be parsed accordingly.

Enclosing a string within single quotes is useful when the string should be interpreted exactly as stated. This means that both variables and escape sequences will not be interpreted when the string is parsed.

Double quotes come in handy when you have single quotes in your mysql syntax

$query = "SELECT * FROM tableName WHERE columnName = '{$value}'";