Hibernate createSQLQuery - How does it validate the SQL?

Question

I have some code that translates a user's search word into a MySQL query:

String sql = String.format("SELECT * FROM my_table WHERE my_column = '%s'", value);
HibernateUtil.getCurrentSession().createSQLQuery(sql);

To test if I was protected—and because I think it would be fun to learn in this way—I wanted to try to SQL inject my own application. So I searched for:

x'; DROP TABLE test;--

which results in the following query:

SELECT * FROM my_table WHERE my_column = 'x'; DROP TABLE test;--

But Hibernate throws a SQLGrammarException. When I run this code via phpMyAdmin, it correctly drops the test table.

How is Hibernate validating my SQL? Perhaps more importantly—is this protecting me against SQL injection or should I be using setParameter. If it's not protecting me, can I have an example of some SQL that will perform the injection. I think it would be fun to actually verify.

@doublesharp, right, but before I change all my data access code, I wanted to verify that `createSQLQuery` does *not* sanitize my SQL. — jds, Oct 06 '15 at 21:51

score 1 · Answer 1 · edited May 23 '17 at 12:29

According to Hibermate documentation, the method createSQLQuery "Create a new instance of Query for the given SQL string", so we can assume that Hibernate do the SQL checking for a single query on every call of this method.

Important: createSQLQuery is deprecated on Hibernate, please check out the link given above to see newers ways to execute SQL queries.

Now, speaking about how you could protect yourself from SQL injection, the best way to do it is using parameters on your query.

This question has the exactly example you are in need for, please check it out.

Hope this help you in your studies, good luck!

Tobias Liefke · Accepted Answer · 2015-10-10T12:32:53.247

You are protected against execution of more than one statement because createSQLQuery allows exactly one statement. It is not Hibernate which protects you here, but your JDBC driver respectively your database - because it does not know how to handle the separator ; in the context of a single statement.

But you are still not safe against SQL injection. There are plenty of other possibilities to inject SQL in that case. Just one example:

Imagine you are searching for some user specific items in your query:

 String sql = String.format(
     "SELECT * FROM my_table WHERE userId = %s AND my_column = '%s'", 
     currentUserId, value);

The user can now enter:

 x' OR '1' = '1

Which will lead to the SQL:

 SELECT * FROM my_table WHERE userId = 1234 AND my_column = 'x' OR '1' = '1'

And because AND has higher precedence, he will see all items - even those of other users.

Even your provided example can be dangerous, for example

 x' OR (SELECT userName FROM USER) = 'gwg

will let me know if your database contains a user that is called gwg (assuming that I know your database layout, which I could find out with similar queries).

Hibernate createSQLQuery - How does it validate the SQL?

2 Answers2