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