When the ALL Sentinel Emptied the Admin List
One ALL string from the middleware was treated as a column value, and the announcement list lost every scoped record.
TL;DR
A super admin's announcement list returned zero rows because the ALL sentinel from auth was passed straight into SQL as a literal division filter. Since global rows use NULL, only IS NULL catches them, so nothing matched. Fix: treat ALL and empty string as no-filter at the repository boundary, backed by a regression test.
The announcement list in a city portal's admin panel suddenly shrank. The super admin account, which should see every record, received a list with zero rows. Data carrying specific division values like culture or sports never appeared, even though a manual query proved the rows existed in the database.
The regression was flagged severity HIGH in the QA report. The first suspicion pointed at the authentication middleware: maybe the token did not carry the right scope. Inspection said otherwise. The token was valid, the signature correct, and the scope it sent matched expectations: the string ALL for super admin. The "correct" value itself turned out to be the trigger.
The root cause sat in how the repository translated scope into a SQL filter. The middleware places the string ALL in the scope field for super admins. The repository treated it as a literal division value. The resulting query became bidang = 'ALL', and not a single row in the database carries that value.
Why the two layers stopped talking
The same field carried two semantics. In the authentication layer, ALL is a sentinel: a special value meaning "every division", not the name of a division. In the data layer, a global row is marked NULL. The MySQL manual explains that NULL is never true in a comparison with any value, including NULL itself, which is why searching for it requires the IS NULL operator [1]. The filter in question exploits that property: bidang IS NULL OR bidang = ?, where NULL rows count as global and always appear. Once the parameter was filled with the string ALL, the same pattern only looked for rows whose division was literally "ALL", which never exist.
This sentinel was actually born at the auth SQL boundary. The login response uses COALESCE(bidang,'ALL'), so a super admin without a division value carries the string ALL home in their session. That layer was correct. What was missing was the conversion when that value arrived at the query layer: one value, two assumed meanings across two layers.
Converting at the query entrance
The fix added no new filter mode. The repository simply acknowledged that the field now has two values meaning no-filter: the empty string and the ALL sentinel. Outside those two values, the scope filter runs as before.
if q.Bidang != "" && !scope.IsGlobal(q.Bidang) {
where += " AND (a.bidang IS NULL OR a.bidang = ?)"
args = append(args, q.Bidang)
}The IsGlobal function itself is a single comparison: true when the value is ALL, false otherwise. That simplicity is the point. A sentinel conversion should be readable at a glance, not buried under layered logic.
The two-layer design here is deliberate. The repository is intentionally fail-open for no-filter values, while the middleware is fail-closed: an unknown division means no manageable modules at all. OWASP states the principle plainly: a security mechanism should fail through the same execution path as disallowing access [2]. Both layers satisfy that principle in different places, and the bug happened exactly at the seam between them.
A regression test as the lock
Without a test, a fix like this is easily undone by the next refactor. The integration test is simple: seed one global announcement with a NULL division and one with the culture division, then run three scenarios. The ALL scope must see both. The culture scope must also see both, including the global row. The sports scope sees only one, the global row. The Go testing framework provides the pattern: failures are signaled through T.Error and its siblings inside TestXxx functions executed by go test [3].
Whoever later changes the query and forgets the sentinel will see the first scenario go red immediately. The easiest way to prove NULL's behavior is to try it: create a small table with one nullable column, insert one NULL row and one ordinary row, then run SELECT ... WHERE col = NULL. The result is zero rows, and the NULL row only appears once the query uses IS NULL.
This pattern is not limited to divisions. Multitenant schemas use the same shape: a tenant_id of NULL means platform-owned, while the application has internal roles allowed to see everything. Whenever those two semantics meet in one column, the conversion at the boundary is the first question worth asking, not after data disappears. Converting the sentinel at the query boundary is not a one-line patch; it is a design decision that states explicitly which value means "all", and the test is what keeps that decision from being forgotten.
Sources: