Unfiltered SQL Injection in Geotools

@@ -45,6 +45,7 @@
 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;
@@ -230,6 +231,9 @@ public class FilterToSQL implements FilterVisitor, ExpressionVisitor {
     /** 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() {}
 
@@ -265,6 +269,16 @@ 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.
      *
@@ -529,7 +543,8 @@ public Object visit(PropertyIsLike filter, Object extraData) {
             literal += multi;
         }
 
-        String pattern = LikeFilterImpl.convertToSQL92(esc, multi, single, matchCase, literal);
+        String pattern =
+                LikeFilterImpl.convertToSQL92(esc, multi, single, matchCase, literal, false);
 
         try {
             if (!matchCase) {
@@ -539,13 +554,12 @@ public Object visit(PropertyIsLike filter, Object extraData) {
             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);
         }
@@ -1170,11 +1184,8 @@ public Object visit(Id filter, Object extraData) {
                         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 ");
@@ -1747,12 +1758,17 @@ protected void writeLiteral(Object literal) throws IOException {
                 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

@@ -16,6 +16,8 @@
  */
 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.
@@ -24,6 +26,28 @@
  * 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) {
 
         // ' --> ''

@@ -28,6 +28,7 @@
 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;
@@ -39,6 +40,8 @@
 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;
@@ -164,6 +167,41 @@ public abstract class SQLDialect {
                 }
             };
 
+    /**
+     * 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;
 
@@ -1415,4 +1453,52 @@ public boolean canGroupOnGeometry() {
     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());
+    }
 }

@@ -465,4 +465,18 @@ public void testEscapeName() {
         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());
+    }
 }

@@ -79,13 +79,49 @@ public class LikeFilterImpl extends AbstractFilter implements PropertyIsLike {
      * 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) {
@@ -96,7 +132,7 @@ public static String convertToSQL92(
                 result.append('_');
             } else if (chr == multi) {
                 result.append('%');
-            } else if (chr == '\'') {
+            } else if (chr == '\'' && escapeSingleQuote) {
                 result.append('\'');
                 result.append('\'');
             } else {
@@ -108,6 +144,7 @@ public static String convertToSQL92(
     }
 
     /** see convertToSQL92 */
+    @Deprecated
     public String getSQL92LikePattern() throws IllegalArgumentException {
         if (escape.length() != 1) {
             throw new IllegalArgumentException(
@@ -126,7 +163,8 @@ public String getSQL92LikePattern() throws IllegalArgumentException {
                 wildcardMulti.charAt(0),
                 wildcardSingle.charAt(0),
                 matchingCase,
-                pattern);
+                pattern,
+                true);
     }
 
     public void setWildCard(String wildCard) {

@@ -187,29 +187,37 @@ 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));
     }
 
     /**

@@ -246,7 +246,12 @@ public void applyLimitOffset(StringBuffer sql, int limit, int 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

@@ -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);
+    }
+}

@@ -26,7 +26,6 @@
 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;
@@ -41,8 +40,6 @@
 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;
@@ -84,34 +81,6 @@
  */
 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;
@@ -121,13 +90,6 @@ public boolean canUnwrap(Connection conn) {
     /** 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;
 
     /**
@@ -653,62 +615,8 @@ public void setGeometryValue(
     }
 
     /** 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() {

@@ -546,9 +546,10 @@ protected void doSDODistance(
         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 + "' ");
     }
 
     /**
@@ -625,10 +626,10 @@ public String jsonExists(Function function) {
 
         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());

@@ -161,6 +161,18 @@ public void testDWithinFilterWithUnit() throws Exception {
                 "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();
@@ -210,6 +222,34 @@ public void testJsonArrayContainsNestedObject() throws Exception {
                 "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

@@ -25,7 +25,6 @@
 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;
@@ -65,6 +64,7 @@
 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;
@@ -569,30 +569,18 @@ public boolean visitFunction(Function function, Object extraData) throws IOExcep
 
             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);
@@ -703,45 +691,42 @@ private void encodeJsonPointer(Function jsonPointer, Object extraData) throws IO
         }
     }
 
-    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) {

@@ -82,6 +82,7 @@
 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 {
 
@@ -211,6 +212,9 @@ public PostGISDialect(JDBCDataStore dataStore) {
 
     boolean topologyPreserved = false;
 
+    // checkStandardConformingStrings will set this based on database configuration
+    boolean escapeBackslash = true;
+
     Version version, pgsqlVersion;
 
     public boolean isLooseBBOXEnabled() {
@@ -250,6 +254,10 @@ 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)
@@ -283,6 +291,7 @@ public void initializeConnection(Connection cx) throws SQLException {
         super.initializeConnection(cx);
         getPostgreSQLVersion(cx);
         getVersion(cx);
+        checkStandardConformingStrings(cx);
     }
 
     @Override
@@ -1384,6 +1393,7 @@ public FilterToSQL createFilterToSQL() {
         sql.setLooseBBOXEnabled(looseBBOXEnabled);
         sql.setEncodeBBOXFilterAsEnvelope(encodeBBOXFilterAsEnvelope);
         sql.setFunctionEncodingEnabled(functionEncodingEnabled);
+        sql.setEscapeBackslash(escapeBackslash);
         return sql;
     }
 
@@ -1517,6 +1527,40 @@ public Version getPostgreSQLVersion(Connection conn) throws SQLException {
         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;

@@ -29,6 +29,7 @@
 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;
@@ -259,6 +260,15 @@ protected JDBCDataStore createDataStoreInternal(JDBCDataStore dataStore, Map<Str
         }
         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;
     }
 

@@ -263,6 +263,7 @@ public PreparedFilterToSQL createPreparedFilterToSQL() {
         fts.setFunctionEncodingEnabled(delegate.isFunctionEncodingEnabled());
         fts.setLooseBBOXEnabled(delegate.isLooseBBOXEnabled());
         fts.setEncodeBBOXFilterAsEnvelope(delegate.isEncodeBBOXFilterAsEnvelope());
+        fts.setEscapeBackslash(delegate.isEscapeBackslash());
         return fts;
     }
 

@@ -235,6 +235,31 @@ public void testEncodeEqualToArraysAll() throws Exception {
         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);
@@ -299,7 +324,7 @@ public void testFunctionJsonArrayContains() throws Exception {
                         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
@@ -313,7 +338,7 @@ public void testFunctionJsonArrayContainsNumber() throws Exception {
                         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
@@ -328,7 +353,35 @@ public void testNestedObjectJsonArrayContains() throws Exception {
         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);
+    }
 }

@@ -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();
+    }
+}