Unfiltered SQL Injection Vulnerabilities in Geoserver

@@ -103,6 +103,7 @@
 import org.geoserver.config.impl.CoverageAccessInfoImpl;
 import org.geoserver.config.impl.GeoServerInfoImpl;
 import org.geoserver.config.impl.JAIInfoImpl;
+import org.geoserver.jdbcloader.JDBCLoaderProperties;
 import org.geoserver.ows.util.OwsUtils;
 import org.geoserver.platform.ExtensionPriority;
 import org.geoserver.platform.resource.Resource;
@@ -160,6 +161,8 @@ public class ConfigDatabase implements ApplicationContextAware {
 
     private Dialect dialect;
 
+    private JDBCLoaderProperties properties;
+
     private DataSource dataSource;
 
     private DbMappings dbMappings;
@@ -198,15 +201,19 @@ protected ConfigDatabase() {
         //
     }
 
-    public ConfigDatabase(final DataSource dataSource, final XStreamInfoSerialBinding binding) {
-        this(dataSource, binding, null);
+    public ConfigDatabase(
+            JDBCLoaderProperties properties,
+            DataSource dataSource,
+            XStreamInfoSerialBinding binding) {
+        this(properties, dataSource, binding, null);
     }
 
     public ConfigDatabase(
+            JDBCLoaderProperties properties,
             final DataSource dataSource,
             final XStreamInfoSerialBinding binding,
             CacheProvider cacheProvider) {
-
+        this.properties = properties;
         this.binding = binding;
         this.template = new NamedParameterJdbcTemplate(dataSource);
         // cannot use dataSource at this point due to spring context config hack
@@ -227,7 +234,7 @@ public ConfigDatabase(
 
     private Dialect dialect() {
         if (dialect == null) {
-            this.dialect = Dialect.detect(dataSource);
+            this.dialect = Dialect.detect(dataSource, properties.isDebugMode());
         }
         return dialect;
     }
@@ -296,7 +303,7 @@ public <T extends CatalogInfo> int count(final Class<T> of, final Filter filter)
 
         QueryBuilder<T> sqlBuilder = QueryBuilder.forCount(dialect, of, dbMappings).filter(filter);
 
-        final StringBuilder sql = sqlBuilder.build();
+        final String sql = sqlBuilder.build();
         final Filter unsupportedFilter = sqlBuilder.getUnsupportedFilter();
         final boolean fullySupported = Filter.INCLUDE.equals(unsupportedFilter);
         if (LOGGER.isLoggable(Level.FINER)) {
@@ -309,7 +316,7 @@ public <T extends CatalogInfo> int count(final Class<T> of, final Filter filter)
             final Map<String, Object> namedParameters = sqlBuilder.getNamedParameters();
             logStatement(sql, namedParameters);
 
-            count = template.queryForObject(sql.toString(), namedParameters, Integer.class);
+            count = template.queryForObject(sql, namedParameters, Integer.class);
         } else {
             LOGGER.fine(
                     "Filter is not fully supported, doing scan of supported part to return the number of matches");
@@ -363,7 +370,7 @@ public <T extends Info> CloseableIterator<T> query(
                         .offset(offset)
                         .limit(limit)
                         .sortOrder(sortOrder);
-        final StringBuilder sql = sqlBuilder.build();
+        final String sql = sqlBuilder.build();
 
         List<String> ids = null;
 
@@ -406,7 +413,7 @@ public <T extends Info> CloseableIterator<T> query(
             // with rownum in the 2nd - queryForList will throw an exception
             ids =
                     template.query(
-                            sql.toString(),
+                            sql,
                             namedParameters,
                             new RowMapper<String>() {
                                 @Override
@@ -468,7 +475,7 @@ public <T extends Info> CloseableIterator<String> queryIds(
 
         QueryBuilder<T> sqlBuilder = QueryBuilder.forIds(dialect, of, dbMappings).filter(filter);
 
-        final StringBuilder sql = sqlBuilder.build();
+        final String sql = sqlBuilder.build();
         final Map<String, Object> namedParameters = sqlBuilder.getNamedParameters();
         final Filter unsupportedFilter = sqlBuilder.getUnsupportedFilter();
         final boolean fullySupported = Filter.INCLUDE.equals(unsupportedFilter);
@@ -485,7 +492,7 @@ public <T extends Info> CloseableIterator<String> queryIds(
         // with rownum in the 2nd - queryForList will throw an exception
         List<String> ids =
                 template.query(
-                        sql.toString(),
+                        sql,
                         namedParameters,
                         new RowMapper<String>() {
                             @Override

@@ -8,12 +8,19 @@
 import com.google.common.base.Joiner;
 import java.sql.Connection;
 import java.sql.SQLException;
+import java.util.regex.Pattern;
 import javax.annotation.Nullable;
 import javax.sql.DataSource;
 
 public class Dialect {
 
-    public static Dialect detect(DataSource dataSource) {
+    // see https://github.com/hibernate/hibernate-orm/commit/59fede7acaaa1579b561407aefa582311f7ebe78
+    private static final Pattern ESCAPE_CLOSING_COMMENT_PATTERN = Pattern.compile("\\*/");
+    private static final Pattern ESCAPE_OPENING_COMMENT_PATTERN = Pattern.compile("/\\*");
+
+    private boolean debugMode;
+
+    public static Dialect detect(DataSource dataSource, boolean debugMode) {
         Dialect dialect;
         try {
             Connection conn = dataSource.getConnection();
@@ -27,9 +34,46 @@ public static Dialect detect(DataSource dataSource) {
         } catch (SQLException ex) {
             throw new RuntimeException(ex);
         }
+        dialect.setDebugMode(debugMode);
         return dialect;
     }
 
+    public boolean isDebugMode() {
+        return debugMode;
+    }
+
+    public void setDebugMode(boolean debugMode) {
+        this.debugMode = debugMode;
+    }
+
+    /** Escapes the contents of the SQL comment to prevent SQL injection. */
+    public String escapeComment(String comment) {
+        String escaped = ESCAPE_CLOSING_COMMENT_PATTERN.matcher(comment).replaceAll("*\\\\/");
+        return ESCAPE_OPENING_COMMENT_PATTERN.matcher(escaped).replaceAll("/\\\\*");
+    }
+
+    /** Appends the objects to the SQL in a comment if debug mode is enabled. */
+    public StringBuilder appendComment(StringBuilder sql, Object... objects) {
+        if (!debugMode) {
+            return sql;
+        }
+        sql.append(" /* ");
+        for (Object object : objects) {
+            sql.append(escapeComment(String.valueOf(object)));
+        }
+        return sql.append(" */\n");
+    }
+
+    /** Appends the objects to the SQL in an comment if debug mode is enabled. */
+    public StringBuilder appendComment(Object sql, Object... objects) {
+        return appendComment((StringBuilder) sql, objects);
+    }
+
+    /** Appends one of the strings to the SQL depending on whether debug mode is enabled. */
+    public StringBuilder appendIfDebug(StringBuilder sql, String ifEnabled, String ifDisabled) {
+        return sql.append(debugMode ? ifEnabled : ifDisabled);
+    }
+
     public void applyOffsetLimit(
             StringBuilder sql, @Nullable Integer offset, @Nullable Integer limit) {
         // some db's require limit to be present of offset is

@@ -19,6 +19,7 @@
 import org.geotools.filter.Capabilities;
 import org.geotools.filter.LikeFilterImpl;
 import org.opengis.filter.And;
+import org.opengis.filter.BinaryLogicOperator;
 import org.opengis.filter.ExcludeFilter;
 import org.opengis.filter.Filter;
 import org.opengis.filter.FilterFactory;
@@ -94,13 +95,16 @@ public class FilterToCatalogSQL implements FilterVisitor, ExpressionVisitor {
         CAPABILITIES = builder.getContents();
     }
 
+    private final Dialect dialect;
+
     private final Class<?> queryType;
 
     private final DbMappings dbMappings;
 
     private final Map<String, Object> namedParams = new LinkedHashMap<>();
 
-    public FilterToCatalogSQL(Class<?> queryType, DbMappings dbMappings) {
+    public FilterToCatalogSQL(Dialect dialect, Class<?> queryType, DbMappings dbMappings) {
+        this.dialect = dialect;
         this.queryType = queryType;
         this.dbMappings = dbMappings;
         List<Integer> concreteQueryTypes = dbMappings.getConcreteQueryTypes(queryType);
@@ -132,8 +136,7 @@ public Object visitNullFilter(Object extraData) {
      */
     @Override
     public Object visit(ExcludeFilter filter, Object extraData) {
-        append(extraData, "0 = 1 /* EXCLUDE */\n");
-        return extraData;
+        return dialect.appendComment(append(extraData, "0 = 1"), "EXCLUDE");
     }
 
     /**
@@ -142,8 +145,7 @@ public Object visit(ExcludeFilter filter, Object extraData) {
      */
     @Override
     public Object visit(IncludeFilter filter, Object extraData) {
-        append(extraData, "1 = 1 /* INCLUDE */\n");
-        return extraData;
+        return dialect.appendComment(append(extraData, "1 = 1"), "INCLUDE");
     }
 
     /**
@@ -171,81 +173,57 @@ public Object visit(PropertyIsEqualTo filter, Object extraData) {
             String valueCol1 = matchingCase ? "o1.value" : "UPPER(o1.value)";
             String valueCol2 = matchingCase ? "o2.value" : "UPPER(o2.value)";
 
-            StringBuilder builder;
-
             switch (matchAction) {
                     // respect matchaction
                 case ALL: // all = another value for the property may not occur
-                    builder =
-                            append(
-                                    extraData,
-                                    "oid NOT IN (SELECT o1.oid FROM object_property o1, object_property o2 WHERE o1.oid = o2.oid ",
-                                    "AND o1.property_type IN (:",
-                                    propertyTypesParam1,
-                                    ") ",
-                                    "AND o2.property_type IN (:",
-                                    propertyTypesParam2,
-                                    ") ",
-                                    "AND ",
-                                    valueCol1,
-                                    " != ",
-                                    valueCol2,
-                                    ") /* ",
-                                    filter.toString(),
-                                    " */\n");
+                    append(
+                            extraData,
+                            "oid NOT IN (SELECT o1.oid FROM object_property o1, object_property o2 WHERE o1.oid = o2.oid ",
+                            "AND o1.property_type IN (:",
+                            propertyTypesParam1,
+                            ") AND o2.property_type IN (:",
+                            propertyTypesParam2,
+                            ") AND ",
+                            valueCol1,
+                            " != ",
+                            valueCol2,
+                            ")");
                     break;
                 case ANY: // any = the value for the property must occur at least once
-                    builder =
-                            append(
-                                    extraData,
-                                    "oid IN (SELECT o1.oid FROM object_property o1, object_property o2 WHERE o1.oid = o2.oid ",
-                                    "AND o1.property_type IN (:",
-                                    propertyTypesParam1,
-                                    ") ",
-                                    "AND o2.property_type IN (:",
-                                    propertyTypesParam2,
-                                    ") ",
-                                    "AND ",
-                                    valueCol1,
-                                    " = ",
-                                    valueCol2,
-                                    ") /* ",
-                                    filter.toString(),
-                                    " */\n");
+                    append(
+                            extraData,
+                            "oid IN (SELECT o1.oid FROM object_property o1, object_property o2 WHERE o1.oid = o2.oid ",
+                            "AND o1.property_type IN (:",
+                            propertyTypesParam1,
+                            ") AND o2.property_type IN (:",
+                            propertyTypesParam2,
+                            ") AND ",
+                            valueCol1,
+                            " = ",
+                            valueCol2,
+                            ")");
                     break;
                 case ONE: // one = the value for the property must occur exactly once
-                    builder =
-                            append(
-                                    extraData,
-                                    "oid IN (SELECT o1.oid FROM object_property o1, object_property o2 WHERE o1.oid = o2.oid ",
-                                    "AND o1.property_type IN (:",
-                                    propertyTypesParam1,
-                                    ") ",
-                                    "AND o2.property_type IN (:",
-                                    propertyTypesParam2,
-                                    ") ",
-                                    "AND ",
-                                    valueCol1,
-                                    " = ",
-                                    valueCol2,
-                                    " GROUP BY (oid) HAVING COUNT(oid) = 1) /* ",
-                                    filter.toString(),
-                                    "/* ",
-                                    filter.toString(),
-                                    " */\n");
+                    append(
+                            extraData,
+                            "oid IN (SELECT o1.oid FROM object_property o1, object_property o2 WHERE o1.oid = o2.oid ",
+                            "AND o1.property_type IN (:",
+                            propertyTypesParam1,
+                            ") AND o2.property_type IN (:",
+                            propertyTypesParam2,
+                            ") AND ",
+                            valueCol1,
+                            " = ",
+                            valueCol2,
+                            " GROUP BY (oid) HAVING COUNT(oid) = 1)");
                     break;
                 default:
                     throw new IllegalArgumentException("MatchAction: " + matchAction);
             }
-
-            return builder;
-
         } else {
 
             if (filter.getExpression1() instanceof IsInstanceOf) {
-                StringBuilder builder =
-                        append(extraData, handleInstanceOf((IsInstanceOf) filter.getExpression1()));
-                return builder;
+                return handleInstanceOf((IsInstanceOf) filter.getExpression1(), extraData);
             }
 
             // comparing a literal with a field
@@ -271,74 +249,60 @@ public Object visit(PropertyIsEqualTo filter, Object extraData) {
                 expectedValue = expectedValue.toUpperCase();
             }
             String valueParam = newParam("value", expectedValue);
-
-            StringBuilder builder;
             String valueCol = matchingCase ? "value" : "UPPER(value)";
 
             switch (matchAction) {
                     // respect match action
                 case ALL: // all = another value for the property may not occur
-                    builder =
-                            append(
-                                    extraData,
-                                    "oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
-                                    propertyTypesParam,
-                                    ") AND ",
-                                    valueCol,
-                                    " != :",
-                                    valueParam,
-                                    ") /* ",
-                                    filter.toString(),
-                                    " */\n");
+                    append(
+                            extraData,
+                            "oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                            propertyTypesParam,
+                            ") AND ",
+                            valueCol,
+                            " != :",
+                            valueParam,
+                            ")");
                     break;
                 case ANY: // any = the value for the property must occur at least once
-                    builder =
-                            append(
-                                    extraData,
-                                    "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
-                                    propertyTypesParam,
-                                    ") AND ",
-                                    valueCol,
-                                    " = :",
-                                    valueParam,
-                                    ") /* ",
-                                    filter.toString(),
-                                    " */\n");
+                    append(
+                            extraData,
+                            "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                            propertyTypesParam,
+                            ") AND ",
+                            valueCol,
+                            " = :",
+                            valueParam,
+                            ")");
                     break;
                 case ONE: // one = the value for the property must occur exactly once
-                    builder =
-                            append(
-                                    extraData,
-                                    "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
-                                    propertyTypesParam,
-                                    ") AND ",
-                                    valueCol,
-                                    " = :",
-                                    valueParam,
-                                    " GROUP BY (oid) HAVING COUNT(oid) = 1) /* ",
-                                    filter.toString(),
-                                    " */\n");
+                    append(
+                            extraData,
+                            "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                            propertyTypesParam,
+                            ") AND ",
+                            valueCol,
+                            " = :",
+                            valueParam,
+                            " GROUP BY (oid) HAVING COUNT(oid) = 1)");
                     break;
                 default:
                     throw new IllegalArgumentException("MatchAction: " + matchAction);
             }
-
-            return builder;
         }
+        return dialect.appendComment(extraData, filter);
     }
 
-    private String handleInstanceOf(IsInstanceOf instanceOf) {
+    private Object handleInstanceOf(IsInstanceOf instanceOf, Object extraData) {
         Expression expression1 = instanceOf.getParameters().get(0);
 
         Class<?> clazz = expression1.evaluate(null, Class.class);
-
-        if (clazz == null || dbMappings.getTypeId(clazz) == null) {
-            return "0 = 1 /* EXCLUDE */\n";
-        }
-
         Integer typeId = dbMappings.getTypeId(clazz);
-
-        return "type_id = " + typeId + " /* isInstanceOf " + clazz.getName() + " */\n";
+        if (typeId == null) {
+            return visit(Filter.EXCLUDE, extraData);
+        }
+        append(extraData, "type_id = ", typeId.toString());
+        return dialect.appendComment(extraData, "isInstanceOf ", clazz.getName());
     }
 
     /**
@@ -348,7 +312,6 @@ private String handleInstanceOf(IsInstanceOf instanceOf) {
     @Override
     public Object visit(PropertyIsLike filter, Object extraData) {
         final PropertyName expression1 = (PropertyName) filter.getExpression();
-        // TODO: check for indexed property name
 
         final String propertyTypesParam = propertyTypesParam(expression1);
 
@@ -360,63 +323,51 @@ public Object visit(PropertyIsLike filter, Object extraData) {
         final boolean matchCase = filter.isMatchingCase();
 
         final String pattern =
-                LikeFilterImpl.convertToSQL92(esc, multi, single, matchCase, literal);
+                LikeFilterImpl.convertToSQL92(esc, multi, single, matchCase, literal, false);
 
         // respect match case
+        String valueParam = newParam("value", pattern);
         String valueCol = matchCase ? "value" : "UPPER(value)";
 
-        StringBuilder builder;
-
         switch (matchAction) {
                 // respect match action
             case ALL: // all = another value for the property may not occur
-                builder =
-                        append(
-                                extraData,
-                                "oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
-                                propertyTypesParam,
-                                ") AND ",
-                                valueCol,
-                                " NOT LIKE '",
-                                pattern,
-                                "') /* ",
-                                filter.toString(),
-                                " */\n");
+                append(
+                        extraData,
+                        "oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                        propertyTypesParam,
+                        ") AND ",
+                        valueCol,
+                        " NOT LIKE :",
+                        valueParam,
+                        ")");
                 break;
             case ANY: // any = the value for the property must occur at least once
-                builder =
-                        append(
-                                extraData,
-                                "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
-                                propertyTypesParam,
-                                ") AND ",
-                                valueCol,
-                                " LIKE '",
-                                pattern,
-                                "') /* ",
-                                filter.toString(),
-                                " */\n");
+                append(
+                        extraData,
+                        "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                        propertyTypesParam,
+                        ") AND ",
+                        valueCol,
+                        " LIKE :",
+                        valueParam,
+                        ")");
                 break;
             case ONE: // one = the value for the property must occur exactly once
-                builder =
-                        append(
-                                extraData,
-                                "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
-                                propertyTypesParam,
-                                ") AND ",
-                                valueCol,
-                                " LIKE '",
-                                pattern,
-                                "' ",
-                                "GROUP BY (oid) HAVING COUNT(oid) = 1) /* ",
-                                filter.toString(),
-                                " */\n");
+                append(
+                        extraData,
+                        "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                        propertyTypesParam,
+                        ") AND ",
+                        valueCol,
+                        " LIKE :",
+                        valueParam,
+                        " GROUP BY (oid) HAVING COUNT(oid) = 1)");
                 break;
             default:
                 throw new IllegalArgumentException("MatchAction: " + matchAction);
         }
-
-        return builder;
+        return dialect.appendComment(extraData, filter);
     }
 
     private String propertyTypesParam(final PropertyName property) {
@@ -477,40 +428,30 @@ public Object visit(PropertyIsNotEqualTo filter, Object extraData) {
     /** @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.And, java.lang.Object) */
     @Override
     public Object visit(And filter, Object extraData) {
-        StringBuilder sql = (StringBuilder) extraData;
-
-        List<Filter> children = filter.getChildren();
-        checkArgument(children.size() > 0);
-        sql.append("(\n    ");
-
-        for (Iterator<Filter> it = children.iterator(); it.hasNext(); ) {
-            Filter child = it.next();
-            sql = (StringBuilder) child.accept(this, sql);
-            if (it.hasNext()) {
-                sql = append(extraData, "    AND\n    ");
-            }
-        }
-        sql.append(")");
-        return sql;
+        return visit(filter, "AND", extraData);
     }
 
     /** @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.Or, java.lang.Object) */
     @Override
     public Object visit(Or filter, Object extraData) {
-        StringBuilder sql = (StringBuilder) extraData;
+        return visit(filter, "OR", extraData);
+    }
 
+    protected Object visit(BinaryLogicOperator filter, String type, Object extraData) {
+        StringBuilder sql = (StringBuilder) extraData;
         List<Filter> children = filter.getChildren();
         checkArgument(children.size() > 0);
-        sql.append("(");
+        sql.append('(');
+        dialect.appendIfDebug(sql, "\n    ", "");
         for (Iterator<Filter> it = children.iterator(); it.hasNext(); ) {
-            Filter child = it.next();
-            sql = (StringBuilder) child.accept(this, sql);
+            it.next().accept(this, sql);
             if (it.hasNext()) {
-                sql = append(extraData, "    OR\n    ");
+                dialect.appendIfDebug(sql, "    ", " ");
+                sql.append(type);
+                dialect.appendIfDebug(sql, "\n    ", " ");
             }
         }
-        sql.append(")");
-        return sql;
+        return sql.append(')');
     }
 
     /** @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.Id, java.lang.Object) */
@@ -523,8 +464,13 @@ public Object visit(Id filter, Object extraData) {
     /** @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.Not, java.lang.Object) */
     @Override
     public Object visit(Not filter, Object extraData) {
-        filter.getFilter().accept(this, append(extraData, "NOT ("));
-        return append(extraData, ")");
+        Filter child = filter.getFilter();
+        // these filter types are already enclosed in parentheses
+        boolean extraParens =
+                !(child instanceof And || child instanceof Or || child instanceof PropertyIsNull);
+        append(extraData, "NOT ", extraParens ? "(" : "");
+        child.accept(this, extraData);
+        return append(extraData, extraParens ? ")" : "");
     }
 
     /**
@@ -587,17 +533,14 @@ public Object visit(PropertyIsNull filter, Object extraData) {
         final PropertyName propertyName = (PropertyName) filter.getExpression();
         final String propertyTypesParam = propertyTypesParam(propertyName);
 
-        StringBuilder builder =
-                append(
-                        extraData,
-                        "(oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
-                        propertyTypesParam,
-                        ") AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
-                                + propertyTypesParam
-                                + "))) /* ",
-                        filter.toString(),
-                        " */\n");
-        return builder;
+        append(
+                extraData,
+                "(oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                propertyTypesParam,
+                ") AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                propertyTypesParam,
+                ")))");
+        return dialect.appendComment(extraData, filter);
     }
 
     /**
@@ -609,15 +552,12 @@ public Object visit(PropertyIsNil filter, Object extraData) {
         final PropertyName propertyName = (PropertyName) filter.getExpression();
         final String propertyTypesParam = propertyTypesParam(propertyName);
 
-        StringBuilder builder =
-                append(
-                        extraData,
-                        "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
-                        propertyTypesParam,
-                        ") AND value IS NULL) /* ",
-                        filter.toString(),
-                        " */\n");
-        return builder;
+        append(
+                extraData,
+                "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                propertyTypesParam,
+                ") AND value IS NULL)");
+        return dialect.appendComment(extraData, filter);
     }
 
     /**

@@ -21,12 +21,17 @@ public void applyOffsetLimit(
             limit += 1; // not zero-based
         }
         if (offset != null && limit != null) {
-            sql.insert(0, "SELECT * FROM (SELECT query.*, rownum rnum FROM (\n");
-            sql.append(") query\n");
+            sql.insert(
+                    0,
+                    "SELECT * FROM (SELECT query.*, rownum rnum FROM ("
+                            + (isDebugMode() ? "\n" : ""));
+            sql.append(") query");
+            appendIfDebug(sql, "\n", " ");
             if (limit != Integer.MAX_VALUE) {
                 limit = offset + limit;
             }
-            sql.append("WHERE rownum <= ").append(limit).append(")\n");
+            sql.append("WHERE rownum <= ").append(limit).append(")");
+            appendIfDebug(sql, "\n", " ");
             sql.append("WHERE rnum > ").append(offset);
         }
     }

@@ -128,7 +128,7 @@ public QueryBuilder<T> filter(Filter filter) {
         return this;
     }
 
-    private void querySortBy(StringBuilder query, StringBuilder whereClause, SortBy[] orders) {
+    private void querySortBy(StringBuilder query, String whereClause, SortBy[] orders) {
 
         /*
          * Start with the oid and id from the object table selecting for type and the filter.
@@ -144,15 +144,15 @@ private void querySortBy(StringBuilder query, StringBuilder whereClause, SortBy[
 
         int i = 0;
 
-        query.append("SELECT id FROM ");
-
-        query.append("\n    (SELECT oid, id FROM object WHERE ");
-        if (queryType != null) {
-            query.append("type_id IN (:types) /* ")
-                    .append(queryType.getCanonicalName())
-                    .append(" */\n      AND ");
+        query.append("SELECT id FROM");
+        dialect.appendIfDebug(query, "\n    ", " ");
+        query.append("(SELECT oid, id FROM object WHERE type_id IN (:types)");
+        dialect.appendComment(query, queryType.getName());
+        if (whereClause != null) {
+            dialect.appendIfDebug(query, "      ", " ");
+            query.append("AND ").append(whereClause);
         }
-        query.append(whereClause).append(") object");
+        query.append(") object");
 
         for (SortBy order : orders) {
             final String sortProperty = order.getPropertyName().getPropertyName();
@@ -167,35 +167,36 @@ private void querySortBy(StringBuilder query, StringBuilder whereClause, SortBy[
             Map<String, Object> namedParameters = getNamedParameters();
             namedParameters.put(propertyParamName, sortPropertyTypeIds);
 
-            query.append("\n  LEFT JOIN");
-            query.append("\n    (SELECT oid, value ")
-                    .append(attributeName)
-                    .append(" FROM \n      object_property WHERE property_type IN (:")
+            dialect.appendIfDebug(query, "\n  ", " ");
+            query.append("LEFT JOIN");
+            dialect.appendIfDebug(query, "\n    ", " ");
+            query.append("(SELECT oid, value ").append(attributeName).append(" FROM");
+            dialect.appendIfDebug(query, "\n      ", " ");
+            query.append("object_property WHERE property_type IN (:")
                     .append(propertyParamName)
                     .append(")) ")
                     .append(subSelectName);
-
-            query.append(" /* ")
-                    .append(order.getPropertyName().getPropertyName())
-                    .append(" ")
-                    .append(ascDesc(order))
-                    .append(" */");
-
-            query.append("\n  ON object.oid = ").append(subSelectName).append(".oid");
+            dialect.appendComment(
+                    query, order.getPropertyName().getPropertyName(), " ", ascDesc(order));
+            dialect.appendIfDebug(query, "  ", " ");
+            query.append("ON object.oid = ").append(subSelectName).append(".oid");
             // Update the ORDER BY clause to be added later
             if (i > 0) orderBy.append(", ");
             orderBy.append(attributeName).append(" ").append(ascDesc(order));
 
             i++;
         }
-
-        query.append("\n  ").append(orderBy);
+        dialect.appendIfDebug(query, "\n  ", " ");
+        query.append(orderBy);
     }
 
-    private StringBuilder buildWhereClause() {
+    private String buildWhereClause() {
+        this.predicateBuilder =
+                new FilterToCatalogSQL(this.dialect, this.queryType, this.dbMappings);
+        if (Filter.INCLUDE.equals(this.originalFilter)) {
+            return null;
+        }
         final SimplifyingFilterVisitor filterSimplifier = new SimplifyingFilterVisitor();
-
-        this.predicateBuilder = new FilterToCatalogSQL(this.queryType, this.dbMappings);
         Capabilities fcs = new Capabilities(FilterToCatalogSQL.CAPABILITIES);
         FeatureType parent = null;
         // use this to instruct the filter splitter which filters can be encoded depending on
@@ -238,36 +239,42 @@ public Filter getDeleteFilter() {
                 (Filter) supported.accept(new LiteralDemultiplyingFilterVisitor(), null);
         this.supportedFilter = (Filter) demultipliedFilter.accept(filterSimplifier, null);
         this.unsupportedFilter = (Filter) unsupported.accept(filterSimplifier, null);
-
+        if (Filter.INCLUDE.equals(this.supportedFilter)) {
+            return null;
+        }
         StringBuilder whereClause = new StringBuilder();
-        return (StringBuilder) this.supportedFilter.accept(predicateBuilder, whereClause);
+        return this.supportedFilter.accept(predicateBuilder, whereClause).toString();
     }
 
-    public StringBuilder build() {
+    public String build() {
 
-        StringBuilder whereClause = buildWhereClause();
+        String whereClause = buildWhereClause();
 
         StringBuilder query = new StringBuilder();
         if (isCountQuery) {
-            if (Filter.INCLUDE.equals(this.originalFilter)) {
-                query.append("SELECT COUNT(oid) FROM object WHERE type_id IN (:types)");
-            } else {
-                query.append("SELECT COUNT(oid) FROM object WHERE type_id IN (:types) AND (\n");
-                query.append(whereClause).append(")");
+            query.append("SELECT COUNT(oid) FROM object WHERE type_id IN (:types)");
+            dialect.appendComment(query, queryType.getName());
+            if (whereClause != null) {
+                dialect.appendIfDebug(query, "", " ");
+                query.append("AND ").append(whereClause);
             }
         } else {
-            SortBy[] orders = this.sortOrder;
-            if (orders == null) {
-                query.append("SELECT id FROM object WHERE type_id IN (:types) AND (\n");
-                query.append(whereClause).append(")");
-                query.append(" ORDER BY oid");
+            if (sortOrder != null) {
+                querySortBy(query, whereClause, sortOrder);
             } else {
-                querySortBy(query, whereClause, orders);
+                query.append("SELECT id FROM object WHERE type_id IN (:types)");
+                dialect.appendComment(query, queryType.getName());
+                dialect.appendIfDebug(query, "", " ");
+                if (whereClause != null) {
+                    query.append("AND ").append(whereClause);
+                    dialect.appendIfDebug(query, whereClause.endsWith("\n") ? "" : " ", " ");
+                }
+                query.append("ORDER BY oid");
             }
             applyOffsetLimit(query);
         }
 
-        return query;
+        return query.toString().trim();
     }
 
     /** When the query was built, were the offset and limit included. */

@@ -107,4 +107,12 @@ public String getDatasourceId() {
     public void setDatasourceId(String datasourceId) {
         this.datasourceId = datasourceId;
     }
+
+    public boolean isDebugMode() {
+        return Boolean.parseBoolean(getProperty("debugMode", "false"));
+    }
+
+    public void setDebugMode(boolean debugMode) {
+        setProperty("debugMode", String.valueOf(debugMode));
+    }
 }

@@ -33,6 +33,7 @@
   </bean>
 
   <bean id="JDBCConfigDB" class="org.geoserver.jdbcconfig.internal.ConfigDatabase">
+    <constructor-arg ref="jdbcConfigProperties" />
     <constructor-arg ref="jdbcConfigDataSource" />
     <constructor-arg ref="jdbcPersistenceBinding" />
   </bean>

@@ -10,6 +10,11 @@ initScript=jdbcconfig/scripts/initdb.postgres.sql
 # flag controlling whether the existing (non-db) catalog should be imported
 import=true
 
+# Flag controlling whether to add comments and some pretty-printing to SQL queries to
+# assist with debugging.  Setting this to false may allow the module to better take
+# advantage of prepared statement caching.
+debugMode=false
+
 # database connection info
 jdbcUrl=jdbc:postgresql://localhost:5432/gscatalog
 driverClassName=org.postgresql.Driver

@@ -14,6 +14,11 @@ initdb=true
 initScript=jdbcconfig/scripts/initdb.h2.sql
 import=true
 
+# Flag controlling whether to add comments and some pretty-printing to SQL queries to
+# assist with debugging.  Setting this to false may allow the module to better take
+# advantage of prepared statement caching.
+debugMode=false
+
 jdbcUrl=jdbc:h2:file:${GEOSERVER_DATA_DIR}/jdbcconfig/catalog;AUTO_SERVER=TRUE
 driverClassName=org.h2.Driver
 username=sa

@@ -31,6 +31,11 @@ initScript=jdbcconfig/scripts/initdb.postgres.sql
 #
 import=true
 
+# Flag controlling whether to add comments and some pretty-printing to SQL queries to
+# assist with debugging.  Setting this to false may allow the module to better take
+# advantage of prepared statement caching.
+debugMode=false
+
 #
 # The JNDI name for the data source.  Uncomment to use JNDI.
 #

@@ -16,8 +16,10 @@
  */
 package org.geoserver.jdbcconfig.internal;
 
-import static org.hamcrest.CoreMatchers.containsString;
-import static org.hamcrest.MatcherAssert.assertThat;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertNotEquals;
+import static org.junit.Assert.assertTrue;
 
 import org.geoserver.catalog.LayerInfo;
 import org.geoserver.catalog.Predicates;
@@ -27,13 +29,17 @@
 import org.junit.Before;
 import org.junit.Test;
 import org.opengis.filter.Filter;
+import org.opengis.filter.FilterFactory;
+import org.opengis.filter.sort.SortBy;
 
 /**
  * @author groldan
  * @author Kevin Smith, OpenGeo
  */
 public class QueryBuilderTest {
 
+    private static final FilterFactory FACTORY = Predicates.factory;
+
     private JDBCConfigTestSupport testSupport;
 
     private DbMappings dbMappings;
@@ -43,7 +49,6 @@ public class QueryBuilderTest {
     @Before
     public void setUp() throws Exception {
         dialect = new Dialect();
-        dbMappings = new DbMappings(dialect);
         testSupport =
                 new JDBCConfigTestSupport(
                         (JDBCConfigTestSupport.DBConfig)
@@ -58,125 +63,926 @@ public void tearDown() throws Exception {
     }
 
     @Test
-    public void testQueryAll() {
-        Filter filter = Predicates.equal("name", "ws1");
-        StringBuilder build =
-                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
-                        .filter(filter)
-                        .build();
+    public void testForIdsSort1DebugDisabled() {
+        String expected =
+                "SELECT id FROM (SELECT oid, id FROM object WHERE type_id IN (:types)) object "
+                        + "LEFT JOIN (SELECT oid, value prop0 FROM object_property "
+                        + "WHERE property_type IN (:sortProperty0)) subSelect0 ON object.oid = subSelect0.oid "
+                        + "ORDER BY prop0 ASC";
+        verifyForIds(expected, false, Predicates.acceptAll(), Predicates.asc("foo"));
     }
 
     @Test
-    public void testSort1() {
-        Filter filter = Predicates.acceptAll();
-        StringBuilder build =
-                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
-                        .filter(filter)
-                        .sortOrder(Predicates.asc("foo"))
-                        .build();
+    public void testForIdsSort1DebugEnabled() {
+        String expected =
+                "SELECT id FROM"
+                        + "\n    (SELECT oid, id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
+                        + "\n) object"
+                        + "\n  LEFT JOIN"
+                        + "\n    (SELECT oid, value prop0 FROM"
+                        + "\n      object_property WHERE property_type IN (:sortProperty0)) subSelect0 /* foo ASC */"
+                        + "\n  ON object.oid = subSelect0.oid"
+                        + "\n  ORDER BY prop0 ASC";
+        verifyForIds(expected, true, Predicates.acceptAll(), Predicates.asc("foo"));
     }
 
     @Test
-    public void testSort2() {
-        Filter filter = Predicates.acceptAll();
-        StringBuilder build =
-                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
-                        .filter(filter)
-                        .sortOrder(Predicates.asc("foo"), Predicates.desc("bar"))
-                        .build();
+    public void testForIdsSort2DebugDisabled() {
+        String expected =
+                "SELECT id FROM (SELECT oid, id FROM object WHERE type_id IN (:types)) object "
+                        + "LEFT JOIN (SELECT oid, value prop0 FROM object_property "
+                        + "WHERE property_type IN (:sortProperty0)) subSelect0 ON object.oid = subSelect0.oid "
+                        + "LEFT JOIN (SELECT oid, value prop1 FROM object_property "
+                        + "WHERE property_type IN (:sortProperty1)) subSelect1 ON object.oid = subSelect1.oid "
+                        + "ORDER BY prop0 ASC, prop1 DESC";
+        verifyForIds(
+                expected,
+                false,
+                Predicates.acceptAll(),
+                Predicates.asc("foo"),
+                Predicates.desc("bar"));
     }
 
     @Test
-    public void testSort3() {
-        Filter filter = Predicates.acceptAll();
-        StringBuilder build =
-                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
-                        .filter(filter)
-                        .sortOrder(
-                                Predicates.asc("foo"),
-                                Predicates.desc("bar"),
-                                Predicates.asc("baz"))
-                        .build();
+    public void testForIdsSort2DebugEnabled() {
+        String expected =
+                "SELECT id FROM"
+                        + "\n    (SELECT oid, id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
+                        + "\n) object"
+                        + "\n  LEFT JOIN"
+                        + "\n    (SELECT oid, value prop0 FROM"
+                        + "\n      object_property WHERE property_type IN (:sortProperty0)) subSelect0 /* foo ASC */"
+                        + "\n  ON object.oid = subSelect0.oid"
+                        + "\n  LEFT JOIN"
+                        + "\n    (SELECT oid, value prop1 FROM"
+                        + "\n      object_property WHERE property_type IN (:sortProperty1)) subSelect1 /* bar DESC */"
+                        + "\n  ON object.oid = subSelect1.oid"
+                        + "\n  ORDER BY prop0 ASC, prop1 DESC";
+        verifyForIds(
+                expected,
+                true,
+                Predicates.acceptAll(),
+                Predicates.asc("foo"),
+                Predicates.desc("bar"));
     }
 
     @Test
-    public void testSort3WithFilter() {
-        Filter filter = Predicates.equal("name", "quux");
-        StringBuilder build =
-                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
-                        .filter(filter)
-                        .sortOrder(
-                                Predicates.asc("foo"),
-                                Predicates.desc("bar"),
-                                Predicates.asc("baz"))
-                        .build();
+    public void testForIdsSort3DebugDisabled() {
+        String expected =
+                "SELECT id FROM (SELECT oid, id FROM object WHERE type_id IN (:types)) object "
+                        + "LEFT JOIN (SELECT oid, value prop0 FROM object_property "
+                        + "WHERE property_type IN (:sortProperty0)) subSelect0 ON object.oid = subSelect0.oid "
+                        + "LEFT JOIN (SELECT oid, value prop1 FROM object_property "
+                        + "WHERE property_type IN (:sortProperty1)) subSelect1 ON object.oid = subSelect1.oid "
+                        + "LEFT JOIN (SELECT oid, value prop2 FROM object_property "
+                        + "WHERE property_type IN (:sortProperty2)) subSelect2 ON object.oid = subSelect2.oid "
+                        + "ORDER BY prop0 ASC, prop1 DESC, prop2 ASC";
+        verifyForIds(
+                expected,
+                false,
+                Predicates.acceptAll(),
+                Predicates.asc("foo"),
+                Predicates.desc("bar"),
+                Predicates.asc("baz"));
     }
 
     @Test
-    public void testNotEquals() {
-        // Create the filter
-        Filter filter = Predicates.notEqual("name", "quux");
-        // Build it
-        StringBuilder build =
-                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
-                        .filter(filter)
-                        .build();
-        String sql = build.toString();
-        // Ensure the following sql is present
-        assertThat(
-                sql,
-                containsString(
-                        "NOT (oid IN (SELECT oid FROM object_property WHERE property_type IN (:ptype0) AND UPPER(value) = :value0)"));
+    public void testForIdsSort3DebugEnabled() {
+        String expected =
+                "SELECT id FROM"
+                        + "\n    (SELECT oid, id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
+                        + "\n) object"
+                        + "\n  LEFT JOIN"
+                        + "\n    (SELECT oid, value prop0 FROM"
+                        + "\n      object_property WHERE property_type IN (:sortProperty0)) subSelect0 /* foo ASC */"
+                        + "\n  ON object.oid = subSelect0.oid"
+                        + "\n  LEFT JOIN"
+                        + "\n    (SELECT oid, value prop1 FROM"
+                        + "\n      object_property WHERE property_type IN (:sortProperty1)) subSelect1 /* bar DESC */"
+                        + "\n  ON object.oid = subSelect1.oid"
+                        + "\n  LEFT JOIN"
+                        + "\n    (SELECT oid, value prop2 FROM"
+                        + "\n      object_property WHERE property_type IN (:sortProperty2)) subSelect2 /* baz ASC */"
+                        + "\n  ON object.oid = subSelect2.oid"
+                        + "\n  ORDER BY prop0 ASC, prop1 DESC, prop2 ASC";
+        verifyForIds(
+                expected,
+                true,
+                Predicates.acceptAll(),
+                Predicates.asc("foo"),
+                Predicates.desc("bar"),
+                Predicates.asc("baz"));
+    }
+
+    @Test
+    public void testForIdsSort3WithFilterDebugDisabled() {
+        String expected =
+                "SELECT id FROM (SELECT oid, id FROM object WHERE type_id IN (:types) AND oid IN "
+                        + "(SELECT oid FROM object_property WHERE property_type IN (:ptype0) AND value = :value0)) object "
+                        + "LEFT JOIN (SELECT oid, value prop0 FROM object_property "
+                        + "WHERE property_type IN (:sortProperty0)) subSelect0 ON object.oid = subSelect0.oid "
+                        + "LEFT JOIN (SELECT oid, value prop1 FROM object_property "
+                        + "WHERE property_type IN (:sortProperty1)) subSelect1 ON object.oid = subSelect1.oid "
+                        + "LEFT JOIN (SELECT oid, value prop2 FROM object_property "
+                        + "WHERE property_type IN (:sortProperty2)) subSelect2 ON object.oid = subSelect2.oid "
+                        + "ORDER BY prop0 ASC, prop1 DESC, prop2 ASC";
+        verifyForIds(
+                expected,
+                false,
+                Predicates.equal("name", "quux"),
+                Predicates.asc("foo"),
+                Predicates.desc("bar"),
+                Predicates.asc("baz"));
+    }
+
+    @Test
+    public void testForIdsSort3WithFilterDebugEnabled() {
+        String expected =
+                "SELECT id FROM"
+                        + "\n    (SELECT oid, id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
+                        + "\n      AND oid IN (SELECT oid FROM object_property WHERE property_type IN (:ptype0) AND value = :value0) /* [ name = quux ] */"
+                        + "\n) object"
+                        + "\n  LEFT JOIN"
+                        + "\n    (SELECT oid, value prop0 FROM"
+                        + "\n      object_property WHERE property_type IN (:sortProperty0)) subSelect0 /* foo ASC */"
+                        + "\n  ON object.oid = subSelect0.oid"
+                        + "\n  LEFT JOIN"
+                        + "\n    (SELECT oid, value prop1 FROM"
+                        + "\n      object_property WHERE property_type IN (:sortProperty1)) subSelect1 /* bar DESC */"
+                        + "\n  ON object.oid = subSelect1.oid"
+                        + "\n  LEFT JOIN"
+                        + "\n    (SELECT oid, value prop2 FROM"
+                        + "\n      object_property WHERE property_type IN (:sortProperty2)) subSelect2 /* baz ASC */"
+                        + "\n  ON object.oid = subSelect2.oid"
+                        + "\n  ORDER BY prop0 ASC, prop1 DESC, prop2 ASC";
+        verifyForIds(
+                expected,
+                true,
+                Predicates.equal("name", "quux"),
+                Predicates.asc("foo"),
+                Predicates.desc("bar"),
+                Predicates.asc("baz"));
+    }
+
+    @Test
+    public void testForCountUnknownProperty() {
+        dialect.setDebugMode(false);
+        String expected = "SELECT COUNT(oid) FROM object WHERE type_id IN (:types)";
+        Filter filter = Predicates.equal("foo.bar.baz", "quux");
+        QueryBuilder<?> builder =
+                QueryBuilder.forCount(dialect, WorkspaceInfo.class, dbMappings).filter(filter);
+        String actual = builder.build();
+        assertEquals(expected, actual);
+        assertEquals(Filter.INCLUDE, builder.getSupportedFilter());
+        assertEquals(filter, builder.getUnsupportedFilter());
+        assertFalse(builder.isOffsetLimitApplied());
+        assertEquals(1, builder.getNamedParameters().size());
+    }
+
+    @Test
+    public void testForIdsUnknownProperty() {
+        dialect.setDebugMode(false);
+        String expected = "SELECT id FROM object WHERE type_id IN (:types) ORDER BY oid";
+        Filter filter = Predicates.equal("foo.bar.baz", "quux");
+        QueryBuilder<?> builder =
+                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings).filter(filter);
+        String actual = builder.build();
+        assertEquals(expected, actual);
+        assertEquals(Filter.INCLUDE, builder.getSupportedFilter());
+        assertEquals(filter, builder.getUnsupportedFilter());
+        assertFalse(builder.isOffsetLimitApplied());
+        assertEquals(1, builder.getNamedParameters().size());
+    }
+
+    @Test
+    public void testForCountSimplifiedInclude() {
+        dialect.setDebugMode(false);
+        String expected = "SELECT COUNT(oid) FROM object WHERE type_id IN (:types)";
+        Filter filter = Predicates.and(Predicates.acceptAll(), Predicates.acceptAll());
+        QueryBuilder<?> builder =
+                QueryBuilder.forCount(dialect, WorkspaceInfo.class, dbMappings).filter(filter);
+        String actual = builder.build();
+        assertEquals(expected, actual);
+        assertEquals(Filter.INCLUDE, builder.getSupportedFilter());
+        assertEquals(Filter.INCLUDE, builder.getUnsupportedFilter());
+        assertFalse(builder.isOffsetLimitApplied());
+        assertEquals(1, builder.getNamedParameters().size());
+    }
+
+    @Test
+    public void testForIdsSimplifiedInclude() {
+        dialect.setDebugMode(false);
+        String expected = "SELECT id FROM object WHERE type_id IN (:types) ORDER BY oid";
+        Filter filter = Predicates.and(Predicates.acceptAll(), Predicates.acceptAll());
+        QueryBuilder<?> builder =
+                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings).filter(filter);
+        String actual = builder.build();
+        assertEquals(expected, actual);
+        assertEquals(Filter.INCLUDE, builder.getSupportedFilter());
+        assertEquals(Filter.INCLUDE, builder.getUnsupportedFilter());
+        assertTrue(builder.isOffsetLimitApplied());
+        assertEquals(1, builder.getNamedParameters().size());
     }
 
     @Test
-    public void testIsInstanceOf() {
-        // Create the filter
-        Filter filter = Predicates.isInstanceOf(LayerInfo.class);
-        // Build it
-        StringBuilder build =
+    public void testForIdsIncludeWithOffSetDebugDisabled() {
+        dialect.setDebugMode(false);
+        String expected =
+                "SELECT id FROM object WHERE type_id IN (:types) ORDER BY oid LIMIT 2147483647 OFFSET 5";
+        String actual =
+                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings).offset(5).build();
+        assertEquals(expected, actual);
+    }
+
+    @Test
+    public void testForIdsIncludeWithLimitDebugDisabled() {
+        dialect.setDebugMode(false);
+        String expected = "SELECT id FROM object WHERE type_id IN (:types) ORDER BY oid LIMIT 10";
+        String actual =
+                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings).limit(10).build();
+        assertEquals(expected, actual);
+    }
+
+    @Test
+    public void testForIdsIncludeWithOffSetAndLimitDebugDisabled() {
+        dialect.setDebugMode(false);
+        String expected =
+                "SELECT id FROM object WHERE type_id IN (:types) ORDER BY oid LIMIT 10 OFFSET 5";
+        String actual =
                 QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
-                        .filter(filter)
+                        .offset(5)
+                        .limit(10)
                         .build();
-        String sql = build.toString();
-        // Ensure the following sql is present
-        assertThat(sql, containsString("type_id = " + dbMappings.getTypeId(LayerInfo.class)));
+        assertEquals(expected, actual);
+    }
+
+    @Test
+    public void testForIdsIncludeWithOffSetDebugEnabled() {
+        dialect.setDebugMode(true);
+        String expected =
+                "SELECT id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
+                        + "\nORDER BY oid LIMIT 2147483647 OFFSET 5";
+        String actual =
+                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings).offset(5).build();
+        assertEquals(expected, actual);
+    }
+
+    @Test
+    public void testForIdsIncludeWithLimitDebugEnabled() {
+        dialect.setDebugMode(true);
+        String expected =
+                "SELECT id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
+                        + "\nORDER BY oid LIMIT 10";
+        String actual =
+                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings).limit(10).build();
+        assertEquals(expected, actual);
     }
 
     @Test
-    public void testIsNull() {
-        // Create the filter
-        Filter filter = Predicates.isNull("name");
-        // Build it
-        StringBuilder build =
+    public void testForIdsIncludeWithOffSetAndLimitDebugEnabled() {
+        dialect.setDebugMode(true);
+        String expected =
+                "SELECT id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
+                        + "\nORDER BY oid LIMIT 10 OFFSET 5";
+        String actual =
                 QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
-                        .filter(filter)
+                        .offset(5)
+                        .limit(10)
                         .build();
-        String sql = build.toString();
+        assertEquals(expected, actual);
+    }
+
+    @Test
+    public void testForCountIncludeDebugDisabled() {
+        String expected = "SELECT COUNT(oid) FROM object WHERE type_id IN (:types)";
+        verifyForCount(expected, false, Predicates.acceptAll());
+    }
+
+    @Test
+    public void testForCountIncludeDebugEnabled() {
+        String expected =
+                "SELECT COUNT(oid) FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */";
+        verifyForCount(expected, true, Predicates.acceptAll());
+    }
+
+    @Test
+    public void testForIdsIncludeDebugDisabled() {
+        String expected = "SELECT id FROM object WHERE type_id IN (:types) ORDER BY oid";
+        verifyForIds(expected, false, Predicates.acceptAll());
+    }
+
+    @Test
+    public void testForIdsIncludeDebugEnabled() {
+        String expected =
+                "SELECT id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
+                        + "\nORDER BY oid";
+        verifyForIds(expected, true, Predicates.acceptAll());
+    }
+
+    @Test
+    public void testForCountAndIsInstanceofDebugDisabled() {
+        String expected = "(type_id = 14 AND 0 = 1)";
+        Filter filter =
+                Predicates.and(
+                        Predicates.isInstanceOf(LayerInfo.class),
+                        Predicates.isInstanceOf(String.class));
+        verifyForCount(expected, false, filter);
+    }
+
+    @Test
+    public void testForCountAndIsInstanceofDebugEnabled() {
+        String expected =
+                "(\n    type_id = 14 /* isInstanceOf org.geoserver.catalog.LayerInfo */"
+                        + "\n    AND\n    0 = 1 /* EXCLUDE */\n)";
+        Filter filter =
+                Predicates.and(
+                        Predicates.isInstanceOf(LayerInfo.class),
+                        Predicates.isInstanceOf(String.class));
+        verifyForCount(expected, true, filter);
+    }
 
-        String sqlNull =
-                "oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
+    @Test
+    public void testForIdsAndIsInstanceofDebugDisabled() {
+        String expected = "(type_id = 14 AND 0 = 1) ";
+        Filter filter =
+                Predicates.and(
+                        Predicates.isInstanceOf(LayerInfo.class),
+                        Predicates.isInstanceOf(String.class));
+        verifyForIds(expected, false, filter);
+    }
+
+    @Test
+    public void testForIdsAndIsInstanceofDebugEnabled() {
+        String expected =
+                "(\n    type_id = 14 /* isInstanceOf org.geoserver.catalog.LayerInfo */"
+                        + "\n    AND\n    0 = 1 /* EXCLUDE */\n) ";
+        Filter filter =
+                Predicates.and(
+                        Predicates.isInstanceOf(LayerInfo.class),
+                        Predicates.isInstanceOf(String.class));
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountOrIsInstanceofDebugDisabled() {
+        String expected = "(type_id = 14 OR 0 = 1)";
+        Filter filter =
+                Predicates.or(
+                        Predicates.isInstanceOf(LayerInfo.class),
+                        Predicates.isInstanceOf(String.class));
+        verifyForCount(expected, false, filter);
+    }
+
+    @Test
+    public void testForCountOrIsInstanceofDebugEnabled() {
+        String expected =
+                "(\n    type_id = 14 /* isInstanceOf org.geoserver.catalog.LayerInfo */"
+                        + "\n    OR\n    0 = 1 /* EXCLUDE */\n)";
+        Filter filter =
+                Predicates.or(
+                        Predicates.isInstanceOf(LayerInfo.class),
+                        Predicates.isInstanceOf(String.class));
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsOrIsInstanceofDebugDisabled() {
+        String expected = "(type_id = 14 OR 0 = 1) ";
+        Filter filter =
+                Predicates.or(
+                        Predicates.isInstanceOf(LayerInfo.class),
+                        Predicates.isInstanceOf(String.class));
+        verifyForIds(expected, false, filter);
+    }
+
+    @Test
+    public void testForIdsOrIsInstanceofDebugEnabled() {
+        String expected =
+                "(\n    type_id = 14 /* isInstanceOf org.geoserver.catalog.LayerInfo */"
+                        + "\n    OR\n    0 = 1 /* EXCLUDE */\n) ";
+        Filter filter =
+                Predicates.or(
+                        Predicates.isInstanceOf(LayerInfo.class),
+                        Predicates.isInstanceOf(String.class));
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsEqualToSensitiveDebugDisabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value = :value0)";
+        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), true);
+        verifyForCount(expected, false, filter);
+    }
+
+    @Test
+    public void testForCountIsEqualToSensitiveDebugEnabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value = :value0) /* [ name = quux ] */";
+        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), true);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsEqualToSensitiveDebugEnabledEscaping() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value = :value0) /* [ name = FOO*\\/BAR ] */";
+        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), true);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsEqualToSensitiveDebugDisabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value = :value0) ";
+        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), true);
+        verifyForIds(expected, false, filter);
+    }
+
+    @Test
+    public void testForIdsIsEqualToSensitiveDebugEnabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value = :value0) /* [ name = quux ] */\n";
+        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), true);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsEqualToSensitiveDebugEnabledEscaping() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value = :value0) /* [ name = FOO*\\/BAR ] */\n";
+        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), true);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsEqualToInsensitiveDebugDisabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) = :value0)";
+        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), false);
+        verifyForCount(expected, false, filter);
+    }
+
+    @Test
+    public void testForCountIsEqualToInsensitiveDebugEnabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = quux ] */";
+        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), false);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsEqualToInsensitiveDebugEnabledEscaping() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = FOO*\\/BAR ] */";
+        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), false);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsEqualToInsensitiveDebugDisabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) = :value0) ";
+        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), false);
+        verifyForIds(expected, false, filter);
+    }
+
+    @Test
+    public void testForIdsIsEqualToInsensitiveDebugEnabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = quux ] */\n";
+        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), false);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsEqualToInsensitiveDebugEnabledEscaping() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = FOO*\\/BAR ] */\n";
+        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), false);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsNotEqualToSensitiveDebugDisabled() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value = :value0))";
+        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), true);
+        verifyForCount(expected, false, filter);
+    }
+
+    @Test
+    public void testForCountIsNotEqualToSensitiveDebugEnabled() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value = :value0) /* [ name = quux ] */\n)";
+        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), true);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsNotEqualToSensitiveDebugEnabledEscaping() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value = :value0) /* [ name = FOO*\\/BAR ] */\n)";
+        Filter filter =
+                FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), true);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsNotEqualToSensitiveDebugDisabled() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value = :value0)) ";
+        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), true);
+        verifyForIds(expected, false, filter);
+    }
+
+    @Test
+    public void testForIdsIsNotEqualToSensitiveDebugEnabled() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value = :value0) /* [ name = quux ] */\n) ";
+        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), true);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsNotEqualToSensitiveDebugEnabledEscaping() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value = :value0) /* [ name = FOO*\\/BAR ] */\n) ";
+        Filter filter =
+                FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), true);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsNotEqualToInsensitiveDebugDisabled() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) = :value0))";
+        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), false);
+        verifyForCount(expected, false, filter);
+    }
+
+    @Test
+    public void testForCountIsNotEqualToInsensitiveDebugEnabled() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = quux ] */\n)";
+        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), false);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsNotEqualToInsensitiveDebugEnabledEscaping() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = FOO*\\/BAR ] */\n)";
+        Filter filter =
+                FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), false);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsNotEqualToInsensitiveDebugDisabled() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) = :value0)) ";
+        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), false);
+        verifyForIds(expected, false, filter);
+    }
+
+    @Test
+    public void testForIdsIsNotEqualToInsensitiveDebugEnabled() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = quux ] */\n) ";
+        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), false);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsNotEqualToInsensitiveDebugEnabledEscaping() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = FOO*\\/BAR ] */\n) ";
+        Filter filter =
+                FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), false);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsLikeSensitiveDebugDisabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value LIKE :value0)";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", true);
+        verifyForCount(expected, false, filter);
+    }
+
+    @Test
+    public void testForCountIsLikeSensitiveDebugDisabledEscaping() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value LIKE :value0)";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", true);
+        verifyForCount(expected, false, filter);
+    }
+
+    @Test
+    public void testForCountIsLikeSensitiveDebugEnabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value LIKE :value0) /* [ name is like %quux% ] */";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", true);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsLikeSensitiveDebugEnabledEscaping1() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value LIKE :value0) /* [ name is like %\\'FOO% ] */";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", true);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsLikeSensitiveDebugEnabledEscaping2() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value LIKE :value0) /* [ name is like %FOO*\\/BAR% ] */";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%FOO*/BAR%", "%", "_", "\\", true);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsLikeSensitiveDebugDisabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value LIKE :value0) ";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", true);
+        verifyForIds(expected, false, filter);
+    }
+
+    @Test
+    public void testForIdsIsLikeSensitiveDebugDisabledEscaping() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value LIKE :value0) ";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", true);
+        verifyForIds(expected, false, filter);
+    }
+
+    @Test
+    public void testForIdsIsLikeSensitiveDebugEnabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value LIKE :value0) /* [ name is like %quux% ] */\n";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", true);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsLikeSensitiveDebugEnabledEscaping1() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value LIKE :value0) /* [ name is like %\\'FOO% ] */\n";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", true);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsLikeSensitiveDebugEnabledEscaping2() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND value LIKE :value0) /* [ name is like %FOO*\\/BAR% ] */\n";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%FOO*/BAR%", "%", "_", "\\", true);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsLikeInsensitiveDebugDisabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) LIKE :value0)";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", false);
+        verifyForCount(expected, false, filter);
+    }
+
+    @Test
+    public void testForCountIsLikeInsensitiveDebugDisabledEscaping() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) LIKE :value0)";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", false);
+        verifyForCount(expected, false, filter);
+    }
+
+    @Test
+    public void testForCountIsLikeInsensitiveDebugEnabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) /* [ name is like %quux% ] */";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", false);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsLikeInsensitiveDebugEnabledEscaping1() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) /* [ name is like %\\'FOO% ] */";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", false);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsLikeInsensitiveDebugEnabledEscaping2() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) /* [ name is like %FOO*\\/BAR% ] */";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%FOO*/BAR%", "%", "_", "\\", false);
+        verifyForCount(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsLikeInsensitiveDebugDisabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) ";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", false);
+        verifyForIds(expected, false, filter);
+    }
+
+    @Test
+    public void testForIdsIsLikeInsensitiveDebugDisabledEscaping() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) ";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", false);
+        verifyForIds(expected, false, filter);
+    }
+
+    @Test
+    public void testForIdsIsLikeInsensitiveDebugEnabled() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) /* [ name is like %quux% ] */\n";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", false);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsLikeInsensitiveDebugEnabledEscaping1() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) /* [ name is like %\\'FOO% ] */\n";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", false);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForIdsIsLikeInsensitiveDebugEnabledEscaping2() {
+        String expected =
+                "oid IN (SELECT oid FROM object_property WHERE property_type "
+                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) /* [ name is like %FOO*\\/BAR% ] */\n";
+        Filter filter = FACTORY.like(FACTORY.property("name"), "%FOO*/BAR%", "%", "_", "\\", false);
+        verifyForIds(expected, true, filter);
+    }
+
+    @Test
+    public void testForCountIsNullDebugDisabled() {
+        String expected =
+                "(oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
                         + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
-                        + "ptype0))";
-        // Ensure the following sql is present
-        assertThat(sql, containsString(sqlNull));
+                        + "ptype0)))";
+        verifyForCount(expected, false, Predicates.isNull("name"));
     }
 
     @Test
