How to prevent SQL Injection with JPA and Hibernate?

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); 

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.

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();

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();