[JIRA] (HHH-13889) Case Select in Criteria API does not bind literals using parameters
by Michiel Haisma (JIRA)
Michiel Haisma ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832a... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiYzFjN2VlMWQz... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiYzFjN2... ) HHH-13889 ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiYzFjN2... ) Case Select in Criteria API does not bind literals using parameters ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiYzFjN2... )
Change By: Michiel Haisma ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832a... )
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]
( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=ey... )
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.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100121- sha1:6148daa )
4 years, 7 months
[JIRA] (HHH-13889) Case Select in Criteria API does not bind literals using parameters
by Michiel Haisma (JIRA)
Michiel Haisma ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832a... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNDI2ZTYyOTE0... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiNDI2ZT... ) HHH-13889 ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiNDI2ZT... ) Case Select in Criteria API does not bind literals using parameters ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiNDI2ZT... )
Change By: Michiel Haisma ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832a... )
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:
{ noformat 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
}{ noformat code }
Then the following test containing a query that will pass, because it's using a parameter:
{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}
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]
( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=ey... )
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.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100121- sha1:6148daa )
4 years, 7 months
[JIRA] (HHH-13889) Case Select in Criteria API does not bind literals using parameters
by Michiel Haisma (JIRA)
Michiel Haisma ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832a... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMzQ4ZGY5NTNm... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiMzQ4ZG... ) HHH-13889 ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiMzQ4ZG... ) Case Select in Criteria API does not bind literals using parameters ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiMzQ4ZG... )
Change By: Michiel Haisma ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832a... )
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 know want to be concerned with all the ins and outs of _EXACTLY_ 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:
{noformat} @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
}{noformat}
Then the following test containing a query that will pass, because it's using a parameter:
{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}
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]
( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=ey... )
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.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100121- sha1:6148daa )
4 years, 7 months
[JIRA] (HHH-13889) Case Select in Criteria API does not bind literals using parameters
by Michiel Haisma (JIRA)
Michiel Haisma ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832a... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNDc5NTY3NWRl... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiNDc5NT... ) HHH-13889 ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiNDc5NT... ) Case Select in Criteria API does not bind literals using parameters ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiNDc5NT... )
Change By: Michiel Haisma ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832a... )
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 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));
noformat }
@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
}
{noformat }
```
Then the following test containing a query that will pass, because it's using a parameter:
```
{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( cb.parameter(String.class, " otherwiseParam escapez'moi ") ) ; // CASE a.name = 'kitty' THEN 'Cat' ELSE :otherwiseParam <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).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= 😛aram0` :param0{noformat}
Simple gradlewrapper project with these tests attached.
[^hibernatecasewhenthensubtest.zip]
( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=ey... )
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.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100121- sha1:6148daa )
4 years, 7 months
[JIRA] (HHH-13889) Case Select in Criteria API does not bind literals using parameters
by Michiel Haisma (JIRA)
Michiel Haisma ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832a... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNjUzYjBlZGY4... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiNjUzYj... ) HHH-13889 ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiNjUzYj... ) Case Select in Criteria API does not bind literals using parameters ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiNjUzYj... )
Change By: Michiel Haisma ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832a... )
I've run into a potential bug or risk of using the criteria API with a 'SELECT ` CASE.. WHEN.. OTHERWISE ..' 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 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 😛aram0 `
Simple gradlewrapper project with these tests attached.
[^hibernatecasewhenthensubtest.zip]
( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=ey... )
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.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100121- sha1:6148daa )
4 years, 7 months
[JIRA] (HHH-13889) Case Select in Criteria API does not bind literals using parameters
by Michiel Haisma (JIRA)
Michiel Haisma ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832a... ) *created* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMzdhNGNiMWNm... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiMzdhNG... ) HHH-13889 ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiMzdhNG... ) Case Select in Criteria API does not bind literals using parameters ( https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiMzdhNG... )
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=5a3832a... )
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_hibernateca... )
( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=ey... )
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.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100121- sha1:6148daa )
4 years, 7 months