Use JDBC bind variables for handling JPA Criteria query numeric literals

Description

A predicate with a String literal criteriaBuilder.equal(from.get("name"), "horse"); creates a bind parameter: name=aram. Fine!

A predicate with a numeric literal criteriaBuilder.equal(from.get("age"), Integer.valueOf(42)); does not create a bind parameter: age=42. Not fine!

The concern here is that the fact that literals are used in the JDBC query rather than bind variables (parameters) a PreparedStatement cannot be effectively cached.

I think caused this issue.

Test Case

Animal.java

CriteriaParameterTest.java

Environment

None

Activity

Show:
Lukas Eder
May 26, 2017, 8:10 PM

I think that SQLi is not really related to this discussion. It is very easy for a SQL builder to correctly escape all sorts of literals, so it can handle this transparently. I mean, you're handling it just the same when using cb.literal(). Btw, using a String literal in a predicate generates a bind variable: cb.equal(from.get("title"), cb.literal("abc")...

A configuration option would definitely be a pragmatic way forward.

Lukas Eder
May 26, 2017, 8:20 PM

Side-note: I misunderstood the working of cb.literal(). Apparently, the burden of escaping input lies with the API user. That's unfortunate and does complicate things...

Lukas Eder
May 29, 2017, 3:44 PM

For the record, I've created a related issue about that inconsistency in cb.literal() handling: https://hibernate.atlassian.net/browse/HHH-11778

Steve Ebersole
July 27, 2017, 2:52 PM

I had actually already tackled this in 6.0. What I have there so far is:

We can add your AUTO here I guess, although I am not sure of the usefulness of it.

The special AS_PARAM_OUTSIDE_SELECT is to account for databases (DB2 for sure) that do not like parameters in the SELECT clause. In such cases we have 2 options:

  1. render the literal directly.

  2. render as a param, but wrap in a cast

We could also offer that as a distinction: AS_PARAM_OUTSIDE_SELECT + AS_PARAM_WRAPPED_IN_SELECT. Or alternatively we could simply interpret AS_PARAM + Dialect#requiresCastingOfParametersInSelectClause == true as "AS_PARAM_WRAPPED_IN_SELECT". I think the second option is the best option.

Vlad Mihalcea
September 26, 2017, 9:45 AM

Applied PR upstream.

Assignee

Andrea Boriero

Reporter

Kai PrĂ¼nte

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

backportDecision

None

Affects versions

Priority

Major
Configure