[ic] sql filter not 100% safe for MySQL
mike at perusion.com
Sat Jul 24 13:42:23 EDT 2004
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.
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
More information about the interchange-users