[GH-ISSUE #8911] [Feature] Inspect SQLite database files and WAL state as read-only Evidence #7786

New issue

Open

opened 2026-11-04 15:15:31 +00:00 by bsakaguchi · 1 comment

bsakaguchi commented

2026-11-04 15:15:31 +00:00

Owner

Originally created by @jayykernel on GitHub (Nov 4, 2026).
Original GitHub issue: https://github.com/morluto/rea/issues/8911

Summary

Add an offline, read-only inspector for SQLite database artifacts so analysts can examine schemas, tables, indexes, constraints, query plans, and transaction-journal state without opening a database through an application or modifying the source files.

Motivation

SQLite databases are common in desktop applications, mobile apps, browsers, and embedded systems. A database file may be accompanied by a write-ahead log (.wal) and shared-memory (.shm) file; inspecting only the main file can miss committed-but-uncheckpointed changes or produce an incomplete picture of the captured state.

Proposed behavior

Accept a standalone SQLite database and, when supplied, its matching .wal and .shm sidecars.
Parse and report database header/version details, page size, encoding, schema objects, tables, columns, indexes, foreign keys, and declared constraints.
Expose table contents through bounded, paginated, read-only views with explicit row and byte limits.
Explain WAL presence, checkpoint state, salts, frame counts, and consistency findings where safely derivable.
Preserve provenance for every finding, including source file, page/frame, table, and row locator when available.
Offer safe schema and metadata export in a machine-readable format.

Safety and resource boundaries

Never execute SQL supplied by the artifact or invoke application-defined functions, extensions, triggers, or virtual-table modules.
Open inputs in immutable/read-only mode; never checkpoint, recover by writing, vacuum, or alter the original files.
Treat malformed pages, oversized values, and hostile schema metadata as untrusted input; enforce bounded parsing and cancellation.
Redact or classify likely secrets and personal data in previews, while keeping access to raw values explicit and controlled.
If WAL and database files do not form a consistent set, report the limitation instead of silently presenting a merged state as authoritative.

Acceptance criteria

Inspect a valid standalone SQLite database without changing its hash.
Inspect a consistent database/WAL pair and distinguish main-file state from WAL-backed state.
Report schema, indexes, foreign keys, and bounded row previews with stable provenance.
Handle missing, mismatched, truncated, and corrupt WAL sidecars with typed diagnostics.
Enforce configurable page, row, value-size, and total-work limits.
Include fixtures for rollback-journal databases, WAL databases, empty databases, and malformed inputs.
Add tests proving that input files remain byte-for-byte unchanged.

Out of scope

Executing arbitrary SQL, modifying records, repairing databases, or loading SQLite extensions.
Application-specific decryption or bypassing database encryption.

Suggested implementation

Start with a format-level parser and an explicit read-only Evidence model. Keep WAL interpretation separate from schema and row decoding so incomplete sidecar sets can be represented accurately.

Originally created by @jayykernel on GitHub (Nov 4, 2026). Original GitHub issue: https://github.com/morluto/rea/issues/8911 ## Summary Add an offline, read-only inspector for SQLite database artifacts so analysts can examine schemas, tables, indexes, constraints, query plans, and transaction-journal state without opening a database through an application or modifying the source files. ## Motivation SQLite databases are common in desktop applications, mobile apps, browsers, and embedded systems. A database file may be accompanied by a write-ahead log (`.wal`) and shared-memory (`.shm`) file; inspecting only the main file can miss committed-but-uncheckpointed changes or produce an incomplete picture of the captured state. ## Proposed behavior - Accept a standalone SQLite database and, when supplied, its matching `.wal` and `.shm` sidecars. - Parse and report database header/version details, page size, encoding, schema objects, tables, columns, indexes, foreign keys, and declared constraints. - Expose table contents through bounded, paginated, read-only views with explicit row and byte limits. - Explain WAL presence, checkpoint state, salts, frame counts, and consistency findings where safely derivable. - Preserve provenance for every finding, including source file, page/frame, table, and row locator when available. - Offer safe schema and metadata export in a machine-readable format. ## Safety and resource boundaries - Never execute SQL supplied by the artifact or invoke application-defined functions, extensions, triggers, or virtual-table modules. - Open inputs in immutable/read-only mode; never checkpoint, recover by writing, vacuum, or alter the original files. - Treat malformed pages, oversized values, and hostile schema metadata as untrusted input; enforce bounded parsing and cancellation. - Redact or classify likely secrets and personal data in previews, while keeping access to raw values explicit and controlled. - If WAL and database files do not form a consistent set, report the limitation instead of silently presenting a merged state as authoritative. ## Acceptance criteria - [ ] Inspect a valid standalone SQLite database without changing its hash. - [ ] Inspect a consistent database/WAL pair and distinguish main-file state from WAL-backed state. - [ ] Report schema, indexes, foreign keys, and bounded row previews with stable provenance. - [ ] Handle missing, mismatched, truncated, and corrupt WAL sidecars with typed diagnostics. - [ ] Enforce configurable page, row, value-size, and total-work limits. - [ ] Include fixtures for rollback-journal databases, WAL databases, empty databases, and malformed inputs. - [ ] Add tests proving that input files remain byte-for-byte unchanged. ## Out of scope - Executing arbitrary SQL, modifying records, repairing databases, or loading SQLite extensions. - Application-specific decryption or bypassing database encryption. ## Suggested implementation Start with a format-level parser and an explicit read-only Evidence model. Keep WAL interpretation separate from schema and row decoding so incomplete sidecar sets can be represented accurately.

bsakaguchi commented

2026-11-04 20:25:28 +00:00

Author

Owner

@harsh0728 commented on GitHub (Nov 4, 2026):

Hi @jayykernel
I am working on this issue.

@harsh0728 commented on GitHub (Nov 4, 2026): Hi @jayykernel I am working on this issue.

bsakaguchi referenced this issue

2026-11-05 01:47:58 +00:00

[PR #7357] [CLOSED] fix(environment): retain conflicting scenario MIME uncertainty #7827

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

morluto/rea#7786

No description provided.

Rows
Columns