By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,375 Members | 1,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,375 IT Pros & Developers. It's quick & easy.

Help with variable interpolation in WHER clause

P: n/a
I am a MySQL and PHP newbie. I am having trouble getting the $w variable in
my code below passed to mysql. When I use the value of $w directly in the
Where clause, the correct rows are returned. However, when I try to use the
variable in the Where clause, either an error occurs or no rows are
returned. Any thoughts greatly appreciated!

I am using php-4.4.2 and MySQL-4.1.18-0 on Suse 9.1

Jerry

<html>
<body>
<?php
$link = mysql_connect('localhost:3306', 'mysql');
mysql_select_db('test_ermd') or die('Could not select database');
$w = 'exec';
// I have tried all the variations below to get the value of $w to process
in the WHERE clause
//------------------------------------------------------------------------------------------
//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
FROM officers WHERE GRP = "exec"';
//--This works: all rows with 'exec' in GRP column are selected

//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
FROM officers WHERE GRP = $w';
//--Invalid query: Unknown column '$w' in 'where clause'
//--Whole query: SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX,
EMAIL FROM officers WHERE GRP = $w

//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
FROM officers WHERE GRP = \"$w\"';
//--Invalid query: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near '\"$w\"' at line 1
//--Whole query: SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX,
EMAIL FROM officers WHERE GRP = \"$w\"

//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
FROM officers WHERE GRP = \$w';
//--Parse error: syntax error, unexpected T_STRING
in /usr/local/bin/apache2/htdocs/test_ermd.php on line 34

//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
FROM officers WHERE GRP = "\$w"';
//--No rows are selected

//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
FROM officers WHERE GRP = \'$w\'';
//--No rows are selected
//------------------------------------------------------------------------------------------

echo 'Raw query: ' . $query;
$result = mysql_query($query);
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "<br>";
$message .= 'Whole query: ' . $query;
die($message);
}
while ($row = mysql_fetch_assoc($result)) {
foreach ($row as $key => $value) {
if ($value) {
echo "$value";
echo "<br>";
}
}
echo "<p>";
}
mysql_free_result($result);
mysql_close($link);
?>
</body>
</html>
//================================================== ==================
May 18 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
> //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
FROM officers WHERE GRP = $w';


When a string is between 'single quotes', variables are not parsed.
This means that after
$query = 'SELECT $w'
The variable query literaly contains SELECT $w. To use the value of the
variable w instead, use double quotes:
$query = "SELECT $w"
This becomes SELECT and then the value of variable w.

Another option is to use the concatination operator, which is a dot.
$query = 'SELECT '.$w
This way, the string 'SELECT ' and the variable w are concatenated
together.

May 18 '06 #2

P: n/a
Carved in mystic runes upon the very living rock, the last words of
Sjoerd of comp.lang.php make plain:
//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX,
EMAIL FROM officers WHERE GRP = $w';


When a string is between 'single quotes', variables are not parsed.
This means that after
$query = 'SELECT $w'
The variable query literaly contains SELECT $w. To use the value of
the variable w instead, use double quotes:
$query = "SELECT $w"
This becomes SELECT and then the value of variable w.

Another option is to use the concatination operator, which is a dot.
$query = 'SELECT '.$w
This way, the string 'SELECT ' and the variable w are concatenated
together.


And don't forget to enclose the value in single quotes within the query:

"...WHERE GRP = '$w'";

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/
May 18 '06 #3

P: n/a
Alan Little wrote:
Carved in mystic runes upon the very living rock, the last words of
Sjoerd of comp.lang.php make plain:

//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX,
EMAIL FROM officers WHERE GRP = $w';


When a string is between 'single quotes', variables are not parsed.
This means that after
$query = 'SELECT $w'
The variable query literaly contains SELECT $w. To use the value of
the variable w instead, use double quotes:
$query = "SELECT $w"
This becomes SELECT and then the value of variable w.

