Hibernate Tutorial - How not to write HQL queries? (SQL Injection in Hibernate)

Hello World!

Before we begin, let me explain SQL Injection in simple words ?

I write my name as “Vinay, you are free to go”.  Judge announces my case by saying: “Calling Vinay, you are free to go.”  The Bailiff lets me go.

What happened here? - The Bailiff interpreted a part of my name (ie - "you are free to go") as a command and executed it. 

Similarly the SQL interpreter cannot differentiate between user supplied input and commands. During an SQL Injection attack the SQL interpreter is tricked into executing the input data as commands.

A lot of developers feel that their applications are safe from SQL Injection if they are using ORM solutions like Hibernate. However, nothing can be further from the truth.

The best approach to avoid SQL Injection is to use Parameterized Queries in both SQL and Hibernate.

The following Query written in HQL is vulnerable to HQL Injection.

String HQLquery;
HQLquery= "from Users where userID='"+userID+"' and password='"+password+"'";
session.createQuery(HQLquery);

The proper way to write this query is following.

HQLquery=session.createQuery("from Users where userID=:u and password=:p");
HQLquery=query.setParameter("u",userID);
HQLquery=query.setParameter("p",password);

In the above query the semi colon ":" indicates that "u" and "p" are place holders.

When the createQuery() method is executed, it fixes the meaning of the SQL query and any parameter substitution in the query after this will be treated as data only. In a parameterized query nothing can change the intent of the query because the SQL Interpreter can clearly differentiate between the Commands and the Data.

In normal SQL, the following would be a vulnerable query.

String loginQuery;
SQLQuery=("Select * FROM users WHERE userID='"+userID+"' AND password='"+password+"'");

To prevent SQL Injection use Parameterized queries in Java

String SQLQuery = "SELECT * FROM users WHERE userID= ? AND password= ?"; 
PreparedStatement pstmt = conn.prepareStatement( SQLQuery );         
pstmt.setString(1,userID);
pstmt.setString(2,password);

Hope this helps :)

Thanks for reading!
Vinay