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

The latest JDBC spec says:

"PreparedStatement objects represent SQL statements that can be prepared, or precompiled, for execution once and then executed mulitple times. Parameter markers, represented by "?" in the SQL string, are used to specify input vaues to the statement that may vary at runtime."

But "precompiled" is never discussed or defined (it's not even technically required since the word "may" is used). But let's look at a real example, the MySQL JDBC driver, since it's open source and widely used. When a PreparedStatement is constructed, the query string is parsed into pieces separated by the "?" marker. Then the user uses the setXXX() methods to fill in the objects. Then when executeQuery() is called, the full query string is assembled, written to the database, and executed. The only "precompiling" is the simple parsing in the constructor. I see no protection against SQL injection gained by using PreparedStatements, at least with the MySQL driver. NOTE: I haven't confirmed this with a test program yet.

Now just to be clear, I think using PreparedStatements is a good idea. But since the spec doesn't provide any meaningful protection against SQL injection, I think anyone using JDBC ought to do their own protection. Then if they ever change drivers they will still be protected. There is really no way to know what a closed source database driver does behind the scenes.

--Jeff

Jeff Williams
Aspect Security, Inc.
http://www.aspectsecurity.com

• Original Message ----- From: Kevin Spett To: Jeff Williams @ Aspect ; secprog@securityfocus.com ; webappsec@securityfocus.com Sent: Friday, January 03, 2003 11:01 AM Subject: 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?

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related securityfocus.com Links bugtraq certification focus-bsd focus-ids focus-ih focus-linux focus-ms focus-sun focus-unix-other focus-virus forensics incidents libnet linux-secnews ms-secnews pen-test secpapers secprog sectools secureshell security-basics securityjobs sf-news vuln-dev webappsec Request more information about Pantek

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
Received on Fri Jan 3 14:20:19 2003