MemberJunction / MemberJunction/MJ
RLS: multiple row-level-security filters are OR'd without parentheses, so a single-record query returns the wrong record (5.51.0)
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
## Summary
`EntityInfo.GetUserRowLevelSecurityWhereClause()` joins multiple RLS filters with `OR` but **does not wrap the resulting group in parentheses**. When the caller holds **two or more** read RLS filters on an entity, the generated single-record resolver produces SQL whose primary-key predicate is silently dropped, and **the query returns a different record than the one requested**.
Confirmed on **MemberJunction 5.51.0**.
## The defect
`packages/MJCore/src/generic/entityInfo.ts` (dist: `entityInfo.js:1645-1651`):
```ts
let sRLSSQL = '';
userRLS.forEach((rls) => {
if (sRLSSQL.length > 0) sRLSSQL += ' OR ';
sRLSSQL += `(${rls.MarkupFilterText(user)})`;
});
return sRLSSQL.length > 0
? `${returnPrefix && returnPrefix.length > 0 ? returnPrefix + ' ' : ''}${sRLSSQL}`
: '';
```
Each individual filter is parenthesised, but the **OR-joined group is not**. With `returnPrefix = 'AND'` the method returns:
```
AND (filter1) OR (filter2)
```
The generated single-record resolver appends that to its key predicate:
```sql
WHERE ID = @p0 AND (filter1) OR (filter2)
```
SQL Server binds `AND` more tightly than `OR`, so this evaluates as:
```sql
WHERE (ID = @p0 AND filter1) OR (filter2)
```
The `ID = @p0` predicate no longer constrains the second branch. The query returns every row matching `filter2`, and the resolver hands back `rows[0]` — an arbitrary row the caller did not ask for.
## Why it hides
It only misbehaves when a **second** filter actually matches rows:
- **One filter** -> `WHERE ID = @p0 AND (filter1)` -> correct.
- **RLS-exempt caller** (any role with an unfiltered permission row) -> the method returns `''` early -> correct.
So it is invisible for single-filter users and for anyone exempt, which is most users on a typical install.
## Reproduction
Entity `LCA.LearnerIssue`, caller holding two read RLS filters on it (one scoping to their own person rows, one scoping to their organization's members):
```graphql
query { LCALearnerIssue(ID: "CB69168B-3D64-4A8E-9C1A-1FE68C1F5C4E") { ID Title } }
```
Observed, on two different requested ids:
```
request CB69168B-3D64-4A8E-9C1A-1FE68C1F5C4E -> returned CB69168B-2F36-4AEA-802D-070BABD661F6
request 11E9379C-9DE2-4C39-8A73-1B2D8A6E0B21 -> returned CB69168B-2F36-4AEA-802D-070BABD661F6
```
Both return the same record, and **neither matches the id requested**. The same caller against the same entity with `RunView` / `RunDynamicView` and `ExtraFilter: "ID = '...'"` returns exactly the correct single row.
## Affected lanes
- Every generated single-record resolver, `(ID: ...)`.
- Every generated FK-array FieldResolver, e.g. `WHERE CourseID = @p0 AND (f1) OR (f2)` returns rows unrelated to that course.
- **Not** `RunView` / `RunDynamicView` — verified returning the correct single row for the same caller and entity.
This is reachable from ordinary product code, not only hand-written queries: `GraphQLDataProvider.Load()` — the client path behind every browser-side `BaseEntity.Load(id)` — builds exactly this single-record query. A UI opening one record can be handed a different one.
## Impact
- **Data integrity: high.** A UI can silently display the wrong record with no error.
- **Disclosure: bounded but real.** The rows returned are those matching the second filter, so a caller can receive a record they would not have matched under the intended `AND` of key + filters. In our deployment cross-tenant reads stayed closed because both filters are tenant-scoped, but that is a property of our filters, not a guarantee of the code path.
## Suggested fix
Wrap the OR-joined group:
```ts
return sRLSSQL.length > 0
? `${returnPrefix && returnPrefix.length > 0 ? returnPrefix + ' ' : ''}(${sRLSSQL})`
: '';
```
That yields `AND ((filter1) OR (filter2))`, which composes correctly with any preceding predicate. It is a no-op for the single-filter case (`AND ((filter1))`).
It may also be worth defensively parenthesising where generated SQL concatenates this clause, so a future caller passing a different `returnPrefix` cannot reintroduce the same precedence bug.
## Note on how this became visible
Most installs are unaffected because most users hold a role with at least one unfiltered permission row and are therefore RLS-exempt. We hit it because we deliberately give end users narrow, fully-filtered roles: an administrator persona legitimately holds a learner filter (own rows) **and** an administrator filter (organization members) on the same entity. Any deployment tightening its roles in that direction will meet the same behaviour.
Contributor guide
Research direction
Start in packages/MJCore/src/generic/entityInfo.ts at EntityInfo.GetUserRowLevelSecurityWhereClause(), then trace the generated single-record resolver and GraphQLDataProvider.Load() callers. Verify the SQL composition for multiple RLS filters, including generated FK-array FieldResolvers, and confirm that requested key predicates remain effective while single-filter and exempt cases stay unchanged.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- graphql, sql, typescript
- Domain
- backend-api-design, security
- Issue type
- Bug
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 78/100