Quirky PHP question (variables in MYSQL statements)
Moderator: General Moderators
Quirky PHP question (variables in MYSQL statements)
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
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
PHP Variables in Strings
I think you are asking about using PHP variables in strings. The following part of the PHP manual addresses variables in strings:
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: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 ?>
Code: Select all
$_SESSION['UserFavorites']['IceCream'] = "Chocolate";
echo "Your Favorite Ice Cream Flavor is {$_SESSION['UserFavorites']['IceCream']}";Never ever ever build SQL statements by directly dumping variables into the string. Do this instead:
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:
(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.
Code: Select all
$sql = 'SELECT * FROM books WHERE book_id = %d';
$sql = sprintf($sql, $book_id);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));You absolutely cannot go wrong with both of these methods, whatever untrusted crap you put in $book_id and $book_name.
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
That's a nice technique alex-weej but by no means the only solution.Code: Select all
$sql = 'SELECT * FROM books WHERE book_name = \'%s\''; $sql = sprintf($sql, mysql_real_escape_string($book_name, $db_link));
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);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);- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
Re: Quirky PHP question (variables in MYSQL statements)
Personally I nearly always use single quotes and and concat ops like this: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
Code: Select all
$str = 'pieces of '. $stuff;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 meWow, 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
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
In the second case, the single quotes (') become part of the SQL. So in the first you are doing:
and in the second you are doing:
Try a simple when you are wondering why your SQL is being strange.
Code: Select all
book_id = 9Code: Select all
book_id = '9'Code: Select all
echo $sql- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
Although, assuming book_id has a non-string column type, in that case it, it shouldn't make any difference. Neither query should fail.In the second case, the single quotes (') become part of the SQL. So in the first you are doing:and in the second you are doing:Code: Select all
book_id = 9Code: Select all
book_id = '9'