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)

Open Beginner friendly
#4,078 1 comment 0 reactions 0 assignees View on GitHub
bug priority: high
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.