Skip to content

README.md

Latest commit

 

History

History
383 lines (336 loc) · 18.9 KB

README.md

File metadata and controls

383 lines (336 loc) · 18.9 KB
Raw

Test Data — tests/data/

Committed SQLite fixtures shared by both workspace members. core/tests/*.rs and forensic/tests/*.rs reach them with the symmetric relative path include_bytes!("../../tests/data/<file>").

For the per-repo index (classification + oracle methodology) see ../../docs/corpus-catalog.md; for the deleted-record carving validation see ../../docs/validation.md. The gitignored independent-oracle corpus is documented separately in ../../tests-oracle-corpus/README.md.

The loose top-level files here are SYNTHETIC — built locally with the real sqlite3 engine (SQLite 3.45.3 2024-04-15, REAL engine / synthetic data). The generator command IS the provenance; there is no download URL.

The committed real-world third-party corpora live in subdirectories, each with its own provenance README (source, NIST/author hashes, licence, ground truth):

  • nemetz/ — Nemetz SQLite Forensic Corpus v2.0 (141 DBs, CC0) — deleted-record ground truth + anti-forensic robustness.
  • cfreds/ — NIST CFReDS / CFTT SQLite test sets (SFT-01 encodings, SFT-03 deleted/modified records) — U.S. Government public domain, MD5-verified against NIST-published hashes.
  • sharifctf/ — a real damaged-header CTF database (robustness).
  • journal/ — real sqlite3-engine rollback--journal scenarios (a hot DML journal and a committed-DDL PERSIST journal) for the audit_journal anomaly arms — minted with the public-domain SQLite engine.
  • paper_fp/ — REAL-engine REPLICATIONS of the three false-positive scenarios from the 2025 SQLite-recovery survey (Lee, Park, Lee & Choi, FSI:DI 55, art. 302031): B-tree rebalancing, overwritten same-schema table, and WAL+secure_delete. CC0; consumed by forensic/tests/paper_fp_scenarios.rs and docs/competitive-landscape.md.

places.db

  • Source: SYNTHETIC — system sqlite3 CLI.

  • Identity: single-table moz_places DB exercising every SQLite storage class (Integer/Real/Text/Blob/Null) and the INTEGER PRIMARY KEY rowid-alias rule. Pre-existing core-reader spike fixture.

  • Generator:

    sqlite3 tests/data/places.db <<'SQL'
CREATE TABLE moz_places (
  id INTEGER PRIMARY KEY,
  url TEXT NOT NULL,
  title TEXT,
  visit_count INTEGER DEFAULT 0,
  last_visit_date INTEGER,
  frecency REAL
);
INSERT INTO moz_places (url, title, visit_count, last_visit_date, frecency) VALUES
 ('https://www.rust-lang.org/', 'Rust Programming Language', 5, 1700000000000000, 2000.5),
 ('https://github.com/', 'GitHub', 12, 1700000100000000, 5500.0),
 ('https://news.ycombinator.com/', 'Hacker News', 3, NULL, -1.0),
 ('https://en.wikipedia.org/wiki/SQLite', 'SQLite - Wikipedia', 1, 1700000200000000, NULL),
 ('https://example.com/', NULL, 0, 1700000300000000, 0.0);
SQL

  • md5: f07a69d05358f227e2120080370bbb6b — 8192 bytes (2 pages, 4096-byte page).

  • Notable contents: frecency as Real; one NULL title, one NULL last_visit_date, one negative frecency. moz_places root page = 2. Used by core/tests/real_db.rs, core/tests/freelist.rs, forensic/tests/audit_realdb.rs.

overflow.db

  • Source: SYNTHETIC — Python sqlite3 module.

  • Identity: one notes row whose ~12 KB TEXT body spills onto an overflow-page chain, plus one small inline row. Used by core/tests/overflow.rs.

  • Generator:

    python3 - <<'PY'
import sqlite3
con = sqlite3.connect('overflow.db')
con.executescript("PRAGMA page_size=4096; PRAGMA auto_vacuum=NONE;")
con.execute("CREATE TABLE notes(id INTEGER PRIMARY KEY, body TEXT)")
big = "OVERFLOW_PAYLOAD_" + ("ABCDEFGHIJ" * 1200)  # 12017 chars
con.execute("INSERT INTO notes VALUES (1, ?)", (big,))
con.execute("INSERT INTO notes VALUES (2, 'small row')")
con.commit(); con.close()
PY

  • md5: 1c17320320a173fb5968c598f9df7373 — 16384 bytes (4 pages).

  • Notable contents: notes root page = 2; row id=1 body length = 12017 (overflow chain); row id=2 inline.

deleted_places.db

  • Source: SYNTHETIC — Python sqlite3 module.

  • Identity: moz_places with 400 rows inserted, ids 201..=400 DELETEd without VACUUM under secure_delete=OFF, freeing whole leaf pages onto the freelist whose old cell content survives. The deleted-record carving fixture (forensic/tests/carve.rs, forensic/tests/oracle_differential.rs, forensic/tests/audit_realdb.rs) and the freelist fixture (core/tests/freelist.rs).

  • Generator:

    python3 - <<'PY'
import sqlite3
con = sqlite3.connect('deleted_places.db')
con.executescript("""
PRAGMA page_size=4096; PRAGMA auto_vacuum=NONE; PRAGMA secure_delete=OFF;
CREATE TABLE moz_places(id INTEGER PRIMARY KEY, url TEXT, title TEXT,
  visit_count INTEGER, last_visit_date INTEGER, frecency REAL);
WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 400)
INSERT INTO moz_places SELECT n,
  'https://site-'||n||'.example.com/path/page',
  'Title for record number '||n||' SECRETMARKER',
  n%100, 1700000000000000+n, n*1.5 FROM seq;
DELETE FROM moz_places WHERE id > 200;
""")
con.commit(); con.close()
PY

  • md5: 16682d7df99b1e8a89287a508d95eb47 — 53248 bytes.

  • Notable contents: live rows = 200 (ids 1..=200), deleted ids 201..=400; PRAGMA freelist_count = 5, PRAGMA page_count = 13; moz_places root = 2. Freelist = trunk page 9 + leaf pages 10–13. Note: secure_delete defaults to ON on this build — without the explicit PRAGMA secure_delete=OFF the deleted content is wiped and nothing is carvable. (Many real browser DBs run with secure_delete off, so this is realistic.)

wal_places.db + wal_places.db-wal

  • Source: SYNTHETIC — Python sqlite3 module, snapshotted mid-transaction.

  • Identity: a main DB + persistent -wal sidecar captured mid-transaction — a held reader connection blocks the checkpoint so the WAL survives on disk with one committed COMMIT frame (page 2) the main file does not yet reflect. Used by core/tests/wal.rs and the WAL branch of forensic/tests/audit_realdb.rs.

  • Generator:

    python3 - <<'PY'
import sqlite3, shutil
con = sqlite3.connect('wal.db')
con.executescript("""
PRAGMA page_size=4096; PRAGMA journal_mode=WAL; PRAGMA wal_autocheckpoint=0;
CREATE TABLE moz_places(id INTEGER PRIMARY KEY, url TEXT, title TEXT,
  visit_count INTEGER, last_visit_date INTEGER, frecency REAL);
INSERT INTO moz_places VALUES (1,'https://www.rust-lang.org/','Rust',5,1700000000000000,2000.5);
INSERT INTO moz_places VALUES (2,'https://github.com/','GitHub',12,1700000100000000,5500.0);
""")
con.commit()
con.execute("PRAGMA wal_checkpoint(TRUNCATE)"); con.commit()  # baseline -> main file
reader = sqlite3.connect('wal.db')                            # hold a read txn
reader.execute("BEGIN"); reader.execute("SELECT count(*) FROM moz_places").fetchone()
con.execute("PRAGMA wal_autocheckpoint=0")
con.execute("UPDATE moz_places SET title='Rust (EDITED IN WAL)', visit_count=777 WHERE id=1")
con.execute("INSERT INTO moz_places VALUES (3,'https://wal-only-row.example/','WAL-ONLY ROW',1,1700000200000000,100.0)")
con.commit()
shutil.copy('wal.db','wal_places.db')          # snapshot while WAL is live
shutil.copy('wal.db-wal','wal_places.db-wal')
PY

  • md5: wal_places.db = bad96eb068359bcb142533696b6515fc (8192 bytes); wal_places.db-wal = 84b08a77d90914c917d92e60a6c8eeab (4152 bytes).

  • Notable contents: main-only view = id=1 title Rust, visit_count 5, 2 rows; WAL-applied view = id=1 title Rust (EDITED IN WAL), visit_count 777, plus id=3 WAL-ONLY ROW, 3 rows. WAL = 1 COMMIT frame for page 2.

wal_carve.db + wal_carve.db-wal

  • Source: SYNTHETIC — Python sqlite3 module, snapshotted mid-transaction.

  • Identity: a main DB + persistent -wal sidecar where the genuinely-different deleted rows live only in the uncheckpointed WAL frames, never on the main file's pages. A wal_checkpoint(TRUNCATE) flushes a clean baseline (rows 1..=50) to the main file and empties the WAL; then, with a held reader blocking any further checkpoint, rows 101..=150 are inserted (COMMIT) and 121..=140 deleted (COMMIT) with no checkpoint. Used by the WAL-frame carving tests in core/tests/wal.rs and forensic/tests/carve_all.rs (#60). Cross-referenced in docs/corpus-catalog.md §J.

  • Generator:

    python3 - <<'PY'
import sqlite3, shutil, os
for f in ('walcarve.db','walcarve.db-wal','walcarve.db-shm'):
    if os.path.exists(f): os.remove(f)
con = sqlite3.connect('walcarve.db')
con.execute("PRAGMA page_size=4096")
con.execute("PRAGMA journal_mode=WAL")
con.execute("PRAGMA wal_autocheckpoint=0")
con.execute("CREATE TABLE msg(id INTEGER PRIMARY KEY, sender TEXT, body TEXT)")
for i in range(1, 51):
    con.execute("INSERT INTO msg VALUES (?,?,?)", (i, f"alice{i}", f"baseline message {i}"))
con.commit()
con.execute("PRAGMA wal_checkpoint(TRUNCATE)"); con.commit()   # baseline -> main file, WAL emptied
reader = sqlite3.connect('walcarve.db')                        # hold a read txn (blocks checkpoint)
reader.execute("BEGIN"); reader.execute("SELECT count(*) FROM msg").fetchone()
con.execute("PRAGMA wal_autocheckpoint=0")
for i in range(101, 151):
    con.execute("INSERT INTO msg VALUES (?,?,?)", (i, f"bob{i}", f"secret WAL body {i}"))
con.commit()                                                   # INSERT commit -> WAL frame 0
con.execute("DELETE FROM msg WHERE id BETWEEN 121 AND 140"); con.commit()  # DELETE commit -> WAL frame 1
shutil.copy('walcarve.db','wal_carve.db')          # snapshot while WAL is live
shutil.copy('walcarve.db-wal','wal_carve.db-wal')
reader.close(); con.close()
PY

  • md5: wal_carve.db = 6747389de0fefcc4c23543353a31325a (8192 bytes); wal_carve.db-wal = 598e80ad38536f4b7a6cb51ddaedc767 (8272 bytes).

  • Notable contents: on-disk-only carve recovers 0 of the WAL-resident deleted rows; WAL-frame carve recovers 20/20 rows 121..=140 (tagged RecoverySource::WalFrame with (salt1, salt2, frame_index) provenance) and re-surfaces 0 surviving (live) rows. WAL = 2 COMMIT frames for page 2.

updated_messages.db

  • Source: SYNTHETIC — Python sqlite3 module.

  • Identity: a messages table where row 7's body is UPDATEd twice (grow then shrink) under secure_delete=OFF, so the pre-edit version survives in freed slack with the same rowid as the live row but different values — the version-aware (prior-version) carving fixture. Drives forensic/tests/prior_version.rs and the prior-version leg of forensic/tests/oracle_differential.rs.

  • Generator:

    python3 - <<'PY'
import sqlite3
con = sqlite3.connect('updated_messages.db')
con.executescript("""
PRAGMA page_size=4096; PRAGMA auto_vacuum=NONE; PRAGMA secure_delete=OFF;
CREATE TABLE messages(id INTEGER PRIMARY KEY, sender TEXT, body TEXT, amount INTEGER);
""")
con.executemany("INSERT INTO messages VALUES(?,?,?,?)",
                [(n, f"user{n}", f"ORIGINAL message body number {n} ZZZ", 707) for n in range(1, 51)])
con.commit()
con.execute("UPDATE messages SET body=? WHERE id=7",
            ("PRIORVERSION secret message body that was later edited " + ("Q" * 120),))
con.execute("UPDATE messages SET body='EDITED final body' WHERE id=7")
con.commit(); con.close()
PY

  • md5: e1edbb56bf37efa6a7c1e738040f1360 (8192 bytes).

  • Notable contents: 50 live rows (ids 1..=50); live row 7 body = EDITED final body. The recoverable prior version is rowid 7, body PRIORVERSION secret …, amount 707 — genuine deleted content whose rowid is still live with different values. The full original body survives nowhere (overwritten); only the intermediate PRIORVERSION version is cleanly carvable.

freeblock_2byte_rowid.db

  • Source: SYNTHETIC — sqlite3 CLI (Tier-2; ground truth derivable from the construction).

  • Identity: a 4-column table whose rows all have 2-byte rowid varints (ids 1000..=1259), with three well-separated rows deleted so each freed cell is its own non-coalesced freeblock. Pins the freeblock-clobbered 2-byte-rowid recovery path (forensic/tests/freeblock_highrowid.rs): the 4-byte freeblock clobber destroys no serial type, so the whole serial array survives and the exact-tile reconstruction recovers the row. The independent sqlite-unhide 09.db (env-gated) is the real-corpus twin.

  • Generator:

    sqlite3 freeblock_2byte_rowid.db <<'SQL'
PRAGMA page_size=4096; PRAGMA secure_delete=0;
CREATE TABLE m(id INTEGER PRIMARY KEY, sid TEXT, line TEXT, n INT);
WITH RECURSIVE c(i) AS (SELECT 1000 UNION ALL SELECT i+1 FROM c WHERE i<1260)
INSERT INTO m SELECT i, 's'||i, 'line text for record '||i, i FROM c;
DELETE FROM m WHERE id IN (1050, 1130, 1210);
SQL

  • md5: e32a55e60a40e3072917d4d5cd3494f5 — 20480 bytes.

  • Notable contents: 258 live rows; deleted ids 1050/1130/1210, each recovered as line text for record <id> with no misaligned phantom; PRAGMA freelist_count = 0 (in-page freeblocks only).

freeblock_coalesced.db

  • Source: SYNTHETIC — sqlite3 CLI (Tier-2; ground truth derivable from the construction). Same shape as freeblock_2byte_rowid.db.
  • Identity: adjacent high-rowid rows (DELETE id BETWEEN 1100 AND 1104) whose freed cells SQLite coalesces into one multi-cell freeblock. Pins the span-level exact-tiling recovery (forensic/tests/freeblock_highrowid.rs): the coalesced cells are recovered only when they tile the freeblock exactly, so a misaligned run yields nothing rather than a phantom.
  • md5: e064fd01f8040c49dc5cf8913532b36f — 20480 bytes.
  • Notable contents: 256 live rows; deleted ids 1100..=1104 recovered (≥4 of 5), no misaligned phantom; PRAGMA freelist_count = 0.

exclusion_invariant/cross_table_rowid.db

  • Source: SYNTHETIC — Python sqlite3 module (Tier-2; ground truth derivable from the construction). Generator + provenance: exclusion_invariant/gen.py.
  • Identity: two tables that SHARE rowids, to exercise the exclusion-invariant hole where live-row identity is keyed by a global rowid with no table dimension (docs/improvement-roadmap.md §1.1). Table t (created first) is the paper 0F B-tree-rebalancing scenario — 80 rows of ~420-byte id-tagged TEXT, DELETE ids 1..50 — which frees pages still holding live rows 51..80. Table z (created second, so it wins Database::live_rows()'s rowid-keyed collapse) holds the same rowids 51..80 with different values. Pins forensic/tests/exclusion_invariant_cross_table.rs: with z hijacking the collapse, the carver re-surfaces exactly the 13 live t rows (ids 51..63) that a rebalance shuffled onto freed pages — the breach the test forbids.
  • md5: f8966f346bf9c6c95aa7791b86e1b52b — 49152 bytes.
  • Notable contents: t live ids 51..80 (v = ROW-<id>-XXXX…), t deleted ids 1..50; z live ids 51..80 (v = ZZZ-<id>-QQQ…); secure_delete=OFF, auto_vacuum=NONE. Ground truth: NO carved record's values may equal any currently-live row of either table.

dedup_cross_table/cross_table_identity.db

  • Source: SYNTHETIC — Python sqlite3 module (Tier-2; ground truth derivable from the construction). Generator: dedup_cross_table/gen.py.
  • Identity: two live tables a and b each holding (1,'DUP-1') and (2,'DUP-2') (identical rowid+values across tables) plus a distinct keeper row, then both DELETE ids 1 and 2. Pins forensic/tests/dedup_cross_table.rs (roadmap §1.2): the carver recovers each deleted identity from BOTH tables' in-page residue, so a dedup keyed only on (rowid, values) collapses two genuinely-distinct deleted records into one. The table-aware identity keeps all four.
  • md5: 3b78c970e13aa5793146ade0aed84cdb — 12288 bytes.
  • Notable contents: a/b each 1 live row (KEEP-a/KEEP-b), deleted (1,'DUP-1') and (2,'DUP-2') in both; secure_delete=OFF, auto_vacuum=NONE.

dropped_table_schema.db

  • Source: SYNTHETIC — sqlite3 CLI (Tier-2; ground truth derivable from the construction).

  • Identity: a secrets table is created, populated, then DROPped (under secure_delete=OFF), leaving only keep live. Its sqlite_master row survives in page-1 free space. Pins dropped-table schema recovery (forensic/tests/dropped_schema.rs): recover_dropped_schemas surfaces the dropped object's name + CREATE statement, and audit reports it as SQLITE-DROPPED-SCHEMA-RECOVERED.

  • Generator:

    sqlite3 dropped_table_schema.db <<'SQL'
PRAGMA page_size=4096; PRAGMA secure_delete=0;
CREATE TABLE keep(id INTEGER PRIMARY KEY, x TEXT);
INSERT INTO keep VALUES (1,'a'),(2,'b');
CREATE TABLE secrets(id INTEGER PRIMARY KEY, account TEXT, password TEXT);
INSERT INTO secrets VALUES (1,'admin','hunter2'),(2,'root','toor');
DROP TABLE secrets;
SQL

  • md5: 69087f66e1fc37a47ebf1803d951301d — 12288 bytes.

  • Notable contents: live table keep; dropped table secrets recovered with its CREATE TABLE secrets(...) statement from page-1 residue.

nist_dlc_snapshot.db (REAL-ext, NIST public domain, Tier-1)

  • Source: NIST CFReDS Data Leakage Case (2015) — the Google Drive client snapshot.db, recovered from a Volume Shadow Copy of the case's PC disk image. Authored by NIST (not us, not our oracles); the published answer key is the independent ground truth. U.S.-Government public domain.

  • Case + answer key: https://cfreds-archive.nist.gov/data_leakage_case/data-leakage-case.html · answer PDF https://cfreds-archive.nist.gov/data_leakage_case/leakage-answers.pdf (question 49: "What files were deleted from Google Drive?").

  • Extraction recipe (the PC image is ~20 GB, gitignored/not committed — only the 20 KB snapshot.db is committed):

    # download cfreds_2015_data_leakage_pc.7z.001..003, then:
7z x cfreds_2015_data_leakage_pc.7z.001          # -> cfreds_2015_data_leakage_pc.dd
mmls cfreds_2015_data_leakage_pc.dd              # C: partition starts at sector 206848
# open the partition's Volume Shadow Copy (libvshadow) and read the NTFS within
# it (pytsk3): /Users/informant/AppData/Local/Google/Drive/user_default/snapshot.db

  • md5: a37a765981eea87d2c2cd5f7be0c6c0a — 20480 bytes, page_size 1024.

  • Notable contents / ground truth: live cloud_entry holds only the Drive root; the two deleted file entries — do_u_wanna_build_a_snow_man.mp3 (a clean freed cell, RowID 3) and happy_holiday.jpg (a freeblock-clobbered cell, first 4 bytes overwritten) — are both recovered by forensic/tests/nist_dlc_snapshot.rs, the latter via freeblock reconstruction (surviving tail …holiday.jpg).