Every business application has this screen somewhere: a table of records, filter boxes above it, and a user who may only see part of the data. The first query is a SELECT with a WHERE on the user's office. Filters arrive one at a time, then a role that sees two offices, then an auditor who sees everything once approved. Eventually the query is a two-hundred-line method nobody wants to touch.
Our argument is that this query was never a string. A search with optional filters and role-based visibility is application logic, and one of its invariants is a security boundary. Built by appending text, it gets the testability and review process of a text file. Built from small units that contribute fragments to a builder that enforces the rules, it gets treated like the rest of the code. The pattern is Strategy, used twice: one family of strategies decides what a user may see, the other what the user asked for, and neither writes the whole query.
The companion repository is search-query-composition-demo: both versions of the search on the same schema, tested against a real SQL Server through Testcontainers, so you need Docker.
Versions used: Spring Boot 4.1.1, Spring Framework 7.0.9, Flyway 12.4.0, Testcontainers 2.0.5, Microsoft JDBC Driver for SQL Server 13.4.0, SQL Server 2025 (mcr.microsoft.com/mssql/server:2025-CU9-ubuntu-24.04), Java 21. No JPA: Spring JDBC, NamedParameterJdbcTemplate and records.
The domain
A document platform for an organization with a national office, regional offices, and local offices under each region, modelled in org_unit with a parent_id. The exception is the chartered units, local offices that report straight to the national office: a regional supervisor sees one only while holding an explicit, dated delegation for it.
Documents belong to a unit and have a type, a status (DRAFT, SUBMITTED, APPROVED, ARCHIVED), an author, dates, attachments and tags. Five roles decide visibility:
| Role | Visibility |
|---|---|
LOCAL_OFFICER |
their own unit |
REGIONAL_SUPERVISOR |
their region and its local offices, plus chartered units with an active delegation |
NATIONAL_ADMIN |
everything, including the author's email |
AUDITOR |
every unit, only APPROVED or ARCHIVED
|
DELEGATE |
only the units they hold an active delegation for |
On top of that come ten optional filters (region, unit, type, status, date range, attachments, author, title, tag, overdue), several of which need joins.
The version that grows naturally
The legacy package has the search you get by adding one requirement at a time: one method, a StringBuilder that starts with SELECT DISTINCT and every join any filter might need, then one if per role and one per filter, with catch-all predicates for the simple filters. For a local officer it sends this (abridged):
SELECT DISTINCT d.id, d.title, d.status, d.created_at, author.email AS author_email
FROM document d
JOIN org_unit unit ON unit.id = d.org_unit_id
JOIN app_user author ON author.id = d.author_id
LEFT JOIN attachment a ON a.document_id = d.id
LEFT JOIN document_tag dt ON dt.document_id = d.id
LEFT JOIN tag t ON t.id = dt.tag_id
WHERE 1 = 1
AND d.org_unit_id = :userUnitId
AND (:regionId IS NULL OR unit.id = :regionId OR unit.parent_id = :regionId)
AND (:author IS NULL OR author.username = :author)
ORDER BY d.created_at DESC, d.id
It works: apart from two quirks covered below, the repository's tests show it returns the same rows as the composed version. The problems are structural.
The rules are not addressable. "A supervisor sees chartered units only with an active delegation" is a real business rule, and here it is five sql.append calls inside an else if. There is nothing to call, reuse or test in isolation. When a count query or an export needs the same visibility, the only option is to copy those lines, and the copies drift.
Catch-alls fight the plan cache. SQL Server caches one plan per statement text, and this text never changes, so every combination of filters shares one plan: compiled for the values of the first call, and required to be valid whenever any parameter is NULL, which pushes the optimizer towards scans. OPTION (RECOMPILE) trades that for a compilation on every execution; Erland Sommarskog's Dynamic Search Conditions in T-SQL covers the options in depth. SQL Server 2025 adds Optional Parameter Plan Optimization, which caches plan variants by NULL state, and how it copes with ten optional predicates is something to measure, not assume.
Data is fetched, then hidden. The author's email is selected for every role and set to null in the row mapper unless the user is an admin. It leaves the database for users who may not see it, with one if in a lambda between it and the response.
Two smaller things: the LEFT JOINs multiply rows and DISTINCT hides that instead of avoiding it, and the mapper reads the nullable reviewer id with rs.getLong, which returns 0 for NULL.
Why one query per role does not fix it
Splitting the method by role gives each role clean SQL, but the search varies along two independent axes: visibility depends on who asks, filtering on what they asked. A query per role removes the first axis by multiplying the second. Five roles means five copies of the tag filter, and every fix to a filter is five fixes.
It also hides the security boundary. In each query the visibility predicate sits inline among the filters, and a reviewer reading a diff cannot tell whether a changed line affects what the user asked for or what the user may see.
Fragments that compose
The composed version keeps the axes apart. A visibility scope decides what a user may see: one per role, exactly one per search, never optional. A filter contributor decides what the user asked for: one per filter, each deciding whether it applies. Both add fragments (predicates, joins, CTEs, columns, parameters) to a builder, and neither sees the whole query.
public interface VisibilityScope {
Role role();
void restrict(SearchQuery query, SearchContext context);
}
public interface FilterContributor {
boolean appliesTo(SearchContext context);
void contribute(SearchQuery query, SearchContext context);
}
SearchContext is a record of the user, the criteria and today's date, resolved once per search for a reason that comes up in the security section. The supervisor scope is where the chartered exception lives, and the only place it lives:
@Component
public class RegionalSupervisorScope implements VisibilityScope {
private static final Cte SUPERVISED_UNIT = new Cte("supervised_unit", """
SELECT u.id AS unit_id
FROM org_unit u
WHERE u.id = :userUnitId OR u.parent_id = :userUnitId
UNION
SELECT dl.org_unit_id
FROM delegation dl
JOIN org_unit cu ON cu.id = dl.org_unit_id
WHERE dl.delegate_user_id = :userId
AND cu.chartered = 1
AND dl.valid_from <= :today
AND (dl.valid_to IS NULL OR dl.valid_to >= :today)
""");
@Override
public Role role() {
return Role.REGIONAL_SUPERVISOR;
}
@Override
public void restrict(SearchQuery query, SearchContext context) {
query.with(SUPERVISED_UNIT)
.restrictVisibility("d.org_unit_id IN (SELECT unit_id FROM supervised_unit)")
.param("userUnitId", context.user().unitId())
.param("userId", context.user().id())
.param("today", context.today());
}
}
When the rule changes, this is the one class to open. The national admin scope calls query.visibleToAll(), which adds no predicate but records that a scope decided; the builder refuses a query where none did. That scope also adds the email column, so for every other role it is never selected.
A filter looks the same from outside:
@Component
public class RegionFilter implements FilterContributor {
@Override
public boolean appliesTo(SearchContext context) {
return context.criteria().regionId() != null;
}
@Override
public void contribute(SearchQuery query, SearchContext context) {
query.join(Joins.UNIT)
.where("unit.id = :regionId OR unit.parent_id = :regionId")
.param("regionId", context.criteria().regionId());
}
}
Keep an eye on the OR in that predicate; it comes back in the security section.
Every contributor declares the joins it depends on, even ones the base query already has. Shared joins are constants with a key, like new Join("unit", "JOIN org_unit unit ON unit.id = d.org_unit_id"); the builder adds each key once and throws if a key arrives with different SQL.
A VisibilityScopeRegistry collects the scopes into a map by role, so two scopes for one role fail at startup. A role with no scope throws NoVisibilityScopeException at search time, and nothing reads a missing scope as "no restriction".
DocumentQueryComposer puts it together; the registry, the List<FilterContributor> injected by Spring and a Clock come in through its constructor:
public SqlQuery compose(SearchUser user, SearchCriteria criteria) {
VisibilityScope scope = scopes.scopeFor(user.role());
SearchContext context = new SearchContext(user, criteria, LocalDate.now(clock));
SearchQuery query = SearchQuery.from("document d")
.join(Joins.UNIT)
.join(Joins.DOCUMENT_TYPE)
.join(Joins.AUTHOR)
.select("d.id", "d.reference", "d.title", "d.status",
"unit.code AS unit_code", "doc_type.code AS document_type", "author.username AS author",
"d.reviewer_id", "d.created_at", "d.due_date");
scope.restrict(query, context);
for (FilterContributor filter : filters) {
if (filter.appliesTo(context)) {
filter.contribute(query, context);
}
}
return query.orderBy(criteria.sortField(), criteria.sortDirection())
.orderBy(SortField.ID, SortDirection.ASC)
.build();
}
Adding a filter means adding a class, and order does not matter because every fragment ends up as one more ANDed condition in its own parentheses. Each combination of filters also produces a different statement text, so SQL Server caches a plan per combination actually used, with only the predicates that are really there.
The builder
The full SearchQuery also handles CTEs, joins and ordering. These two methods carry the rules the next section depends on:
public SearchQuery param(String name, Object value) {
requireIdentifier(name);
if (value == null) {
throw new QueryCompositionException("Parameter :" + name + " has no value");
}
Object existing = parameters.putIfAbsent(name, value);
if (existing != null && !Objects.equals(existing, value)) {
throw new QueryCompositionException(
"Parameter :" + name + " is already bound to " + existing + ", refusing " + value);
}
return this;
}
public SqlQuery build() {
if (!visibilityDecided) {
throw new QueryCompositionException("No visibility scope was applied to this query");
}
StringBuilder sql = new StringBuilder();
// WITH, SELECT, FROM and JOIN clauses omitted
List<String> conditions = Stream.concat(visibilityPredicates.stream(), predicates.stream())
// An OR inside one fragment must never escape into the AND chain around it
.map(predicate -> "(" + predicate.strip() + ")")
.toList();
if (!conditions.isEmpty()) {
sql.append("WHERE ").append(String.join("\n AND ", conditions)).append('\n');
}
// ORDER BY omitted
return new SqlQuery(sql.toString().strip(), Map.copyOf(parameters));
}
Security is a property of the composition
Nobody can review every combination of fragments written by different people, so the builder has to make the unsafe combinations impossible to build.
Values are parameters
Every value goes through param(...) and reaches SQL Server as a bound parameter. The builder cannot prove that nobody concatenated a value, and Java has no safe SQL templating (String Templates were previewed in JDK 21 and 22, then withdrawn). What it can do is reject any fragment containing ', ;, -- or /*. Without quotes there is nowhere to put a string literal, so even the auditor's fixed statuses travel as parameters. It is a tripwire, not a proof, and it keeps every piece of data a fragment touches visible in the param calls under it.
Identifiers are a whitelist
Column names cannot be bound, and SQL Server rejects a variable in ORDER BY outright. SortField maps the names the UI may send to real columns:
public enum SortField {
CREATED_AT("createdAt", "d.created_at"),
TITLE("title", "d.title");
// other constants, fields, constructor and fromLogicalName omitted
}
The builder's only sort method is orderBy(SortField field, SortDirection direction), with no String overload to misuse.
The AND/OR precedence leak
This is the bug the design is built around, and it gets past both code review and most test suites.
The region filter's predicate, unit.id = :regionId OR unit.parent_id = :regionId, is correct. So is the local officer's d.org_unit_id = :userUnitId. Concatenated the way a string builder does it:
WHERE d.org_unit_id = :userUnitId AND unit.id = :regionId OR unit.parent_id = :regionId
AND binds tighter than OR, as * binds tighter than +, so the database reads:
WHERE (d.org_unit_id = :userUnitId AND unit.id = :regionId)
OR unit.parent_id = :regionId
The second branch has no visibility predicate at all: any document in any local office under the requested region matches, whoever asks. In the demo data, a local officer from the north who filters on the south region receives every document of the south's local office. PrecedenceLeakTest runs those two concatenated strings against SQL Server and asserts the leaked rows, then runs the same fragments through the builder and gets nothing back.
It survives review because each fragment is correct on its own; the bug is in the space between them, which nobody wrote. It survives testing because the natural test checks that the right documents come back. Filtering on their own region, the northern officer does get all of their documents, plus those of the other northern office (the test has that case too). Only a test that asserts absence catches it, and the authorization matrix below does exactly that.
The legacy method gets this right almost by accident, since a catch-all only works inside parentheses. The leak arrives the day someone tidies it into if (regionId != null) sql.append("AND unit.id = :regionId OR unit.parent_id = :regionId "), which reads like an improvement.
The builder's defence is the commented line in build(): every predicate is wrapped in parentheses, visibility first. Contributors cannot forget it or opt out, and the worst a stray OR can do is return the wrong subset of what the user could see anyway.
Parameter names collide
Contributors are written independently, so two of them will eventually pick the same parameter name. Sometimes that is right: the supervisor scope and the overdue filter both bind :today. Sometimes it is a hole. Had the local officer scope bound its unit as :unitId, the natural name, the unit filter's :unitId would silently replace it in a plain map, and the visibility predicate would check the unit the user asked for instead of the one they belong to. param accepts a second binding only if it equals the first, and SearchQueryTest has that exact case. Naming the scope's parameter userUnitId makes collisions rarer; the rule catches the rest.
The same rule is why today lives in SearchContext. If the scope and the overdue filter each called LocalDate.now(clock), the two calls could straddle midnight, the builder would reject the second binding, and the search would fail. Resolving the date once per search removes the race.
Fail closed
The legacy method has branches for local officer, supervisor, auditor and delegate. The admin has none, because "everything" is what you get when no branch adds a predicate, and that is also what any new role gets. The repository has a sixth role, EXTERNAL_REVIEWER, with no scope yet: FailClosedTest shows the legacy search returning all 21 documents to it and the composed one throwing NoVisibilityScopeException.
The composed version fails closed twice. The registry throws for a role without a scope, and build() throws if no scope called restrictVisibility or visibleToAll, so a buggy restrict does not produce an unrestricted query either. Seeing everything has to be written down.
LIKE has its own syntax
A bound parameter protects the SQL, not the pattern. In the legacy version, 100% also matches "Budget review for 100 days", and since [ opens a character class on SQL Server, [DRAFT] matches every title containing one of those letters: all 21 documents in the demo. LikePattern brackets the three special characters, the SQL Server idiom:
static String escape(String text) {
StringBuilder escaped = new StringBuilder(text.length());
for (char c : text.toCharArray()) {
// SQL Server reads [ as the start of a character class, so it is a wildcard too
if (c == '%' || c == '_' || c == '[') {
escaped.append('[').append(c).append(']');
} else {
escaped.append(c);
}
}
return escaped.toString();
}
Testing it
The whole visibility policy fits in a table, so the main test is one: each row a document, each column a user, each cell whether that user sees it.
private static final String MATRIX = """
reference officer.n1 super.north super.south auditor delegate.one
N1-001 Y Y . . .
S1-002 . . Y Y Y
CA-001 . Y . Y .
CB-001 . . . . Y
""";
The full matrix has 21 documents and 7 users, run against both implementations for 294 cases. The . cells matter as much as the Y ones, because they assert that something is not returned.
Characterization tests run the legacy and the composed search on 20 combinations of criteria for every user and require the same rows in the same order. They are how the two legacy quirks above, the 0 reviewer and the unescaped LIKE, were found, and each became a named test rather than a looser comparison. Testing SQL you inherited without tests is the subject of a follow-up article.
Trade-offs and alternatives
Spring Data Specifications and the Criteria API. A Specification is the same idea as a filter contributor, and since Criteria predicates are objects combined into a tree, the precedence leak cannot happen there, a real advantage over any string-based builder, this one included. We did not use them because they need JPA entities, and because the visibility rules want CTEs (and screens like this soon want window functions), which standard JPA Criteria lacks and Hibernate 6 offers only through its own extensions. The SQL is also generated rather than written, which matters when this query's plan is what you are tuning.
jOOQ. Starting from zero, this is what we would evaluate first. Conditions combine into an AST, so parentheses are rendered for you; generated code makes the schema itself the sort whitelist; CTEs, window functions and the SQL Server dialect are all there. The costs are a code generation step and, for SQL Server, a commercial license, since the open source edition covers only open source databases. Most of this design carries over to it unchanged.
Row-Level Security. SQL Server can attach a filter predicate to document through a security policy, so every query gets it, ad-hoc reports included, with the user's identity set through sp_set_session_context on every connection checkout. The predicate is invisible in the application's SQL and harder to test, and Microsoft documents side-channel attacks against it, so we see it as a second line of defence.
A closure table. u.id = :userUnitId OR u.parent_id = :userUnitId works only because the hierarchy has exactly three levels. With a fourth, an org_unit_closure(ancestor_id, descendant_id, depth) table, or a recursive CTE, turns "everything under this unit" into one indexed lookup, and the chartered exception stays in its one scope.
When this is too much. One role, three filters, an internal tool used by five people: write the query, parenthesize it, test it and move on. The same goes for reports that look like searches but are fixed queries. The design earns its cost when visibility has more than two cases, filters keep arriving, and a leak would be an incident rather than a bug report.
Closing
The search keeps growing because it is where the rules about who may see what meet the user's question about what they want to see. Written as one string, both kinds of rule end up in the same StringBuilder, and the security boundary is only as good as the last person who appended to it.
Treated as a program, the query has parts with names: the supervisor rule is a class you can open, and the region filter cannot leak because the builder owns its parentheses. Every fragment is still plain SQL you can paste into a console. It just stops being the place where the rules live.
