This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Preventing SQL Injection in Dynamic SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Preventing SQL Injection in Dynamic SQL" Watch "Preventing SQL Injection in Dynamic SQL" New topic
Author

Preventing SQL Injection in Dynamic SQL

steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
I know PreparedStatements are the best practice for not allowing SQL injection, but in my case I have to come up with a dynamic query screen that allows users to enter queries that return various ResultSets of their choosing. Is there a java library that protects against SQL injection for non-PreparedStatements? Ideally it would be configurable to go beyond that and allow me to specify things like how many tables are allowed in the query, whether deletes/updates/inserts are allowed, and how many tables can be used in a join etc.


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Though it is a bit more work to build PreparedStatements dynamically (since you don't know the parameters beforehand), I'd still use PreparedStatement in this case. You'd do it in the same way you build a standard Statement, just instead of stuffing literal value there you'd add in a question mark and store the actual value of the parameter and its class in some structure structure (probably a list). When you complete the statement, use that information to set parameters. It's best to build the statement from left to right, so that you don't mess order of the parameters, but that's about all.
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
Good idea. If possible I would rather use a well tested framework than to create my own. Being as many people have to have dynamic sql in their applications I was hoping there was something already out there. And it isn't just about sql injection. It is also about disallowing dialect specific commands such as drop database, add user etc.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

I haven't ever thought using framework for such a thing (to build dynamic prepared statements). I'd say that learning the framework and coping with possible limitations is simply not worth it, since building it on your own is pretty easy actually, especially if you create an auxiliary method or two for it.

steve souza wrote:And it isn't just about sql injection. It is also about disallowing dialect specific commands such as drop database, add user etc.

All of that is exactly what SQL injection is about. Please meet Little Bobby tables

steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
Ok, I know PreparedStatements are the way to go in general and I'm a big fan. However, the more I think about it it I think it would be quite difficult to write a generalized query engine that would work for any select statement with any backend where users can input any sql. For example the following are possible and of course any other selects the user might want to input.


select col, sum(col), count(col), (select max(age) from companies) from table where name in (select name from archivetable where name in ('joe', 'jim', 'jon'))



SELECT
Customers.*,
Orders.OrderTime AS LatestOrderTime, 'Q1'
(SELECT COUNT(*) FROM dbo.OrderItems WHERE OrderID IN
(SELECT ID FROM dbo.Orders WHERE CustomerID = Customers.ID))
AS TotalItemsPurchased
FROM
dbo.Customers INNER JOIN dbo.Orders
ON Customers.ID = Orders.CustomerID
WHERE
Orders.ID IN (SELECT ID FROM LatestOrders)
Koen Aerts
Ranch Hand

Joined: Feb 07, 2012
Posts: 344

steve souza wrote:It is also about disallowing dialect specific commands such as drop database, add user etc.

Depending on which DB you're using, you might be able to control that at the DB security. For instance don't allow certain users to run any DDL commands, don't allow DML commands such as DELETE, INSERT, UPDATE, etc.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19649
    
  18

Unless you can do what Koen suggested, you will need to parse the SQL command and verify that it doesn't contain any undesired query. You can use a library like Zql for the parsing. If I understood Zql correctly:
I did find that Zql is a bit limited in the statements it can handle; there is little supported other than INSERT, UPDATE, DELETE or SELECT. Dropping or altering a table isn't even allowed with it.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

A similar question was today discussed here.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Preventing SQL Injection in Dynamic SQL
 
Similar Threads
How to rollback multiple prepared statements
SQL queries
SQL injection?
how to fetch data from resultset containing result of two queries
Suggestions: Complete Java Skill Sets?