Hibernate : To handle special characters in HQL

In this article we will see to handle special characters in HQL similar to PreparedStatement in JDBC. It can also used for additional security purpose like to avoid SQL Injection.

For example, I have a below function in my BookDAO class to get detail of a particular book. It accepts bookTitle as a parameter and returns list of books. Here assume bookTitle can contain special characters.

public List findBook(String bookTitle) {
try {
String queryString = "from BsBooks where bookTitle = :bookTitle";
Query queryObject = getSession().createQuery(queryString);
queryObject.setParameter("bookTitle", bookTitle);
return queryObject.list();
} catch (RuntimeException re) {
throw re;
}
}

Note - the method setParameter(), it is used to set the value for bookTitle, which can contain special characters.

queryObject.setParameter("bookTitle", bookTitle);

The above method will give following error if you try to use it like - queryObject.setParameter(0, bookTitle);

Exception in thread "main" java.lang.IndexOutOfBoundsException: Remember that ordinal parameters are 1-based!
at org.hibernate.engine.query.ParameterMetadata.getOrdinalParameterDescriptor(ParameterMetadata.java:79)
at org.hibernate.engine.query.ParameterMetadata.getOrdinalParameterExpectedType(ParameterMetadata.java:85)
at org.hibernate.impl.AbstractQueryImpl.determineType(AbstractQueryImpl.java:421)
at org.hibernate.impl.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:393)


To avoid it you can use it as a '?' placeholder. When you execute the query, you would need to supply the value for it, which would replace the '?' in the query in below function.

public List findBook(String bookTitle) {
try {
String queryString = "from BsBooks where bookTitle = ?";
Query queryObject = getSession().createQuery(queryString);
queryObject.setParameter(0, bookTitle);
return queryObject.list();
} catch (RuntimeException re) {
throw re;
}
}


Below are the variations of the method setParameter, to know please see the API.

setParameter(int position, Object val) - Bind a value to a JDBC-style query parameter.

setParameter(int position, Object val, Type type) - Bind a value to a JDBC-style query parameter.

setParameter(String name, Object val) - Bind a value to a named query parameter.

setParameter(String name, Object val, Type type) - Bind a value to a named query parameter.

setParameterList(String name, Collection vals)  - Bind multiple values to a named query parameter.

setParameterList(String name, Collection vals, Type type) - Bind multiple values to a named query parameter.

setParameterList(String name, Object[] vals) - Bind multiple values to a named query parameter.

setParameterList(String name, Object[] vals, Type type) - Bind multiple values to a named query parameter.

setParameters(Object[] values, Type[] types) - Bind values and types to positional parameters.

Why PreparedStatement is used to prevents SQL Injection?

What is a SQL Injection?

In simple language SQL injection is injecting malicious sql code into the application's sql that may help attacker to retrieve sensitive information like user name / passwords etc OR it can also be used by hackers for login without authentication.

For example, We have a human resource management system, where in employee logs in and can view his / her sensitive information like attendance, salary etc. To log on to system every employee requires username and password. Now suppose below function is used to do authentication.

private boolean isAuthenticate(String userName, String password) {

Statement stat = connection.createStatement();

String query = "SELECT 1 FROM EMPLOYEE WHERE USER_NAME  = '"+userName+"'  AND PASSWORD = '"+password+"';";

ResultSet rset = stat.executeQuery(query);

if (rset.next()) {
return true;
}
else {
return false;
}
}


if the above query fetches result then allow the user to enter into the system else don't allow. You might think that the above process is correct but it's not like that, the above function has a serious flaw. let's see how.