CVE-2023-25158: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: GeoTools is an open source Java library that provides tools for geospatial data. GeoTools includes support for OGC Filter expression language parsing, encoding and execution against a range of datastore. SQL Injection Vulnerabilities have been found when executing OGC Filters with JDBCDataStore implementations. Users are advised to upgrade to either version 27.4 or to 28.2 to resolve this issue. Users unable to upgrade may disable `encode functions` for PostGIS DataStores or enable `prepared statements` for JDBCDataStores as a partial mitigation.
CWE description: The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
CVSS v3 base score: 9.8
Published: 2023-02-21T21:15Z
Repository: https://github.com/geotools/geotools
Language: Java
Files changed: modules/library/jdbc/src/main/java/org/geotools/data/jdbc/FilterToSQL.java, modules/library/jdbc/src/main/java/org/geotools/jdbc/EscapeSql.java, modules/library/jdbc/src/main/java/org/geotools/jdbc/SQLDialect.java, modules/library/jdbc/src/test/java/org/geotools/data/jdbc/FilterToSQLTest.java, modules/library/main/src/main/java/org/geotools/filter/LikeFilterImpl.java, modules/library/main/src/test/java/org/geotools/filter/FilterTest.java, modules/plugin/jdbc/jdbc-mysql/src/main/java/org/geotools/data/mysql/MySQLDialectBasic.java, modules/plugin/jdbc/jdbc-mysql/src/test/java/org/geotools/data/mysql/MySQLFilterToSQLTest.java, modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java, modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleFilterToSQL.java, modules/plugin/jdbc/jdbc-oracle/src/test/java/org/geotools/data/oracle/OracleFilterToSqlTest.java, modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java, modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISDialect.java, modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISPSDialect.java, modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostgisNGDataStoreFactory.java, modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisFilterToSQLTest.java, modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisNGDataStoreFactoryTest.java
Commit date: 2023-02-10 11:48:44 +0100
Commit message: Merge pull request from GHSA-99c3-qc2q-p94m


Diff: diff --git a/modules/library/jdbc/src/main/java/org/geotools/data/jdbc/FilterToSQL.java b/modules/library/jdbc/src/main/java/org/geotools/data/jdbc/FilterToSQL.java
index 9c51f9b6f4..21f4e29361 100644
--- a/modules/library/jdbc/src/main/java/org/geotools/data/jdbc/FilterToSQL.java
+++ b/modules/library/jdbc/src/main/java/org/geotools/data/jdbc/FilterToSQL.java
@@ -43,10 +43,11 @@
 import org.geotools.filter.LikeFilterImpl;
 import org.geotools.filter.capability.FunctionNameImpl;
 import org.geotools.filter.function.InFunction;
 import org.geotools.filter.spatial.BBOXImpl;
 import org.geotools.jdbc.EnumMapper;
+import org.geotools.jdbc.EscapeSql;
 import org.geotools.jdbc.JDBCDataStore;
 import org.geotools.jdbc.JoinId;
 import org.geotools.jdbc.JoinPropertyName;
 import org.geotools.jdbc.PrimaryKey;
 import org.geotools.jdbc.PrimaryKeyColumn;