-    public void testIsNil() {
-        // Create the filter
-        Filter filter = Predicates.isNull("name");
-        // Build it
-        StringBuilder build =
-                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
-                        .filter(filter)
-                        .build();
-        String sql = build.toString();
+    public void testForCountIsNullDebugEnabled() {
+        String expected =
+                "(oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0))) /* [ name IS NULL ] */";
+        verifyForCount(expected, true, Predicates.isNull("name"));
+    }
+
+    @Test
+    public void testForIdsIsNullDebugDisabled() {
+        String expected =
+                "(oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0))) ";
+        verifyForIds(expected, false, Predicates.isNull("name"));
+    }
+
+    @Test
+    public void testForIdsIsNullDebugEnabled() {
+        String expected =
+                "(oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0))) /* [ name IS NULL ] */\n";
+        verifyForIds(expected, true, Predicates.isNull("name"));
+    }
+
+    @Test
+    public void testForCountIsNotNullDebugDisabled() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0)))";
+        verifyForCount(expected, false, Predicates.not(Predicates.isNull("name")));
+    }
+
+    @Test
+    public void testForCountIsNotNullDebugEnabled() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0))) /* [ name IS NULL ] */";
+        verifyForCount(expected, true, Predicates.not(Predicates.isNull("name")));
+    }
+
+    @Test
+    public void testForIdsIsNotNullDebugDisabled() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0))) ";
+        verifyForIds(expected, false, Predicates.not(Predicates.isNull("name")));
+    }
+
+    @Test
+    public void testForIdsIsNotNullDebugEnabled() {
+        String expected =
+                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
+                        + "ptype0))) /* [ name IS NULL ] */\n";
+        verifyForIds(expected, true, Predicates.not(Predicates.isNull("name")));
+    }
+
+    private void verifyForCount(String expectedSQL, boolean debugMode, Filter filter) {
+        String expected = expectedSQL;
+        if (!expected.startsWith("SELECT")) {
+            expected =
+                    "SELECT COUNT(oid) FROM object WHERE type_id IN (:types) "
+                            + (debugMode ? "/* org.geoserver.catalog.WorkspaceInfo */\n" : "")
+                            + "AND "
+                            + expectedSQL;
+        }
+        QueryBuilder<?> builder = QueryBuilder.forCount(dialect, WorkspaceInfo.class, dbMappings);
+        verifyQuery(builder, expected, debugMode, filter);
+    }
+
+    private void verifyForIds(
+            String expectedSQL, boolean debugMode, Filter filter, SortBy... order) {
+        String expected = expectedSQL;
+        if (!expected.startsWith("SELECT")) {
+            expected =
+                    "SELECT id FROM object WHERE type_id IN (:types) "
+                            + (debugMode ? "/* org.geoserver.catalog.WorkspaceInfo */\n" : "")
+                            + "AND "
+                            + expectedSQL
+                            + "ORDER BY oid";
+        }
+        QueryBuilder<?> builder = QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings);
+        verifyQuery(builder, expected, debugMode, filter, order);
+    }
 
