File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes PreparedStatements and the IN clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatements and the IN clause" Watch "PreparedStatements and the IN clause" New topic
Author

PreparedStatements and the IN clause

Greg Charles
Sheriff

Joined: Oct 01, 2001
Posts: 2853
    
  11

I'm currently working on refactoring some old JDBC code with the goal of shutting Fortify up about possible SQL Injection vulnerabilities. Fortify is somewhat cryptic, and not totally consistent, about what it considers a problem, but generally it doesn't like anything that builds up query strings at run-time. It doesn't mind concatenating query fragments together based on runtime parameters, but you can't say, pass it an array of strings representing columns you want to retrieve, and build up the SELECT statement from it.

I have a couple of queries that use and IN clause to retrieve information based on user roles. Something like



Values for some_condition and a list of roles are parameters coming into the method. The problem is that I don't know at compile time how many items are going to be in the list of roles, and anything dynamic is flagged by Fortify, even if I just dynamically generate the correct number of question marks to put in IN (?,?,...). Jeanne Boyarsky has an excellent discussion of the issue here, but I have a couple of questions:

1. If I do have to write a store procedure (Oracle), what type do I make the list of roles?
2. Has anything changed in JDBC over the past 6 1/2 years since that might make this easier?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Is there a reasonable upper limit of the number of roles in the IN clause? The implicit limit is 1000, since Oracle won't let you specify more than 1000 values in the IN clause. If you have such a limit, put that many question marks there, set up as many parameters as you have actual roles to retrieve and set the rest to NULL. That will work perfectly well with Oracle and Oracle will actually like this more than your current approach because you'll have only one SQL statement in the shared pool.

If the roles column is a numeric one, you could concatenate all role ids into one String in Java (separated by commas, for example) and create a SQL construct that will split the string into individual role ids, cast that as a table and use that in the IN clause. You'd then bind one String variable (the concatenated role ids) and there would be nothing dynamic to be frowned upon by Fortify. I've never used this approach, but it was advocated by Thomas Kyte, so I assume it is fairly good.

If you want to use a stored procedure, you'd need to create this type:
Replace the NUMBER(10,0) with the actual type of the roles column. You'd need to grant the EXECUTE privilege to relevant users if your application is not completely contained in one schema. I could help with this one, I use such type with a procedure in my project.

Yet another approach would be to create a global temporary table, fill it up with roles and use that table in the IN clause. The quickest way to populate the table would be to use the Oracle 11 driver and JDBC update batching.

I like the first approach most. I'd probably use that one, even if maintaining the question marks for the IN clause would be cumbersome.

Though I've read Oracle Database JDBC Developer Guide, I don't know anything in JDBC (general or Oracle specific) which would be useful here, but it certainly is possible I've missed something.
Greg Charles
Sheriff

Joined: Oct 01, 2001
Posts: 2853
    
  11

Well, there might be a reasonable upper bound for the roles, but I've found another query to fix that uses "entity IDs" in the IN clause, and I've been informed that there could be 100+ of these, though usually there would only be a few. I guess I'd need to use Thomas Kyte's approach in that case. From the link, it looks like I need to define a function that would parse out the parameters from a string, and return them in something that looks to the query like a table. It doesn't actually create a table though, right? I didn't know such a thing was possible.

The roles column wouldn't need to be numeric though, would it? I mean, couldn't I as easily make a function that parsed up strings like "tinker,tailor,solider,spy" as one that parsed "101, 301, 420,86"?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

The link I gave is actually quite dated. Here is an update regarding Oracle 9g and newer. Neither of these constructs involves physically creating a table.

Greg Charles wrote:The roles column wouldn't need to be numeric though, would it? I mean, couldn't I as easily make a function that parsed up strings like "tinker,tailor,solider,spy" as one that parsed "101, 301, 420,86"?

Yes, though that will fail miserably if the values themselves contain the separator. Some kind of escape trickery might do it, though.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PreparedStatements and the IN clause