@@ -228,10 +229,13 @@ public class FilterToSQL implements FilterVisitor, ExpressionVisitor {
     protected boolean inline = false;
 
     /** Whether the encoder should try to encode "in" function into a SQL IN operator */
     protected boolean inEncodingEnabled = true;
 
+    /** Whether to escape backslash characters in string literals */
+    protected boolean escapeBackslash = false;
+
     /** Default constructor */
     public FilterToSQL() {}
 
     public FilterToSQL(Writer out) {
         this.out = out;
@@ -263,10 +267,20 @@ public boolean isInEncodingEnabled() {
      */
     public void setInEncodingEnabled(boolean inEncodingEnabled) {
         this.inEncodingEnabled = inEncodingEnabled;
     }
 
+    /** @return whether to escape backslash characters in string literals */
+    public boolean isEscapeBackslash() {
+        return escapeBackslash;
+    }
+
+    /** @param escapeBackslash whether to escape backslash characters in string literals */
+    public void setEscapeBackslash(boolean escapeBackslash) {
+        this.escapeBackslash = escapeBackslash;
+    }
+
     /**
      * Performs the encoding, sends the encoded sql to the writer passed in.
      *
      * @param filter the Filter to be encoded.
      * @throws FilterToSQLException If filter type not supported, or if there were io problems.
@@ -527,27 +541,27 @@ public Object visit(PropertyIsLike filter, Object extraData) {
         // from getExpressionType
         if (attributeType != null && Date.class.isAssignableFrom(attributeType)) {
             literal += multi;
         }
 
-        String pattern = LikeFilterImpl.convertToSQL92(esc, multi, single, matchCase, literal);
+        String pattern =
+                LikeFilterImpl.convertToSQL92(esc, multi, single, matchCase, literal, false);
 
         try {
             if (!matchCase) {
                 out.write(" UPPER(");
             }
 
             att.accept(this, extraData);
 
             if (!matchCase) {
-                out.write(") LIKE '");
+                out.write(") LIKE ");
             } else {
-                out.write(" LIKE '");
+                out.write(" LIKE ");
             }
 
-            out.write(pattern);
-            out.write("' ");
+            writeLiteral(pattern);
         } catch (java.io.IOException ioe) {
             throw new RuntimeException(IO_ERROR, ioe);
         }
         return extraData;
     }
@@ -1168,15 +1182,12 @@ public Object visit(Id filter, Object extraData) {
                     if (filter instanceof JoinId) {
                         out.write(escapeName(((JoinId) filter).getAlias()));
                         out.write(".");
                     }
                     out.write(escapeName(columns.get(j).getName()));
-                    out.write(" = '");
-                    out.write(
-                            attValues.get(j).toString()); // DJB: changed this to attValues[j] from
-                    // attValues[i].
-                    out.write("'");
+                    out.write(" = ");
+                    writeLiteral(attValues.get(j));
 
                     if (j < (attValues.size() - 1)) {
                         out.write(" AND ");
                     }
                 }
@@ -1745,16 +1756,21 @@ protected void writeLiteral(Object literal) throws IOException {
             if (encoding == null) {
                 // could not convert back to string, use original l value
                 encoding = literal.toString();
             }
 
-            // sigle quotes must be escaped to have a valid sql string
-            String escaped = encoding.replaceAll("'", "''");
+            // single quotes must be escaped to have a valid sql string
+            String escaped = escapeLiteral(encoding);
             out.write("'" + escaped + "'");
         }
     }
 
+    /** Escapes the string literal. */
+    public String escapeLiteral(String literal) {
+        return EscapeSql.escapeLiteral(literal, escapeBackslash, false);
+    }
+
     /**
      * Subclasses must implement this method in order to encode geometry filters according to the
      * specific database implementation
      */
     protected void visitLiteralGeometry(Literal expression) throws IOException {
diff --git a/modules/library/jdbc/src/main/java/org/geotools/jdbc/EscapeSql.java b/modules/library/jdbc/src/main/java/org/geotools/jdbc/EscapeSql.java
index b18c2637cb..a72428acb8 100644
--- a/modules/library/jdbc/src/main/java/org/geotools/jdbc/EscapeSql.java
+++ b/modules/library/jdbc/src/main/java/org/geotools/jdbc/EscapeSql.java
@@ -14,18 +14,42 @@
  *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  *    Lesser General Public License for more details.
  */
 package org.geotools.jdbc;
 
+import java.util.regex.Pattern;
+
 /**
  * Perform basic SQL validation on input string. This is to allow safe encoding of parameters that
  * must contain quotes, while still protecting users from SQL injection.
  *
  * <p>We prevent SQL from breaking out of quotes by replacing any quotes in input stream with double
  * quotes. Backslashes are too risky to allow so are removed completely
  */
 public class EscapeSql {
+
+    private static final Pattern SINGLE_QUOTE_PATTERN = Pattern.compile("'");
+
+    private static final Pattern DOUBLE_QUOTE_PATTERN = Pattern.compile("\"");
+
+    private static final Pattern BACKSLASH_PATTERN = Pattern.compile("\\\\");
+
+    public static String escapeLiteral(
+            String literal, boolean escapeBackslash, boolean escapeDoubleQuote) {
+        // ' --> ''
+        String escaped = SINGLE_QUOTE_PATTERN.matcher(literal).replaceAll("''");
+        if (escapeBackslash) {
+            // \ --> \\
+            escaped = BACKSLASH_PATTERN.matcher(escaped).replaceAll("\\\\\\\\");
+        }
+        if (escapeDoubleQuote) {
+            // " --> \"
+            escaped = DOUBLE_QUOTE_PATTERN.matcher(escaped).replaceAll("\\\\\"");
+        }
+        return escaped;
+    }
+
     public static String escapeSql(String str) {
 
         // ' --> ''
         str = str.replaceAll("'", "''");
 
diff --git a/modules/library/jdbc/src/main/java/org/geotools/jdbc/SQLDialect.java b/modules/library/jdbc/src/main/java/org/geotools/jdbc/SQLDialect.java
index 79d558b0e2..a1ed097f05 100644
--- a/modules/library/jdbc/src/main/java/org/geotools/jdbc/SQLDialect.java
+++ b/modules/library/jdbc/src/main/java/org/geotools/jdbc/SQLDialect.java
@@ -26,10 +26,11 @@
 import java.sql.Statement;
 import java.sql.Time;
 import java.sql.Timestamp;
 import java.sql.Types;
 import java.util.ArrayList;
+import java.util.HashMap;
 import java.util.LinkedHashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.Objects;
 import java.util.Optional;
@@ -37,10 +38,12 @@
 import java.util.function.Function;
 import java.util.logging.Level;
 import java.util.logging.Logger;
 import org.geotools.data.Join.Type;
 import org.geotools.data.Query;
+import org.geotools.data.jdbc.datasource.DataSourceFinder;
+import org.geotools.data.jdbc.datasource.UnWrapper;
 import org.geotools.feature.visitor.AverageVisitor;
 import org.geotools.feature.visitor.CountVisitor;
 import org.geotools.feature.visitor.FeatureAttributeVisitor;
 import org.geotools.feature.visitor.MaxVisitor;
 import org.geotools.feature.visitor.MinVisitor;
@@ -162,10 +165,45 @@ public abstract class SQLDialect {
                     // native filter support
                     addType(NativeFilter.class);
                 }
             };
 
+    /**
+     * Sentinel value used to mark that the unwrapper lookup happened already, and an unwrapper was
+     * not found
+     */
+    protected static final UnWrapper UNWRAPPER_NOT_FOUND =
+            new UnWrapper() {
+
+                @Override
+                public Statement unwrap(Statement statement) {
+                    throw new UnsupportedOperationException();
+                }
+
+                @Override
+                public Connection unwrap(Connection conn) {
+                    throw new UnsupportedOperationException();
+                }
+
+                @Override
+                public boolean canUnwrap(Statement st) {
+                    return false;
+                }
+
+                @Override
+                public boolean canUnwrap(Connection conn) {
+                    return false;
+                }
+            };
+
+    /**
+     * Map of {@code UnWrapper} objects keyed by the class of {@code Connection} it is an unwrapper
+     * for. This avoids the overhead of searching the {@code DataSourceFinder} service registry at
+     * each unwrap.
+     */
+    protected final Map<Class<? extends Connection>, UnWrapper> uwMap = new HashMap<>();
+
     /** The datastore using the dialect */
     protected JDBCDataStore dataStore;
 
     /** Used to influence the CRS axis ordering in {@link #createCRS(int, java.sql.Connection) }. */
     protected boolean forceLongitudeFirst = false;
@@ -1413,6 +1451,54 @@ public boolean canGroupOnGeometry() {
      * @return The mapped java class, or <code>null</code>. if no such mapping exists.
      */
     public Class<?> getMapping(String sqlTypeName) {
         return null;
     }
+
+    /** Obtains the native connection object given a database connection. */
+    @SuppressWarnings("PMD.CloseResource")
+    protected <T extends Connection> T unwrapConnection(Connection cx, Class<T> clazz)
+            throws SQLException {
+        if (clazz.isInstance(cx)) {
+            return clazz.cast(cx);
+        }
+        try {
+            // Unwrap the connection multiple levels as necessary to get at the underlying
+            // connection. Maintain a map of UnWrappers to avoid searching the registry
+            // every time we need to unwrap.
+            Connection testCon = cx;
+            Connection toUnwrap;
+            do {
+                UnWrapper unwrapper = uwMap.get(testCon.getClass());
+                if (unwrapper == null) {
+                    unwrapper = DataSourceFinder.getUnWrapper(testCon);
+                    if (unwrapper == null) {
+                        unwrapper = UNWRAPPER_NOT_FOUND;
+                    }
+                    uwMap.put(testCon.getClass(), unwrapper);
+                }
+                if (unwrapper == UNWRAPPER_NOT_FOUND) {
+                    // give up and do Java unwrap below
+                    break;
+                }
+                toUnwrap = testCon;
+                testCon = unwrapper.unwrap(testCon);
+                if (clazz.isInstance(testCon)) {
+                    return clazz.cast(cx);
+                }
+            } while (testCon != null && testCon != toUnwrap);
+            // try to use Java unwrapping
+            try {
+                if (cx.isWrapperFor(clazz)) {
+                    return cx.unwrap(clazz);
+                }
+            } catch (Throwable t) {
+                // not a mistake, old DBCP versions will throw an Error here, we need to catch it
+                LOGGER.log(Level.FINER, "Failed to unwrap connection using Java facilities", t);
+            }
+        } catch (IOException e) {
+            throw new SQLException(
+                    "Could not obtain " + clazz.getName() + " from " + cx.getClass(), e);
+        }
+        throw new SQLException("Could not obtain " + clazz.getName() + " from " + cx.getClass());
+    }
 }
diff --git a/modules/library/jdbc/src/test/java/org/geotools/data/jdbc/FilterToSQLTest.java b/modules/library/jdbc/src/test/java/org/geotools/data/jdbc/FilterToSQLTest.java
index b027495ba6..2503f69bdb 100644
--- a/modules/library/jdbc/src/test/java/org/geotools/data/jdbc/FilterToSQLTest.java
+++ b/modules/library/jdbc/src/test/java/org/geotools/data/jdbc/FilterToSQLTest.java
@@ -463,6 +463,20 @@ public void testEscapeName() {
         Assert.assertEquals("\"a\"\"bc\"", encoder.escapeName("a\"bc"));
         Assert.assertEquals("\"abc\"\"\"", encoder.escapeName("abc\""));
         encoder.setSqlNameEscape("");
         Assert.assertEquals("abc", encoder.escapeName("abc"));
     }
+
+    @Test
+    public void testLikeEscaping() throws Exception {
+        Filter filter = ff.like(ff.property("testString"), "\\'FOO", "%", "-", "\\", true);
+        FilterToSQL encoder = new FilterToSQL(output);
+        Assert.assertEquals("WHERE testString LIKE '''FOO'", encoder.encodeToString(filter));
+    }
+
+    @Test
+    public void testIdEscaping() throws Exception {
+        Id id = ff.id(Collections.singleton(ff.featureId("'FOO")));
+        encoder.encode(id);
+        Assert.assertEquals("WHERE (id = '''FOO')", output.toString());
+    }
 }
diff --git a/modules/library/main/src/main/java/org/geotools/filter/LikeFilterImpl.java b/modules/library/main/src/main/java/org/geotools/filter/LikeFilterImpl.java
index 1fd4a73eb3..44c6e11c80 100644
--- a/modules/library/main/src/main/java/org/geotools/filter/LikeFilterImpl.java
+++ b/modules/library/main/src/main/java/org/geotools/filter/LikeFilterImpl.java
@@ -77,28 +77,64 @@ public class LikeFilterImpl extends AbstractFilter implements PropertyIsLike {
      *
      * <p>NOTE: we dont handle "'" as a 'special' character because it would be too confusing to
      * have a special char as another special char. Using this will throw an error
      * (IllegalArgumentException).
      */
+    @Deprecated
     public static String convertToSQL92(
             char escape, char multi, char single, boolean matchCase, String pattern)
             throws IllegalArgumentException {
+        return convertToSQL92(escape, multi, single, matchCase, pattern, true);
+    }
+
+    /**
+     * Given OGC PropertyIsLike Filter information, construct an SQL-compatible 'like' pattern.
+     *
+     * <p>SQL % --> match any number of characters _ --> match a single character
+     *
+     * <p>NOTE; the SQL command is 'string LIKE pattern [ESCAPE escape-character]' We could
+     * re-define the escape character, but I'm not doing to do that in this code since some
+     * databases will not handle this case.
+     *
+     * <p>Method: 1.
+     *
+     * <p>Examples: ( escape ='!', multi='*', single='.' ) broadway* -> 'broadway%' broad_ay ->
+     * 'broad_ay' broadway -> 'broadway'
+     *
+     * <p>broadway!* -> 'broadway*' (* has no significance and is escaped) can't -> 'can''t' ( '
+     * escaped for SQL compliance)
+     *
+     * <p>NOTE: when the escapeSingleQuote parameter is false, this method will not convert ' to ''
+     * (double single quote) and it is the caller's responsibility to ensure that the resulting
+     * pattern is used safely in SQL queries.
+     *
+     * <p>NOTE: we dont handle "'" as a 'special' character because it would be too confusing to
+     * have a special char as another special char. Using this will throw an error
+     * (IllegalArgumentException).
+     */
+    public static String convertToSQL92(
+            char escape,
+            char multi,
+            char single,
+            boolean matchCase,
+            String pattern,
+            boolean escapeSingleQuote) {
         if ((escape == '\'') || (multi == '\'') || (single == '\''))
             throw new IllegalArgumentException("do not use single quote (') as special char!");
 
-        StringBuffer result = new StringBuffer(pattern.length() + 5);
+        StringBuilder result = new StringBuilder(pattern.length() + 5);
         for (int i = 0; i < pattern.length(); i++) {
             char chr = pattern.charAt(i);
             if (chr == escape) {
                 // emit the next char and skip it
                 if (i != (pattern.length() - 1)) result.append(pattern.charAt(i + 1)); //
                 i++; // skip next char
             } else if (chr == single) {
                 result.append('_');
             } else if (chr == multi) {
                 result.append('%');
-            } else if (chr == '\'') {
+            } else if (chr == '\'' && escapeSingleQuote) {
                 result.append('\'');
                 result.append('\'');
             } else {
                 result.append(matchCase ? chr : Character.toUpperCase(chr));
             }
@@ -106,10 +142,11 @@ public static String convertToSQL92(
 
         return result.toString();
     }
 
     /** see convertToSQL92 */
+    @Deprecated
     public String getSQL92LikePattern() throws IllegalArgumentException {
         if (escape.length() != 1) {
             throw new IllegalArgumentException(
                     "Like Pattern --> escape char should be of length exactly 1");
         }
@@ -124,11 +161,12 @@ public String getSQL92LikePattern() throws IllegalArgumentException {
         return LikeFilterImpl.convertToSQL92(
                 escape.charAt(0),
                 wildcardMulti.charAt(0),
                 wildcardSingle.charAt(0),
                 matchingCase,
-                pattern);
+                pattern,
+                true);
     }
 
     public void setWildCard(String wildCard) {
         if (wildCard == null || wildCard.isEmpty() || wildCard.length() > 1) {
             throw new IllegalArgumentException(
diff --git a/modules/library/main/src/test/java/org/geotools/filter/FilterTest.java b/modules/library/main/src/test/java/org/geotools/filter/FilterTest.java
index ec59c94ebf..8dfae8e28d 100644
--- a/modules/library/main/src/test/java/org/geotools/filter/FilterTest.java
+++ b/modules/library/main/src/test/java/org/geotools/filter/FilterTest.java
@@ -185,33 +185,41 @@ public void setUp() throws SchemaException {
     }
 
     @Test
     public void testLikeToSQL() {
         Assert.assertEquals(
-                "BroadWay%", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "BroadWay*"));
+                "BroadWay%", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "BroadWay*", true));
         Assert.assertEquals(
-                "broad#ay", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broad#ay"));
+                "broad#ay", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broad#ay", true));
         Assert.assertEquals(
-                "broadway", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway"));
+                "broadway", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway", true));
 
         Assert.assertEquals(
-                "broad_ay", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broad.ay"));
+                "broad_ay", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broad.ay", true));
         Assert.assertEquals(
-                "broad.ay", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broad!.ay"));
+                "broad.ay", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broad!.ay", true));
 
         Assert.assertEquals(
-                "broa''dway", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broa'dway"));
+                "broa''dway",
+                LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broa'dway", true));
         Assert.assertEquals(
                 "broa''''dway",
-                LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broa" + "''dway"));
+                LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broa" + "''dway", true));
+        Assert.assertEquals(
+                "broa'dway",
+                LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broa'dway", false));
+        Assert.assertEquals(
+                "broa''dway",
+                LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broa" + "''dway", false));
 
         Assert.assertEquals(
-                "broadway_", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway."));
+                "broadway_", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway.", true));
         Assert.assertEquals(
-                "broadway", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway!"));
+                "broadway", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway!", true));
         Assert.assertEquals(
-                "broadway!", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway!!"));
+                "broadway!",
+                LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway!!", true));
     }
 
     /**
      * Sets up a schema and a test feature.
      *
diff --git a/modules/plugin/jdbc/jdbc-mysql/src/main/java/org/geotools/data/mysql/MySQLDialectBasic.java b/modules/plugin/jdbc/jdbc-mysql/src/main/java/org/geotools/data/mysql/MySQLDialectBasic.java
index ae7e839702..5ee780e80c 100644
--- a/modules/plugin/jdbc/jdbc-mysql/src/main/java/org/geotools/data/mysql/MySQLDialectBasic.java
+++ b/modules/plugin/jdbc/jdbc-mysql/src/main/java/org/geotools/data/mysql/MySQLDialectBasic.java
@@ -244,11 +244,16 @@ public void applyLimitOffset(StringBuffer sql, int limit, int offset) {
         delegate.applyLimitOffset(sql, limit, offset);
     }
 
     @Override
     public FilterToSQL createFilterToSQL() {
-        return new MySQLFilterToSQL(delegate.getUsePreciseSpatialOps());
+        MySQLFilterToSQL fts = new MySQLFilterToSQL(delegate.getUsePreciseSpatialOps());
+        // see https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_backslash_escapes
+        // NOTE: for future enhancement, do not escape backslashes when the NO_BACKSLASH_ESCAPES
+        // mode is enabled since that would create an incorrect string in the SQL
+        fts.setEscapeBackslash(true);
+        return fts;
     }
 
     @Override
     public void dropIndex(
             Connection cx, SimpleFeatureType schema, String databaseSchema, String indexName)
diff --git a/modules/plugin/jdbc/jdbc-mysql/src/test/java/org/geotools/data/mysql/MySQLFilterToSQLTest.java b/modules/plugin/jdbc/jdbc-mysql/src/test/java/org/geotools/data/mysql/MySQLFilterToSQLTest.java
new file mode 100644
index 0000000000..d0ffc910d9
--- /dev/null
+++ b/modules/plugin/jdbc/jdbc-mysql/src/test/java/org/geotools/data/mysql/MySQLFilterToSQLTest.java
@@ -0,0 +1,48 @@
+/*
+ *    GeoTools - The Open Source Java GIS Toolkit
+ *    http://geotools.org
+ *
+ *    (C) 2023, Open Source Geospatial Foundation (OSGeo)
+ *
+ *    This library is free software; you can redistribute it and/or
+ *    modify it under the terms of the GNU Lesser General Public
+ *    License as published by the Free Software Foundation;
+ *    version 2.1 of the License.
+ *
+ *    This library is distributed in the hope that it will be useful,
+ *    but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ *    Lesser General Public License for more details.
+ */
+package org.geotools.data.mysql;
+
+import static org.junit.Assert.assertEquals;
+
+import org.geotools.data.jdbc.SQLFilterTestSupport;
+import org.geotools.factory.CommonFactoryFinder;
+import org.geotools.feature.SchemaException;
+import org.junit.Before;
+import org.junit.Test;
+import org.opengis.filter.FilterFactory2;
+import org.opengis.filter.PropertyIsEqualTo;
+
+public class MySQLFilterToSQLTest extends SQLFilterTestSupport {
+
+    private static FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();
+
+    private MySQLFilterToSQL filterToSql;
+
+    @Override
+    @Before
+    public void setUp() throws SchemaException {
+        filterToSql = (MySQLFilterToSQL) new MySQLDialectBasic(null).createFilterToSQL();
+        filterToSql.setFeatureType(testSchema);
+    }
+
+    @Test
+    public void testEncodeEqualToWithSpecialCharacters() throws Exception {
+        PropertyIsEqualTo expr = ff.equals(ff.property("testString"), ff.literal("\\'FOO"));
+        String actual = filterToSql.encodeToString(expr);
+        assertEquals("WHERE testString = '\\\\''FOO'", actual);
+    }
+}
diff --git a/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java b/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java
index 92e8c97335..36fe85b273 100644
--- a/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java
+++ b/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java
@@ -24,11 +24,10 @@
 import java.sql.SQLException;
 import java.sql.Savepoint;
 import java.sql.Statement;
 import java.sql.Struct;
 import java.sql.Types;
-import java.sql.Wrapper;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Collections;
 import java.util.HashMap;
 import java.util.List;
@@ -39,12 +38,10 @@
 import java.util.logging.Level;
 import java.util.regex.Pattern;
 import oracle.jdbc.OracleConnection;
 import oracle.jdbc.OracleStruct;
 import org.geotools.data.jdbc.FilterToSQL;
-import org.geotools.data.jdbc.datasource.DataSourceFinder;
-import org.geotools.data.jdbc.datasource.UnWrapper;
 import org.geotools.data.oracle.sdo.GeometryConverter;
 import org.geotools.data.oracle.sdo.SDOSqlDumper;
 import org.geotools.data.oracle.sdo.TT;
 import org.geotools.geometry.jts.JTS;
 import org.geotools.geometry.jts.ReferencedEnvelope;
@@ -82,54 +79,19 @@
  * @author Andrea Aime, OpenGEO
  * @author Mark Prins, B3Partners
  */
 public class OracleDialect extends PreparedStatementSQLDialect {
 
-    /**
-     * Sentinel value used to mark that the unwrapper lookup happened already, and an unwrapper was
-     * not found
-     */
-    UnWrapper UNWRAPPER_NOT_FOUND =
-            new UnWrapper() {
-
-                @Override
-                public Statement unwrap(Statement statement) {
-                    throw new UnsupportedOperationException();
-                }
-
-                @Override
-                public Connection unwrap(Connection conn) {
-                    throw new UnsupportedOperationException();
-                }
-
-                @Override
-                public boolean canUnwrap(Statement st) {
-                    return false;
-                }
-
-                @Override
-                public boolean canUnwrap(Connection conn) {
-                    return false;
-                }
-            };
-
     private static final int DEFAULT_AXIS_MAX = 10000000;
 
     private static final int DEFAULT_AXIS_MIN = -10000000;
 
     private static final Pattern AXIS_NAME_VALIDATOR = Pattern.compile("^[\\w]{1,30}");
 
     /** Marks a geometry column as geodetic */
     public static final String GEODETIC = "geodetic";
 
-    /**
-     * Map of {@code UnWrapper} objects keyed by the class of {@code Connection} it is an unwrapper
-     * for. This avoids the overhead of searching the {@code DataSourceFinder} service registry at
-     * each unwrap.
-     */
-    Map<Class<? extends Connection>, UnWrapper> uwMap = new HashMap<>();
-
     private int nameLenghtLimit = 30;
 
     /**
      * A map from JTS Geometry type to Oracle geometry type. See Oracle Spatial documentation, Table
      * 2-1, Valid SDO_GTYPE values.
@@ -651,66 +613,12 @@ public void setGeometryValue(
             LOGGER.fine("Setting parameter " + column + " as " + sdo);
         }
     }
 
     /** Obtains the native oracle connection object given a database connection. */
-    @SuppressWarnings("PMD.CloseResource")
     OracleConnection unwrapConnection(Connection cx) throws SQLException {
-        if (cx == null) {
-            return null;
-        }
-
-        if (cx instanceof OracleConnection) {
-            return (OracleConnection) cx;
-        }
-
-        try {
-            // Unwrap the connection multiple levels as necessary to get at the underlying
-            // OracleConnection. Maintain a map of UnWrappers to avoid searching
-            // the registry every time we need to unwrap.
-            Connection testCon = cx;
-            Connection toUnwrap;
-            do {
-                UnWrapper unwrapper = uwMap.get(testCon.getClass());
-                if (unwrapper == null) {
-                    unwrapper = DataSourceFinder.getUnWrapper(testCon);
-                    if (unwrapper == null) {
-                        unwrapper = UNWRAPPER_NOT_FOUND;
-                    }
-                    uwMap.put(testCon.getClass(), unwrapper);
-                }
-                if (unwrapper == UNWRAPPER_NOT_FOUND) {
-                    // give up and do Java 6 unwrap below
-                    break;
-                }
-                toUnwrap = testCon;
-                testCon = unwrapper.unwrap(testCon);
-                if (testCon instanceof OracleConnection) {
-                    return (OracleConnection) testCon;
-                }
-            } while (testCon != null && testCon != toUnwrap);
-
-            if (cx instanceof Wrapper) {
-                // try to use java 6 unwrapping
-                try {
-                    Wrapper w = cx;
-                    if (w.isWrapperFor(OracleConnection.class)) {
-                        return w.unwrap(OracleConnection.class);
-                    }
-                } catch (Throwable t) {
-                    // not a mistake, old DBCP versions will throw an Error here, we need to catch
-                    // it
-                    LOGGER.log(
-                            Level.FINER, "Failed to unwrap connection using java 6 facilities", t);
-                }
-            }
-        } catch (IOException e) {
-            throw (SQLException)
-                    new SQLException("Could not obtain native oracle connection.").initCause(e);
-        }
-
-        throw new SQLException("Could not obtain native oracle connection for " + cx.getClass());
+        return unwrapConnection(cx, OracleConnection.class);
     }
 
     public FilterToSQL createFilterToSQL() {
         throw new UnsupportedOperationException("This dialect works with prepared statements only");
     }
diff --git a/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleFilterToSQL.java b/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleFilterToSQL.java
index f5311fd428..d6b53aba15 100644
--- a/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleFilterToSQL.java
+++ b/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleFilterToSQL.java
@@ -544,13 +544,14 @@ protected void doSDODistance(
         e1.accept(this, extraData);
         out.write(",");
         e2.accept(this, extraData);
 
         // encode the unit verbatim when available
-        if (unit != null && !"".equals(unit.trim()))
+        if (unit != null && !"".equals(unit.trim())) {
+            unit = escapeLiteral(unit);
             out.write(",'distance=" + distance + " unit=" + unit + "') = '" + within + "' ");
-        else out.write(",'distance=" + distance + "') = '" + within + "' ");
+        } else out.write(",'distance=" + distance + "') = '" + within + "' ");
     }
 
     /**
      * The mapping between OGC filter units and Oracle Units. The full list of Oracle Units can be
      * obtained by issuing "select * from MDSYS.SDO_DIST_UNITS WHERE SDO_UNIT IS NOT NULL order by
@@ -623,14 +624,14 @@ public String jsonExists(Function function) {
         Literal jsonPath = (Literal) getParameter(function, 1, true);
         Expression expected = getParameter(function, 2, true);
 
         String[] pointers = jsonPath.getValue().toString().split("/");
         if (pointers.length > 0) {
-            String strJsonPath = String.join(".", pointers);
+            String strJsonPath = escapeLiteral(String.join(".", pointers));
+            String strExpected = escapeLiteral(expected.evaluate(null, String.class));
             return String.format(
-                    "json_exists(%s, '$%s?(@ == \"%s\")')",
-                    columnName, strJsonPath, expected.evaluate(null));
+                    "json_exists(%s, '$%s?(@ == \"%s\")')", columnName, strJsonPath, strExpected);
         } else {
             throw new IllegalArgumentException(
                     "Cannot encode filter Invalid pointer " + jsonPath.getValue());
         }
     }
diff --git a/modules/plugin/jdbc/jdbc-oracle/src/test/java/org/geotools/data/oracle/OracleFilterToSqlTest.java b/modules/plugin/jdbc/jdbc-oracle/src/test/java/org/geotools/data/oracle/OracleFilterToSqlTest.java
index 32b81e8afc..de922ebba0 100644
--- a/modules/plugin/jdbc/jdbc-oracle/src/test/java/org/geotools/data/oracle/OracleFilterToSqlTest.java
+++ b/modules/plugin/jdbc/jdbc-oracle/src/test/java/org/geotools/data/oracle/OracleFilterToSqlTest.java
@@ -159,10 +159,22 @@ public void testDWithinFilterWithUnit() throws Exception {
         String encoded = encoder.encodeToString(dwithin);
         assertEquals(
                 "WHERE SDO_WITHIN_DISTANCE(\"GEOM\",?,'distance=10.0 unit=km') = 'TRUE' ", encoded);
     }
 
+    @Test
+    public void testDWithinFilterWithUnitEscaping() throws Exception {
+        Coordinate coordinate = new Coordinate();
+        DWithin dwithin =
+                ff.dwithin(
+                        ff.property("GEOM"), ff.literal(gf.createPoint(coordinate)), 10.0, "'FOO");
+        String encoded = encoder.encodeToString(dwithin);
+        assertEquals(
+                "WHERE SDO_WITHIN_DISTANCE(\"GEOM\",?,'distance=10.0 unit=''FOO') = 'TRUE' ",
+                encoded);
+    }
+
     @Test
     public void testDWithinFilterWithoutUnit() throws Exception {
         Coordinate coordinate = new Coordinate();
         DWithin dwithin =
                 ff.dwithin(ff.property("GEOM"), ff.literal(gf.createPoint(coordinate)), 10.0, null);
@@ -208,10 +220,38 @@ public void testJsonArrayContainsNestedObject() throws Exception {
         String encoded = encoder.encodeToString(filter);
         assertEquals(
                 "WHERE json_exists(operations, '$.operations.parameters?(@ == \"1\")')", encoded);
     }
 
+    @Test
+    public void testFunctionJsonArrayContainsEscapingPointer() throws Exception {
+        Function function =
+                ff.function(
+                        "jsonArrayContains",
+                        ff.property("operations"),
+                        ff.literal("/'FOO"),
+                        ff.literal(1));
+        Filter filter = ff.equals(function, ff.literal(true));
+        String encoded = encoder.encodeToString(filter);
+        assertEquals("WHERE json_exists(operations, '$.''FOO?(@ == \"1\")')", encoded);
+    }
+
+    @Test
+    public void testFunctionJsonArrayContainsEscapingExpected() throws Exception {
+        Function function =
+                ff.function(
+                        "jsonArrayContains",
+                        ff.property("operations"),
+                        ff.literal("/operations/parameters"),
+                        ff.literal("'FOO"));
+        Filter filter = ff.equals(function, ff.literal(true));
+        String encoded = encoder.encodeToString(filter);
+        assertEquals(
+                "WHERE json_exists(operations, '$.operations.parameters?(@ == \"''FOO\")')",
+                encoded);
+    }
+
     // THIS ONE WON'T PASS RIGHT NOW, BUT WE NEED TO PUT A TEST LIKE THIS
     // SOMEHWERE
     // THAT IS, SOMETHING CHECKING THAT TYPED FIDS GET CONVERTED INTO THE PROPER
     // WHERE CLAUSE
     // public void testFIDEncoding() throws Exception {
diff --git a/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java b/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java
index d56534fd26..16bffcbfdb 100644
--- a/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java
+++ b/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java
@@ -23,11 +23,10 @@
 import java.math.BigInteger;
 import java.sql.Date;
 import java.sql.Time;
 import java.sql.Timestamp;
 import java.util.ArrayList;
-import java.util.Arrays;
 import java.util.List;
 import java.util.concurrent.TimeUnit;
 import java.util.function.BiConsumer;
 import java.util.stream.Collectors;
 import java.util.stream.Stream;
@@ -63,10 +62,11 @@
 import org.geotools.filter.function.math.FilterFunction_abs_3;
 import org.geotools.filter.function.math.FilterFunction_abs_4;
 import org.geotools.filter.function.math.FilterFunction_ceil;
 import org.geotools.filter.function.math.FilterFunction_floor;
 import org.geotools.geometry.jts.JTS;
+import org.geotools.jdbc.EscapeSql;
 import org.geotools.jdbc.JDBCDataStore;
 import org.geotools.jdbc.PreparedFilterToSQL;
 import org.geotools.jdbc.PrimaryKeyColumn;
 import org.geotools.jdbc.SQLDialect;
 import org.geotools.util.factory.Hints;
@@ -567,34 +567,22 @@ public boolean visitFunction(Function function, Object extraData) throws IOExcep
             Expression str = getParameter(function, 0, true);
             Expression end = getParameter(function, 1, true);
 
             out.write("(");
             str.accept(delegate, String.class);
-            out.write(" LIKE ");
-            if (end instanceof Literal) {
-                out.write("'%" + end.evaluate(null, String.class) + "'");
-            } else {
-                out.write("('%' || ");
-                end.accept(delegate, String.class);
-                out.write(")");
-            }
-            out.write(")");
+            out.write(" LIKE ('%' || ");
+            end.accept(delegate, String.class);
+            out.write("))");
         } else if (function instanceof FilterFunction_strStartsWith) {
             Expression str = getParameter(function, 0, true);
             Expression start = getParameter(function, 1, true);
 
             out.write("(");
             str.accept(delegate, String.class);
-            out.write(" LIKE ");
-            if (start instanceof Literal) {
-                out.write("'" + start.evaluate(null, String.class) + "%'");
-            } else {
-                out.write("(");
-                start.accept(delegate, String.class);
-                out.write(" || '%')");
-            }
-            out.write(")");
+            out.write(" LIKE (");
+            start.accept(delegate, String.class);
+            out.write(" || '%'))");
         } else if (function instanceof FilterFunction_strEqualsIgnoreCase) {
             Expression first = getParameter(function, 0, true);
             Expression second = getParameter(function, 1, true);
 
             out.write("(lower(");
@@ -701,49 +689,46 @@ private void encodeJsonPointer(Function jsonPointer, Object extraData) throws IO
                 out.write(cast("", (Class) extraData));
             }
         }
     }
 
-    public String buildJsonFromStrPointer(String[] pointers, Expression expectedExp) {
-        if (!"".equals(pointers[0])) {
-            if (pointers.length == 1) {
-                final String expected =
-                        getBaseType(expectedExp).isAssignableFrom(String.class)
-                                ? String.format(
-                                        "\"%s\"", ((Literal) expectedExp).getValue().toString())
-                                : ((Literal) expectedExp).getValue().toString();
-                return String.format("\"%s\": [%s]", pointers[0], expected);
-            } else {
-                return String.format(
-                        "\"%s\": { %s }",
-                        pointers[0],
-                        buildJsonFromStrPointer(
-                                Arrays.copyOfRange(pointers, 1, pointers.length), expectedExp));
+    public String buildJsonFromStrPointer(String[] pointers, int index, Expression expected) {
+        if (pointers[index].isEmpty()) {
+            return buildJsonFromStrPointer(pointers, index + 1, expected);
+        } else if (index == pointers.length - 1) {
+            String strExpected = escapeJsonLiteral(expected.evaluate(null, String.class));
+            if (getBaseType(expected).isAssignableFrom(String.class)) {
+                strExpected = '"' + strExpected + '"';
             }
-        } else
-            return buildJsonFromStrPointer(
-                    Arrays.copyOfRange(pointers, 1, pointers.length), expectedExp);
+            return String.format("\"%s\": [%s]", pointers[index], strExpected);
+        } else {
+            String jsonPointers = buildJsonFromStrPointer(pointers, index + 1, expected);
+            return String.format("\"%s\": { %s }", pointers[index], jsonPointers);
+        }
     }
 
     private void encodeJsonArrayContains(Function jsonArrayContains) throws IOException {
         PropertyName column = (PropertyName) getParameter(jsonArrayContains, 0, true);
         Literal jsonPath = (Literal) getParameter(jsonArrayContains, 1, true);
         Expression expected = getParameter(jsonArrayContains, 2, true);
 
-        String[] strJsonPath = jsonPath.getValue().toString().split("/");
+        String[] strJsonPath = escapeJsonLiteral(jsonPath.getValue().toString()).split("/");
         if (strJsonPath.length > 0) {
-            String jsonFilter =
-                    String.format("{ %s }", buildJsonFromStrPointer(strJsonPath, expected));
-            out.write(
-                    String.format(
-                            "\"%s\"::jsonb @> '%s'::jsonb", column.getPropertyName(), jsonFilter));
+            column.accept(delegate, null);
+            out.write("::jsonb @> '{ ");
+            out.write(buildJsonFromStrPointer(strJsonPath, 0, expected));
+            out.write(" }'::jsonb");
         } else {
             throw new IllegalArgumentException(
                     "Cannot encode filter Invalid pointer " + jsonPath.getValue());
         }
     }
 
+    private static String escapeJsonLiteral(String literal) {
+        return EscapeSql.escapeLiteral(literal, true, true);
+    }
+
     Expression getParameter(Function function, int idx, boolean mandatory) {
         final List<Expression> params = function.getParameters();
         if (params == null || params.size() <= idx) {
             if (mandatory) {
                 throw new IllegalArgumentException(
diff --git a/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISDialect.java b/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISDialect.java
index 6e3648b078..5c792699d2 100644
--- a/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISDialect.java
+++ b/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISDialect.java
@@ -80,10 +80,11 @@
 import org.opengis.filter.expression.Expression;
 import org.opengis.filter.expression.Function;
 import org.opengis.filter.expression.Literal;
 import org.opengis.referencing.FactoryException;
 import org.opengis.referencing.crs.CoordinateReferenceSystem;
+import org.postgresql.jdbc.PgConnection;
 
 public class PostGISDialect extends BasicSQLDialect {
 
     public static final String BIGDATE_UDT = "bigdate";
 
@@ -209,10 +210,13 @@ public PostGISDialect(JDBCDataStore dataStore) {
 
     boolean base64EncodingEnabled = true;
 
     boolean topologyPreserved = false;
 
+    // checkStandardConformingStrings will set this based on database configuration
+    boolean escapeBackslash = true;
+
     Version version, pgsqlVersion;
 
     public boolean isLooseBBOXEnabled() {
         return looseBBOXEnabled;
     }
@@ -248,10 +252,14 @@ public void setFunctionEncodingEnabled(boolean functionEncodingEnabled) {
 
     public boolean isSimplifyEnabled() {
         return simplifyEnabled;
     }
 
+    public boolean isEscapeBackslash() {
+        return escapeBackslash;
+    }
+
     @Override
     public boolean canSimplifyPoints() {
         // TWKB encoding is a form of simplified points representation (reduced precision)
         return version != null && version.compareTo(V_2_2_0) >= 0 && isSimplifyEnabled();
     }
@@ -281,10 +289,11 @@ public void setTopologyPreserved(boolean topologyPreserved) {
     @Override
     public void initializeConnection(Connection cx) throws SQLException {
         super.initializeConnection(cx);
         getPostgreSQLVersion(cx);
         getVersion(cx);
+        checkStandardConformingStrings(cx);
     }
 
     @Override
     public boolean includeTable(String schemaName, String tableName, Connection cx)
             throws SQLException {
@@ -1382,10 +1391,11 @@ public void encodeGeometryValue(Geometry value, int dimension, int srid, StringB
     public FilterToSQL createFilterToSQL() {
         PostgisFilterToSQL sql = new PostgisFilterToSQL(this);
         sql.setLooseBBOXEnabled(looseBBOXEnabled);
         sql.setEncodeBBOXFilterAsEnvelope(encodeBBOXFilterAsEnvelope);
         sql.setFunctionEncodingEnabled(functionEncodingEnabled);
+        sql.setEscapeBackslash(escapeBackslash);
         return sql;
     }
 
     @Override
     public boolean isLimitOffsetSupported() {
@@ -1515,10 +1525,44 @@ public Version getPostgreSQLVersion(Connection conn) throws SQLException {
                                     md.getDatabaseMajorVersion(), md.getDatabaseMinorVersion()));
         }
         return pgsqlVersion;
     }
 
+    /**
+     * Determines whether or not to escape backslashes based on the PostgreSQL server's
+     * standard_conforming_strings setting.
+     */
+    @SuppressWarnings("PMD.CloseResource")
+    private void checkStandardConformingStrings(Connection conn) throws SQLException {
+        Boolean escape = null;
+        // first, try to determine the setting from a native connection object
+        try {
+            PgConnection bc = unwrapConnection(conn, PgConnection.class);
+            escape = !bc.getStandardConformingStrings();
+        } catch (SQLException e) {
+            LOGGER.log(Level.FINER, "Unable to get native connection; falling back to query", e);
+        }
+        // otherwise, try to determine the setting from a database query
+        if (escape == null) {
+            Statement st = null;
+            ResultSet rs = null;
+            try {
+                st = conn.createStatement();
+                rs = st.executeQuery("SHOW standard_conforming_strings");
+                escape = !rs.next() || !"on".equals(rs.getString(1));
+            } catch (SQLException e) {
+                LOGGER.warning(
+                        "Unable to check standard_conforming_strings setting: " + e.getMessage());
+            } finally {
+                dataStore.closeSafe(rs);
+                dataStore.closeSafe(st);
+            }
+        }
+        // default to escape backslashes if both checks failed
+        escapeBackslash = !Boolean.FALSE.equals(escape);
+    }
+
     /** Returns true if the PostGIS version is >= 1.5.0 */
     boolean supportsGeography(Connection cx) throws SQLException {
         return getVersion(cx).compareTo(V_1_5_0) >= 0;
     }
 
diff --git a/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISPSDialect.java b/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISPSDialect.java
index 5fe10ccb21..56fa085136 100644
--- a/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISPSDialect.java
+++ b/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISPSDialect.java
@@ -261,10 +261,11 @@ public void setGeometryValue(
     public PreparedFilterToSQL createPreparedFilterToSQL() {
         PostgisPSFilterToSql fts = new PostgisPSFilterToSql(this);
         fts.setFunctionEncodingEnabled(delegate.isFunctionEncodingEnabled());
         fts.setLooseBBOXEnabled(delegate.isLooseBBOXEnabled());
         fts.setEncodeBBOXFilterAsEnvelope(delegate.isEncodeBBOXFilterAsEnvelope());
+        fts.setEscapeBackslash(delegate.isEscapeBackslash());
         return fts;
     }
 
     @Override
     public boolean isLimitOffsetSupported() {
diff --git a/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostgisNGDataStoreFactory.java b/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostgisNGDataStoreFactory.java
index a07aa88804..472baa6010 100644
--- a/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostgisNGDataStoreFactory.java
+++ b/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostgisNGDataStoreFactory.java
@@ -27,10 +27,11 @@
 import java.util.Map;
 import java.util.logging.Level;
 import java.util.logging.Logger;
 import javax.sql.DataSource;
 import org.geotools.data.Parameter;
+import org.geotools.data.Transaction;
 import org.geotools.jdbc.JDBCDataStore;
 import org.geotools.jdbc.JDBCDataStoreFactory;
 import org.geotools.jdbc.SQLDialect;
 import org.geotools.util.KVP;
 import org.geotools.util.logging.Logging;
@@ -257,10 +258,19 @@ protected JDBCDataStore createDataStoreInternal(JDBCDataStore dataStore, Map<Str
         if (largeGeometriesOptimized != null) {
             encodeBBOXAsEnvelope = largeGeometriesOptimized.toLowerCase().equals("true");
         }
         dialect.setEncodeBBOXFilterAsEnvelope(Boolean.TRUE.equals(encodeBBOXAsEnvelope));
 
+        Connection cx = dataStore.getConnection(Transaction.AUTO_COMMIT);
+        try {
+            // creating a new connection will internally call
+            // org.geotools.data.postgis.PostGISDialect.initializeConnection(Connection)
+            // the following line is really just to prevent empty try block PMD violation
+            LOGGER.finest("escaping backslashes: " + dialect.isEscapeBackslash());
+        } finally {
+            dataStore.closeSafe(cx);
+        }
         return dataStore;
     }
 
     @Override
     protected void setupParameters(Map<String, Object> parameters) {
diff --git a/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisFilterToSQLTest.java b/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisFilterToSQLTest.java
index f5a9497250..b5b819a8b5 100644
--- a/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisFilterToSQLTest.java
+++ b/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisFilterToSQLTest.java
@@ -233,10 +233,35 @@ public void testEncodeEqualToArraysAll() throws Exception {
         filterToSql.encode(expr);
         String sql = writer.toString().toLowerCase();
         assertEquals("where testarray = array['1', '2', '3']", sql);
     }
 
+    @Test
+    public void testFunctionStrEndsWithEscaping() throws Exception {
+        filterToSql.setFeatureType(testSchema);
+        Filter filter =
+                ff.equals(
+                        ff.literal(true),
+                        ff.function("strEndsWith", ff.property("testString"), ff.literal("'FOO")));
+        filterToSql.encode(filter);
+        String sql = writer.toString();
+        assertEquals("WHERE true = (testString LIKE ('%' || '''FOO'))", sql);
+    }
+
+    @Test
+    public void testFunctionStrStartsWithEscaping() throws Exception {
+        filterToSql.setFeatureType(testSchema);
+        Filter filter =
+                ff.equals(
+                        ff.literal(true),
+                        ff.function(
+                                "strStartsWith", ff.property("testString"), ff.literal("'FOO")));
+        filterToSql.encode(filter);
+        String sql = writer.toString();
+        assertEquals("WHERE true = (testString LIKE ('''FOO' || '%'))", sql);
+    }
+
     @Test
     public void testFunctionLike() throws Exception {
         filterToSql.setFeatureType(testSchema);
         PropertyIsLike like =
                 ff.like(
@@ -297,11 +322,11 @@ public void testFunctionJsonArrayContains() throws Exception {
                         ff.property("OPERATIONS"),
                         ff.literal("/operations"),
                         ff.literal("OP1"));
         filterToSql.encode(pointer);
         String sql = writer.toString().trim();
-        assertEquals("\"OPERATIONS\"::jsonb @> '{ \"operations\": [\"OP1\"] }'::jsonb", sql);
+        assertEquals("OPERATIONS::jsonb @> '{ \"operations\": [\"OP1\"] }'::jsonb", sql);
     }
 
     @Test
     public void testFunctionJsonArrayContainsNumber() throws Exception {
         filterToSql.setFeatureType(testSchema);
@@ -311,11 +336,11 @@ public void testFunctionJsonArrayContainsNumber() throws Exception {
                         ff.property("OPERATIONS"),
                         ff.literal("/operations"),
                         ff.literal(1));
         filterToSql.encode(pointer);
         String sql = writer.toString().trim();
-        assertEquals("\"OPERATIONS\"::jsonb @> '{ \"operations\": [1] }'::jsonb", sql);
+        assertEquals("OPERATIONS::jsonb @> '{ \"operations\": [1] }'::jsonb", sql);
     }
 
     @Test
     public void testNestedObjectJsonArrayContains() throws Exception {
         filterToSql.setFeatureType(testSchema);
@@ -326,9 +351,37 @@ public void testNestedObjectJsonArrayContains() throws Exception {
                         ff.literal("/operations/parameters"),
                         ff.literal("P1"));
         filterToSql.encode(pointer);
         String sql = writer.toString().trim();
         assertEquals(
-                "\"OPERATIONS\"::jsonb @> '{ \"operations\": { \"parameters\": [\"P1\"] } }'::jsonb",
+                "OPERATIONS::jsonb @> '{ \"operations\": { \"parameters\": [\"P1\"] } }'::jsonb",
                 sql);
     }
+
+    @Test
+    public void testFunctionJsonArrayContainsEscapingPointer() throws Exception {
+        filterToSql.setFeatureType(testSchema);
+        Function pointer =
+                ff.function(
+                        "jsonArrayContains",
+                        ff.property("OPERATIONS"),
+                        ff.literal("/\"'FOO"),
+                        ff.literal("OP1"));
+        filterToSql.encode(pointer);
+        String sql = writer.toString().trim();
+        assertEquals("OPERATIONS::jsonb @> '{ \"\\\"''FOO\": [\"OP1\"] }'::jsonb", sql);
+    }
+
+    @Test
+    public void testFunctionJsonArrayContainsEscapingExpected() throws Exception {
+        filterToSql.setFeatureType(testSchema);
+        Function pointer =
+                ff.function(
+                        "jsonArrayContains",
+                        ff.property("OPERATIONS"),
+                        ff.literal("/operations"),
+                        ff.literal("\"'FOO"));
+        filterToSql.encode(pointer);
+        String sql = writer.toString().trim();
+        assertEquals("OPERATIONS::jsonb @> '{ \"operations\": [\"\\\"''FOO\"] }'::jsonb", sql);
+    }
 }
diff --git a/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisNGDataStoreFactoryTest.java b/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisNGDataStoreFactoryTest.java
new file mode 100644
index 0000000000..e2c5bc6d5a
--- /dev/null
+++ b/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisNGDataStoreFactoryTest.java
@@ -0,0 +1,163 @@
+/*
+ *    GeoTools - The Open Source Java GIS Toolkit
+ *    http://geotools.org
+ *
+ *    (C) 2023, Open Source Geospatial Foundation (OSGeo)
+ *
+ *    This library is free software; you can redistribute it and/or
+ *    modify it under the terms of the GNU Lesser General Public
+ *    License as published by the Free Software Foundation;
+ *    version 2.1 of the License.
+ *
+ *    This library is distributed in the hope that it will be useful,
+ *    but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ *    Lesser General Public License for more details.
+ */
+package org.geotools.data.postgis;
+
+import static org.geotools.data.postgis.PostgisNGDataStoreFactory.PREPARED_STATEMENTS;
+import static org.geotools.jdbc.JDBCDataStoreFactory.DATASOURCE;
+import static org.hamcrest.CoreMatchers.instanceOf;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNotNull;
+import static org.mockito.Mockito.never;
+import static org.mockito.Mockito.times;
+import static org.mockito.Mockito.verify;
+import static org.mockito.Mockito.when;
+
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.ResultSet;
+import java.sql.Statement;
+import java.util.HashMap;
+import java.util.Map;
+import javax.sql.DataSource;
+import org.geotools.jdbc.JDBCDataStore;
+import org.geotools.jdbc.SQLDialect;
+import org.geotools.util.Version;
+import org.junit.After;
+import org.junit.Before;
+import org.junit.Test;
+import org.mockito.Mock;
+import org.mockito.MockitoAnnotations;
+import org.postgresql.jdbc.PgConnection;
+
+public class PostgisNGDataStoreFactoryTest {
+
+    @Mock private DataSource ds = null;
+
+    @Mock private Connection conn = null;
+
+    @Mock private PgConnection pgConn = null;
+
+    @Mock private DatabaseMetaData md = null;
+
+    @Mock private Statement st1 = null;
+
+    @Mock private Statement st2 = null;
+
+    @Mock private ResultSet rs1 = null;
+
+    @Mock private ResultSet rs2 = null;
+
+    private JDBCDataStore store = null;
+
+    private AutoCloseable mocks = null;
+
+    @Before
+    public void setUp() throws Exception {
+        this.mocks = MockitoAnnotations.openMocks(this);
+        when(this.ds.getConnection()).thenReturn(this.conn);
+        when(this.conn.getMetaData()).thenReturn(this.md);
+        when(this.md.getDatabaseMajorVersion()).thenReturn(15);
+        when(this.md.getDatabaseMinorVersion()).thenReturn(1);
+        when(this.conn.createStatement()).thenReturn(this.st1, this.st2);
+        when(this.st1.executeQuery("select PostGIS_Lib_Version()")).thenReturn(this.rs1);
+        when(this.rs1.next()).thenReturn(true);
+        when(this.rs1.getString(1)).thenReturn("3.3.2");
+    }
+
+    @After
+    public void tearDown() throws Exception {
+        if (this.mocks != null) {
+            this.mocks.close();
+            this.mocks = null;
+        }
+        if (this.store != null) {
+            this.store.dispose();
+        }
+    }
+
+    @Test
+    public void testStandardConformingStringsOnFromConnection() throws Exception {
+        verifyFilterToSqlSettings(false, false, false);
+    }
+
+    @Test
+    public void testStandardConformingStringsOffFromConnection() throws Exception {
+        verifyFilterToSqlSettings(true, false, false);
+    }
+
+    @Test
+    public void testStandardConformingStringsOnFromQuery() throws Exception {
+        verifyFilterToSqlSettings(false, false, true);
+    }
+
+    @Test
+    public void testStandardConformingStringsOffFromQuery() throws Exception {
+        verifyFilterToSqlSettings(true, false, true);
+    }
+
+    @Test
+    public void testStandardConformingStringsOnWithPSFromConnection() throws Exception {
+        verifyFilterToSqlSettings(false, true, false);
+    }
+
+    @Test
+    public void testStandardConformingStringsOffWithPSFromConnection() throws Exception {
+        verifyFilterToSqlSettings(true, true, false);
+    }
+
+    @Test
+    public void testStandardConformingStringsOnWithPSFromQuery() throws Exception {
+        verifyFilterToSqlSettings(false, true, true);
+    }
+
+    @Test
+    public void testStandardConformingStringsOffWithPSFromQuery() throws Exception {
+        verifyFilterToSqlSettings(true, true, true);
+    }
+
+    private void verifyFilterToSqlSettings(
+            boolean escapeBackslash, boolean withPS, boolean withQuery) throws Exception {
+        if (withQuery) {
+            when(this.st2.executeQuery("SHOW standard_conforming_strings")).thenReturn(this.rs2);
+            when(this.rs2.next()).thenReturn(true);
+            when(this.rs2.getString(1)).thenReturn(escapeBackslash ? "off" : "on");
+        } else {
+            when(this.conn.isWrapperFor(PgConnection.class)).thenReturn(true);
+            when(this.conn.unwrap(PgConnection.class)).thenReturn(this.pgConn);
+            when(this.pgConn.getStandardConformingStrings()).thenReturn(!escapeBackslash);
+        }
+        Map<String, Object> params = new HashMap<>();
+        params.put(DATASOURCE.key, this.ds);
+        params.put(PREPARED_STATEMENTS.key, withPS);
+        this.store = new PostgisNGDataStoreFactory().createDataStore(params);
+        assertNotNull(this.store);
+        SQLDialect dialect = this.store.getSQLDialect();
+        assertThat(dialect, instanceOf(withPS ? PostGISPSDialect.class : PostGISDialect.class));
+        PostGISDialect pgDialect =
+                withPS ? ((PostGISPSDialect) dialect).getDelegate() : (PostGISDialect) dialect;
+        assertEquals(new Version("15.1"), pgDialect.getPostgreSQLVersion(this.conn));
+        assertEquals(new Version("3.3.2"), pgDialect.getVersion(this.conn));
+        assertEquals(escapeBackslash, pgDialect.isEscapeBackslash());
+        verify(this.conn, withQuery ? times(2) : times(1)).createStatement();
+        verify(this.conn).close();
+        verify(this.st1).close();
+        verify(this.rs1).close();
+        verify(this.st2, withQuery ? times(1) : never()).close();
+        verify(this.rs2, withQuery ? times(1) : never()).close();
+    }
+}

Vulnerable code:         String pattern = LikeFilterImpl.convertToSQL92(esc, multi, single, matchCase, literal);
                out.write(") LIKE '");
                out.write(" LIKE '");
            out.write(pattern);
            out.write("' ");
                    out.write(" = '");
                    out.write(
                            attValues.get(j).toString()); // DJB: changed this to attValues[j] from
                    // attValues[i].
                    out.write("'");
            // sigle quotes must be escaped to have a valid sql string
            String escaped = encoding.replaceAll("'", "''");
        StringBuffer result = new StringBuffer(pattern.length() + 5);
            } else if (chr == '\'') {
                pattern);
                "BroadWay%", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "BroadWay*"));
                "broad#ay", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broad#ay"));
                "broadway", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway"));
                "broad_ay", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broad.ay"));
                "broad.ay", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broad!.ay"));
                "broa''dway", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broa'dway"));
                LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broa" + "''dway"));
                "broadway_", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway."));
                "broadway", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway!"));
                "broadway!", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway!!"));
        return new MySQLFilterToSQL(delegate.getUsePreciseSpatialOps());
import java.sql.Wrapper;
import org.geotools.data.jdbc.datasource.DataSourceFinder;
import org.geotools.data.jdbc.datasource.UnWrapper;
    /**
     * Sentinel value used to mark that the unwrapper lookup happened already, and an unwrapper was
     * not found
     */
    UnWrapper UNWRAPPER_NOT_FOUND =
            new UnWrapper() {

                @Override
                public Statement unwrap(Statement statement) {
                    throw new UnsupportedOperationException();
                }

                @Override
                public Connection unwrap(Connection conn) {
                    throw new UnsupportedOperationException();
                }

                @Override
                public boolean canUnwrap(Statement st) {
                    return false;
                }

                @Override
                public boolean canUnwrap(Connection conn) {
                    return false;
                }
            };

    /**
     * Map of {@code UnWrapper} objects keyed by the class of {@code Connection} it is an unwrapper
     * for. This avoids the overhead of searching the {@code DataSourceFinder} service registry at
     * each unwrap.
     */
    Map<Class<? extends Connection>, UnWrapper> uwMap = new HashMap<>();

    @SuppressWarnings("PMD.CloseResource")
        if (cx == null) {
            return null;
        }

        if (cx instanceof OracleConnection) {
            return (OracleConnection) cx;
        }

        try {
            // Unwrap the connection multiple levels as necessary to get at the underlying
            // OracleConnection. Maintain a map of UnWrappers to avoid searching
            // the registry every time we need to unwrap.
            Connection testCon = cx;
            Connection toUnwrap;
            do {
                UnWrapper unwrapper = uwMap.get(testCon.getClass());
                if (unwrapper == null) {
                    unwrapper = DataSourceFinder.getUnWrapper(testCon);
                    if (unwrapper == null) {
                        unwrapper = UNWRAPPER_NOT_FOUND;
                    }
                    uwMap.put(testCon.getClass(), unwrapper);
                }
                if (unwrapper == UNWRAPPER_NOT_FOUND) {
                    // give up and do Java 6 unwrap below
                    break;
                }
                toUnwrap = testCon;
                testCon = unwrapper.unwrap(testCon);
                if (testCon instanceof OracleConnection) {
                    return (OracleConnection) testCon;
                }
            } while (testCon != null && testCon != toUnwrap);

            if (cx instanceof Wrapper) {
                // try to use java 6 unwrapping
                try {
                    Wrapper w = cx;
                    if (w.isWrapperFor(OracleConnection.class)) {
                        return w.unwrap(OracleConnection.class);
                    }
                } catch (Throwable t) {
                    // not a mistake, old DBCP versions will throw an Error here, we need to catch
                    // it
                    LOGGER.log(
                            Level.FINER, "Failed to unwrap connection using java 6 facilities", t);
                }
            }
        } catch (IOException e) {
            throw (SQLException)
                    new SQLException("Could not obtain native oracle connection.").initCause(e);
        }

        throw new SQLException("Could not obtain native oracle connection for " + cx.getClass());
        if (unit != null && !"".equals(unit.trim()))
        else out.write(",'distance=" + distance + "') = '" + within + "' ");
            String strJsonPath = String.join(".", pointers);
                    "json_exists(%s, '$%s?(@ == \"%s\")')",
                    columnName, strJsonPath, expected.evaluate(null));
import java.util.Arrays;
            out.write(" LIKE ");
            if (end instanceof Literal) {
                out.write("'%" + end.evaluate(null, String.class) + "'");
            } else {
                out.write("('%' || ");
                end.accept(delegate, String.class);
                out.write(")");
            }
            out.write(")");
            out.write(" LIKE ");
            if (start instanceof Literal) {
                out.write("'" + start.evaluate(null, String.class) + "%'");
            } else {
                out.write("(");
                start.accept(delegate, String.class);
                out.write(" || '%')");
            }
            out.write(")");
    public String buildJsonFromStrPointer(String[] pointers, Expression expectedExp) {
        if (!"".equals(pointers[0])) {
            if (pointers.length == 1) {
                final String expected =
                        getBaseType(expectedExp).isAssignableFrom(String.class)
                                ? String.format(
                                        "\"%s\"", ((Literal) expectedExp).getValue().toString())
                                : ((Literal) expectedExp).getValue().toString();
                return String.format("\"%s\": [%s]", pointers[0], expected);
            } else {
                return String.format(
                        "\"%s\": { %s }",
                        pointers[0],
                        buildJsonFromStrPointer(
                                Arrays.copyOfRange(pointers, 1, pointers.length), expectedExp));
        } else
            return buildJsonFromStrPointer(
                    Arrays.copyOfRange(pointers, 1, pointers.length), expectedExp);
        String[] strJsonPath = jsonPath.getValue().toString().split("/");
            String jsonFilter =
                    String.format("{ %s }", buildJsonFromStrPointer(strJsonPath, expected));
            out.write(
                    String.format(
                            "\"%s\"::jsonb @> '%s'::jsonb", column.getPropertyName(), jsonFilter));
        assertEquals("\"OPERATIONS\"::jsonb @> '{ \"operations\": [\"OP1\"] }'::jsonb", sql);
        assertEquals("\"OPERATIONS\"::jsonb @> '{ \"operations\": [1] }'::jsonb", sql);
                "\"OPERATIONS\"::jsonb @> '{ \"operations\": { \"parameters\": [\"P1\"] } }'::jsonb",
Fixed code: import org.geotools.jdbc.EscapeSql;
    /** Whether to escape backslash characters in string literals */
    protected boolean escapeBackslash = false;

    /** @return whether to escape backslash characters in string literals */
    public boolean isEscapeBackslash() {
        return escapeBackslash;
    }

    /** @param escapeBackslash whether to escape backslash characters in string literals */
    public void setEscapeBackslash(boolean escapeBackslash) {
        this.escapeBackslash = escapeBackslash;
    }

        String pattern =
                LikeFilterImpl.convertToSQL92(esc, multi, single, matchCase, literal, false);
                out.write(") LIKE ");
                out.write(" LIKE ");
            writeLiteral(pattern);
                    out.write(" = ");
                    writeLiteral(attValues.get(j));
            // single quotes must be escaped to have a valid sql string
            String escaped = escapeLiteral(encoding);
    /** Escapes the string literal. */
    public String escapeLiteral(String literal) {
        return EscapeSql.escapeLiteral(literal, escapeBackslash, false);
    }

import java.util.regex.Pattern;


    private static final Pattern SINGLE_QUOTE_PATTERN = Pattern.compile("'");

    private static final Pattern DOUBLE_QUOTE_PATTERN = Pattern.compile("\"");

    private static final Pattern BACKSLASH_PATTERN = Pattern.compile("\\\\");

    public static String escapeLiteral(
            String literal, boolean escapeBackslash, boolean escapeDoubleQuote) {
        // ' --> ''
        String escaped = SINGLE_QUOTE_PATTERN.matcher(literal).replaceAll("''");
        if (escapeBackslash) {
            // \ --> \\
            escaped = BACKSLASH_PATTERN.matcher(escaped).replaceAll("\\\\\\\\");
        }
        if (escapeDoubleQuote) {
            // " --> \"
            escaped = DOUBLE_QUOTE_PATTERN.matcher(escaped).replaceAll("\\\\\"");
        }
        return escaped;
    }

import java.util.HashMap;
import org.geotools.data.jdbc.datasource.DataSourceFinder;
import org.geotools.data.jdbc.datasource.UnWrapper;
    /**
     * Sentinel value used to mark that the unwrapper lookup happened already, and an unwrapper was
     * not found
     */
    protected static final UnWrapper UNWRAPPER_NOT_FOUND =
            new UnWrapper() {

                @Override
                public Statement unwrap(Statement statement) {
                    throw new UnsupportedOperationException();
                }

                @Override
                public Connection unwrap(Connection conn) {
                    throw new UnsupportedOperationException();
                }

                @Override
                public boolean canUnwrap(Statement st) {
                    return false;
                }

                @Override
                public boolean canUnwrap(Connection conn) {
                    return false;
                }
            };

    /**
     * Map of {@code UnWrapper} objects keyed by the class of {@code Connection} it is an unwrapper
     * for. This avoids the overhead of searching the {@code DataSourceFinder} service registry at
     * each unwrap.
     */
    protected final Map<Class<? extends Connection>, UnWrapper> uwMap = new HashMap<>();


    /** Obtains the native connection object given a database connection. */
    @SuppressWarnings("PMD.CloseResource")
    protected <T extends Connection> T unwrapConnection(Connection cx, Class<T> clazz)
            throws SQLException {
        if (clazz.isInstance(cx)) {
            return clazz.cast(cx);
        }
        try {
            // Unwrap the connection multiple levels as necessary to get at the underlying
            // connection. Maintain a map of UnWrappers to avoid searching the registry
            // every time we need to unwrap.
            Connection testCon = cx;
            Connection toUnwrap;
            do {
                UnWrapper unwrapper = uwMap.get(testCon.getClass());
                if (unwrapper == null) {
                    unwrapper = DataSourceFinder.getUnWrapper(testCon);
                    if (unwrapper == null) {
                        unwrapper = UNWRAPPER_NOT_FOUND;
                    }
                    uwMap.put(testCon.getClass(), unwrapper);
                }
                if (unwrapper == UNWRAPPER_NOT_FOUND) {
                    // give up and do Java unwrap below
                    break;
                }
                toUnwrap = testCon;
                testCon = unwrapper.unwrap(testCon);
                if (clazz.isInstance(testCon)) {
                    return clazz.cast(cx);
                }
            } while (testCon != null && testCon != toUnwrap);
            // try to use Java unwrapping
            try {
                if (cx.isWrapperFor(clazz)) {
                    return cx.unwrap(clazz);
                }
            } catch (Throwable t) {
                // not a mistake, old DBCP versions will throw an Error here, we need to catch it
                LOGGER.log(Level.FINER, "Failed to unwrap connection using Java facilities", t);
            }
        } catch (IOException e) {
            throw new SQLException(
                    "Could not obtain " + clazz.getName() + " from " + cx.getClass(), e);
        }
        throw new SQLException("Could not obtain " + clazz.getName() + " from " + cx.getClass());
    }

    @Test
    public void testLikeEscaping() throws Exception {
        Filter filter = ff.like(ff.property("testString"), "\\'FOO", "%", "-", "\\", true);
        FilterToSQL encoder = new FilterToSQL(output);
        Assert.assertEquals("WHERE testString LIKE '''FOO'", encoder.encodeToString(filter));
    }

    @Test
    public void testIdEscaping() throws Exception {
        Id id = ff.id(Collections.singleton(ff.featureId("'FOO")));
        encoder.encode(id);
        Assert.assertEquals("WHERE (id = '''FOO')", output.toString());
    }
    @Deprecated
        return convertToSQL92(escape, multi, single, matchCase, pattern, true);
    }

    /**
     * Given OGC PropertyIsLike Filter information, construct an SQL-compatible 'like' pattern.
     *
     * <p>SQL % --> match any number of characters _ --> match a single character
     *
     * <p>NOTE; the SQL command is 'string LIKE pattern [ESCAPE escape-character]' We could
     * re-define the escape character, but I'm not doing to do that in this code since some
     * databases will not handle this case.
     *
     * <p>Method: 1.
     *
     * <p>Examples: ( escape ='!', multi='*', single='.' ) broadway* -> 'broadway%' broad_ay ->
     * 'broad_ay' broadway -> 'broadway'
     *
     * <p>broadway!* -> 'broadway*' (* has no significance and is escaped) can't -> 'can''t' ( '
     * escaped for SQL compliance)
     *
     * <p>NOTE: when the escapeSingleQuote parameter is false, this method will not convert ' to ''
     * (double single quote) and it is the caller's responsibility to ensure that the resulting
     * pattern is used safely in SQL queries.
     *
     * <p>NOTE: we dont handle "'" as a 'special' character because it would be too confusing to
     * have a special char as another special char. Using this will throw an error
     * (IllegalArgumentException).
     */
    public static String convertToSQL92(
            char escape,
            char multi,
            char single,
            boolean matchCase,
            String pattern,
            boolean escapeSingleQuote) {
        StringBuilder result = new StringBuilder(pattern.length() + 5);
            } else if (chr == '\'' && escapeSingleQuote) {
    @Deprecated
                pattern,
                true);
                "BroadWay%", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "BroadWay*", true));
                "broad#ay", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broad#ay", true));
                "broadway", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway", true));
                "broad_ay", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broad.ay", true));
                "broad.ay", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broad!.ay", true));
                "broa''dway",
                LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broa'dway", true));
                LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broa" + "''dway", true));
        Assert.assertEquals(
                "broa'dway",
                LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broa'dway", false));
        Assert.assertEquals(
                "broa''dway",
                LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broa" + "''dway", false));
                "broadway_", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway.", true));
                "broadway", LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway!", true));
                "broadway!",
                LikeFilterImpl.convertToSQL92('!', '*', '.', true, "broadway!!", true));
        MySQLFilterToSQL fts = new MySQLFilterToSQL(delegate.getUsePreciseSpatialOps());
        // see https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_backslash_escapes
        // NOTE: for future enhancement, do not escape backslashes when the NO_BACKSLASH_ESCAPES
        // mode is enabled since that would create an incorrect string in the SQL
        fts.setEscapeBackslash(true);
        return fts;
/*
 *    GeoTools - The Open Source Java GIS Toolkit
 *    http://geotools.org
 *
 *    (C) 2023, Open Source Geospatial Foundation (OSGeo)
 *
 *    This library is free software; you can redistribute it and/or
 *    modify it under the terms of the GNU Lesser General Public
 *    License as published by the Free Software Foundation;
 *    version 2.1 of the License.
 *
 *    This library is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *    Lesser General Public License for more details.
 */
package org.geotools.data.mysql;

import static org.junit.Assert.assertEquals;

import org.geotools.data.jdbc.SQLFilterTestSupport;
import org.geotools.factory.CommonFactoryFinder;
import org.geotools.feature.SchemaException;
import org.junit.Before;
import org.junit.Test;
import org.opengis.filter.FilterFactory2;
import org.opengis.filter.PropertyIsEqualTo;

public class MySQLFilterToSQLTest extends SQLFilterTestSupport {

    private static FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();

    private MySQLFilterToSQL filterToSql;

    @Override
    @Before
    public void setUp() throws SchemaException {
        filterToSql = (MySQLFilterToSQL) new MySQLDialectBasic(null).createFilterToSQL();
        filterToSql.setFeatureType(testSchema);
    }

    @Test
    public void testEncodeEqualToWithSpecialCharacters() throws Exception {
        PropertyIsEqualTo expr = ff.equals(ff.property("testString"), ff.literal("\\'FOO"));
        String actual = filterToSql.encodeToString(expr);
        assertEquals("WHERE testString = '\\\\''FOO'", actual);
    }
}
        return unwrapConnection(cx, OracleConnection.class);
        if (unit != null && !"".equals(unit.trim())) {
            unit = escapeLiteral(unit);
        } else out.write(",'distance=" + distance + "') = '" + within + "' ");
            String strJsonPath = escapeLiteral(String.join(".", pointers));
            String strExpected = escapeLiteral(expected.evaluate(null, String.class));
                    "json_exists(%s, '$%s?(@ == \"%s\")')", columnName, strJsonPath, strExpected);
    @Test
    public void testDWithinFilterWithUnitEscaping() throws Exception {
        Coordinate coordinate = new Coordinate();
        DWithin dwithin =
                ff.dwithin(
                        ff.property("GEOM"), ff.literal(gf.createPoint(coordinate)), 10.0, "'FOO");
        String encoded = encoder.encodeToString(dwithin);
        assertEquals(
                "WHERE SDO_WITHIN_DISTANCE(\"GEOM\",?,'distance=10.0 unit=''FOO') = 'TRUE' ",
                encoded);
    }

    @Test
    public void testFunctionJsonArrayContainsEscapingPointer() throws Exception {
        Function function =
                ff.function(
                        "jsonArrayContains",
                        ff.property("operations"),
                        ff.literal("/'FOO"),
                        ff.literal(1));
        Filter filter = ff.equals(function, ff.literal(true));
        String encoded = encoder.encodeToString(filter);
        assertEquals("WHERE json_exists(operations, '$.''FOO?(@ == \"1\")')", encoded);
    }

    @Test
    public void testFunctionJsonArrayContainsEscapingExpected() throws Exception {
        Function function =
                ff.function(
                        "jsonArrayContains",
                        ff.property("operations"),
                        ff.literal("/operations/parameters"),
                        ff.literal("'FOO"));
        Filter filter = ff.equals(function, ff.literal(true));
        String encoded = encoder.encodeToString(filter);
        assertEquals(
                "WHERE json_exists(operations, '$.operations.parameters?(@ == \"''FOO\")')",
                encoded);
    }

import org.geotools.jdbc.EscapeSql;
            out.write(" LIKE ('%' || ");
            end.accept(delegate, String.class);
            out.write("))");
            out.write(" LIKE (");
            start.accept(delegate, String.class);
            out.write(" || '%'))");
    public String buildJsonFromStrPointer(String[] pointers, int index, Expression expected) {
        if (pointers[index].isEmpty()) {
            return buildJsonFromStrPointer(pointers, index + 1, expected);
        } else if (index == pointers.length - 1) {
            String strExpected = escapeJsonLiteral(expected.evaluate(null, String.class));
            if (getBaseType(expected).isAssignableFrom(String.class)) {
                strExpected = '"' + strExpected + '"';
            return String.format("\"%s\": [%s]", pointers[index], strExpected);
        } else {
            String jsonPointers = buildJsonFromStrPointer(pointers, index + 1, expected);
            return String.format("\"%s\": { %s }", pointers[index], jsonPointers);
        }
        String[] strJsonPath = escapeJsonLiteral(jsonPath.getValue().toString()).split("/");
            column.accept(delegate, null);
            out.write("::jsonb @> '{ ");
            out.write(buildJsonFromStrPointer(strJsonPath, 0, expected));
            out.write(" }'::jsonb");
    private static String escapeJsonLiteral(String literal) {
        return EscapeSql.escapeLiteral(literal, true, true);
    }

import org.postgresql.jdbc.PgConnection;
    // checkStandardConformingStrings will set this based on database configuration
    boolean escapeBackslash = true;

    public boolean isEscapeBackslash() {
        return escapeBackslash;
    }

        checkStandardConformingStrings(cx);
        sql.setEscapeBackslash(escapeBackslash);
    /**
     * Determines whether or not to escape backslashes based on the PostgreSQL server's
     * standard_conforming_strings setting.
     */
    @SuppressWarnings("PMD.CloseResource")
    private void checkStandardConformingStrings(Connection conn) throws SQLException {
        Boolean escape = null;
        // first, try to determine the setting from a native connection object
        try {
            PgConnection bc = unwrapConnection(conn, PgConnection.class);
            escape = !bc.getStandardConformingStrings();
        } catch (SQLException e) {
            LOGGER.log(Level.FINER, "Unable to get native connection; falling back to query", e);
        }
        // otherwise, try to determine the setting from a database query
        if (escape == null) {
            Statement st = null;
            ResultSet rs = null;
            try {
                st = conn.createStatement();
                rs = st.executeQuery("SHOW standard_conforming_strings");
                escape = !rs.next() || !"on".equals(rs.getString(1));
            } catch (SQLException e) {
                LOGGER.warning(
                        "Unable to check standard_conforming_strings setting: " + e.getMessage());
            } finally {
                dataStore.closeSafe(rs);
                dataStore.closeSafe(st);
            }
        }
        // default to escape backslashes if both checks failed
        escapeBackslash = !Boolean.FALSE.equals(escape);
    }

        fts.setEscapeBackslash(delegate.isEscapeBackslash());
import org.geotools.data.Transaction;
        Connection cx = dataStore.getConnection(Transaction.AUTO_COMMIT);
        try {
            // creating a new connection will internally call
            // org.geotools.data.postgis.PostGISDialect.initializeConnection(Connection)
            // the following line is really just to prevent empty try block PMD violation
            LOGGER.finest("escaping backslashes: " + dialect.isEscapeBackslash());
        } finally {
            dataStore.closeSafe(cx);
        }
    @Test
    public void testFunctionStrEndsWithEscaping() throws Exception {
        filterToSql.setFeatureType(testSchema);
        Filter filter =
                ff.equals(
                        ff.literal(true),
                        ff.function("strEndsWith", ff.property("testString"), ff.literal("'FOO")));
        filterToSql.encode(filter);
        String sql = writer.toString();
        assertEquals("WHERE true = (testString LIKE ('%' || '''FOO'))", sql);
    }

    @Test
    public void testFunctionStrStartsWithEscaping() throws Exception {
        filterToSql.setFeatureType(testSchema);
        Filter filter =
                ff.equals(
                        ff.literal(true),
                        ff.function(
                                "strStartsWith", ff.property("testString"), ff.literal("'FOO")));
        filterToSql.encode(filter);
        String sql = writer.toString();
        assertEquals("WHERE true = (testString LIKE ('''FOO' || '%'))", sql);
    }

        assertEquals("OPERATIONS::jsonb @> '{ \"operations\": [\"OP1\"] }'::jsonb", sql);
        assertEquals("OPERATIONS::jsonb @> '{ \"operations\": [1] }'::jsonb", sql);
                "OPERATIONS::jsonb @> '{ \"operations\": { \"parameters\": [\"P1\"] } }'::jsonb",

    @Test
    public void testFunctionJsonArrayContainsEscapingPointer() throws Exception {
        filterToSql.setFeatureType(testSchema);
        Function pointer =
                ff.function(
                        "jsonArrayContains",
                        ff.property("OPERATIONS"),
                        ff.literal("/\"'FOO"),
                        ff.literal("OP1"));
        filterToSql.encode(pointer);
        String sql = writer.toString().trim();
        assertEquals("OPERATIONS::jsonb @> '{ \"\\\"''FOO\": [\"OP1\"] }'::jsonb", sql);
    }

    @Test
    public void testFunctionJsonArrayContainsEscapingExpected() throws Exception {
        filterToSql.setFeatureType(testSchema);
        Function pointer =
                ff.function(
                        "jsonArrayContains",
                        ff.property("OPERATIONS"),
                        ff.literal("/operations"),
                        ff.literal("\"'FOO"));
        filterToSql.encode(pointer);
        String sql = writer.toString().trim();
        assertEquals("OPERATIONS::jsonb @> '{ \"operations\": [\"\\\"''FOO\"] }'::jsonb", sql);
    }
/*
 *    GeoTools - The Open Source Java GIS Toolkit
 *    http://geotools.org
 *
 *    (C) 2023, Open Source Geospatial Foundation (OSGeo)
 *
 *    This library is free software; you can redistribute it and/or
 *    modify it under the terms of the GNU Lesser General Public
 *    License as published by the Free Software Foundation;
 *    version 2.1 of the License.
 *
 *    This library is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *    Lesser General Public License for more details.
 */
package org.geotools.data.postgis;

import static org.geotools.data.postgis.PostgisNGDataStoreFactory.PREPARED_STATEMENTS;
import static org.geotools.jdbc.JDBCDataStoreFactory.DATASOURCE;
import static org.hamcrest.CoreMatchers.instanceOf;
import static org.hamcrest.MatcherAssert.assertThat;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.mockito.Mockito.never;
import static org.mockito.Mockito.times;
import static org.mockito.Mockito.verify;
import static org.mockito.Mockito.when;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.geotools.jdbc.JDBCDataStore;
import org.geotools.jdbc.SQLDialect;
import org.geotools.util.Version;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.mockito.Mock;
import org.mockito.MockitoAnnotations;
import org.postgresql.jdbc.PgConnection;

public class PostgisNGDataStoreFactoryTest {

    @Mock private DataSource ds = null;

    @Mock private Connection conn = null;

    @Mock private PgConnection pgConn = null;

    @Mock private DatabaseMetaData md = null;

    @Mock private Statement st1 = null;

    @Mock private Statement st2 = null;

    @Mock private ResultSet rs1 = null;

    @Mock private ResultSet rs2 = null;

    private JDBCDataStore store = null;

    private AutoCloseable mocks = null;

    @Before
    public void setUp() throws Exception {
        this.mocks = MockitoAnnotations.openMocks(this);
        when(this.ds.getConnection()).thenReturn(this.conn);
        when(this.conn.getMetaData()).thenReturn(this.md);
        when(this.md.getDatabaseMajorVersion()).thenReturn(15);
        when(this.md.getDatabaseMinorVersion()).thenReturn(1);
        when(this.conn.createStatement()).thenReturn(this.st1, this.st2);
        when(this.st1.executeQuery("select PostGIS_Lib_Version()")).thenReturn(this.rs1);
        when(this.rs1.next()).thenReturn(true);
        when(this.rs1.getString(1)).thenReturn("3.3.2");
    }

    @After
    public void tearDown() throws Exception {
        if (this.mocks != null) {
            this.mocks.close();
            this.mocks = null;
        }
        if (this.store != null) {
            this.store.dispose();
        }
    }

    @Test
    public void testStandardConformingStringsOnFromConnection() throws Exception {
        verifyFilterToSqlSettings(false, false, false);
    }

    @Test
    public void testStandardConformingStringsOffFromConnection() throws Exception {
        verifyFilterToSqlSettings(true, false, false);
    }

    @Test
    public void testStandardConformingStringsOnFromQuery() throws Exception {
        verifyFilterToSqlSettings(false, false, true);
    }

    @Test
    public void testStandardConformingStringsOffFromQuery() throws Exception {
        verifyFilterToSqlSettings(true, false, true);
    }

    @Test
    public void testStandardConformingStringsOnWithPSFromConnection() throws Exception {
        verifyFilterToSqlSettings(false, true, false);
    }

    @Test
    public void testStandardConformingStringsOffWithPSFromConnection() throws Exception {
        verifyFilterToSqlSettings(true, true, false);
    }

    @Test
    public void testStandardConformingStringsOnWithPSFromQuery() throws Exception {
        verifyFilterToSqlSettings(false, true, true);
    }

    @Test
    public void testStandardConformingStringsOffWithPSFromQuery() throws Exception {
        verifyFilterToSqlSettings(true, true, true);
    }

    private void verifyFilterToSqlSettings(
            boolean escapeBackslash, boolean withPS, boolean withQuery) throws Exception {
        if (withQuery) {
            when(this.st2.executeQuery("SHOW standard_conforming_strings")).thenReturn(this.rs2);
            when(this.rs2.next()).thenReturn(true);
            when(this.rs2.getString(1)).thenReturn(escapeBackslash ? "off" : "on");
        } else {
            when(this.conn.isWrapperFor(PgConnection.class)).thenReturn(true);
            when(this.conn.unwrap(PgConnection.class)).thenReturn(this.pgConn);
            when(this.pgConn.getStandardConformingStrings()).thenReturn(!escapeBackslash);
        }
        Map<String, Object> params = new HashMap<>();
        params.put(DATASOURCE.key, this.ds);
        params.put(PREPARED_STATEMENTS.key, withPS);
        this.store = new PostgisNGDataStoreFactory().createDataStore(params);
        assertNotNull(this.store);
        SQLDialect dialect = this.store.getSQLDialect();
        assertThat(dialect, instanceOf(withPS ? PostGISPSDialect.class : PostGISDialect.class));
        PostGISDialect pgDialect =
                withPS ? ((PostGISPSDialect) dialect).getDelegate() : (PostGISDialect) dialect;
        assertEquals(new Version("15.1"), pgDialect.getPostgreSQLVersion(this.conn));
        assertEquals(new Version("3.3.2"), pgDialect.getVersion(this.conn));
        assertEquals(escapeBackslash, pgDialect.isEscapeBackslash());
        verify(this.conn, withQuery ? times(2) : times(1)).createStatement();
        verify(this.conn).close();
        verify(this.st1).close();
        verify(this.rs1).close();
        verify(this.st2, withQuery ? times(1) : never()).close();
        verify(this.rs2, withQuery ? times(1) : never()).close();
    }
}