Thursday, August 9, 2007

How to avoid SQL injection in Hibernate (A Hibernate Urban Legend)

Somewhere along the line java developers came to believe that Hibernate protects you from SQL injection. I'm not sure where they came to believe that. Maybe it is because you no longer have to write SQL and Hibernate does many other magical things - it has to protect you against SQL injection.

I'm tired of telling java developers that HQL has the same vulnerabilities as SQL, they don't believe me and think Hibernate offers them some sort of magical protection from bad HQL. Basically, what I term bad HQL is when named parameters are not used. Consider the following example:

String goodParameter="Raj lane";

Query badQuery = session.createQuery("from Address a where a.street='"+goodParameter+"'");

I have SQL logging turned on so I can see that the generated SQL is as follows:

select address0_.addressId as addressId, address0_.street as street1_ from Address address0_ where address0_.street='Raj lane'

Now consider the following where I attempt "HQL Injection"

String badParameter="la' or '1'='1";

Query reallyBadQuery = session.createQuery("from Address a where a.street='"+badParameter+"'");

And the resulting SQL:

select address0_.addressId as addressId, address0_.street as street1_ from Address address0_ where address0_.street='la' or '1'='1'

Note that the above SQL passes the parameter directly into the SQL. The generated SQL will return all rows in the table. Which is bad, but SQL injection opens us up to much worse attacks. So the moral of the story is to use named parameters, the above code can be fixed as follows:

String badParameter="la' or '1'='1";

Query reallyBadQuery = session.createQuery("from Address a where a.street=:street");

reallyBadQuery.setParameter("street", badParameter);

Rajesh Patel

Harpoon Technologies

2 comments:

M Chisty said...

The point of SQL injection is pretty clear. But still it is not clear: how using named parameter gonna solve the issue, because you are still passing the bad parameter in your named query. Can you please explain a bit?

Anonymous said...

M Chisty...it escapes special characters. so passing "'; drop table Blah;'asdf" (minus the ") will not work because it turns it into "\'; drop table Blah;\'asdf"