GetParentFolderId bypasses GetFolder — case-insensitive parent resolution can distort the folder tree on MySQL #608

Open
opened 2026-07-25 15:24:26 +02:00 by timothy · 0 comments
Owner

Carved out of the adversarial review of #491 (finding L5). Pre-existing, not reachable from #491's code, and correctly left out of scope there — but it is the same defect class that #491's H1 fixed, so it should not be left silent.

Problem

ErsatzTV.Infrastructure/Data/Repositories/LibraryRepository.cs:179-183 (GetParentFolderId) resolves a folder's parent with:

SelectOneAsync(lf => lf.Path, lf => lf.Path == parent.FullName, …)

which is OrderBy(Path).FirstOrDefault(predicate) — no ordinal re-check. On MySQL, LibraryFolder.Path is longtext under the server's default case-insensitive collation (the codebase acknowledges this: ErsatzTV.Scanner/Program.cs sets CaseInsensitiveCollation = "utf8mb4_general_ci"), so the predicate matches case-differing siblings and the OrderBy(Path) key itself collates case-insensitively. The tie between /x/Foo and /x/foo is therefore arbitrary.

#491 fixed exactly this class on the GetFolder path — narrow in SQL, OrderBy(Id) for determinism, then settle identity with string.Equals(..., StringComparison.Ordinal) — and routed the GetOrAddFolder lookup, the post-violation re-read and SetEtag through that single resolution point. GetParentFolderId was not routed through it.

Consequence

A child of /x/foo can be parented on the /x/Foo row. That distorts the folder tree and, downstream, the NOT EXISTS (… WHERE ParentId = …) guard in CleanEtagsForLibraryPath (LibraryRepository.cs:158-160), which decides whether a folder's etag may be cleared.

Bounded relative to #491's findings: it cannot corrupt PathHash, cannot violate the new unique index, and cannot throw. MySQL-only — SQLite's = is already binary. Prod runs SQLite, so this is latent here.

Fix direction

Route parent resolution through the same exact-match resolver GetFolder now uses, rather than re-deriving it. #491's polish pass is lifting that in-memory settle into a pure static (ResolveExact(IReadOnlyList<LibraryFolder>, string)) precisely so it can be reused and unit-tested without a provider — reuse that seam rather than adding a second copy of the logic.

While in there, audit for any other Path-equality lookup that resolves a LibraryFolder identity and does not apply the ordinal re-check; the review confirmed GetFolder's three call sites are covered, but the sweep was not exhaustive beyond them.

Done-when

  • Parent resolution uses the same exact-match resolver as GetFolder, not a second implementation
  • Every remaining LibraryFolder identity lookup by Path audited for the same gap, with the result stated
  • Covered by a provider-independent unit test over the shared resolver (a candidate list containing both spellings)
  • Adversarial review passed

Ref: #491 review, finding L5; decision scan.libraryfolder-unique-identity. Related: #488.

Carved out of the adversarial review of #491 (finding L5). Pre-existing, not reachable from #491's code, and correctly left out of scope there — but it is the same defect class that #491's H1 fixed, so it should not be left silent. ## Problem `ErsatzTV.Infrastructure/Data/Repositories/LibraryRepository.cs:179-183` (`GetParentFolderId`) resolves a folder's parent with: ```csharp SelectOneAsync(lf => lf.Path, lf => lf.Path == parent.FullName, …) ``` which is `OrderBy(Path).FirstOrDefault(predicate)` — no ordinal re-check. On MySQL, `LibraryFolder.Path` is `longtext` under the server's default **case-insensitive** collation (the codebase acknowledges this: `ErsatzTV.Scanner/Program.cs` sets `CaseInsensitiveCollation = "utf8mb4_general_ci"`), so the predicate matches case-differing siblings and the `OrderBy(Path)` key *itself* collates case-insensitively. The tie between `/x/Foo` and `/x/foo` is therefore arbitrary. #491 fixed exactly this class on the `GetFolder` path — narrow in SQL, `OrderBy(Id)` for determinism, then settle identity with `string.Equals(..., StringComparison.Ordinal)` — and routed the `GetOrAddFolder` lookup, the post-violation re-read and `SetEtag` through that single resolution point. `GetParentFolderId` was not routed through it. ## Consequence A child of `/x/foo` can be parented on the `/x/Foo` row. That distorts the folder tree and, downstream, the `NOT EXISTS (… WHERE ParentId = …)` guard in `CleanEtagsForLibraryPath` (`LibraryRepository.cs:158-160`), which decides whether a folder's etag may be cleared. Bounded relative to #491's findings: it cannot corrupt `PathHash`, cannot violate the new unique index, and cannot throw. MySQL-only — SQLite's `=` is already binary. Prod runs SQLite, so this is latent here. ## Fix direction Route parent resolution through the same exact-match resolver `GetFolder` now uses, rather than re-deriving it. #491's polish pass is lifting that in-memory settle into a pure static (`ResolveExact(IReadOnlyList<LibraryFolder>, string)`) precisely so it can be reused and unit-tested without a provider — reuse that seam rather than adding a second copy of the logic. While in there, audit for any *other* `Path`-equality lookup that resolves a `LibraryFolder` identity and does not apply the ordinal re-check; the review confirmed `GetFolder`'s three call sites are covered, but the sweep was not exhaustive beyond them. ## Done-when - [ ] Parent resolution uses the same exact-match resolver as `GetFolder`, not a second implementation - [ ] Every remaining `LibraryFolder` identity lookup by `Path` audited for the same gap, with the result stated - [ ] Covered by a provider-independent unit test over the shared resolver (a candidate list containing both spellings) - [ ] Adversarial review passed Ref: #491 review, finding L5; decision `scan.libraryfolder-unique-identity`. Related: #488.
timothy added the bugpriority: low labels 2026-07-25 15:24:26 +02:00
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: timothy/ersatztv#608