<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0">
<base href="https://hibernate.atlassian.net">
<title>Message Title</title>
</head>
<body class="jira" style="color: #333333; font-family: Arial, sans-serif; font-size: 14px; line-height: 1.429">
<table id="background-table" cellpadding="0" cellspacing="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; background-color: #f5f5f5; border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0" bgcolor="#f5f5f5">
<!-- header here -->
<tbody>
<tr>
<td id="header-pattern-container" style="padding: 0; border-collapse: collapse; padding: 10px 20px">
<table id="header-pattern" cellspacing="0" cellpadding="0" border="0" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td id="header-avatar-image-container" valign="top" style="padding: 0; border-collapse: collapse; vertical-align: top; width: 32px; padding-right: 8px" width="32"> <img id="header-avatar-image" class="image_fix" src="https://secure.gravatar.com/avatar/dec60688cba5af01c4b2d885511f15f9?d=https%3A%2F%2Favatar-management--avatars.us-west-2.prod.public.atl-paas.net%2Finitials%2FMH-2.png&size=48&s=48" height="32" width="32" border="0" style="border-radius: 3px; vertical-align: top"> </td>
<td id="header-text-container" valign="middle" style="padding: 0; border-collapse: collapse; vertical-align: middle; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 1px"> <a class="user-hover" rel="michielhaisma" style="color:#6c797f;; color: #3b73af; text-decoration: none" id="email_michielhaisma" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832af72a734387e2c73e9"> Michiel Haisma </a> <strong>updated</strong> an issue </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td id="email-content-container" style="padding: 0; border-collapse: collapse; padding: 0 20px">
<table id="email-content-table" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; border-spacing: 0; border-collapse: separate">
<tbody>
<tr>
<!-- there needs to be content in the cell for it to render in some clients -->
<td class="email-content-rounded-top mobile-expand" style="padding: 0; border-collapse: collapse; color: #ffffff; padding: 0 15px 0 16px; height: 15px; background-color: #ffffff; border-left: 1px solid #cccccc; border-top: 1px solid #cccccc; border-right: 1px solid #cccccc; border-bottom: 0; border-top-right-radius: 5px; border-top-left-radius: 5px; height: 10px; line-height: 10px; padding: 0 15px 0 16px; mso-line-height-rule: exactly" height="10" bgcolor="#ffffff"> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand " style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff" bgcolor="#ffffff">
<table class="page-title-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td class="page-title-pattern-first-line " style="padding: 0; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; padding-top: 10px"> <a href="https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNjUzYjBlZGY4OTc5NDM4NGJjZTQzMTM3MmEwYjBhNTciLCJwIjoiaiJ9" style="color: #3b73af; text-decoration: none">Hibernate ORM</a> / <a href="https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiNjUzYjBlZGY4OTc5NDM4NGJjZTQzMTM3MmEwYjBhNTciLCJwIjoiaiJ9" style="color: #3b73af; text-decoration: none"><img src="cid:jira-generated-image-avatar-738d93cd-58c6-4c76-8326-4bfd234adab1" height="16" width="16" border="0" align="absmiddle" alt="Bug" style="vertical-align: text-bottom"></a> <a href="https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiNjUzYjBlZGY4OTc5NDM4NGJjZTQzMTM3MmEwYjBhNTciLCJwIjoiaiJ9" style="color: #3b73af; text-decoration: none">HHH-13889</a> </td>
</tr>
<tr>
<td style="vertical-align: top;; padding: 0; border-collapse: collapse; padding-right: 5px; font-size: 20px; line-height: 30px; mso-line-height-rule: exactly" class="page-title-pattern-header-container"> <span class="page-title-pattern-header" style="font-family: Arial, sans-serif; padding: 0; font-size: 20px; line-height: 30px; mso-text-raise: 2px; mso-line-height-rule: exactly; vertical-align: middle"> <a href="https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiNjUzYjBlZGY4OTc5NDM4NGJjZTQzMTM3MmEwYjBhNTciLCJwIjoiaiJ9" style="color: #3b73af; text-decoration: none">Case Select in Criteria API does not bind literals using parameters</a> </span> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand wrapper-special-margin" style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff; padding-top: 10px; padding-bottom: 5px" bgcolor="#ffffff">
<table class="keyvalue-table" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Change By:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <a class="user-hover" rel="michielhaisma" style="color:#6c797f;; color: #3b73af; text-decoration: none" id="email_michielhaisma" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a3832af72a734387e2c73e9"> Michiel Haisma </a> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand issue-description-container" style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff; padding-top: 5px; padding-bottom: 10px" bgcolor="#ffffff">
<table class="text-paragraph-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 2px">
<tbody>
<tr>
<td class="text-paragraph-pattern-container mobile-resize-text " style="padding: 0; border-collapse: collapse; padding: 0 0 10px"> <span class="diffcontext">I've run into a potential bug or risk of using the criteria API with a</span> <span class="diffremovedchars" style="background-color: #ffe7e7; text-decoration:line-through;"> 'SELECT</span> <span class="diffaddedchars" style="background-color:#ddfade;"> `</span> <span class="diffcontext"> CASE</span> <span class="diffremovedchars" style="background-color: #ffe7e7; text-decoration:line-through;"> ..</span> <span class="diffcontext"> WHEN</span> <span class="diffremovedchars" style="background-color: #ffe7e7; text-decoration:line-through;"> .. OTHERWISE ..'</span> <span class="diffaddedchars" style="background-color:#ddfade;"> THEN ELSE `</span> <span class="diffcontext"> 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).<br><br>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`.<br><br>My main question is: is this expected behavior?<br>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`.<br><br>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:<br><br>```<br>public class AnimalTest</span> <span class="diffaddedchars" style="background-color:#ddfade;"> \</span> <span class="diffcontext"> {<br><br> private EntityManager entityManager;<br><br> @Before<br> public void setUp()</span> <span class="diffaddedchars" style="background-color:#ddfade;"> \</span> <span class="diffcontext"> {<br> EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("casewhenthennosub");<br> entityManager = entityManagerFactory.createEntityManager(<br> Collections.singletonMap(AvailableSettings.CRITERIA_LITERAL_HANDLING_MODE, LiteralHandlingMode.BIND));<br> }<br><br> @Test<br> public void testCaseWhenOtherwise()</span> <span class="diffaddedchars" style="background-color:#ddfade;"> \</span> <span class="diffcontext"> {<br> CriteriaBuilder cb = entityManager.getCriteriaBuilder();<br> CriteriaQuery<Animal> criteriaQuery = cb.createQuery(Animal.class);<br><br> Root<Animal> animalRoot = criteriaQuery.from(Animal.class);<br> CriteriaBuilder.Case<String> sCase = cb.selectCase();<br> Expression<String> caseSelect = sCase.when(cb.equal(animalRoot.get("name"), cb.literal("kitty")), cb.literal("Cat"))<br> .otherwise("escapez'moi");<br> criteriaQuery.multiselect(caseSelect);<br> criteriaQuery<br> .where(cb.equal(animalRoot.get("name"), "myFavoriteAnimal")); // myFavorite will be bound by a variable as I would expect<br> TypedQuery<?> typedQuery = entityManager.createQuery(criteriaQuery);<br><br> QueryImpl<?> query = typedQuery.unwrap(QueryImpl.class);<br> System.out.println(query.getQueryString()); // will throw up due to the apostrophe in the 'otherwise' expression<br> }<br>}<br>```<br><br>Then the following test containing a query that will pass, because it's using a parameter:<br>```<br> CriteriaBuilder cb = entityManager.getCriteriaBuilder();<br> CriteriaQuery<Animal> criteriaQuery = cb.createQuery(Animal.class);<br><br> Root<Animal> animalRoot = criteriaQuery.from(Animal.class);<br> CriteriaBuilder.Case<String> sCase = cb.selectCase();<br> Expression<String> caseSelect = sCase.when(cb.equal(animalRoot.get("name"), cb.literal("kitty")), cb.literal("Cat"))<br> .otherwise(cb.parameter(String.class, "otherwiseParam")); // CASE a.name = 'kitty' THEN 'Cat' ELSE :otherwiseParam<br> criteriaQuery.multiselect(caseSelect);<br> criteriaQuery.where(cb.equal(animalRoot.get("name"), "myFavoriteAnimal"));<br> TypedQuery<?> typedQuery = entityManager.createQuery(criteriaQuery).setParameter("otherwiseParam", "escapez'moi");<br><br> QueryImpl<?> query = typedQuery.unwrap(QueryImpl.class);<br> System.out.println(query.getQueryString());<br>```<br>prints: `select new Animal(case when generatedAlias0.name='kitty' then 'Cat' else :otherwiseParam end) from Animal as generatedAlias0 where generatedAlias0.name=</span> <span class="diffremovedchars" style="background-color: #ffe7e7; text-decoration:line-through;">:param0</span> <span class="diffaddedchars" style="background-color:#ddfade;">😛aram0</span> <span class="diffcontext">`<br><br>Simple gradlewrapper project with these tests attached.</span> <span class="diffaddedchars" style="background-color:#ddfade;"> <br><br></span> <span class="diffcontext"> [^hibernatecasewhenthensubtest.zip]</span> <span class="diffremovedchars" style="background-color: #ffe7e7; text-decoration:line-through;"> </span> <span class="diffaddedchars" style="background-color:#ddfade;"><br><br></span> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand " style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff" bgcolor="#ffffff"> <script type="application/ld+json">
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"description": "View Issue",
"potentialAction": {
"@type": "ViewAction",
"target": "https://hibernate.atlassian.net/browse/HHH-13889?inbox=true&",
"name": "View Issue"
},
"publisher": {
"@type": "Organization",
"name": "Atlassian",
"url": "https://www.atlassian.com"
}
}
</script>
<table id="actions-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 1px">
<tbody>
<tr>
<td id="actions-pattern-container" valign="middle" style="padding: 0; border-collapse: collapse; padding: 10px 0 10px 24px; vertical-align: middle; padding-left: 0">
<table align="left" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td class="actions-pattern-action-icon-container" style="padding: 0; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 0; vertical-align: middle"> <a href="https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=eyJpIjoiNjUzYjBlZGY4OTc5NDM4NGJjZTQzMTM3MmEwYjBhNTciLCJwIjoiaiJ9" target="_blank" title="Add Comment" style="color: #3b73af; text-decoration: none"> <img class="actions-pattern-action-icon-image" src="cid:jira-generated-image-static-comment-icon-2765e135-9cce-4da0-98d5-57e4f5a88bfc" alt="Add Comment" title="Add Comment" height="16" width="16" border="0" style="vertical-align: middle"> </a> </td>
<td class="actions-pattern-action-text-container" style="padding: 0; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 4px; padding-left: 5px"> <a href="https://hibernate.atlassian.net/browse/HHH-13889#add-comment?atlOrigin=eyJpIjoiNjUzYjBlZGY4OTc5NDM4NGJjZTQzMTM3MmEwYjBhNTciLCJwIjoiaiJ9" target="_blank" title="Add Comment" style="color: #3b73af; text-decoration: none">Add Comment</a> </td>
</tr>
</tbody>
</table> </td>
</tr>
</tbody>
</table> </td>
</tr>
<!-- there needs to be content in the cell for it to render in some clients -->
<tr>
<td class="email-content-rounded-bottom mobile-expand" style="padding: 0; border-collapse: collapse; color: #ffffff; padding: 0 15px 0 16px; height: 5px; line-height: 5px; background-color: #ffffff; border-top: 0; border-left: 1px solid #cccccc; border-bottom: 1px solid #cccccc; border-right: 1px solid #cccccc; border-bottom-right-radius: 5px; border-bottom-left-radius: 5px; mso-line-height-rule: exactly" height="5" bgcolor="#ffffff"> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td id="footer-pattern" style="padding: 0; border-collapse: collapse; padding: 12px 20px">
<table id="footer-pattern-container" cellspacing="0" cellpadding="0" border="0" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td id="footer-pattern-mobile-separated-links" class="mobile-resize-text" width="100%" colspan="2" style="padding: 0; border-collapse: collapse; color: #999999; font-size: 12px; line-height: 18px; font-family: Arial, sans-serif; mso-line-height-rule: exactly; mso-text-raise: 2px"> Get Jira notifications on your phone! Download the Jira Cloud app for <a href="https://play.google.com/store/apps/details?id=com.atlassian.android.jira.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail" style="color: #3b73af; text-decoration: none">Android</a> or <a href="https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8" style="color: #3b73af; text-decoration: none">iOS</a>
<hr> </td>
</tr>
<tr>
<td id="footer-pattern-text" class="mobile-resize-text" width="100%" style="padding: 0; border-collapse: collapse; color: #999999; font-size: 12px; line-height: 18px; font-family: Arial, sans-serif; mso-line-height-rule: exactly; mso-text-raise: 2px"> This message was sent by Atlassian Jira <span id="footer-build-information">(v1001.0.0-SNAPSHOT#100121-<span title="6148daa82d6c5e3dfe784a063f488875cd8fe6d7" data-commit-id="6148daa82d6c5e3dfe784a063f488875cd8fe6d7}">sha1:6148daa</span>)</span> </td>
<td id="footer-pattern-logo-desktop-container" valign="top" style="padding: 0; border-collapse: collapse; padding-left: 20px; vertical-align: top">
<table style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td id="footer-pattern-logo-desktop-padding" style="padding: 0; border-collapse: collapse; padding-top: 3px; opacity: 0.150"> <img id="footer-pattern-logo-desktop" src="cid:jira-generated-image-static-footer-desktop-logo-a12fbcd2-1b0e-45f6-8438-1f0dcb84817f" alt="Atlassian logo" title="Atlassian logo" width="192" height="24" class="image_fix"> </td>
</tr>
</tbody>
</table> </td>
</tr>
</tbody>
</table> </td>
</tr>
</tbody>
</table>
</body>
</html>