Re: JDBC PreparedStatements, Java Data Objects/O-R mapping, and SQL Injection

["Kevin Spett" <kspett () spidynamics com>]

Using a PreparedStatement to call a stored proc will not protect against
string building going on inside the stored proc.  Note that a stored proc
that does so would go against best practices for stored procedures in
general, let alone security.  Stored procedures work like
PreparedStatements... they're pre-compiled.  So normal code in a stored
procedures, using input that was passed into it through a parameter, would
not be susceptible to SQL injection.  However, if you do something like this
inside your stored proc, you're still screwing yourself:

@sqlString = "SELECT col FROM tab WHERE value = '" + @clientSupplied + "';"
EXEC @sqlString;

String building SQL statements inside stored procs isn't terribly common,
but it's out there.  The correct way to do that would be a) another stored
proc or b) a prepared statement.

And of course, the standard disclaimers... Someone's database server may
implement stored procs in a strange way that goes against standards,
validate your input, blah blah blah.


Kevin Spett
SPI Labs
http://www.spidynamics.com/

----- Original Message -----
From: "Dave Aitel" <dave () immunitysec com>
To: <secprog () securityfocus com>; <webappsec () securityfocus com>
Sent: Friday, January 03, 2003 11:16 AM
Subject: Re: JDBC PreparedStatements, Java Data Objects/O-R mapping, and SQL
Injection


