[ic] sql filter not 100% safe for MySQL

[Mike Heins]

Quoting John1 (list_subscriber at yahoo.co.uk):
> 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??

Aha. Yes, this makes sense.

> 
> 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.

Query will not execute two statements, but it would certainly be possible
to create a subquery situation.

I wonder if MySQL has a way to ensure that \' is not interpreted
as a single quote? That would be the best way to solve this.

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.765.647.1295  tollfree 800-949-1889 <mike at perusion.com>

Fast, reliable, cheap.  Pick two and we'll talk.  -- unknown