[ic] sql filter not 100% safe for MySQL

[John1]

On Saturday, July 24, 2004 4:12 PM, mike at perusion.com wrote:

> Quoting John1 (list_subscriber at yahoo.co.uk):
>> The sql filter doubles up and single quotes to avoid single quotes
>> ruining a query and protect against sql injection.
>>
>> However, as you may also escape single quotes i.e. \'  it is still
>> possible to trip up a query.
>
> You shouldn't mix and match the two. You should do either or.
>
Thanks for your reply Mike - it's not that *I* would mix and match the two,
I am really just pointing out that it is still possible to inject SQL even
if all user input is run through the sql filter.   Unless I am
misunderstanding something??

What I am saying is that \''  (a backslash followed by 2 single quotes) is
converted by the sql filter into:

\''''

This is then interpreted by MySQL as 1 escaped quote, followed by 2 single
quotes (i.e. another escaped quote), followed by 1 single quote.  So it is
possible to "sneak" a "close quote" through the sql filter by mixing and
matching \' and ''.

e.g.  Say that a user put the following in a search box:

something\'';drop database somedatabase

Then if this were passed through the sql filter you would get:

something\'''';drop database somedatabase

Then, the query passed via a query tag might be:

SELECT field1, field2 from products WHERE description='something\'''';drop
database somedatabase

Perhaps [query] won't execute 2 SQL statements separated by a semi-colon?
So perhaps there is no risk of SQL injection??  But, it is possible to
create a bad SQL statement in this way and so generate an error.