<!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=eyJpIjoiNDI2ZTYyOTE0ZGVhNDIwYWEwYmQyZWYwZGRmYzhmY2UiLCJwIjoiaiJ9" style="color: #3b73af; text-decoration: none">Hibernate ORM</a> / <a href="https://hibernate.atlassian.net/browse/HHH-13889?atlOrigin=eyJpIjoiNDI2ZTYyOTE0ZGVhNDIwYWEwYmQyZWYwZGRmYzhmY2UiLCJwIjoiaiJ9" style="color: #3b73af; text-decoration: none"><img src="cid:jira-generated-image-avatar-a16e4746-14ff-4c1e-9348-60a91a105f58" 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=eyJpIjoiNDI2ZTYyOTE0ZGVhNDIwYWEwYmQyZWYwZGRmYzhmY2UiLCJwIjoiaiJ9" 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=eyJpIjoiNDI2ZTYyOTE0ZGVhNDIwYWEwYmQyZWYwZGRmYzhmY2UiLCJwIjoiaiJ9" 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 `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).<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 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`.<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>{</span> <span class="diffremovedchars" style="background-color: #ffe7e7; text-decoration:line-through;">noformat</span> <span class="diffaddedchars" style="background-color:#ddfade;">code:java</span> <span class="diffcontext">} @Test<br> public void testCaseWhenOtherwise() {<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"); // CASE a.name = 'kitty' THEN 'Cat' ELSE <Hibernate throws up><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> }{</span> <span class="diffremovedchars" style="background-color: #ffe7e7; text-decoration:line-through;">noformat</span> <span class="diffaddedchars" style="background-color:#ddfade;">code</span> <span class="diffcontext">}<br><br><br><br>Then the following test containing a query that will pass, because it's using a parameter:<br><br>{code:java} @Test<br> public void testCaseWhenOtherwise() {<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"); // CASE a.name = 'kitty' THEN 'Cat' ELSE <Hibernate throws up><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> }{code}<br><br><br>prints:<br><br>{noformat}select new mypackage.Animal(case when generatedAlias0.name='kitty' then 'Cat' else :otherwiseParam end) from Animal as generatedAlias0 where generatedAlias0.name=:param0{noformat}<br><br>Simple gradlewrapper project with these tests attached. <br><br>[^hibernatecasewhenthensubtest.zip]<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=eyJpIjoiNDI2ZTYyOTE0ZGVhNDIwYWEwYmQyZWYwZGRmYzhmY2UiLCJwIjoiaiJ9" 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-6021939e-85f1-4089-8455-fa55de482e45" 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=eyJpIjoiNDI2ZTYyOTE0ZGVhNDIwYWEwYmQyZWYwZGRmYzhmY2UiLCJwIjoiaiJ9" 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-fd2c0d6d-91a8-44e6-a609-02298f14810a" 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>