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

From: Kevin Spett (kspettat_private)
Date: Fri Jan 03 2003 - 09:01:39 PST

  • Next message: Andrew MacKenzie: "PGP scripting..."

    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" <daveat_private>
    To: <secprogat_private>; <webappsecat_private>
    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" <kspettat_private> 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.williamsat_private>
    > > To: "Kevin Spett" <kspettat_private>; "Dave Aitel"
    > > <daveat_private>; <webappsecat_private>
    > > 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 ; webappsecat_private
    > > > 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" <daveat_private>
    > > > To: <webappsecat_private>
    > > > 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" <kspettat_private> 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" <chrisat_private>
    > > > > > To: <webappsecat_private>
    > > > > > 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
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    



    This archive was generated by hypermail 2b30 : Fri Jan 03 2003 - 18:58:20 PST