Posted by Andy Jeffries on 05/18/06 23:20

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

[Back to original message]