Repository: olingo-odata2 Updated Branches: refs/heads/master ff4ab9956 -> 474d8f3e7
[OLINGO-1098] Incorrect handling of SQL wildcards '_' and '%' on filtering Signed-off-by: Christian Amend <[email protected]> Project: http://git-wip-us.apache.org/repos/asf/olingo-odata2/repo Commit: http://git-wip-us.apache.org/repos/asf/olingo-odata2/commit/474d8f3e Tree: http://git-wip-us.apache.org/repos/asf/olingo-odata2/tree/474d8f3e Diff: http://git-wip-us.apache.org/repos/asf/olingo-odata2/diff/474d8f3e Branch: refs/heads/master Commit: 474d8f3e76efa01a2a267d038aaa61a3bb14244f Parents: ff4ab99 Author: i050510 <[email protected]> Authored: Fri Mar 31 13:17:20 2017 +0530 Committer: Christian Amend <[email protected]> Committed: Mon Apr 24 14:00:14 2017 +0200 ---------------------------------------------------------------------- .../processor/core/ODataExpressionParser.java | 24 ++++++++++++++++---- .../core/ODataFilterExpressionParserTest.java | 24 +++++++++++--------- .../resources/SQL_Insert_Material.properties | 8 ++++++- 3 files changed, 40 insertions(+), 16 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/olingo-odata2/blob/474d8f3e/odata2-jpa-processor/jpa-core/src/main/java/org/apache/olingo/odata2/jpa/processor/core/ODataExpressionParser.java ---------------------------------------------------------------------- diff --git a/odata2-jpa-processor/jpa-core/src/main/java/org/apache/olingo/odata2/jpa/processor/core/ODataExpressionParser.java b/odata2-jpa-processor/jpa-core/src/main/java/org/apache/olingo/odata2/jpa/processor/core/ODataExpressionParser.java index 0f4b7ca..66190a4 100644 --- a/odata2-jpa-processor/jpa-core/src/main/java/org/apache/olingo/odata2/jpa/processor/core/ODataExpressionParser.java +++ b/odata2-jpa-processor/jpa-core/src/main/java/org/apache/olingo/odata2/jpa/processor/core/ODataExpressionParser.java @@ -193,20 +193,26 @@ public class ODataExpressionParser { case SUBSTRINGOF: if (methodFlag.get() != null && methodFlag.get() == 1) { methodFlag.set(null); - return String.format("(CASE WHEN (%s LIKE CONCAT('%%',CONCAT(%s,'%%'))) THEN TRUE ELSE FALSE END)", + updateValueIfWildcards(first); + return String.format("(CASE WHEN (%s LIKE CONCAT('%%',CONCAT(%s,'%%')) ESCAPE '\\') " + + "THEN TRUE ELSE FALSE END)", second, first); } else { - return String.format("(CASE WHEN (%s LIKE CONCAT('%%',CONCAT(%s,'%%'))) THEN TRUE ELSE FALSE END) = true", + first = updateValueIfWildcards(first); + return String.format("(CASE WHEN (%s LIKE CONCAT('%%',CONCAT(%s,'%%')) ESCAPE '\\') " + + "THEN TRUE ELSE FALSE END) = true", second, first); } case TOLOWER: return String.format("LOWER(%s)", first); case STARTSWITH: // second = second.substring(1, second.length() - 1); - return String.format("%s LIKE CONCAT(%s,'%%')", first, second); + second = updateValueIfWildcards(second); + return String.format("%s LIKE CONCAT(%s,'%%') ESCAPE '\\'", first, second); case ENDSWITH: // second = second.substring(1, second.length() - 1); - return String.format("%s LIKE CONCAT('%%',%s)", first, second); + second = updateValueIfWildcards(second); + return String.format("%s LIKE CONCAT('%%',%s) ESCAPE '\\'", first, second); default: throw new ODataNotImplementedException(); } @@ -217,6 +223,16 @@ public class ODataExpressionParser { } /** + * This method escapes the wildcards + * @param first + */ + private static String updateValueIfWildcards(String value) { + value = value.replace("\\", "\\\\"); + value = value.replace("%", "\\%"); + value = value.replace("_", "\\_"); + return value; + } + /** * This method parses the select clause * * @param tableAlias http://git-wip-us.apache.org/repos/asf/olingo-odata2/blob/474d8f3e/odata2-jpa-processor/jpa-core/src/test/java/org/apache/olingo/odata2/jpa/processor/core/ODataFilterExpressionParserTest.java ---------------------------------------------------------------------- diff --git a/odata2-jpa-processor/jpa-core/src/test/java/org/apache/olingo/odata2/jpa/processor/core/ODataFilterExpressionParserTest.java b/odata2-jpa-processor/jpa-core/src/test/java/org/apache/olingo/odata2/jpa/processor/core/ODataFilterExpressionParserTest.java index a29ba5c..af798fe 100644 --- a/odata2-jpa-processor/jpa-core/src/test/java/org/apache/olingo/odata2/jpa/processor/core/ODataFilterExpressionParserTest.java +++ b/odata2-jpa-processor/jpa-core/src/test/java/org/apache/olingo/odata2/jpa/processor/core/ODataFilterExpressionParserTest.java @@ -49,45 +49,47 @@ public class ODataFilterExpressionParserTest { private static final String[] EXPRESSION_BINARY_AND = { "id le '123' and soId eq 123L and not (substringof(id,'123') eq false) eq true", - "(((E1.id <= '123') AND (E1.soId = 123L)) AND (NOT(((CASE WHEN ('123' LIKE CONCAT('%',CONCAT(E1.id,'%'))) " + "(((E1.id <= '123') AND (E1.soId = 123L)) AND (NOT(((CASE WHEN ('123' LIKE CONCAT('%',CONCAT(E1.id,'%')" + + ") ESCAPE '\\') " + "THEN TRUE ELSE FALSE END) = false)) = true))" }; private static final String[] EXPRESSION_BINARY_OR = { "id ge '123' or soId gt 123L", "((E1.id >= '123') OR (E1.soId > 123L))" }; private static final String[] EXPRESSION_MEMBER_OR = { "id lt '123' or oValue/Currency eq 'INR'", "((E1.id < '123') OR (E1.oValue.Currency = 'INR'))" }; private static final String[] EXPRESSION_STARTS_WITH = { "startswith(oValue/Currency,'INR')", - "E1.oValue.Currency LIKE CONCAT('INR','%')" }; + "E1.oValue.Currency LIKE CONCAT('INR','%') ESCAPE '\\'" }; private static final String[] EXPRESSION_STARTS_WITH_EQUAL = { "startswith(oValue/Currency,'INR') eq true", - "(E1.oValue.Currency LIKE CONCAT('INR','%') )" }; + "(E1.oValue.Currency LIKE CONCAT('INR','%') ESCAPE '\\' )" }; private static final String[] EXPRESSION_NOT_STARTS_WITH = { "startswith(oValue/Currency,'INR') eq false", - "(E1.oValue.Currency NOT LIKE CONCAT('INR','%') )" }; + "(E1.oValue.Currency NOT LIKE CONCAT('INR','%') ESCAPE '\\' )" }; private static final String[] EXPRESSION_NOT_ENDS_WITH = { "endswith(oValue/Currency,tolower('INR')) eq false", - "(E1.oValue.Currency NOT LIKE CONCAT('%',LOWER('INR')) )" }; + "(E1.oValue.Currency NOT LIKE CONCAT('%',LOWER('INR')) ESCAPE '\\' )" }; private static final String[] EXPRESSION_NESTED_METHOD = { "endswith(substring(oValue/Currency,2),'INR') eq false", - "(SUBSTRING(E1.oValue.Currency, 2 + 1 ) NOT LIKE CONCAT('%','INR') )" }; + "(SUBSTRING(E1.oValue.Currency, 2 + 1 ) NOT LIKE CONCAT('%','INR') ESCAPE '\\' )" }; private static final String[] EXPRESSION_SUBSTRING_OF = { "substringof(id,'123') ne true", - "((CASE WHEN ('123' LIKE CONCAT('%',CONCAT(E1.id,'%'))) THEN TRUE ELSE FALSE END) <> true)" }; + "((CASE WHEN ('123' LIKE CONCAT('%',CONCAT(E1.id,'%')) ESCAPE '\\') THEN TRUE ELSE FALSE END) <> true)" }; private static final String[] EXPRESSION_STARTS_WITH_WRONG_OP = { "startswith(oValue/Currency,'INR') lt true", "" }; private static final String[] EXPRESSION_SUBSTRING_ALL_OP = { "substring(oValue/Currency,1,3) eq 'INR'", "(SUBSTRING(E1.oValue.Currency, 1 + 1 , 3) = 'INR')" }; private static final String[] EXPRESSION_SUBSTRINGOF_INJECTION1 = { "substringof('a'' OR 1=1 OR E1.id LIKE ''b',id) eq true", - "((CASE WHEN (E1.id LIKE CONCAT('%',CONCAT('a'' OR 1=1 OR E1.id LIKE ''b','%'))) " + "((CASE WHEN (E1.id LIKE CONCAT('%',CONCAT('a'' OR 1=1 OR E1.id LIKE ''b','%')) ESCAPE '\\') " + "THEN TRUE ELSE FALSE END) = true)" }; private static final String[] EXPRESSION_SUBSTRINGOF_INJECTION2 = { "substringof('substringof(''a'' OR 1=1 OR E1.id LIKE ''b'',id)',id) eq true", - "((CASE WHEN (E1.id LIKE CONCAT('%',CONCAT('substringof(''a'' OR 1=1 OR E1.id LIKE ''b'',id)','%'))) " + "((CASE WHEN (E1.id LIKE CONCAT('%',CONCAT('substringof(''a'' OR 1=1 OR E1.id LIKE ''b'',id)','%')) ESCAPE '\\') " + "THEN TRUE ELSE FALSE END) = true)" }; private static final String[] EXPRESSION_SUBSTRINGOF_INJECTION3 = { "substringof( substring(' ) OR execute_my_sql OR '' LIKE ',3),'de''') eq true", - "((CASE WHEN ('de''' LIKE CONCAT('%',CONCAT(SUBSTRING(' ) OR execute_my_sql OR '' LIKE ', 3 + 1 ),'%'))) " + "((CASE WHEN ('de''' LIKE CONCAT('%',CONCAT(SUBSTRING(' ) OR execute_my_sql OR '' LIKE ', 3 + 1 ),'%')" + + ") ESCAPE '\\') " + "THEN TRUE ELSE FALSE END) = true)" }; private static final String[] EXPRESSION_ENDSWITH_INJECTION1 = { "endswith(id,'Str''eet') eq true", - "(E1.id LIKE CONCAT('%','Str''eet') )" }; + "(E1.id LIKE CONCAT('%','Str''eet') ESCAPE '\\' )" }; private static final String[] EXPRESSION_PRECEDENCE = { "id eq '123' and id ne '123' or (id eq '123' and id ne '123')", "(((E1.id = '123') AND (E1.id <> '123')) OR ((E1.id = '123') AND (E1.id <> '123')))" }; http://git-wip-us.apache.org/repos/asf/olingo-odata2/blob/474d8f3e/odata2-jpa-processor/jpa-web/src/main/resources/SQL_Insert_Material.properties ---------------------------------------------------------------------- diff --git a/odata2-jpa-processor/jpa-web/src/main/resources/SQL_Insert_Material.properties b/odata2-jpa-processor/jpa-web/src/main/resources/SQL_Insert_Material.properties index 7c64d36..5ee054c 100644 --- a/odata2-jpa-processor/jpa-web/src/main/resources/SQL_Insert_Material.properties +++ b/odata2-jpa-processor/jpa-web/src/main/resources/SQL_Insert_Material.properties @@ -27,4 +27,10 @@ material_query_7 = insert into T_MATERIAL (MATERIAL_ID , MATERIAL_NAME, TYPE_COD material_query_8 = insert into T_MATERIAL (MATERIAL_ID , MATERIAL_NAME, TYPE_CODE, CAT_ID, PRICE, MEASUREMENT_UNIT) values(118, 'Sharp', 'S1', 2, 118.1, 'Piece'); material_query_9 = insert into T_MATERIAL (MATERIAL_ID , MATERIAL_NAME, TYPE_CODE, CAT_ID, PRICE, MEASUREMENT_UNIT) values(119, 'Cut Sharp', 'S1', 2, 119.1, 'Piece'); material_query_10 = insert into T_MATERIAL (MATERIAL_ID , MATERIAL_NAME, TYPE_CODE, CAT_ID, PRICE, MEASUREMENT_UNIT) values(120, 'Thin line', 'S1', 2, 120.1, 'Piece'); -material_query_11 = insert into T_MATERIAL (MATERIAL_ID , MATERIAL_NAME, TYPE_CODE, CAT_ID, PRICE, MEASUREMENT_UNIT) values(121, 'Clean', 'E1', 3, 121.1, 'Piece'); \ No newline at end of file +material_query_11 = insert into T_MATERIAL (MATERIAL_ID , MATERIAL_NAME, TYPE_CODE, CAT_ID, PRICE, MEASUREMENT_UNIT) values(121, 'Clean', 'E1', 3, 121.1, 'Piece'); +material_query_12 = insert into T_MATERIAL (MATERIAL_ID , MATERIAL_NAME, TYPE_CODE, CAT_ID, PRICE, MEASUREMENT_UNIT) values(122, 'TEST_CHAR', 'E1', 3, 121.1, 'Piece'); +material_query_13 = insert into T_MATERIAL (MATERIAL_ID , MATERIAL_NAME, TYPE_CODE, CAT_ID, PRICE, MEASUREMENT_UNIT) values(123, 'TEST_CHAR1', 'E1', 3, 121.1, 'Piece'); +material_query_14 = insert into T_MATERIAL (MATERIAL_ID , MATERIAL_NAME, TYPE_CODE, CAT_ID, PRICE, MEASUREMENT_UNIT) values(124, 'TESTCHAR', 'E1', 3, 121.1, 'Piece'); +material_query_15 = insert into T_MATERIAL (MATERIAL_ID , MATERIAL_NAME, TYPE_CODE, CAT_ID, PRICE, MEASUREMENT_UNIT) values(125, '%TESTCHAR%', 'E1', 3, 121.1, 'Piece'); +material_query_16 = insert into T_MATERIAL (MATERIAL_ID , MATERIAL_NAME, TYPE_CODE, CAT_ID, PRICE, MEASUREMENT_UNIT) values(126, 'TEST%_%5CCHAR', 'E1', 3, 121.1, 'Piece'); +material_query_17 = insert into T_MATERIAL (MATERIAL_ID , MATERIAL_NAME, TYPE_CODE, CAT_ID, PRICE, MEASUREMENT_UNIT) values(127, 'TEST%_\\CCHAR', 'E1', 3, 121.1, 'Piece'); \ No newline at end of file
