[thelist] Re: Avoiding SQL Injection

[Ken Schaefer]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Joe Ngo
: Subject: [thelist] Re: Avoiding SQL Injection
: 
: > a system of simply escaping single quotes when 
: > inserting data from your users is *not* sufficient 
: > for preventing every time of malicious attack
: > against your application.
: 
: I am not disagreeing, but I am curious. Can you supply a single
: example when this is not enough? Prepared statements might make
: writing the application program easier, but if an application escapes
: all strings and checks validity of numbers
: <emphasis>everytime</emphasis>  when building SQL statements, how
: could SQL injection still be possible? Escaping strings should not
: only be limited to user input, but also data coming from the database
: which an attacker could sneak in.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

OK, it seems we want some quick answers here.

Here is a post from Adam Tupliper, with a follow-up by Michael Howard (author
of Writing Secure Code) on how it may be possible to encode values, which are
then decoded by SQL Server. David Litchfield (of NGSSoftware) also has a post
somewhere (which I can't find now), where he demonstrates the ability to
encode a ' character (so a simply Find/Replace in your application layer code
won't pick it up). This is one area the developer needs to be aware of -
encoding mechanisms that the underlying DBMS may use:
http://www.securityfocus.com/archive/107/384633/2004-12-15/2004-12-20/0


Here is a post that I don't claim to fully understand, claiming that you may
be able to get information from a mySQL database based on blind SQL
injection:
http://www.securityfocus.com/archive/1/354785/2004-02-22/2004-02-28/0

If you check this document, by Chris Anley
(http://www.nextgenss.com/papers/advanced_sql_injection.pdf), another way of
performing an attack is shown (on page 19). A malicious injection is attack
is performed by relying on the system to initially store the malicious data,
but then when the data is subsequently reused (e.g. on another page), the
injection occurs. The example given is a password reset page, when the
existing password is compared to the one stored in the database. So, it
doesn't get around your caveat of /always/ sanitising data, but my experience
is that a lot of developers do /not/ resanitize data that's just come /out/
of the database, eg:

<psuedoCode>
newUserPassword = EscapeSingleQuotes(someDataFromUser)
oldUserPassword = myResultsSetFromDatabase("OldUserPasswordFromDatabase")

UPDATE Users SET UserPassword = ' & newUserPassword & ' 
WHERE OldUserPassword = ' & oldUserPassword & '
</psuedoCode>

Now, there are lots of these scenarios. If I was able to convincingly argue
about this, I wouldn't be a systems engineer - I'd be a security specialist
in a much higher payer job in a more niche consultancy :-) All I can do is
point out a few of them. For each one, you can come up with a specific "fix"
for it.

The point being made is that one can always try to plug specific holes as
they come up. However our understanding of what is malicious changes over
time as researchers come up with new ways to break the application (either
through novel ways of using the database, or because what applications are
required to do changes over time).

So, it's pretty much a security given that attempting to filter for known-bad
input is a losing battle. Just about every book on writing secure code (if
not every book, but I can't claim that because I haven't read every book)
tells you the same thing. Maybe in the future this will be shown to be the
wrong way to go about things, but at the moment it's accepted orthodoxy.
David Litchfield says so. Michael Howard (and David Le Blanc) say so.
Unfortunately, I do not have the knowledge that those guys have, so I'm
probably not the best person to convince you that it's the better way to go.
I suggest you directly engage them, and the community, via lists like
webAppSec or Bugtraq

Cheers
Ken

--
www.adOpenStatic.com/cs/blogs/ken/