Fundamentally Safe Database APIs
Is anyone aware of work on fundamentally safe database APIs for server side programs that completely avoid the possibility of SQL injection? What I envision is a somewhat limited API that does not execute raw SQL statements or provide any facility to do so. Instead you’d set up something like this:
Statement s = database.getSelectStatement();
s.setTable("customers");
s.addField("email");
s.addField("telephone");
s.addCondition(
new EqualsCondition("id", "p17")
);
ResultSet r = s.execute();
The library would turn this into the usual SQL statement
SELECT email, telephone FROM customers WHERE id = "p17"
The library could verify the individual parts of the query before submitting it to the database. If you passed a string like "id = \"p17\" OR true; DELETE * FROM customers; SELECT * FROM customers WHERE " to EqualsCondition() it would throw an exception.
This API doesn’t have to be in Java or any particular language. Nor does it have to have the full power of SQL. Basic SELECTs would be sufficient. Safety is valued over power here.
Surely I’m not the first person to think of this? Are there any APIs of this nature? in any environment or language?
April 6th, 2007 at 11:12 AM
Hibernate offers functionality like this in their Criteria API.
See: http://www.hibernate.org/hib_docs/v3/reference/en/html/querycriteria.html
April 6th, 2007 at 11:18 AM
Google for LINQ; that’s exactly what it’s about. Unfortunately, it’s trapped inside the forthcoming C# 3.0 and VB 9.0, but we can hope the Mono folks will do the right thing.
April 6th, 2007 at 12:31 PM
java.sql.PreparedStatement? What am I missing?
April 6th, 2007 at 1:16 PM
Hibernate does this type of thing.
April 6th, 2007 at 1:35 PM
regular PreparedStatement does this.
April 6th, 2007 at 2:50 PM
Not exactly the point of your post, but are you sure you’d want EqualsCondition to throw an exception in that case? Wouldn’t you want it to just escape the special characters (quotes) properly?
April 6th, 2007 at 4:10 PM
It’s true that regular PreparedStatement is a good tool to fight SQL injection.
A guy named Carsten Clasohm thought of writing an API that would avoid SQL injection and wrote someting called “global filter”, which is decribed on the web page:
http://www.eveandersson.com/arsdigita/doc/core-arch-guide/security-sql-smuggling
Hibernate has a neat validator option that can help prevent SQL injection if it’s put to use.
April 6th, 2007 at 4:49 PM
How about just the PreparedStatement api of java.sql?
April 7th, 2007 at 2:36 AM
RIFE already does this.
April 7th, 2007 at 8:43 AM
Ed,
In some cases EqualsCondition might indeed be able to escape the relevant text. However in this particular example I think that the supplied string is never a legal SQL field name, escaped or otherwise. Therefore the only logical response in Java is to throw an exception. Languages without exceptions might have some other error response. However I don’t think it’s possible to escape this. I could be wrong about that. I’m not a SQL expert.
April 8th, 2007 at 3:22 AM
While you’re at it: Is there something like this for (W3C) XML-Query? I think, DBMS processing XML Query can as easily be messed up with XML Query injection (provided they do support insert, update, delete syntax).
April 9th, 2007 at 2:20 PM
I don’t think java.sql.PreparedStatement qualifies as “Fundamentally Safe” since you can easily write unsafe code using it.
The Hibernate Criteria API is think would qualifty. Also, as far as I can remember Oracle Pro*C completely insulated you from SQL injection attacks. They have something similar which allows you to embed SQL directly in Java. I’ve never tried it though.
April 9th, 2007 at 2:33 PM
This question has been on my mind and I just had a hard time not thinking about it. I had in the back of my mind, that security best practice would be to centralize security validation. After looking through several books and papers written about security patterns, I found that a centralized security validation is indeed best practice. A centralized security validation, used to check web interface input, input to SQL data bases, input into business logic modules, etc., has the advantage of being more maintainable and more reusable, as well as simplifying the addition of new validators. Otherwise, an API for validating SQL has to be maintained, an API for validating XML-Query has to be maintained, an API for validating user input into web interfaces ( on the server side) has to be maintained, and on and on! This topic was covered very well by Chr. Steel, Ramesh Nagappan and Ray Lai in the section describing the Intercepting Validator pattern in their book, Core Security Patterns.