The Use of ' Around Variables In SQL Queries

[SpacePhoenix]

In another thread someone mentioned that it was bad to enclose a php variable in quotes when it is part of an sql query

WHERE field='$something' 

because it can apparently cause errors as some DB servers won't allow it whilst some will.

I've been flicking through the books I've got on php and they all enclose the variable with ' that includes recent ones by SitePoint. When looking at the PHP Manual, some examples enclose the variables in ' and some use placeholders (which I don't know if they are very secure). Why don't all books and sites show the variables without being enclosed by '

WHERE field=$something 

which should be more universally correct to enable the same query to be used on any db server, ie MySQL, Oracle, MS SQL, etc?

[sk89q]

You enclose the strings in quotes because they are strings. You don't need to use quotation symbols if the value is not a string.

The fact that it is a variable does not matter, because that's something PHP processes before it gets to the database query function.

<?php
$something = "Hello World!";
"WHERE field='$something'"
// is exactly the same as
"WHERE field='Hello World!'"
//

[AnthonySterling]

I think the OP is confusing quoting, with MySQLs backtick...

[cranial-bore]

I doubt that is the confusion, because backticks go around the column/table/db name, not the value.
SpacePheonix, as mentioned, strings (i.e matching against a string column type) need to be enclosed in quotes. Integer or decimal columns should not have the quotes, but it will work in MySQL. This is probably why you've read comments about compatibility.

e.g

SELECT username FROM users WHERE userID = 918;
SELECT userID FROM users WHERE username = 'JohnDoe';

[r937]

... as some DB servers won't allow it whilst some will.

almost every database server will throw a syntax error if you compare a numeric column value to a string

mysql will "silently" try to convert the string to a number (i.e. no error message, no warning message even), which leads to some unexpected results, like if you have

WHERE userid = '23skidoo'

obviously, if it's a character column, then you must enclose the string in quotes

WHERE keyword = 'awesome'

[ahundiak]

almost every database server will throw a syntax error if you compare a numeric column value to a string

mysql will "silently" try to convert the string to a number (i.e. no error message, no warning message even), which leads to some unexpected results, like if you have

WHERE userid = '23skidoo'

obviously, if it's a character column, then you must enclose the string in quotes

WHERE keyword = 'awesome'

Or just use PDO's prepared statements and the whole quote thingee goes away.

From the manual:

/* Execute a prepared statement by passing an array of values */
$sql = 'SELECT name, colour, calories FROM fruit
    WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql);
$red = $sth->fetchAll(array('calories' => 150, 'colour' => 'red')); 

[r937]

From the manual:

that's interesting

(disclaimer: i don't do php)

what happens if you write this instead --

$red = $sth->fetchAll(array('calories' => '150', 'colour' => red));

has the "whole quote thingee" gone away if the person writing the array doesn't understand the difference between a numeric constant and a string and when to pass which one to the query?

[felgall]

has the "whole quote thingee" gone away if the person writing the array doesn't understand the difference between a numeric constant and a string and when to pass which one to the query?

Obviously it hasn't. All that has happened is that the SQL is being generated in a different way and the quotes have been moved into a different call that is going to build the command prior to running it.

[BonyYousuf]

u can always use {$variable_name} to display ur variable's value inside of a string..

[ahundiak]

that's interesting

(disclaimer: i don't do php)

what happens if you write this instead --

$red = $sth->fetchAll(array('calories' => '150', 'colour' => red));

has the "whole quote thingee" gone away if the person writing the array doesn't understand the difference between a numeric constant and a string and when to pass which one to the query?

It's not a problem. The conversion happens behind the scene based on the actual database schema. Try it. It's quite liberating not to have to have your code be aware of data types.

I guess I should point out that 'colour' => red will not produce a useful result unless red happens to be a defined constant. That is a php thing. But 'calories' => '150' will work just fine.

[felgall]

But 'calories' => '150' will work just fine.

That's a PHP thing with strings that contain numbers being automatically converted to numbers whether you want them to be or not.