From e05a59a2361394d6fc4a93193b0ed35ba305fcf8 Mon Sep 17 00:00:00 2001
From: danielhanrahan <daniel.hanrahan@est.tech>
Date: Fri, 23 Jun 2023 14:37:12 +0100
Subject: Handle special characters in CpsPath queries (CPS-1760 #2)

This fixes issues with special characters for CPS-500, CPS-1756,
CPS-1758, and CPS-1760. It also improves query performance.

- use SQL LIKE instead of regex in Cps Path queries

Issue-ID: CPS-1763
Signed-off-by: danielhanrahan <daniel.hanrahan@est.tech>
Change-Id: I5c179882bfba71d3b009c60059e9073f46227e7d
---
 .../cps/spi/repository/FragmentQueryBuilder.java   | 32 ++++------------------
 .../org/onap/cps/spi/utils/EscapeUtilsSpec.groovy  |  4 +--
 2 files changed, 7 insertions(+), 29 deletions(-)

(limited to 'cps-ri')

diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentQueryBuilder.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentQueryBuilder.java
index 34dea9bc1..7b5c0c693 100644
--- a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentQueryBuilder.java
+++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentQueryBuilder.java
@@ -44,9 +44,6 @@ import org.springframework.stereotype.Component;
 @Slf4j
 @Component
 public class FragmentQueryBuilder {
-    private static final String REGEX_ABSOLUTE_PATH_PREFIX = "^";
-    private static final String REGEX_DESCENDANT_PATH_PREFIX = "^.*\\/";
-    private static final String REGEX_OPTIONAL_LIST_INDEX_POSTFIX = "(\\[@(?!.*\\[).*?])?$";
     private static final AnchorEntity ACROSS_ALL_ANCHORS = null;
 
     @PersistenceContext
@@ -77,12 +74,6 @@ public class FragmentQueryBuilder {
         return getQueryForDataspaceOrAnchorAndCpsPath(dataspaceEntity, ACROSS_ALL_ANCHORS, cpsPathQuery);
     }
 
-    private static String getXpathSqlRegex(final CpsPathQuery cpsPathQuery) {
-        final StringBuilder xpathRegexBuilder = getRegexStringBuilderWithPrefix(cpsPathQuery);
-        xpathRegexBuilder.append(REGEX_OPTIONAL_LIST_INDEX_POSTFIX);
-        return xpathRegexBuilder.toString();
-    }
-
     private Query getQueryForDataspaceOrAnchorAndCpsPath(final DataspaceEntity dataspaceEntity,
                                                          final AnchorEntity anchorEntity,
                                                          final CpsPathQuery cpsPathQuery) {
@@ -110,26 +101,13 @@ public class FragmentQueryBuilder {
     private static void addXpathSearch(final CpsPathQuery cpsPathQuery,
                                        final StringBuilder sqlStringBuilder,
                                        final Map<String, Object> queryParameters) {
-        sqlStringBuilder.append(" AND xpath ~ :xpathRegex");
-        final String xpathRegex = getXpathSqlRegex(cpsPathQuery);
-        queryParameters.put("xpathRegex", xpathRegex);
-    }
-
-    private static StringBuilder getRegexStringBuilderWithPrefix(final CpsPathQuery cpsPathQuery) {
-        final StringBuilder xpathRegexBuilder = new StringBuilder();
+        sqlStringBuilder.append(" AND (xpath LIKE :escapedXpath OR "
+                + "(xpath LIKE :escapedXpath||'[@%]' AND xpath NOT LIKE :escapedXpath||'[@%]/%[@%]'))");
         if (CpsPathPrefixType.ABSOLUTE.equals(cpsPathQuery.getCpsPathPrefixType())) {
-            xpathRegexBuilder.append(REGEX_ABSOLUTE_PATH_PREFIX);
-            xpathRegexBuilder.append(escapeXpath(cpsPathQuery.getXpathPrefix()));
-            return xpathRegexBuilder;
+            queryParameters.put("escapedXpath", EscapeUtils.escapeForSqlLike(cpsPathQuery.getXpathPrefix()));
+        } else {
+            queryParameters.put("escapedXpath", "%/" + EscapeUtils.escapeForSqlLike(cpsPathQuery.getDescendantName()));
         }
-        xpathRegexBuilder.append(REGEX_DESCENDANT_PATH_PREFIX);
-        xpathRegexBuilder.append(escapeXpath(cpsPathQuery.getDescendantName()));
-        return xpathRegexBuilder;
-    }
-
-    private static String escapeXpath(final String xpath) {
-        // See https://jira.onap.org/browse/CPS-500 for limitations of this basic escape mechanism
-        return xpath.replace("[@", "\\[@");
     }
 
     private static Integer getTextValueAsInt(final CpsPathQuery cpsPathQuery) {
diff --git a/cps-ri/src/test/groovy/org/onap/cps/spi/utils/EscapeUtilsSpec.groovy b/cps-ri/src/test/groovy/org/onap/cps/spi/utils/EscapeUtilsSpec.groovy
index 17eb8846a..7de9b97ba 100644
--- a/cps-ri/src/test/groovy/org/onap/cps/spi/utils/EscapeUtilsSpec.groovy
+++ b/cps-ri/src/test/groovy/org/onap/cps/spi/utils/EscapeUtilsSpec.groovy
@@ -25,8 +25,8 @@ import spock.lang.Specification
 class EscapeUtilsSpec extends Specification {
 
     def 'Escape text for using in SQL LIKE operation'() {
-        expect:
-            EscapeUtils.escapeForSqlLike(unescapedText) == escapedText
+        expect: 'SQL LIKE special characters to be escaped with forward-slash'
+            assert EscapeUtils.escapeForSqlLike(unescapedText) == escapedText
         where:
             unescapedText                   || escapedText
             'Only %, _, and \\ are special' || 'Only \\%, \\_, and \\\\ are special'
-- 
cgit 1.2.3-korg