Is CriteriaAPI query prone to SQL Injection?

Hibernate ORM
1

I was recently investigating SQL result of CriteriaQuery and noticed Hibernate 5.6.11 using PreparedStatement when ParameterExpression was set and lack of PreparedStatement when parameter was passed directly as a value. Does it mean that if we pass a value instead of ParameterExpression, then SQL injection will be possible ?

Example with ParameterExpression:


ParameterExpression carIdsParameter = builder.parameter(Collection.class);
query.multiselect(
carHibernate.get(carHibernate_.id),
carHibernate.get(carHibernate_.name),
carHibernate.get(carHibernate_.productionYear),
).where(carHibernate.get(carHibernate_.id)).in(carIdsParameter));

val result = session.createQuery(query)
					.setParameter(carIdsParameter, carIds)
					.setReadOnly(true)
					.getResultList();

Example with value passed directly:

query.multiselect(
carHibernate.get(carHibernate_.id),
carHibernate.get(carHibernate_.name),
carHibernate.get(carHibernate_.productionYear),
).where(carHibernate.get(carHibernate_.id)).in(carIds));

val result =  session.createQuery(query)
                     .setReadOnly(true)
                     .getResultList();
2

Due to limitations of the implementation in Hibernate 5, integer values are inlined as literals. In Hibernate 6, this was fixed, but there is no SQL injection possible. Even for string literals, the escaping is done to avoid SQL injection.

3

Thanks for such quick response, so as I understand in both cases PreparedStatement will be created anyway ?

4

Yes, Hibernate always uses the PreparedStatement API.

5

Ok, but in this case what is the major difference between those two queries in the example above ?

Why question marks are present in case of first result and does it have any impact on performance ?

First example SQL result :

select carHibernate_."ID", carHibernate_."NAME", carHibernate_."PRODUCTION_YEAR"
from "public"."CAR_CORE" carHibernate_
where carHibernate_."ID" in (?, ?, ?, ?, ?)

Second example SQL result :

select carHibernate_."ID", carHibernate_."NAME", carHibernate_."PRODUCTION_YEAR"
from "public"."CAR_CORE" carHibernate_
where carHibernate_."ID" in (123, 3, 23, 1, 43)
6

The query plan of the first query is probably going to be cached by the DB which reduces latency a bit as the database doesn’t always have to parse the query string and some databases even cache the execution plan. Depending on the selectivity of the column the performance might be worse though, because the optimizer doesn’t know anything about the parameter values.

In your particular case, it’s probably better to use parameter markers, as I guess the id column has a high selectivity since it is unique.

1 Like