How to prevent SQL Injection with JPA and Hibernate?

Question

I am developing an application using hibernate. When I try to create a Login page, The problem of Sql Injection arises. I have the following code:

@Component
@Transactional(propagation = Propagation.SUPPORTS)
public class LoginInfoDAOImpl implements LoginInfoDAO{

@Autowired
private SessionFactory sessionFactory;      
@Override
public LoginInfo getLoginInfo(String userName,String password){
    List<LoginInfo> loginList = sessionFactory.getCurrentSession().createQuery("from LoginInfo where userName='"+userName+"' and password='"+password+"'").list();
    if(loginList!=null )
        return loginList.get(0);
    else return null;   
          }
      }

How will i prevent Sql Injection in this scenario ?The create table syntax of loginInfo table is as follows:

create table login_info
  (user_name varchar(16) not null primary key,
  pass_word varchar(16) not null); 

Solution 1

Query q = sessionFactory.getCurrentSession().createQuery("from LoginInfo where userName = :name");
q.setParameter("name", userName);
List<LoginInfo> loginList = q.list();

You have other options too, see this nice article from mkyong.

Solution 2

You need to use named parameters to avoid sql injection. Also (nothing to do with sql injection but with security in general) do not return the first result but use getSingleResult so if there are more than one results for some reason, the query will fail with NonUniqueResultException and login will not be succesful

 Query query= sessionFactory.getCurrentSession().createQuery("from LoginInfo where userName=:userName  and password= :password");
 query.setParameter("username", userName);
 query.setParameter("password", password);
 LoginInfo loginList = (LoginInfo)query.getSingleResult();

Solution 3

What is SQL Injection?

SQL Injection happens when a rogue attacker can manipulate the query building process so that he can execute a different SQL statement than what the application developer has originally intended

How to prevent the SQL injection attack

The solution is very simple and straight-forward. You just have to make sure that you always use bind parameters:

public PostComment getPostCommentByReview(String review) {
    return doInJPA(entityManager -> {
        return entityManager.createQuery("""
            select p
            from PostComment p
            where p.review = :review
            """, PostComment.class)
        .setParameter("review", review)
        .getSingleResult();
    });
}

Now, if some is trying to hack this query:

getPostCommentByReview("1 AND 1 >= ALL ( SELECT 1 FROM pg_locks, pg_sleep(10) )");

the SQL Injection attack will be prevented:

Time:1, Query:["select postcommen0_.id as id1_1_, postcommen0_.post_id as post_id3_1_, postcommen0_.review as review2_1_ from post_comment postcommen0_ where postcommen0_.review=?"], Params:[(1 AND 1 >= ALL ( SELECT 1 FROM pg_locks, pg_sleep(10) ))]

JPQL Injection

SQL Injection can also happen when using JPQL or HQL queries, as demonstrated by the following example:

public List<Post> getPostsByTitle(String title) {
    return doInJPA(entityManager -> {
        return entityManager.createQuery(
            "select p " +
            "from Post p " +
            "where" +
            "   p.title = '" + title + "'", Post.class)
        .getResultList();
    });
}

The JPQL query above does not use bind parameters, so it’s vulnerable to SQL injection.

Check out what happens when I execute this JPQL query like this:

List<Post> posts = getPostsByTitle(
    "High-Performance Java Persistence' and " +
    "FUNCTION('1 >= ALL ( SELECT 1 FROM pg_locks, pg_sleep(10) ) --',) is '"
);

Hibernate executes the following SQL query:

Time:10003, QuerySize:1, BatchSize:0, Query:["select p.id as id1_0_, p.title as title2_0_ from post p where p.title='High-Performance Java Persistence' and 1 >= ALL ( SELECT 1 FROM pg_locks, pg_sleep(10) ) --()=''"], Params:[()]

Dynamic queries

You should avoid queries that use String concatenation to build the query dynamically:

String hql = " select e.id as id,function('getActiveUser') as name from " + domainClass.getName() + " e ";
Query query=session.createQuery(hql);
return query.list();

If you want to use dynamic queries, you need to use Criteria API instead:

Class<Post> entityClass = Post.class;
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<?> root = query.from(entityClass);
query.select(
    cb.tuple(
        root.get("id"),
        cb.function("now", Date.class)
    )
);


return entityManager.createQuery(query).getResultList();

Solution 4

I would like to add here that is a peculiar SQL Injection that is possible with the use of Like queries in searches.

Let us say we have a query string as follows:

queryString = queryString + " and c.name like :name";

While setting the name parameter, most would generally use this.

query.setParameter("name", "%" + name + "%");

Now, as mentioned above traditional parameter like "1=1" cannot be injected because of the TypedQuery and Hibernate will handle it by default.

But there is peculiar SQL Injection possible here which is because of the LIKE Query Structure which is the use of underscores

The underscore wildcard is used to match exactly one character in MySQL meaning, for example, select * from users where user like 'abc_de'; This will produce outputs as users that start with abc, end with de and have exactly 1 character in between.

Now, if in our scenario, if we set

• name="_" produces customers whose name is at least 1 letter
• name="__" produces customers whose name is at least 2 letters
• name="___" produces customers whose name is at least 3 letters

and so on.

Ideal fix:

To mitigate this, we need to escape all underscores with a prefix .

___ will become \_\_\_ (equivalent to 3 raw underscores)

Likewise, the vice-versa query will also result in an injection in which %'s need to be escaped.

View more solutions

Comments

[Mr. Singthoi]

Thanks, How can we include password in the Query??@Petr Mensik

[Petr Mensik]

In the same way as I included the userName

[Dennis]

I agree, it is technically better, because of speed. however, no ORM framework I know of in any language generates stored procedure s. It would be really nice if they did. But since they don't, and the productivity provided by ORM frameworks is essential, the the prepared statements are going to dominate.

[Diablo]

usage of stored procedures for everything is not so wise in ORM. prepared statements should be good!