lishman levelup
«previous  next»


Getting Started
Associations
HQL



Parameter Binding in Hibernate Query Language


To appreciate the importance of parameter binding in HQL, we must first understand the SQL injection security threat.

SQL Injection

Suppose we have an application which allows an end user to search for a country. Partial matching and case insensitive searches are supported.

Now let us assume that users of this application are only authorized to search for African countries. We hard code this condition into the query to simulate the rule.
String userInput = "Ga";
String searchFor = "%" + userInput.toLowerCase() + "%";

List<Country> onlyAfricanCountries = session.createQuery(
      "from Country " +
      "where continent.name = 'Africa' " +
      "and lower(name) like '" + searchFor + "'")
      .list();
The appropriate SQL is generated with this where clause:
where CONT_NAME='Africa'
  and lower(CTRY_NAME) like '%ga%'
The query runs successfully and the correct results are returned.

However, suppose a malicious user enters this exact text in the input field:

     ' or '%'='

Now the where clause in the SQL looks like this:
CONT_NAME='Africa'
and lower(CTRY_NAME) like '%' or '%'='%'
The result - all rows are returned, including the ones the user is not authorized to see. or '%'='%' is true for every row.

Hackers can wreak havoc using this technique by executing stored procedures or DDL commands such as DROP TABLE.

The solution is to use parameter binding.

Parameter Binding

A bind variable is a named placeholder (preceded by a colon) that is embedded in the query string in place of a literal. The actual value is substituted at runtime using the setParameter() method.
String userInput = "' or '%'='";
String searchFor = "%" + userInput.toLowerCase() + "%";

List<Country> onlyAfricanCountries = session.createQuery(
    "from Country " +
    "where continent.name = 'Africa' " +
    "and lower(name) like :ctry_name")
    .setParameter("ctry_name", searchFor)
    .list();
Now, only countries in Africa which contain the characters ' or '%'=' in the country name will be returned and, of course, there will be none.

Only use bind variables where the value changes from one execution to the next.
List<Country> countries = session.createQuery(
    "from Country " +
    "where area between :smallest and :largest " +
    "and populationUpdatedOn >= '1-Jan-09'")
    .setParameter("smallest", 20000)
    .setParameter("largest", 500000)
    .list();
In this query, there is no need to compare the populationUpdatedOn property against a bind variable as the value is fixed for all executions.

Entity Binding

We can also use a persistent entity as the bind parameter.
Continent europe = (Continent) session.load(Continent.class, 3);
List<Country> europeanCountries = session.createQuery(
    "from Country where continent = :cont")
    .setParameter("cont", europe)
    .list();
This SQL is generated for us:
select
  CTRY_ID,
  AREA,
  CONT_ID,
  CURRENCY,
  CTRY_NAME,
  POP,
  POP_UPD_ON
from
  COUNTRY
where
  CONT_ID=?

Null Comparison

Beware when checking for null values using parameter binding. This HQL
List<Country> missingUpdatedOn = session.createQuery(
    "from Country where populationUpdatedOn = :upd_on")
    .setParameter("upd_on", null)
    .list();
produces this where clause in the SQL
 
where POP_UPD_ON=null
 
which returns no rows.

Use is null instead.
Query hqlQuery = session.createQuery(
      "from Country where populationUpdatedOn " +
      (popUpdOn==null ? "is null" : "= :upd_on"));

if (popUpdOn != null) {
  hqlQuery.setParameter("upd_on", popUpdOn);
}

List<Country> countries = hqlQuery.list();

Performance

Parameter binding should always be used to avoid the threat of SQL injection but there is another benefit too. When parameters are used, the database is able to use precompiled prepared statements which greatly improves performance.
»