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

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.
Received on Fri Jan 3 12:34:36 2003