I've run into a potential bug or risk of using the criteria API with a `CASE WHEN THEN ELSE ` selection. I see that when I pass in expressions that contain literals, they will be inlined into the query, and not bound using parameters. This causes errors when literals contain certain characters and could lead to SQL injection problems potentially. This behavior differs from when I use literals in `WHERE` clauses, where String-typed literals are bound by default (could be inlined depending on your inline-vs-binding setting, I suppose).
It also does not seem to matter whether I create criteria queries based on direct literals or use a literal expression, they never seem to get bound unless I manually create a parameter binding expression and use that in my `CASE`.
My main question is: is this expected behavior?
Personally I kind of expect anything I throw into a criteria API to be bound using parameters when its at any risk of containing unescaped code, because as a query writer, I don't want to be concerned with all the ins and outs of how to escape all my values properly, I want Hibernate to do that for me. Nor do I expect to manually create parameter bindings for every single String literal (although I am doing that now). It does seem to work for all 'normal' WHERE clauses using literals, but not for `CASE`.
I've crafted an example project that has two tests. One is using criteria API as normally, but it will fail if my String literal contains some illegal character and Hibernate will choke on it at some point. The other test I use a parameter binding to let Hibernate take care of proper escaping. Example test that will currently fail:
{code:java} @Test
public void testCaseWhenOtherwise() {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Animal> criteriaQuery = cb.createQuery(Animal.class);
Root<Animal> animalRoot = criteriaQuery.from(Animal.class);
CriteriaBuilder.Case<String> sCase = cb.selectCase();
Expression<String> caseSelect = sCase.when(cb.equal(animalRoot.get("name"), cb.literal("kitty")), cb.literal("Cat"))
.otherwise("escapez'moi"); // CASE a.name = 'kitty' THEN 'Cat' ELSE <Hibernate throws up>
criteriaQuery.multiselect(caseSelect);
criteriaQuery
.where(cb.equal(animalRoot.get("name"), "myFavoriteAnimal")); // myFavorite will be bound by a variable as I would expect
TypedQuery<?> typedQuery = entityManager.createQuery(criteriaQuery);
QueryImpl<?> query = typedQuery.unwrap(QueryImpl.class);
System.out.println(query.getQueryString()); // will throw up due to the apostrophe in the 'otherwise' expression
}{code}
Then the following test containing a query that will pass, because it's using a parameter:
{code:java} @Test
public void testCaseWhenOtherwise testCaseWhenOtherwiseParameter () {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Animal> criteriaQuery = cb.createQuery(Animal.class);
Root<Animal> animalRoot = criteriaQuery.from(Animal.class);
CriteriaBuilder.Case<String> sCase = cb.selectCase();
Expression<String> caseSelect = sCase.when(cb.equal(animalRoot.get("name"), cb.literal("kitty")), cb.literal("Cat"))
.otherwise( cb.parameter(String.class, " escapez'moi otherwiseParam ") ) ; // CASE a.name = 'kitty' THEN 'Cat' ELSE <Hibernate throws up> :otherwiseParam
criteriaQuery.multiselect(caseSelect);
criteriaQuery
.where(cb.equal(animalRoot.get("name"), "myFavoriteAnimal")); // myFavorite will be bound by a variable as I would expect
TypedQuery<?> typedQuery = entityManager.createQuery(criteriaQuery) .setParameter("otherwiseParam", "escapez'moi") ;
QueryImpl<?> query = typedQuery.unwrap(QueryImpl.class);
System.out.println(query.getQueryString()); // will throw up due to the apostrophe in the 'otherwise' expression
}{code}
prints:
{noformat}select new mypackage.Animal(case when generatedAlias0.name='kitty' then 'Cat' else :otherwiseParam end) from Animal as generatedAlias0 where generatedAlias0.name=:param0{noformat}
Simple gradlewrapper project with these tests attached.
[^hibernatecasewhenthensubtest.zip]
|
|
