Re: FW: Introducing a new generic approach to detecting SQL injection

["Paul J. Morris" <mole () morris net>]

On Fri, 22 Apr 2005 18:34:52 -0400
Mohit Muthanna <mohit.muthanna () gmail com> wrote:
> > > Don't use simple regexp matching.
> > Why not?  I am not matching known attacks, I am stripping everything
> > but a small set of known good characters.  How are you going to
> > construct a sql injection attack using the character set [A-Za-z]?  
> > Yes, you can
>
> Sorry. I should have clarified. Your regex is perfectly fine since it
> is very restrictive. My point was that when trying to sanitize a
> string for SQL queries don't rely on just regular expressions as you
> may miss something. Instead use a trusted library, since they _try to_
> avoid all the little loopholes.

     I agree, we are indeed thinking the same thing here.  

> My main point was that the idea presented by the original poster is a
> waste of resources, and a workaround for being lazy.

     What is intriguing me about it is that it is a different way of
approaching the problem of untrusted user input, one that uses the
strengths of the dbms itself rather than relying on code written on a
layer above the dbms to send safe data to the dbms.  If you are
developing a simple web interface to a database where you have full
control of everything except the user input, it does seem like overkill.
 If you are developing multiple different interfaces into a database or
are developing an interface that could layer over several different
sorts of database, it seems a potentially useful element to add to the
security.

> >     In the realm of multibyte characters with multiple kinds of
> >     clients
> > I'm not at all convinced it is.  I don't know that an attacker isn't
> > going to encode a query terminating character in a way that is going
> > to get through the decoding and escaping.  The fundamental principle
> > of
>
> Please explain why. How do you think the database's SQL parser does
> it?
>
> Take PostgreSQL for example:
>
> select * from table where key = '$value';
>
> If from $value, one escapes the quotes (') and the escape characters
> (\), how would one inject SQL there?
 
   Because we are not talking about an attacker on a psql console, we
are talking about an attacker passing characters through an interface
layer that might be using different rules than the PostgreSQL
parser. If I can encode' as a multibyte character in a way that the
layer which is doing the escaping does not recognize it as a '
character, but the PostgreSQL parser does, then I can bypass your
escaping.  There was an old PostgreSQL bug that did something similar -
the PostgreSQL parser ate an extra character after an invalid multibyte
character, allowing an attacker to construct a string that would contain
an ' that got escaped by an interface layer to\' (or '') but where the
postgresql parser saw a'.  What I find interesting about Gregg's
poisoned query suggestion is that it allows the database engine itself
to be used to evaluate whether an arbitrary string is an attack or not,
and doesn't depend on an external piece of code recognizing and escaping
attacks in the same way that it does.  One form of attack I see that it
won't recognise is a valid clause that extends the scope of the result
set without invoking the name of a entity in the database: "' and 1=1
and 1='"

> Now, bolster that with a prepared statement:
> select * from table where key = ?
> And execute it with $value. It's locked down.

     Agreed.  I don't recall anyone yet suggesting an effective way of
attacking prepared statements.  

> Pretty easy, methinks.

     I have learned to worry when some piece of security seems easy.... 
 :)

Merry Snailing,
-Paul
--------------
Paul J. Morris
Biodiversity Information Manager, The Academy of Natural Sciences
1900 Ben Franklin Parkway, Philadelphia PA, 19103, USA
mole () morris net  AA3SD  PGP public key available

Attachment: _bin
Description:

_______________________________________________
Full-Disclosure - We believe in it.
Charter: http://lists.grok.org.uk/full-disclosure-charter.html
Hosted and sponsored by Secunia - http://secunia.com/