Named parameters not bound

Question

I have a multiple HQL statements which is vulnerable to the SQL injection example:

public List<Person> SearchList(String userName, String firstName, String lastName, String email) Exception {
   List<Person> personList = new ArrayList<>();
   String hql = " FROM **.***.***.entity.Person P WHERE ";
   boolean buName = false;
   boolean bfName = false;
   if (StringUtils.isNotEmpty(userName)){
      hql = hql + "lower(P.userName) like :userName "; 
      buName = true;
   }
   if (StringUtils.isNotEmpty(firstName) && StringUtils.isNotEmpty(lastName)){    
      if(buName){
         hql = hql + " OR ";
      }
      hql = hql + "(lower(P.firstName) like :firstName AND lower(P.lastName) like :lastName) ";  
      bfName = true;    
   }
   if (StringUtils.isNotEmpty(internetAddr)){    
      if(buName || bfName){
         hql = hql + " OR ";
      }
      hql = hql + "lower(P.email) = :email"; 
   }
   try {
      Query query = getCurrentSession().createQuery(hql);
        
      if (StringUtils.isNotEmpty(userName)) {
         query.setParameter("userName", '%'+userName.toLowerCase()+'%');
      } else if (StringUtils.isNotEmpty(firstName) && StringUtils.isNotEmpty(lastName)) {
         query.setParameter("firstName", '%'+firstName.toLowerCase()+'%');
         query.setParameter("lastName", '%'+lastName.toLowerCase()+'%');
      } else if (StringUtils.isNotEmpty(email)) {
         query.setParameter("email", email.toLowerCase());
      }

      personList = query.list();
   } catch(Exception e){
      throw new Exception(e.getMessage());
   }
   return personList;
}

Here I am avoiding concatenations in the query "'%" + userName + "%'" to avoid SQL injection vulnerability and now I am seeing exceptions related to

Named parameters not bound: lastname

This happens based the searches with firstname. How can I avoid this?

Answer 1

You should correct this:

if (StringUtils.isNotEmpty(userName)) {
   query.setParameter("userName", '%'+userName.toLowerCase()+'%');
} else if (StringUtils.isNotEmpty(firstName) && StringUtils.isNotEmpty(lastName)) {
   query.setParameter("firstName", '%'+firstName.toLowerCase()+'%');
   query.setParameter("lastName", '%'+lastName.toLowerCase()+'%');
} else if (StringUtils.isNotEmpty(email)) {
   query.setParameter("email", email.toLowerCase());
}   

to this:

if (StringUtils.isNotEmpty(userName)) {
   query.setParameter("userName", '%'+userName.toLowerCase()+'%');
}
if (StringUtils.isNotEmpty(firstName) && StringUtils.isNotEmpty(lastName)) {
   query.setParameter("firstName", '%'+firstName.toLowerCase()+'%');
   query.setParameter("lastName", '%'+lastName.toLowerCase()+'%');
}
if (StringUtils.isNotEmpty(email)) {
   query.setParameter("email", email.toLowerCase());
}   

as you should add named parameters to HQL and set them via query.setParameter consistently.

NOTE

I am avoiding concatenations in the query "'%" + userName + "%'" to avoid SQL injection vulnerability

You can use concatenation (operation) in HQL like below:

... 
lower(P.userName) like '%' || :userName || '%'
...

It will allow you to avoid SQL injection too. But please note that this like most likely will lead to the full table scan.