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();
  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?

13 Responses to “Fundamentally Safe Database APIs”

  1. Dolan Halbrook Says:

    Hibernate offers functionality like this in their Criteria API.


  2. John Cowan Says:

    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.

  3. Stefan Tilkov Says:

    java.sql.PreparedStatement? What am I missing?

  4. Rob Koberg Says:

    Hibernate does this type of thing.

  5. robert Says:

    regular PreparedStatement does this.

  6. Ed Davies Says:

    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?

  7. James Orenchak, CISSP Says:

    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:

    Hibernate has a neat validator option that can help prevent SQL injection if it’s put to use.

  8. Erik Tribou Says:

    How about just the PreparedStatement api of java.sql?

  9. Frederic Daoud Says:

    RIFE already does this.

  10. Elliotte Rusty Harold Says:


    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.

  11. Andreas Schödl Says:

    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).

  12. Ben Says:

    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.

  13. James Orenchak, CISSP Says:

    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.

Leave a Reply