Preventing SQL Injection in Dynamic SQL

[steve souza]

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.

[Martin Vashko]

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]

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 Vashko]

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]

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]

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]

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:

ZqlParser parser = new ZqlParser(...);
ZStatement statement;
while ((statement = parser.readStatement()) != null)
{
    // check for type of statement; ZQuery should be the only one allowed
}

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.

[Martin Vashko]

A similar question was today discussed here.