[ic] sql filter not 100% safe for MySQL

[Daniel Davenport]

> -----Original Message-----
> From: interchange-users-bounces at icdevgroup.org
> [mailto:interchange-users-bounces at icdevgroup.org]On Behalf Of Mike Heins
> Sent: Saturday, July 24, 2004 1:42 PM
> To: interchange-users at icdevgroup.org
> Subject: Re: [ic] sql filter not 100% safe for MySQL
>
>
> 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.

It might be the easiest way outside of interchange, if it exists at
all...but the mysql docs don't mention whether it exists, and my tests
indicate that even "--sql-mode=ANSI_QUOTES" doesn't seem to disable
backspace quoting (on mysql 4.0.17-max/win32...ymmv).

Would it be difficult to add a "table" parameter to the sql filter?  If set,
the parameter would indicate which table the query will end up going to.
The filter could then tell the appropriate DB handler to quote the value,
thereby making it safe for any DBI table that interchange knows about.

Short of that, the only purely interchange solution might be to implement a
mysql filter.  That's ugly, though, as it requires that the coder always
specify which tables are which types, and that could potentially make it a
huge mess to move tables from one db type to another.

There is another way, but it's not very pretty, and it's an all-or-nothing
deal.

$data =~ s/(['"\\<>&\[])(?{local $__ord = ord($1)})/&#$__ord;/;

This will html-entity-ify quotes, backslashes, and HTML/ITL special
characters all in one shot. You can change which characters are escaped by
modifying the list inside the brackets.  (Don't mess with '&', though, or
you won't be able to reliably unmangle your data.)  One side benefit of this
type of escaping is that you can filter the HTML characters <>"& as well, so
that's one problem to worry less about--all the browsers i've dealt with
display the characters correctly, and even pass the unescaped sequence back
when forms are submitted.

To unmangle it...

$data =~ s/&#(\d+);(?{local $__chr = chr($1)})/$__chr/;

Data mangled in this fashion won't be easily searchable unless you can
mangle your search strings the same way.

/me