> Hmm, can prepared statements call stored procedures which then do their
> own SQL query building and reexecing? hmm.
>
> -dave
>
>
>
> On Fri, 3 Jan 2003 11:01:20 -0500
> "Kevin Spett" <kspett () spidynamics com> wrote:
>
> > As far as I can tell, the JDBC spec requires that a PreparedStatement
> > be precompiled.  This has the effect of seperating the client-supplied
> > values from the SQL statement, which prevents SQL injection.  Ever
> > database server/JDBC API I have seen does this.  Does anyone know of
> > any exceptions?
> >
> > Now of course, you can still shoot yourself in the foot programming
> > with PreparedStatements if you build them by concatenating
> > client-supplied data into them as opposed to using the '?'
> > substitutions.  But not only is that insecure, it also completely
> > defeats the purpose of using PreparedStatements.
> >
> >
> > Kevin Spett
> > SPI Labs
> > http://www.spidynamics.com/
> >
> > ----- Original Message -----
> > From: "Jeff Williams @ Aspect" <jeff.williams () aspectsecurity com>
> > To: "Kevin Spett" <kspett () spidynamics com>; "Dave Aitel"
> > <dave () immunitysec com>; <webappsec () securityfocus com>
> > Sent: Monday, December 30, 2002 10:37 PM
> > Subject: Re: JDBC PreparedStatements, Java Data Objects/O-R mapping,
> > and SQL Injection
> >
> >
> > > I think there's a very important point here about specifications and
> > > security guarantees.
> > >
> > > Not to rehash the whole discussion from the old PreparedStatement
> > > thread, but nothing in the JDBC spec precludes SQL injection from
> > > working with a PreparedStatement.  That means that it depends on how
> > > the JDBC driver is implemented and what support is provided in the
> > > database for pre-compiling queries. I've checked the source for a
> > > few open-source JDBC drivers, and there is definitely room for
> > > security improvements. Who knows what's going on under the covers in
> > > a proprietary JDBC driver.
> > >
> > > Excellent question about OR mapping technologies and what I'll call
> > > "OQL injection." For those who don't know, OQL is a subset of SQL
> > > used to query objects from an object store that is generally backed
> > > by a relational database. I checked the Castor JDO implementation,
> > > and it uses a PreparedStatement under the hood, so it appears to be
> > > resistant to these attacks (depending on your JDBC driver and
> > > database). The translation from OQL to SQL is done with a *very*
> > > simple parser based on StringTokenizer. The JDO spec is silent on
> > > use of PreparedStatements and SQL injection, so there are no
> > > guarantees that your JDO implementation is resistant to OQL
> > > injection.
> > >
> > > In both of your questions, the specs don't detail the security
> > > guarantees -- meaning that if you want security you have to build it
> > > yourself.  Even if you are currently not susceptible because your
> > > code is running with a strong driver/database, you have a latent
> > > flaw waiting to bite you.
> > >
> > > Bottom line -- if the spec doesn't guarantee it, you should protect
> > > your app against it. Using PreparedStatement *may* help, but that
> > > protection may disappear when you change platforms a few years out.
> > > In my opinion, the right approach here is to very carefully validate
> > > parameters yourself before they are used in any kind of JDBC query.
> > >
> > > --Jeff
> > >
> > > Jeff Williams
> > > Aspect Security, Inc.
> > > http://www.aspectsecurity.com
> > >
> > >
> > > ----- Original Message -----
> > > From: Kevin Spett
> > > To: Dave Aitel ; webappsec () securityfocus com
> > > Sent: Monday, December 30, 2002 6:48 PM
> > > Subject: Re: JDBC PreparedStatements, Java Data Objects/O-R mapping,
> > > and SQL Injection
> > >
> > >
> > > Stored procedures by themselves do not provide protection, sorry if
> > > I worded
> > > that poorly.  Prepared statements, *combined* with prepared
> > > statements do,
> > > which is how I meant that statement to be interpereted.  Of course,
> > > "impossible" should be taken with a grain of salt.
> > >
> > >
> > > Kevin Spett
> > > SPI Labs
> > > http://www.spidynamics.com/
> > >
> > > ----- Original Message -----
> > > From: "Dave Aitel" <dave () immunitysec com>
> > > To: <webappsec () securityfocus com>
> > > Sent: Monday, December 30, 2002 6:14 PM
> > > Subject: Re: JDBC PreparedStatements, Java Data Objects/O-R mapping,
> > > and SQL
> > > Injection
> > >
> > >
> > > > I dunno about that. Impossible is such a big word, and I've seen
> > > > SQL Injection successfully done at least few times against a
> > > > stored procedure.
> > > >
> > > > You should put your sample apps on a web site somewhere so people
> > > > can knock it around a bit.
> > > >
> > > > Dave Aitel
> > > > Immunity, Inc.
> > > > http://www.immunitysec.com/CANVAS/ (Remote SQL Server exploits
> > > > make
> > > SQL
> > > > Injection even more fun than usual!)
> > > >
> > > >
> > > > On Mon, 30 Dec 2002 17:32:13 -0500
> > > > "Kevin Spett" <kspett () spidynamics com> wrote:
> > > >
> > > > > The use of prepared statements and stored procedures makes SQL
> > > > > injection impossible.  A prepared statement is compiled before
> > > > > the user input is added to the SQL statement, effectively making
> > > > > it impossible to execute the client-supplied data because it is
> > > > > never compiled.  There was a thread about this a couple of
> > > > > months back on this list, here's the first post:
> > > http://archives.neohapsis.com/archives/sf/www-mobile/2002-q3/0105.html
> > > > > Have a fun and securely programmed new year, everyone.
> > > > >
> > > > > Kevin Spett
> > > > > SPI Labs
> > > > > http://www.spidynamics.com
> > > > >
> > > > >
> > > > > ----- Original Message -----
> > > > > From: "Christopher Todd" <chris () christophertodd com>
> > > > > To: <webappsec () securityfocus com>
> > > > > Sent: Monday, December 30, 2002 3:29 PM
> > > > > Subject: JDBC PreparedStatements, Java Data Objects/O-R mapping,
> > > > > and SQL Injection
> > > > >
> > > > >
> > > > > > I am working on the Java language section of the OWASP Guide
> > > > > > to Securing
> > > > > Web
> > > > > > Applications, and I have a question for the list.  Have any of
> > > > > > you elite
> > > > > SQL
> > > > > > Injectors ever been able to hack an application that was using
> > > JDBC
> > > > > > PreparedStatements?  Are any of you aware of a theoretical
> > > > > > reason this should be impossible?  I have tried, and been
> > > > > > unsuccessful,
> > > to
> > > > > > perform SQL injection on an example app I coded up, but then
> > > again,
> > > > > > I am not the
> > > > > world's
> > > > > > most talented SQL Injector.
> > > > > >
> > > > > > On another note, have any of you ever successfully used SQL
> > > > > > Injection against a web app that was using Castor JDO, or
> > > > > > other similar Object-Relational mapping tools?  Again, I have
> > > > > > tried to attack an example app I coded up and failed.  Same
> > > > > > question - is
> > > it
> > > > > > theoretically impossible to execute SQL injection against apps
> > > coded
> > > > > > using these techniques and tools?
> > > > > >
> > > > > > I ask these questions because I think these two techniques can
> > > > > > be used effectively to thwart (or at least make more
> > > > > > difficult) SQL injection attacks against Java-based web apps,
> > > > > > but I want to validate that belief to the best extent I can
> > > > > > prior to putting
> > > such
> > > > > > statements into the Guide. Thanks in advance for any help you
> > > > > > can provide, as it will improve the quality and usefullness of
> > > > > > the Guide.
> > > > > >
> > > > > > Chris