You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@olingo.apache.org by ch...@apache.org on 2017/04/24 12:01:17 UTC
olingo-odata2 git commit: [OLINGO-1098] Incorrect handling of SQL
wildcards
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 <ch...@sap.com>
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 <ra...@sap.com>
Authored: Fri Mar 31 13:17:20 2017 +0530
Committer: Christian Amend <ch...@sap.com>
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