Export includes soft-deleted entries; CSV cells not guarded against formula injection #94

New issue

Closed

opened 2026-07-15 19:51:29 +00:00 by claude-bot · 1 comment

claude-bot commented

2026-07-15 19:51:29 +00:00

Contributor

Severity: Low · Confidence: High · Effort: XS · Category: security

Evidence

api/src/routes/export.ts:20-49 (fetchAllEntries) and 53-77 (fetchMotifWithDetail) omit deleted_at IS NULL.
api/src/routes/export.ts:104-111 - escapeCSV doesn't neutralize leading = + - @.

Problem
Owner exports include trashed rows, and CSV cells beginning with a formula character can execute if opened in a spreadsheet.

Impact
Self-targeted (owner exports own data). Low, but the soft-delete leak is a correctness inconsistency and CSV injection is a trivially-avoided footgun if the export is shared.

Fix
Add deleted_at IS NULL to export queries; prefix formula-leading CSV cells with a single quote.

Acceptance criteria

Exported JSON/CSV excludes soft-deleted entries and motifs.
CSV cells starting with = + - @ are prefixed so spreadsheets don't evaluate them.

Filed from the 2026-07-15 codebase audit. Full report: docs/.internal/report-2026-07-15.md (gitignored).

**Severity:** Low · **Confidence:** High · **Effort:** XS · Category: security **Evidence** - `api/src/routes/export.ts:20-49` (`fetchAllEntries`) and `53-77` (`fetchMotifWithDetail`) omit `deleted_at IS NULL`. - `api/src/routes/export.ts:104-111` - `escapeCSV` doesn't neutralize leading `= + - @`. **Problem** Owner exports include trashed rows, and CSV cells beginning with a formula character can execute if opened in a spreadsheet. **Impact** Self-targeted (owner exports own data). Low, but the soft-delete leak is a correctness inconsistency and CSV injection is a trivially-avoided footgun if the export is shared. **Fix** Add `deleted_at IS NULL` to export queries; prefix formula-leading CSV cells with a single quote. **Acceptance criteria** - [ ] Exported JSON/CSV excludes soft-deleted entries and motifs. - [ ] CSV cells starting with = + - @ are prefixed so spreadsheets don't evaluate them. --- _Filed from the 2026-07-15 codebase audit. Full report: `docs/.internal/report-2026-07-15.md` (gitignored)._

claude-bot added this to the v7.2.0 - Reliability, data integrity & docs milestone

2026-07-15 19:51:29 +00:00

claude-bot added the

bug

label

2026-07-15 19:51:29 +00:00

rbrooks referenced this issue from a commit

2026-07-17 02:28:29 +00:00

fix(v7.2): wave 1 — data integrity (#98, #93, #94, #96)

claude-bot commented

2026-07-17 02:31:59 +00:00

Author

Contributor

Fixed in 763aa0b (v7.2.0 wave 1).

Soft-delete — added deleted_at IS NULL to all four export queries: fetchAllEntries, the fetchMotifWithDetail motif lookup, its entries query, and fetchAllMotifs.

One extra leak the issue didn't name: fetchMotifWithDetail's connections query projects LEFT(se.content, 120) / LEFT(te.content, 120) and matches on source_entry_id = ANY(...) OR target_entry_id = ANY(...). A connection from a live entry to a trashed one therefore embedded the trashed entry's content in the export, even after the entries query was filtered. Added AND se.deleted_at IS NULL AND te.deleted_at IS NULL.

CSV formula injection — escapeCSV now prefixes a single quote when a cell starts with = + - @ or a leading tab/CR (spreadsheets strip leading whitespace before parsing, so \t=cmd|… is the same attack). Applied before the quoting branch so the quotes wrap the escaped value:

if (/^[=+\-@\t\r]/.test(str)) str = `'${str}`;

Acceptance criteria:

Exported JSON/CSV excludes soft-deleted entries and motifs.
CSV cells starting with = + - @ are prefixed so spreadsheets don't evaluate them.

Known trade-off: this also prefixes genuine negative numbers (-5 → '-5). That's the standard OWASP guidance and the current CSV columns are text/timestamps, so it's not worth a type-aware exception here.

CI run 191 green (241/241).

Fixed in `763aa0b` (v7.2.0 wave 1). **Soft-delete** — added `deleted_at IS NULL` to all four export queries: `fetchAllEntries`, the `fetchMotifWithDetail` motif lookup, its entries query, and `fetchAllMotifs`. **One extra leak the issue didn't name:** `fetchMotifWithDetail`'s connections query projects `LEFT(se.content, 120)` / `LEFT(te.content, 120)` and matches on `source_entry_id = ANY(...) OR target_entry_id = ANY(...)`. A connection from a *live* entry to a *trashed* one therefore embedded the trashed entry's content in the export, even after the entries query was filtered. Added `AND se.deleted_at IS NULL AND te.deleted_at IS NULL`. **CSV formula injection** — `escapeCSV` now prefixes a single quote when a cell starts with `= + - @` **or a leading tab/CR** (spreadsheets strip leading whitespace before parsing, so `\t=cmd|…` is the same attack). Applied before the quoting branch so the quotes wrap the escaped value: ```ts if (/^[=+\-@\t\r]/.test(str)) str = `'${str}`; ``` **Acceptance criteria:** - [x] Exported JSON/CSV excludes soft-deleted entries and motifs. - [x] CSV cells starting with `= + - @` are prefixed so spreadsheets don't evaluate them. Known trade-off: this also prefixes genuine negative numbers (`-5` → `'-5`). That's the standard OWASP guidance and the current CSV columns are text/timestamps, so it's not worth a type-aware exception here. CI run 191 green (241/241).

claude-bot closed this issue

2026-07-17 02:32:15 +00:00