Parameter binding is the process of binding a Java variable with an HQL statement. Using Parameter binding and not string concatenation for HQL statement creation will also guard against attacks like SQL injection. Examples in this article are a continuation of the ones in the article http://www.javajee.com/introduction-to-hibernate-query-language-hql.  Instead of hard-cording the values in the HQL statement like "Select id from User where id = 1 ", we can bind a java variable to an HQL statement as:

int idVar=1;

Query query = session.createQuery("Select id from User where id = ?");

query.setInteger(0, idVar);

List<Integer> ids = (List<Integer>) query.list();

  for (int i : ids) {

    System.out.println(i);

    }

…

You should have this code within a transaction boundary as in the examples at http://www.javajee.com/introduction-to-hibernate-query-language-hql and test it simililarly.

We pass the position of the question mark and the variable name to the query.setXXX (XXX= any type like Integer, Decimal etc.) methods. Here we have only one ‘?’ and its position is 0.  Find another HQL example statement with multiple query parameters:

int idVar = 1;

String namePattern="User%";

Query query = session.createQuery("Select id from User where id > ? and name like ? ");

query.setInteger(0, idVar);

query.setString(1, namePattern);

…

Creating HQL statement through string concatenation and SQL injection risks

You can also create HQL queries like above using String concatenation like  “Select id from User where id = “ + idVar + “and name =”+ namePattern. But this syntax is difficult to maintain and also invite opportunities for SQL injection. We will see SQL injection later, but in summary, it is a process by which user will pass something else in a form parameter and get those record which he is not supposed to get. For instance, assuming if namePattern is taken from user through a form, he can pass “X% or 1=1”.

int idVar = 1;

String namePattern = "'X%' or 1=1";

Query query = session.createQuery("Select id from User where id > " + idVar

    + " and name like " + namePattern);

List<Integer> ids = (List<Integer>) query.list();

…

Final query created by appending will be "Select id from User where id > 0 and name like 'X%' or 1=1". This will return all the records irrespective of the ids and names as the or condition 1=1 will be always true.

Above situation will not happen in the case of parameter binding, as HQL creates the final HQL statement in such a way that the whole of namePattern is compared with the name field alone and not just appended to the HQL statement.  Now try passing the same idVar and namePattern variables to the HQL statement using parameter binding:

int idVar = 1;

String namePattern="'X%' or 1=1";

Query query = session.createQuery("Select id from User where id > ? and name like ?");

query.setInteger(0, idVar);

query.setString(1, namePattern);

List<Integer> ids = (List<Integer>) query.list();

…

Named Parameters

We can use named placeholders in the HQL statement rather than using question marks (?) and avoid the need to remember the positions of each question marks (?) for binding variables;

int idVar = 1;

String namePattern="U%";

Query query = session.createQuery("Select id from User where id > :idVarHolder and name like :namePatternHolder");

query.setInteger("idVarHolder", idVar);

query.setString("namePatternHolder", namePattern);

List<Integer> ids = (List<Integer>) query.list();

…

You define a named placeholder by writing the placeholder name after a colon (:) as :holdername and use it within a setXXX method without the colon (:) as “holdername”.