Why can dynamically composed AND/OR filter fragments leak rows, and how do you compose them safely?

## The failure Any code that assembles a `WHERE` clause from parts — a search endpoint with optional filters, a saved-report builder, a repository layer that appends conditions — is building one boolean expression out of fragments written at different times by different people. Each fragment is correct in isolation. The composition is where the bug lives. ```sql -- scope fragment: tenant_id = 7 -- status fragment: status = 'open' OR status = 'closed' SELECT * FROM tickets WHERE tenant_id = 7 AND status = 'open' OR status = 'closed'; ``` Because `AND` binds tighter than `OR`, this parses as: ```sql WHERE (tenant_id = 7 AND status = 'open') OR status = 'closed' ``` Every closed ticket in the database is returned, from every tenant. The query does not error, it returns more rows than expected, and the extra rows are precisely the ones the scope predicate existed to exclude. When the scope is a tenant, a customer or an owning user, this is a data-exposure defect rather than a reporting nuisance. ## Why it survives review The fragment authors are not wrong. Whoever wrote `status = 'open' OR status = 'closed'` wrote a valid predicate; whoever wrote `tenant_id = 7` wrote a valid predicate. The defect exists only in the concatenated string, which frequently no human ever reads — it is built at runtime from whichever optional filters the caller supplied. Worse, the combination that breaks may require two specific optional filters to be active at once, so it is invisible until a user picks that combination. ## The compositional rule The fix is a construction rule, not a review habit: 1. **Parenthesize every fragment as it is produced.** A fragment's text is `(` + predicate + `)`. Then its internal connectives cannot escape. 2. **Join fragments with `AND` only.** If a filter needs alternatives, the alternatives live *inside* one fragment, already wrapped. 3. **Never let a caller pass raw predicate text into the join.** Fragments come from the builder's own vocabulary, so the parenthesization invariant holds by construction. Applied to the example: ```sql SELECT * FROM tickets WHERE (tenant_id = 7) AND (status = 'open' OR status = 'closed'); ``` With this rule, a fragment added next year cannot change the meaning of a fragment written today. That is the property you actually want — local correctness composing into global correctness. ## Keep the mandatory scope out of the composable set A tenant or ownership predicate is not an optional filter and should not be an element of the same list the optional filters go into. Emit it as a fixed prefix that the composition cannot touch, or better, remove it from the composition problem entirely by scoping at a level the builder cannot bypass — a view that already restricts rows, or a required parameter the builder always applies. The general principle: a security-relevant predicate should not depend on a string-assembly step being right. ## The 1=1 idiom, and its limit A common trick is to start the clause with a constant true predicate so every fragment can be appended uniformly: ```sql WHERE 1 = 1 AND (tenant_id = 7) AND (status = 'open' OR status = 'closed') ``` This removes the "is this the first fragment?" special case that produces stray `AND`s and dangling clauses. It is a readability and code-simplicity aid only — it does nothing about precedence. Without the inner parentheses, `WHERE 1 = 1 AND tenant_id = 7 AND status = 'open' OR status = 'closed'` is just as broken. ## Formatting that exposes the shape When the generated SQL is logged or shown in a debug view, format it one condition per line with the connective leading: ```sql WHERE (tenant_id = 7) AND (created_on >= DATE '2026-01-01') AND (status = 'open' OR status = 'closed') ``` A stray top-level `OR` is then immediately visible in the log, which turns an invisible composition bug into something a developer notices while debugging something else. ## Test it like a boundary, not like a filter Unit tests for filter builders usually assert the happy path: "filtering by status returns open tickets". The test that catches this class of bug asserts the *invariant*: for every combination of optional filters, the result contains no row outside the caller's scope. Seed two tenants, run the combinations, assert that the foreign tenant's rows never appear. That test fails loudly on the day someone adds an OR fragment without parentheses. ## What interviewers listen for They want the precedence explanation applied to composition, the recognition that the leaked rows are exactly the scoped-out ones, a construction-level fix (parenthesize by construction, join with AND) rather than "be careful", the point that mandatory scoping should not live in the composable set at all, and a test strategy that asserts the invariant across filter combinations.