Quirky PHP question (variables in MYSQL statements)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
speedmonk
Forum Newbie
Posts: 13
Joined: Thu Apr 06, 2006 10:54 am

Quirky PHP question (variables in MYSQL statements)

Post by speedmonk »

Hi there, I have an odd question.

When does one use '{}' around the variable in a MYSQL statement? Sometimes I need to do it, othertimes I don't..

I just can't figure out when or what the difference is.

mysql_query("SELECT * FROM books WHERE book_id= $book_id")

mysql_query("SELECT * FROM books WHERE book_id='{$book_id}'")

Thanks,
JM
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

PHP Variables in Strings

Post by tr0gd0rr »

I think you are asking about using PHP variables in strings. The following part of the PHP manual addresses variables in strings:
PHP Manual wrote:If a dollar sign ($) is encountered, the parser will greedily take as many tokens as possible to form a valid variable name. Enclose the variable name in curly braces if you want to explicitly specify the end of the name.

Code: Select all

<?php
$beer = 'Heineken';
echo "$beer's taste is great"; // works, "'" is an invalid character for varnames
echo "He drank some $beers";  // won't work, 's' is a valid character for varnames
echo "He drank some ${beer}s"; // works
echo "He drank some {$beer}s"; // works
?>
In the example, i think by "works" they mean that the statement will replace $beer with Heineken. Also note that brackets allow variable array access:

Code: Select all

$_SESSION['UserFavorites']['IceCream'] = "Chocolate";
echo "Your Favorite Ice Cream Flavor is {$_SESSION['UserFavorites']['IceCream']}";
alex-weej
Forum Newbie
Posts: 19
Joined: Sun May 14, 2006 11:20 am

Post by alex-weej »

Never ever ever build SQL statements by directly dumping variables into the string. Do this instead:

Code: Select all

$sql = 'SELECT * FROM books WHERE book_id = %d';
$sql = sprintf($sql, $book_id);
The %d sprintf() token is for integers. Whatever $book_id is, be it a number, boolean, string, whatever, it won't break your SQL because sprintf() forces it to display as an integer.

When you are inserting strings, do this:

Code: Select all

$sql = 'SELECT * FROM books WHERE book_name = \'%s\'';
$sql = sprintf($sql, mysql_real_escape_string($book_name, $db_link));
(The %s is for a string insertion.)

You absolutely cannot go wrong with both of these methods, whatever untrusted crap you put in $book_id and $book_name.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Code: Select all

$sql = 'SELECT * FROM books WHERE book_name = \'%s\'';
$sql = sprintf($sql, mysql_real_escape_string($book_name, $db_link));
That's a nice technique alex-weej but by no means the only solution.
For instance I find this easier to read (and probably better performing):

Code: Select all

$q = 'SELECT columna,columnb FROM table WHERE columna = '.(int)$stuff;
$result = mysql_query($q);
Course, nowadays I have a class for it:

Code: Select all

$tainted = $_GET['dirty'];
$cola= new SafeVar($tainted);
$tainted = $_GET['flithy'];
$colb = new SafeVar($tainted);

// getMySQL returns real_escaped_string
$q = 'SELECT columna,columnb FROM table WHERE columna = '.$cola->getMySQL().' AND columnb = '.$colb->getInt();
$result = mysql_query($q);
this way you don't have to run real_escape_string over the parts of the query you know at safe.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: Quirky PHP question (variables in MYSQL statements)

Post by Ollie Saunders »

speedmonk wrote:Hi there, I have an odd question.

When does one use '{}' around the variable in a MYSQL statement? Sometimes I need to do it, othertimes I don't..

I just can't figure out when or what the difference is.

mysql_query("SELECT * FROM books WHERE book_id= $book_id")

mysql_query("SELECT * FROM books WHERE book_id='{$book_id}'")

Thanks,
JM
Personally I nearly always use single quotes and and concat ops like this:

Code: Select all

$str = 'pieces of '. $stuff;
but to answer your question, use brackets when you need to separate the varable name from other text around it:

Code: Select all

$animal = 'pig';
echo "the $animal is here"; // -- the pig is here
echo "$animals scare me";  // will try and find $animals variable -- undefined variable
echo "{$animal}s scare me"; // will find $animal and then put an 's' on the end, {}'s won't be echoed -- pigs scare me
speedmonk
Forum Newbie
Posts: 13
Joined: Thu Apr 06, 2006 10:54 am

Post by speedmonk »

Wow, thanks for the info guys.

I have some reading to do!


My original question, perhaps I was not clear.

I have cases where I have to change $book_id so it looks like either below in order for the query to work at all. For example, on works the other does not. But in both cases! I can't see any differences in the scripts why either would work in one place and not another. :(


mysql_query("SELECT * FROM books WHERE book_id= $book_id")

mysql_query("SELECT * FROM books WHERE book_id='{$book_id}'")


Off to look up sprintf....

Thanks,
JM
alex-weej
Forum Newbie
Posts: 19
Joined: Sun May 14, 2006 11:20 am

Post by alex-weej »

In the second case, the single quotes (') become part of the SQL. So in the first you are doing:

Code: Select all

book_id = 9
and in the second you are doing:

Code: Select all

book_id = '9'
Try a simple

Code: Select all

echo $sql
when you are wondering why your SQL is being strange.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

In the second case, the single quotes (') become part of the SQL. So in the first you are doing:

Code: Select all

book_id = 9
and in the second you are doing:

Code: Select all

book_id = '9'
Although, assuming book_id has a non-string column type, in that case it, it shouldn't make any difference. Neither query should fail.
Post Reply