[hibernate-issues] [JIRA] (HHH-13889) Case Select in Criteria API does not bind literals using parameters

[Michiel Haisma (JIRA)]

Michiel Haisma ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832af72a734387e2c73e9 ) *created* an issue

Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMzdhNGNiMWNmOWQwNDJhZjliMzVhZGMyYjQzYWVhZDgiLCJwIjoiaiJ9 ) / Bug ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiMzdhNGNiMWNmOWQwNDJhZjliMzVhZGMyYjQzYWVhZDgiLCJwIjoiaiJ9 ) HHH-13889 ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiMzdhNGNiMWNmOWQwNDJhZjliMzVhZGMyYjQzYWVhZDgiLCJwIjoiaiJ9 ) Case Select in Criteria API does not bind literals using parameters ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiMzdhNGNiMWNmOWQwNDJhZjliMzVhZGMyYjQzYWVhZDgiLCJwIjoiaiJ9 )

Issue Type: Bug Affects Versions: 5.4.12 Assignee: Unassigned Attachments: hibernatecasewhenthensubtest.zip Components: hibernate-core, query-criteria Created: 05/Mar/2020 09:16 AM Environment: hibernate-core 5.4.12.Final
java persistence API hibernate-jpa-2.1-api Priority: Major Reporter: Michiel Haisma ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832af72a734387e2c73e9 )

I've run into a potential bug or risk of using the criteria API with a 'SELECT CASE .. WHEN .. OTHERWISE ..' 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 know all the ins and outs of EXACTLY how to escape all my values properly. 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:

```
public class AnimalTest {

private EntityManager entityManager;

@Before
public void setUp()

{ EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("casewhenthennosub"); entityManager = entityManagerFactory.createEntityManager( Collections.singletonMap(AvailableSettings.CRITERIA_LITERAL_HANDLING_MODE, LiteralHandlingMode.BIND)); }

@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"); 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 }

}
```

Then the following test containing a query that will pass, because it's using a parameter:
```
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, "otherwiseParam")); // CASE a.name = 'kitty' THEN 'Cat' ELSE :otherwiseParam
criteriaQuery.multiselect(caseSelect);
criteriaQuery.where(cb.equal(animalRoot.get("name"), "myFavoriteAnimal"));
TypedQuery<?> typedQuery = entityManager.createQuery(criteriaQuery).setParameter("otherwiseParam", "escapez'moi");

QueryImpl<?> query = typedQuery.unwrap(QueryImpl.class);
System.out.println(query.getQueryString());
```
prints: `select new Animal(case when generatedAlias0.name='kitty' then 'Cat' else :otherwiseParam end) from Animal as generatedAlias0 where generatedAlias0.name=:param0`

Simple gradlewrapper project with these tests attached. hibernatecasewhenthensubtest.zip ( https://hibernate.atlassian.net/secure/attachment/49639/49639_hibernatecasewhenthensubtest.zip )

( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=eyJpIjoiMzdhNGNiMWNmOWQwNDJhZjliMzVhZGMyYjQzYWVhZDgiLCJwIjoiaiJ9 ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=eyJpIjoiMzdhNGNiMWNmOWQwNDJhZjliMzVhZGMyYjQzYWVhZDgiLCJwIjoiaiJ9 )

Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8 ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100121- sha1:6148daa )
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-issues/attachments/20200305/1f366136/attachment.html