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

From: Dave Aitel (dave@immunitysec.com)
Date: 01/03/03

  • Next message: Kevin Spett: "Re: JDBC PreparedStatements, Java Data Objects/O-R mapping, and SQL Injection" 
    Date: Fri, 3 Jan 2003 11:16:50 -0500
From: Dave Aitel <dave@immunitysec.com>
To: secprog@securityfocus.com, webappsec@securityfocus.com

    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
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > >
    > >
    >
    >

    Relevant Pages