ORM (Hibernate) SQL Injection - Security Series #14

During Bob Silverberg's awesome ORM presentation today the topic of SQL injection came up. There was a question was about whether or not the Hibernate ORM service built into ColdFusion 9 would prevent SQL injection. On the surface it would seem that it does, but just like everything else, there are exceptions.

Typical ORM protection against SQLi

Internally Hibernate uses prepared statements with bind parameters when it generates dynamic SQL. So let's say you have some code like this:


<cfscript>
    myObj = entityLoad('ProtectedContent', url.id);
</cfscript>

This script will dynamically retrieved some protected content from your application based on the URL parameter "id". The URL that retrieves this data might look like:


    http://localhost/ormtest/?id=1

And our result might be:



Now what would happen if someone tried to inject SQL into this?


    http://localhost/ormtest/?id=1+OR+1=1

This is a common type of SQL injection attack that attempts to retrieve more information than the application would normally provide. By appending "OR 1=1" to the end of a query string it would ensure that EVERY record in the queried table is returned instead of just those defined in the WHERE clause. So the resulting dynamically generated query would look like:


    SELECT title, text
    FROM ProtectedContent
    WHERE id = 1
        OR 1=1

If this type of injection attack were to actually work (which in this case it will not because of Hibernate's use of bind parameters under-the-hood) then the result would look like this



So what happens when we try to inject code into the above statement?


Error Occurred While Processing Request
The value 1 OR 1=1 cannot be converted to a number.

Because hibernate is using bind parameters that use type checking in the background, this injection attempt fails.

This is great. It means that in situations like this, we are just as well protected against SQL injection as we are when we use <cfqueryparam> in our <cfquery> blocks.

So what's the problem then?

The problem comes in when we get into some more advanced Hibernate goodness. Specifically when we use the Hibernate Query Language (HQL) to make our queries.

HQL??

For those that do not know what HQL is, I will briefly explain. For more information on HQL, check out the Hibernate docs on the subject.

So what is HQL? Well, if you ever use Hibernate for anything beyond the very basics, you will eventually get to a point where Hibernate does not have out-of-the-box support for exactly what you need. You might need to perform a query in a way beyond the basic abilities Hibernate has, like with some complex JOINs or extended WHERE clause. That is where HQL comes in.

HQL gives us the ability to write SQL-like queries from which Hibernate can return persistent objects.

Here is a simple, contrived example.


<cfscript>
    myObj = ORMExecuteQuery('FROM ProtectedContent WHERE id = ' & url.id);
</cfscript>

Like I said, this is a contrived example that you would not likely see in the real world. In fact, we are doing the EXACT same thing as in the simpler EntityLoad() statement above. So pretend with me that this is a more complicated HQL statement and you will get the idea.

Here we are using the ColdFusion function ORMExecuteQuery(). This function will accept HQL as a string parameter and will return the appropriate objects. In this case we will get back exactly the same object as we would have in my first example.

This statement above is vulnerable to ORM Injection. Really it is just another form of SQL injection because Hibernate take this input and builds real SQL from it.

The problem with this statement is the appending of the URL.id variable onto the end of the HQL statement. For the same reasons that we should never create a SQL statement by concatenating strings together, we should never do that for HQL either. Instead we should use bind parameters. This is very simply done.


<cfscript>
    myObj = ORMExecuteQuery('FROM ProtectedContent WHERE id = :id', {id=url.id});
</cfscript>
Here I have changed my HQL to use a named parameter, called id, instead of appending the URL.id string onto the end of the statement. The colon(:) tells Hibernate that this is a token that needs to be replaced with a parameter.

ORMExecuteQuery() takes a second argument as well which is a collection of parameters to use in the query. In this case I am passing in an implicitly created struct with a single key-value pair. The key is named after the token from the HQL, minus the colon, and the value is the value I want to use in the dynamic SQL.

I could have done this using unnamed parameters. You do this by using question marks for the tokens.


<cfscript>
    myObj = ormExecuteQuery('FROM ProtectedContent WHERE id = ?', [url.id]);
</cfscript>

This works the same way except that we pass in an array as the second argument instead of a struct and the array only contains the values since we don't have any named tokens with which to match them up. The important thing to note here is that the array values MUST be in the same order as the question mark tokens need to be replaced.

Now with either of these solutions, if someone tried to inject the malicious SQL again:


Error Occurred While Processing Request
The value 1 OR 1=1 cannot be converted to a number.

Conclusion

You may be thinking that you'll never need to use HQL, but believe me, you will. And you should. HQL is an awesome feature of Hibernate and is something that you should absolutely get comfortable with. Don't let this post scare you off from using HQL, just let it make you aware of how to properly use it.

Comments
jens's Gravatar thanks, was very helpful your article!!
# Posted By jens | 4/20/10 1:38 AM
mujimu's Gravatar thanks Jason for making it so i didn't have to test this.

was just looking into the fact that cfquery dbtype="orm" is now allowed to use HQL in cfquery calls, meaning you can use cfqueryparam to bind params.

good to know my existing (pre 9.01 release) method of using named binding (id = :id, then passing {id=arguments.id} as named param struct) had the same protection.
# Posted By mujimu | 8/8/10 8:27 PM
Jason Dean's Gravatar mujimu,

Thanks for the comment, and you are welcome.

I did not realize that they had added dbtype="orm" in 9.01. That's pretty cool, will have to try that out. Also cool that the support cfqueryparam in there. Should look into it and blog about that.
# Posted By Jason Dean | 8/9/10 1:47 PM
Glyn Jackson's Gravatar Very interesting, thanks for posting this.
# Posted By Glyn Jackson | 9/12/10 9:34 AM
liago86's Gravatar 3 years late, i know, but i have to make a comment on this. What about the case where the url parameter is not numeric but varchar. Will then the orm protect from the sql injection?
# Posted By liago86 | 12/11/13 5:48 AM
Jason Dean's Gravatar @liago86,

Yes, it will. It will still treat it as a parameterized statement, so it will protect in the same may as cfqueryparam.
# Posted By Jason Dean | 12/11/13 11:02 AM
Sean's Gravatar Thanks for the write up. Any ideas for constructing the query on the fly before ormExecuteQuery? I have WHERE/AND constraints that may or may not need to be in the query each time.
# Posted By Sean | 1/27/14 1:45 PM
Sean's Gravatar I think I got it. I can construct the statement itself as a variable, including the named parameters, and then use that variable as the first argument. It still lets me use the second argument to give those parameters values.
# Posted By Sean | 1/27/14 1:54 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1. Contact Blog Owner