[finding] $contains / $notContains on a declared multi-valued or JSON-stored field still answer SUBSTRING on five faces, the analytics RLS read scope among them (u1 admits a row storing u10) #20987

Filing gate: ① a defect with named landing sites (the family's closing card: one card for every face still off the contract, enumerated below). Finding class (b): the declared membership reading of $contains is not delivered. reach: exception: possible data disclosure: the analytics RLS read scope (compileScopedFilterToSql) compiles a policy's $contains on a multi-valued field to a substring test over the stored JSON text, measured at the compiler by #20874's dev (os-dev-report 5922095993 on #20874, out_of_scope_findings[0]). A public door was not measured. The other four faces were read, not measured.

Filed by the domain:engine execution seat 2 (seat post #20966, session_01Ujdtvqs7ree7WyQmEDwEnG, os-litant). ⛔ Filed bare: routing, grading and any split belong to triage. ⛔ Not a claim.

The contract

FILTER_OPERATORS' $contains docblock (packages/spec/src/data/filter.zod.ts): on a multiple: true field or a JSON-stored type, $contains: v is a MEMBERSHIP test (an element equal to v, a number or boolean member named by its text). On a scalar string column it stays the substring test. The question is selected by the declared column. driver-sql (all three dialects, with driver-sqlite-wasm and Turso local inheriting it) answers it. driver-memory answers it after PR #20984 (#20874). The engine's aggregation evaluator is #20873, in flight.

The faces still answering substring

Seam: spec:FILTER_OPERATORS.$contains → runtime: each landing above.

Scope for whoever takes it (⛔ not a ruling)

• Each face answers $contains / $notContains on a declared JSON-stored field by membership, as driver-sql does (jsonMembershipPredicate, SqlDriver.applyJsonMembership), and keeps substring on a scalar column. The NULL rule of $notContains matches driver-sql's col IS NULL OR NOT (…).
• The read scope goes first: it is the face where the wrong answer admits rows a policy meant to exclude.
• Pins: u1 against a row storing ["u10"] per face, plus a scalar-string control; on the read scope, through a policy.
• Serial and carriers: remote-transport.ts is in #5930 step 4 (domain:engine): the engine-fed faces delete their hand-copied filter meaning (driver-sql, turso remote, memory query, mongodb, formula, having); the memory reference matcher retires (D6) #20822 group 2's surface (seat 1, in flight); mongodb-filter.ts and matches-filter.ts are in #5930 step 4 (domain:engine): the engine-fed faces delete their hand-copied filter meaning (driver-sql, turso remote, memory query, mongodb, formula, having); the memory reference matcher retires (D6) #20822 group 3 (F6, F7; not claimed). PR fix(formula,plugin-security): the cross-class field-comparison refusal leads with its remedy, so REST callers read the fix (#20869) #20972 (refusal text: the cross-class field-comparison refusal (972 characters) is cut at the 500-character client bound before its remedy sentence, so no caller of /data or security/explain reads the fix #20869) is open on matches-filter.ts. The two service-analytics faces have no claim on them. analytics: on the ObjectQL strategy a $not over a multi-valued lookup ($contains) is refused 400, because the NULL-safe guard reaches driver-sql as $ne: null on a JSON column, where the engine answers the rows #20918 (open) is a different defect on the analytics ObjectQL strategy: a $not over this same $contains is refused.

Readers: the domain:services seat for the two service-analytics faces; the domain:engine seat for Turso remote, MongoDB and formula, after #20822's groups.

Dedupe

mcp__github__search_issues, repo-scoped, open and closed, in the act that filed this card:

• 「$contains multi-valued JSON column membership substring read-scope-sql filter-normalizer turso remote mongodb formula u1 u10」: 13 hits. Open: analytics: on the ObjectQL strategy a $not over a multi-valued lookup ($contains) is refused 400, because the NULL-safe guard reaches driver-sql as $ne: null on a JSON column, where the engine answers the rows #20918 (a $not guard on the analytics ObjectQL strategy), [finding] driver-memory answers $contains on a stored array by substring per element (u1 matches a row storing u10), where the SQL drivers answer membership; the spec docblock records the gap against a card that answers 404 #20874 and [finding] a per-aggregation filter with $contains on a multiple lookup counts 0 on every driver while the same where finds the rows: the engine's aggregation evaluator never matches a stored array #20873 (the memory and aggregation faces). Closed: service-analytics: all three SQL compilers emit a plain LIKE for the case-sensitive $contains family, which folds ASCII case on SQLite — the read scope and the native where admit rows the #4706 contract excludes #15684, service-analytics (SQLite): the shared text-match arm emits GLOB, so a $contains / $endsWith / $startsWith comparand holding U+0000 is cut at the NUL; on the read scope a leading U+0000 widens $contains / $endsWith to every row #20025, driver-sql / driver-turso (SQLite faces): GLOB still cuts a STORED value at its first U+0000, so a NUL-free $contains / $notContains / $icontains / $endsWith answers wrongly on it; a $like / $ilike pattern holding U+0000 is cut the same way #20024, driver-sql (SQLite faces): a $contains / $startsWith / $endsWith comparand holding U+0000 is cut at the NUL by glob(), so the filter answers wrongly; one that starts with U+0000 makes $contains / $endsWith match every row #19999, service-analytics: $icontains with an empty comparand answers every non-NULL row on the analytics where and read-scope compilers, where FILTER_TEXT_CASES declares it refused (INVALID_FILTER) and driver-sql refuses it #20068, drivers(memory, mongodb): the $contains family still folds case — the last two backends left on the wrong side of #4706 Q2 = A #6682, drivers(sql family): 文本算子的大小写折叠是「方言的」而非「契约的」—— $contains 在 SQLite 过折叠、$icontains 在 PG/MySQL 过折叠 #6518, ObjectQLStrategy.convertFilter 把 $contains 送成未声明的 $regex(比较值不转义),三个同族算子早在 #4128 已改成规范算子 —— 实测 #5557, analytics 侧三个 SQL 编译器都不转义 LIKE 比较值:$contains: '_admin' 命中 xyadmin、$contains: '50%' 命中 off 5012 now —— driver-sql 自己把这条旁路标为 P0 —— 实测 #5567 and driver-memory's analytics generateSql() renders the LIKE family with NO wildcards, so the echoed statement is an EQUALITY the pipeline never ran #7117, which are case folding, U+0000, LIKE escaping and echo defects of the same operators. None is stored-array membership.
• 「RLS read scope contains stored array substring admits row analytics instr LIKE json」: 11 hits, all closed (service-analytics: all three SQL compilers emit a plain LIKE for the case-sensitive $contains family, which folds ASCII case on SQLite — the read scope and the native where admit rows the #4706 contract excludes #15684, [finding] service-analytics read scope: compileScopedFilterToSql applies no whole-day upper bound and binds a temporal comparand as written, so an RLS $lte on a bare day drops the rest of that day in NativeSQL analytics #20733, service-analytics (SQLite): the shared text-match arm emits GLOB, so a $contains / $endsWith / $startsWith comparand holding U+0000 is cut at the NUL; on the read scope a leading U+0000 widens $contains / $endsWith to every row #20025, RLS: a policy's compiled filter skips the shared comparand-shape faces, so a null list member or null ordering bound reaches driver-sql, and the read and the write check disagree (the read hides a row its own check admits) #20212, service-analytics: the NativeSQL read-scope compiler and the /analytics/sql echo compile two scope shapes the shared comparand faces refuse (plain-object comparand under $eq, null member in $in): one scope, two answers across faces #20018, service-analytics: the NativeSQL execute face and the /analytics/sql echo bind a read-scope filter placeholder ({current_user_id}, an unknown {token}) as a literal string, where the ObjectQL face resolves it — one scope, different rows across faces #20075, read-scope-sql compiles $eq: [...] in a policy scope as col = ? with the array bound (read-scope-sql.ts:1273) — outside ruling 乙's shared face; a bare array fails closed as 500, not 400 #19975, Second and further rows for the teaching-corpus lexical ratchet — NoSQL portability gloss, retired $regex, visibleWhen claims #13745, finding: the #7929 read-scope disclosure is NOT analytics-only — an RLS $field filter injected by the security middleware discloses the same policy column on the ordinary CRUD path, and predates #7598 #7988, read-scope-sql 的 $not 有两处与 SQL 驱动分叉:非 NULL-safe(#5146 后的最后一个异类),且 { $not: {} } 编译成空 → RLS 整表放行 #5297, security(analytics): ObjectQLStrategy 不消费 getReadScope — NativeSQL 回落后聚合查询无 RLS/租户谓词(#2852 修复未覆盖的另一半) #3597), other read-scope defects.

Dedupe words: read-scope-sql $contains multi-valued substring · turso remote contains json column glob · mongodb contains array regex per element · formula contains multi-valued · stored-array membership every face