Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Preventing SQL Injection in Dynamic SQL

 
steve souza
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 344
Java Linux Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20667
65
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A similar question was today discussed here.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic