OR/M and SQL Injections

time to read 2 min | 374 words

ScottGu is talking about SQL Injections and he mentions that neither stored procedures nor OR/M are a complete protection from it.

In a way, he is correct. If the stored procedure that you are using is called sp_executesql, then you are not protected from SQL injection. A more problematic approach is when your SP uses sp_executesql to do some of its work (usually because it need to work on a table dynamically, or your precedecor was a sadistic freak that enjoy doing things the really hard way).

Users of ORM are usually working with objects, and most ORMs offer strong OO query capabilities, so this is much less of an issue. Even when string queries are used, ORMs usually make working with parameters so much more easier than working with ADO.Net parameters, that there isn't any drive to use string concntation with most ORMs.

Nevertheless, I got several questions about issues related to just that, creating queries for NHibernate using string concantation. This is an issue all by itself, regardless of security (maintainablity and performance are both directly affected by it). But, following Scott's post, I tried to create an HQL injection using NHibernate.

 Now, NHibernate doesn't use SQL directly, it uses HQL, and object oriented querying language that is translated to SQL at the end of the road. After playing it for about 10 minutes, I was unable to come up with a query with string concantation that will pass both HQL and the translated SQL syntaxes property.

Here is a simple example:

session.Find("from User u where u.Username = '"+username+"' and u.Password = '"+password+"'");

What parameters will cause this query not to fail, but successfully do something that I didn't intend it to?

Note: I am not saying that NHibernate (or any other ORM) is not vulnerable to SQL injections, and the above code is in a very bad style. Just that I couldn't make it work after a few minutes of trying.