1

Is there any differences in using single quotes vs. using double quotes around a whole SQL query?

Which is better:

This approach (with single quotes):

    $username = mysql_real_escape_string($username);
    $password = mysql_real_escape_string($password);

    $sql = 'SELECT * FROM users WHERE username = "' . $username . '" AND password = "' . $password . '" LIMIT 1';

?

Or this approach (using double quotes):

    $username = mysql_real_escape_string($username);
    $password = mysql_real_escape_string($password);

    $sql = "SELECT * FROM users WHERE username = '{$username}' AND password = '{$password}' LIMIT 1";

Is there a better way to accomplish this?

For me I like the first approach as I always prefer single quotes in PHP. So I want to make sure that using single quotes around a whole SQL query is OK and using double quotes around variables or data is OK and is cross-platform and could be used with databases other than MySQL!

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
XO39
  • 481
  • 2
  • 9
  • 22
  • I may be mistaken but I recall interpolation (your second approach) is faster than concatenation (your first) – Phil Aug 29 '11 at 00:52
  • 3
    Neither are recommended practice because of susceptibility to [SQL Injection attacks](http://xkcd.com/327/). PDO or prepared statements are highly recommended. – OMG Ponies Aug 29 '11 at 00:52
  • @OMG Ponies: Even though they're using `mysql_real_escape_string`? – icktoofay Aug 29 '11 at 00:53
  • The only difference is that one of things makes less effort than the other. And while MySQL is forgiving (hinges on the config!), only single quotes are valid for enclosing strings within SQL. – mario Aug 29 '11 at 00:54
  • 1
    possible duplicate of [PHP e mySQL single quote or double quote?](http://stackoverflow.com/questions/5877892/php-e-mysql-single-quote-or-double-quote) – mario Aug 29 '11 at 00:55
  • @icktoofay Escaping strings for SQL parameters is definitely not safe. Consider an unquoted integer parameter that just happens to have extra SQL appended – Phil Aug 29 '11 at 01:07
  • @Phil: That's true for integers, but we're talking strings here. – icktoofay Aug 29 '11 at 01:13
  • Thanks everyone for your input, good points there. – XO39 Aug 29 '11 at 03:42
  • [Douglas Crockford wants to get rid of the single quote](https://www.youtube.com/watch?v=MBWAP_8zxaM&t=10m45s). – Peter Mortensen Jul 16 '19 at 02:08

2 Answers2

10

Use PDO instead of either of these approaches. It will allow you to use parameters instead of strings.

$sth = $dbh->prepare('SELECT * FROM users WHERE username = :username AND password = :password LIMIT 1');
$sth->bindParam(':username', $username, PDO::PARAM_STR);
$sth->bindParam(':password', $password, PDO::PARAM_STR);
$sth->execute();

By the way, make sure that you're not using passwords in plain text at the same time.

Amir Raminfar
  • 33,777
  • 7
  • 93
  • 123
acrosman
  • 12,814
  • 10
  • 39
  • 55
  • Thanks for your answer. One thing, can you explain to me what do you mean by "make sure that you're not using password in plain text at the same time"? I can't get what you mean (maybe because I'm too sleepy and my brain almost stopped thinking!). – XO39 Aug 29 '11 at 03:50
  • @XO39, The sample you gave implied that you're storing passwords the way people enter them. This is bad! You should never store passwords in plain text. You should always pass them through a one-way crypto function with a salt. So if someone accesses your database you have not given them access to everyone's accounts all over the place. See: http://stackoverflow.com/questions/1581610/how-can-i-store-my-users-passwords-safely – acrosman Sep 04 '11 at 03:44
  • Thanks again for your help. I know that, I just want to about which syntax/code is better. Thanks again! :) – XO39 Oct 29 '11 at 04:13
3

They are both equally horrid. :) Forget that you ever heard of mysql_real_escape_string and use PDO as acrosman mentioned. Specifically look at the prepare statement: http://www.php.net/manual/en/pdo.prepare.php. This is especially important as you even say: "is cross-platform and could be used with databases other than MySQL!" Not trying to come off as a jerk, but hint: code that calls to methods that start with mysql_ is probabl not that cross-platform ;). Using PDO however, will allow you to use databases other than Mysql. Of course, the obvious caveat here of different flavors of sql, but at least you don't have different flavors of what strings need to be escaped if you use parameterized queries.

aquinas
  • 23,318
  • 5
  • 58
  • 81