[thelist] Need help with a simple regex (Monday annoyance)

Paul Cowan evolt at funkwit.com
Tue Apr 5 01:36:46 CDT 2005


On 4/04/2005 11:04 PM -0400 Matt Warden wrote:
> I think we showed in an earlier thread that you can basically guard
> against this by escaping single quotes. There was some discussion also
> about encoding attacks, but it seemed to be largely theoretical, as we
> could not get an example of the attack to work.

It's a common myth that you can guard against SQL injection by replacing 
quotes in strings. Remember that any integer-type values you take from the 
request are also vulnerable -- possibly more so. For example, let's suppose 
the following URL:

http://monkeyspoon.com/products?category=10

where the code in the page builds something like

sql = "select * from product where cat = " & request("category")

which is mega-common, right? We've all seen code like this a zillion times. 
But what about if someone hits

http://monkeyspoon.com/products?category=10 OR (1 = 1)

? All of a sudden that query returns EVERY product in the database... which 
might reveal some info you don't want revealed. And even if you don't care 
(they're products, right? Who gives a damn the user can hack the 'category 
view' page to see all the products?), what about if they do this:

http://monkeyspoon.com/products?category=10 UNION ALL SELECT * FROM 
TopSecretPasswords

All of a sudden, they've changed your product search page into something 
that tries to display all your secret passwords. Your product display page 
probably won't work, but the error message might reveal information about 
the table structure of the TopSecretPasswords table... and they can refine 
their query, and try again, until they get what they want...

They can also do something like

http://monkeyspoon.com/products?category=10 ; DROP TABLE Orders

which will wreak no end of havoc, I'm sure!

The list goes on... there's much more to SQL encoding that just replacing 
quotes. You need to make sure that every piece of data that goes to the DB 
matches the type it should be -- if it's supposed to be an INT, make sure 
it IS an INT before you send it to the DB. Only if it's a string, and it's 
SUPPOSED to be a string, do you need to worry about escaping quotes and so 
on. This is where parameterised queries come into their own.

Cheers,

Paul


More information about the thelist mailing list