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.
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.
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.
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.
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'))
Orders.OrderTime AS LatestOrderTime, 'Q1'
(SELECT COUNT(*) FROM dbo.OrderItems WHERE OrderID IN
(SELECT ID FROM dbo.Orders WHERE CustomerID = Customers.ID))
dbo.Customers INNER JOIN dbo.Orders
ON Customers.ID = Orders.CustomerID
Orders.ID IN (SELECT ID FROM LatestOrders)
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.
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.