CVE-2023-25158 (Java, published 2023-02-21T21:15Z)
Description: 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.
Severity: nan (CVSS v3 base score: 9.8)
Weakness: CWE-89 \u2014 Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
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.
Repository: https://github.com/geotools/geotools
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 message: Merge pull request from GHSA-99c3-qc2q-p94m



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