1

I am using syntax like 

$query  = "SELECT * FROM contacts WHERE contact_id = $id";

often it works but often it does not work. Then if I use 

$query  = 'SELECT * FROM contacts WHERE contact_id = "'.$id.'";

then it works. Which one is right approach ??

Php Php
  • 87
  • 1
  • 4
  • 10
  • Also have a look at http://stackoverflow.com/questions/3446216/difference-between-single-quote-and-double-quote-string-in-php – clentfort Oct 21 '12 at 10:44

2 Answers2

3

In fact, neither of these syntaxes is best, when it comes to writing a SQL query.

If you're writing a SQL query, you are far better off writing it using parameterised queries, like so:

$query = 'SELECT * FROM contacts WHERE contact_id = ?';
$prep = $mysqli->prepare($query);  //prepares the query for action
$prep->bind_param("i", $id);       //inserts the $id variable into the '?' as an integer value.

...or similar methods using the PDO library.

Doing queries this way will make your queries more secure.

Please note that if you're using the old style mysql_xx() functions (which do not support this style of code), these are considered obsolete and insecure, and will be removed from a future version of PHP. You should stop using them as soon as possible. (see the PHP manual for more info on this)

It wasn't clear from the question whether you were asking about string syntax or query writing style. The above helps with query writing, and also avoid the issue with string syntax, but in case you still want to know about the string syntax issues, I will continue with some thoughts on that topic too....

Both syntaxes are perfectly valid. The short answer is that it's fine either way, so do it whichever way works best for you in any given bit of code.

You mentioned that your first syntax "often does not work". It would be helpful if you could elaborate on that, because it is perfectly valid PHP syntax.

Reasons it may fail are if you have other words joining onto variable names so PHP, or if you are trying to use an array element as the variable. In this case, you should wrap your variable names in braces like so:

$string = "this is a string with a {$variable} in it";

In fact, this works in all cases (and also helps make it clearer when you're using a variable in a string), so is best to do it all the time.

Some people will say that using single quotes is better for performance. It is.... but the difference is very marginal, and in fact, when you're concatenating a lot of variables it becomes even less. To be honest, if they're that worried about performance that this kind of thing is an issue for them then they shouldn't be using an interpreted language like PHP.

Spudley
  • 157,081
  • 38
  • 222
  • 293
  • Thanks @Spudley for your long answer. I am getting error while I am using below syntax `$query = "SELECT * FROM contacts WHERE contact_name = $name";` I am not getting error if I use below syntax. `$query = "SELECT * FROM contacts WHERE contact_name = '$name'";` As far as I know first one is correct syntax. – Php Php Oct 22 '12 at 04:33
  • okay. The error you're getting is a SQL error, not a PHP error. See the first half of my answer for the best solution. (@JohnWoo's answer also has some important hints as to why you're getting errors. It's important to understand the concept of SQL injection and know how to avoid it) – Spudley Oct 22 '12 at 06:51
  • That means you are saying `$query = "SELECT * FROM contacts WHERE contact_name = $name";` is wrong syntax and `$query = "SELECT * FROM contacts WHERE contact_name = '$name'";` is right syntax if I would like to avoid error. – Php Php Oct 22 '12 at 12:54
  • What I'm saying is that the second one is better, but neither is correct. The first query will *never* work; the second one will work, but has **serious** flaws. Please *please* use the techniques I described in my answer to avoid these flaws. – Spudley Oct 22 '12 at 19:18
1
$query  = "SELECT * FROM contacts WHERE contact_id = '". $id . "'";

but the query is vulnerable with SQL Injection. Here's an article how to secure your code from SQL Injection

Best way to prevent SQL injection in PHP?

The query below will not work if the column you want to filter is not numeric.

$query  = "SELECT * FROM tableName WHERE username = $name";
Community
  • 1
  • 1
John Woo
  • 238,432
  • 61
  • 456
  • 464