-        String sqlNil =
-                "oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
-                        + "ptype0) AND value IS NULL)";
-        // Ensure the following sql is present
-        assertThat(sql, containsString(sqlNil));
+    private void verifyQuery(
+            QueryBuilder<?> builder,
+            String expected,
+            boolean debugMode,
+            Filter filter,
+            SortBy... order) {
+        dialect.setDebugMode(debugMode);
+        String actual = builder.filter(filter).sortOrder(order).build();
+        assertEquals(expected, actual);
+        if (Filter.INCLUDE.equals(filter)) {
+            assertEquals(Filter.INCLUDE, builder.getSupportedFilter());
+        } else {
+            assertNotEquals(Filter.INCLUDE, builder.getSupportedFilter());
+        }
+        assertEquals(Filter.INCLUDE, builder.getUnsupportedFilter());
+        assertEquals(!actual.startsWith("SELECT COUNT"), builder.isOffsetLimitApplied());
+        assertFalse(builder.getNamedParameters().isEmpty());
     }
 }

@@ -32,6 +32,7 @@
 import org.geoserver.jdbcconfig.internal.JDBCConfigXStreamPersisterInitializer;
 import org.geoserver.jdbcconfig.internal.Util;
 import org.geoserver.jdbcconfig.internal.XStreamInfoSerialBinding;
+import org.geoserver.jdbcloader.JDBCLoaderProperties;
 import org.geoserver.platform.GeoServerExtensionsHelper;
 import org.geoserver.platform.GeoServerResourceLoader;
 import org.springframework.context.annotation.AnnotationConfigApplicationContext;
@@ -418,7 +419,9 @@ public PlatformTransactionManager jdbcConfigTransactionManager() {
         @Bean
         public ConfigDatabase configDatabase() {
             return new ConfigDatabase(
-                    dataSource(), new XStreamInfoSerialBinding(new XStreamPersisterFactory()));
+                    new JDBCLoaderProperties(null),
+                    dataSource(),
+                    new XStreamInfoSerialBinding(new XStreamPersisterFactory()));
         }
 
         @Bean