15 tenant leaks in 75 queries

In a multi-tenant SaaS, every query on a customer table needs WHERE tenant_id = ?. Forget it once and one customer sees another customer's orders. Code review rarely catches it, because a missing filter is an absence, and tests stay green, because test data usually holds a single tenant.

QueryFence checks the SQL your integration tests actually send to the database and fails the build on the statement that forgets the tenant filter. Before releasing 0.1.0 we wanted to know how it behaves on code it was not designed around, so we pointed it at Northwind Shop: a separate sample application in the QueryFence repository, written in the style of a real Spring Boot codebase that grew for a few years, with Spring Data JPA, JPQL, native queries and MyBatis mappers.

How we ran it

We followed our own adoption guide from the outside, the way a new user would:

1. Add the queryfence-spring-test dependency.
2. List the tenant tables from information_schema and write a policy with mode: REPORT, so nothing fails yet.
3. Run the tests the application already had. No test was added or changed.
4. Read the report and decide, finding by finding, whether it is a real leak.

The numbers

queryfence.yml [REPORT]: 20 findings, 75 statements, 62 tests

Verdict
  real leak             15
  false positive         4
  deliberate exception   1

Every finding came from a different place in the code, so each one is its own fix. Just as important: every query the application wrote correctly passed. Derived queries with the tenant, pagination and its count(*), joins filtered through the other side, correlated exists subqueries, a CTE with the filter inside, and MyBatis dynamic SQL that keeps the tenant all came through clean.

Leaks worth a closer look

Most of the 15 are the usual suspects: a findByStatus left over from the first prototype, a findAll() on a tenant table, a bulk update that touches every tenant, a findByNumber that relies on numbers being unique, which is not the same as isolation, and a findById from a support console. Two are the kind a reviewer almost never sees:

A MyBatis filter that disappears at runtime

The mapper builds its WHERE clause with <if>. When the tenant parameter is null, the condition silently drops out and the query returns every tenant's orders. The same mapper statement is safe on one call and unsafe on the next, so reading the XML does not reveal it. Only checking the statement that actually ran does.

The second half of a UNION ALL

A settlement report has two branches. The first carries p.tenant_id = ?, so the query looks filtered at a glance. The second branch sums open invoices across all tenants. QueryFence checks each branch on its own, so it flagged the second one.

Where QueryFence was wrong

Four findings were false alarms, 20% of findings or about 5% of statements:

• Two ORM association loads. A fetch join and a lazy load of child rows that are only reachable through a parent that was already filtered. The engine looks at one statement at a time, so it asks for the filter on the child too. Mapping the tenant on the child entity (@TenantId) removes both.
• A derived table. The inner query exposes tenant_id and the outer query filters on it. That is safe, but 0.1 does not push filters into subqueries yet.
• A parser bug. An unqualified column named number does not parse in JSqlParser, so the statement was reported as unparseable. Qualifying it (i.number) works around it.

QueryFence fails closed on purpose: when it cannot prove a statement safe, it says so instead of staying quiet. We would rather show you four false alarms than miss one leak.

What we fixed before 0.1.0

The exercise also found problems in QueryFence itself, and the main ones were fixed before release: the onUnparseable setting is now honoured, the console summary now reaches Maven users, and an unparseable statement now names the protected tables it mentions instead of hiding them.

Try it

QueryFence 0.1.0 is on Maven Central. For a Spring Boot project, add one test dependency and a policy file. There is no annotation to add and no test code to change.

<dependency>
  <groupId>com.steelreed</groupId>
  <artifactId>queryfence-spring-test</artifactId>
  <version>0.1.0</version>
  <scope>test</scope>
</dependency>

Start in REPORT mode on an existing project, read what it finds, then switch to FAIL. The quick start takes about five minutes, the full report lists every finding, and the code is on GitHub. If you find a query shape that slips past it, please tell us: that is the bug we most want to hear about.