iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Treat a search that has role-based visibility and optional filters as application logic, not as a string you keep appending to. Pick one visibility strategy by role to decide what the user may see. Add separate filter contributors for what the user asked for. Wrap every predicate in parentheses, join them with AND, and fail closed when a role has no visibility rule. That is the design proposed in a DEV Community article by Paolo, posted September 26, 2026, which implements it as a Java and Spring JDBC demo against SQL Server 2025. Read it as a worked design proposal, not as proof that one architecture is always safer or faster.
Why a plain WHERE clause can leak documents
Suppose a search begins with a visibility condition and then appends a filter for a region and the units beneath it. Built from string fragments, it can look like this. This is a simplified version of the pattern the article describes:
WHERE u.region_id = :userRegion
AND u.id = :regionId OR u.parent_id = :regionIdSQL evaluates AND before OR, so the database reads the statement as:
WHERE (u.region_id = :userRegion AND u.id = :regionId)
OR u.parent_id = :regionIdThe second branch carries no visibility check. Any document whose unit has a parent equal to the requested region is returned, whichever region the user belongs to. The article’s local-officer example reports exactly this result: documents from another region came back. Each fragment looks reasonable on its own, which is why the defect survives a quick review.
#1 Best Overall
The article frames the stakes in one sentence:
“A search with optional filters and role-based visibility is application logic, and one of its invariants is a security boundary.”
Two families of strategies that never write the whole query
The design splits the search into two families. Paolo puts it this way:
“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.”
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.
Each rule is a self-contained piece that contributes joins, predicates and parameters, and a builder assembles them. The visibility family is required and exactly one strategy is chosen per request. The filter family is optional, and any subset of its contributors can be active.
Visibility strategies: what a user may see
The example defines one visibility strategy per role:
| Role | What it can see in the example |
|---|---|
| LOCAL_OFFICER | Their own unit |
| REGIONAL_SUPERVISOR | The region and its local offices, plus chartered units only during an active explicit delegation |
| NATIONAL_ADMIN | All documents; author email is selected only in this scope |
| AUDITOR | Approved or archived documents across units |
| DELEGATE | Only units with an active delegation |
Filter contributors: what the user asked for
The example adds ten optional filters. Each contributes its own predicate and parameters only when the request uses it:
- Region
- Unit
- Type
- Status
- Date range
- Attachments
- Author
- Title
- Tag
- Overdue
A filter adds a condition; it does not decide access. That decision belongs to the visibility strategy alone.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallBuilding the query: one visibility rule, then the filters
- Resolve the user’s scope. Read the role from the authenticated user and look up its visibility strategy in the registry.
- Create one search context. Capture the request’s filter values and resolve “today” once. The visibility scope and the overdue filter then use the same date, even if the request straddles midnight.
- Apply exactly one visibility strategy. It contributes its joins, predicates and parameters, and it must make an access decision.
- Apply each active filter. Each adds its own parenthesized predicate. Inactive filters contribute nothing.
- Compose and bind. The builder joins the predicates with AND, adds any CTEs, joins, selected columns and ordering, and passes values as bound parameters.
Because the statement is built from the filters that are actually active, each combination produces its own SQL text. The usual alternative is one fixed statement with clauses such as (:region IS NULL OR u.region_id = :region) for every optional input, which has to cope with every combination at once.
Per-combination statements raise a performance question. The article notes that SQL Server 2025’s Optional Parameter Plan Optimization handles optional predicates through plan variants. It does not treat speed as settled, and it recommends measuring performance with ten optional predicates rather than assuming it. Measure against your own data and query mix before relying on any speed claim, in either direction.
Invariants that stop contributors from weakening the query
Parenthesize every predicate and reject parameter conflicts
Each predicate is wrapped in parentheses before it is joined, which is the fix for the leak described above. The builder also tracks parameter names. Missing bindings are rejected. A duplicate name is rejected when its new value differs, and a name may be shared only when every use carries an equal value. Without that rule, one filter can silently overwrite the value another filter bound under the same name.
Bind values, whitelist identifiers
Values travel as bound parameters. SQL identifiers cannot be bound, so sort fields are selected from a whitelist rather than taken from user input. The builder also rejects certain characters in fragments. The article describes that check as a tripwire, not a proof against unsafe SQL, so it should not be treated as your injection defense.
Recommended Free Tools
Fail closed for roles nobody planned for
The registry rejects any role that has no visibility strategy, and the builder refuses to compose a query when no strategy makes a visibility decision. The article’s demo shows the case: an unhandled role, EXTERNAL_REVIEWER, made the composed query throw instead of returning every document. An error during testing is the outcome you want when a new role appears. An open result set is the one to avoid.
Wildcards and sensitive columns
- LIKE patterns. Bound parameters do not neutralize wildcard meaning. A user who types % or _ can broaden a title match unless those characters are escaped. The article’s SQL Server example escapes %, _ and [ in its patterns.
- Sensitive columns. Author email is selected only in the national-admin scope, rather than fetched for everyone and hidden later. A column that is never selected cannot leak through a later rendering mistake.
What the tests show, and what they do not
The article’s authorization matrix covers 21 documents and 7 users, and it was run against both implementations for 294 cases. Characterization testing compares both implementations across 20 criteria combinations for every user. These are the author’s figures for a demo dataset, reported in the September 26, 2026 article. They have not been independently reproduced, and they say nothing about production data volume.
The matrix tests absence as much as presence. It checks that users do not see documents outside their scope, not only that they see the ones they should. Include the same negative cases in your own suite: a user one unit away from a document, a delegate whose delegation is not active, and a role with no visibility strategy.
Alternatives compared
The article weighs the design against several options. Five axes make the comparison concrete: whether predicates form a structured tree that handles precedence; how much control you keep over SQL and database-specific features; whether ORM entities are required; where authorization is enforced and how visible that is during review; and what the option costs to operate or license.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
| Option | Structured predicates | SQL and database feature control | Entity requirement | Where authorization is enforced | Trade-off or cost noted in the article |
|---|---|---|---|---|---|
| Parenthesized SQL, as in this builder | Not structural; correctness depends on parenthesization and tests | Full control of the SQL text | None; Spring JDBC with records, no JPA | In the query itself, visible in application SQL | Relies on parenthesization and tests to stay correct |
| Spring Data Specifications / Criteria API | Yes; predicates compose structurally, which blocks this concatenation leak | Limited for the CTE needs of the example, according to the author | JPA entities required | Not stated | Not stated |
| jOOQ | Yes; conditions are rendered from an AST | Supports CTEs, window functions and SQL Server dialect features | Not stated; code generation adds a build step | Not stated | SQL Server use requires a commercial license |
| SQL Server Row-Level Security | Not applicable; the database applies a filter predicate | Applies to every query, including ad-hoc reports | Not stated | In the database; session context must be set on connection checkout | Visibility in application SQL and testing becomes harder; the article treats it as a second line of defense |
Choosing the abstraction
The article’s guidance is to match the abstraction to the scale of the problem:
| Situation | Suggested approach |
|---|---|
| One role, a few filters, a small internal audience | A straightforward parenthesized query, with tests |
| Many visibility cases, filters that keep arriving, and a leak with serious consequences | The composed design: one visibility strategy per role and one contributor per filter |
For a new project, the author would evaluate jOOQ first among the alternatives. The composed design earns its extra structure when precedence mistakes would be expensive to find later.
Deeper hierarchies
The parent-and-child condition in the example assumes a three-level hierarchy. Deeper trees may need a closure table or a recursive CTE for descendant lookup, as the article notes.
Example environment
The article states these versions for its demo. They describe the author’s environment and may not be the latest releases:
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →- Java 21
- 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 CU9
The code uses Spring JDBC with NamedParameterJdbcTemplate and records, and it does not use JPA.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
