0

The statement in question is:

$sql="SELECT * FROM user WHERE id = '".$q."'";

Where q is an integer. Why type it like that when you can just type it like

$sql="SELECT * FROM user WHERE id = $q";

because it's an integer, it shouldn't require the quotes right, or even if it did require quotes, why not just one pair. What's the significance of '".$var_name."'?

Dave Chen
  • 10,887
  • 8
  • 39
  • 67

2 Answers2

2

It doesn't require any '', but in combination with *real_escape_string, to make it effective, you need these single quotes.

Because if the user input would be: 1 OR 1 = 1, then the query is:

SELECT * FROM user WHERE id = 1 OR 1 = 1

what would select the whole table.

But if you use single quotes with *real_escape_string and the user input is 1' OR '1' = '1, then the query would be:

SELECT * FROM user WHERE id = '1\' OR \'1\' = \'1'

what won't select the whole table, but just one id.

tl;dr: It is not needed, but it makes your code safe against sql injection.

bwoebi
  • 23,637
  • 5
  • 58
  • 79
  • +1, another approach without prepare would be `intval` or some freaky technique. – Dave Chen Jul 26 '13 at 21:58
  • Yes, I sometimes use `(int)` what looks a bit cleaner. But I prefer too many single quotes than not enough ;-) – bwoebi Jul 26 '13 at 21:59
  • Hey, thanks for helping me out, but I'm still a beginner and I still don't quite understand it. Would it be alright if I could get an explanation a little more detailed? –  Jul 26 '13 at 22:02
  • @user2624376 I'd consider to read this article in the manual: http://www.php.net/manual/en/security.database.sql-injection.php – bwoebi Jul 26 '13 at 22:07
  • I just did and I still don't understand all of it. How would WHERE id = 1 or 1 = 1 display the whole table? Wouldn't it have to be WHERE id = 1 OR 2 OR 3...? And how would quotes really prevent this? It'd say WHERE '". 1 OR 1 = 1 ."'? How would this prevent anything? I'm really new to this and I've been learning off of the w3schools website and there's pretty much no mention about this. –  Jul 26 '13 at 22:19
  • `1=1` is a condition which is always true, that's why it selects the whole table – bwoebi Jul 26 '13 at 22:22
  • @bwoebi I spent some time reading up on mysql_real_escape_string and I've fairly understood teh concept now. But I still don't understand why the quotes are necessary. Why can't we just use $sql="SELECT * FROM user WHERE id = $q"; in conjunction with mysql_real_escape_string($q)? Wouldn't this be just as safe as '".$q."'? –  Jul 27 '13 at 06:54
  • My only guess is that the quotes are added because in the situation like 1 OR 1 = 1, the quotes tell the system to treat that as a string instead of as an sql command. I'm not sure how true that is. –  Jul 27 '13 at 07:05
  • @user2624376 your guess is correct. The `'` are delimiting a string and mysql_real_escape_string will (also) escape the single quotes from the input, so that one cannot write sql commands, but just a string. – bwoebi Jul 27 '13 at 10:48
0

First off, the double quotes are there because concatenation is being used; the following two statements are equivalent:

$sql = "SELECT * FROM user WHERE id = '" . $q . "'";
$sql = "SELECT * FROM user WHERE id = '$q'";

That said, integers don't need to be quoted in SQL, so this would be fine:

$sql = "SELECT * FROM user WHERE id = $q";

If $q wasn't sanitized yet and you don't wish to use prepared statements, you could use sprintf() in the following manner:

$sql = sprintf("SELECT * FROM user WHERE id = %d", (int)$q);

In the case of strings I would go for prepared statements or make sure the variable is properly escaped (in case the deprecrated mysql_ functions are still used):

$sql = sprintf("SELECT * FROM user WHERE id = '%s'", mysql_real_escape_string($q));
Ja͢ck
  • 170,779
  • 38
  • 263
  • 309