Another option is to use the concatination operator, which is a dot.
$query = 'SELECT '.$w
This way, the string 'SELECT ' and the variable w are concatenated
together.

And don't forget to enclose the value in single quotes within the query:

"...WHERE GRP = '$w'";


Only if it's non-numeric.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
May 18 '06 #4

P: n/a
WHERE GRP = '$w' . Had the same problem, but tinkered it into
submission =)

May 18 '06 #5

P: n/a
TristaSD wrote:
WHERE GRP = '$w' . Had the same problem, but tinkered it into
submission =)


Thanks to all! The single-quoted variable (textual value) within a
double-quoted SQL statement returns all the desired rows.

Jerry
May 18 '06 #6

P: n/a
On Thu, 18 May 2006 15:01:31 -0400, Jerry Stuckle wrote:
And don't forget to enclose the value in single quotes within the query:

"...WHERE GRP = '$w'";

Only if it's non-numeric.


It's actually a good idea to do it even if the field is non-numeric. Say
you have a form that does:

DELETE FROM PrivateMessages
WHERE ID=$id AND OwnedByID=$_SESSION["memberid"];

You think you're being safe as you're ensuring a member can only delete
PrivateMessages that are owned by themselves.

However, what if a malicious user changed the value of $id from "2" to
"ID OR 1=1". Easy enough to do by saving the page, editing the field
value and hitting submit. You then get the SQL statement:

DELETE FROM PrivateMessages
WHERE ID=ID OR 1=1 AND OwnedByID=$_SESSION["memberid"];

This would delete all PrivateMessages (as the value of the ID column
always equals itself and the additional OR 1=1 gives a positive left hand
side to the following AND).

The next question could easily be "but how would the attacker know the
name of the field, I don't use ID as my field name". Easy, a)it could
have been revealed in an error message if they're displayed on the site
and b)this error message may have been fixed, but previously cached by
Google.

Even if the attacker doesn't know the field name, they could set the
variable contents to be something like "0 OR 1=1 OR 1=1" which then gives
the SQL statement:

DELETE FROM PrivateMessages
WHERE ID=0 OR 1=1 OR 1=1 AND OwnedByID=$_SESSION["memberid"];

Which then always works.

It's always a good idea to wrap field values in '', even if they're
numeric as it stops this kind of attack. If you did so, the SQL statement
would be:

DELETE FROM PrivateMessages
WHERE ID='ID OR 1=1' AND OwnedByID=$_SESSION["memberid"];

And the numeric ID column will never equal that string, no records deleted.

Of course, you also need to ensure the field is safe using
mysql_real_escape_string, but that's a separate conversation to this one
about wrapping numeric field values in ''.

Cheers,
Andy

--
Andy Jeffries MBCS CITP ZCE | gPHPEdit Lead Developer
http://www.gphpedit.org | PHP editor for Gnome 2
http://www.andyjeffries.co.uk | Personal site and photos

May 18 '06 #7

P: n/a
On Thu, 18 May 2006 20:20:14 +0000, Andy Jeffries wrote:
And don't forget to enclose the value in single quotes within the
query:

"...WHERE GRP = '$w'";

Only if it's non-numeric.


It's actually a good idea to do it even if the field is non-numeric.


I mean "is numeric" there, I guess it's obvious from the rest of the post,
but hey I'm a programmer I have to try and correct my mistakes... :-)

Cheers,
Andy
--
Andy Jeffries MBCS CITP ZCE | gPHPEdit Lead Developer
http://www.gphpedit.org | PHP editor for Gnome 2
http://www.andyjeffries.co.uk | Personal site and photos

May 18 '06 #8

P: n/a
Carved in mystic runes upon the very living rock, the last words of
Jerry Stuckle of comp.lang.php make plain:
Alan Little wrote:

And don't forget to enclose the value in single quotes within the
query:

"...WHERE GRP = '$w'";


Only if it's non-numeric.


In his example it was "exec".

Plus what Andy said.

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/
May 19 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.