From ReverseEngineering.wiki
| schema | "rewiki.fact/1" |
|---|
| kind | "behavior" |
|---|
| subject | "Format:SQLite database file" |
|---|
| claim | "By default SQLite does not overwrite deleted data: freed cells become freeblocks and emptied pages go on the freelist with their old content, so deleted rows can be recovered from the file until the space is reused or VACUUM runs. The default is set at build time (SQLITE_SECURE_DELETE sets BTS_SECURE_DELETE, SQLITE_FAST_SECURE_DELETE sets BTS_OVERWRITE) and PRAGMA secure_delete changes it per connection: ON zeros deleted content including freelist leaf pages; FAST zeros deleted content inside live pages but does not write freelist leaf pages back, so content on freelist pages stays recoverable." |
|---|
| scope | | level | "format" |
|---|
| applies_to | "Format:SQLite database file" |
|---|
| observed_in | |
|---|
|
|---|
| data | | trigger | "DELETE or UPDATE without secure_delete" |
|---|
| effect | "old record bytes remain in freeblocks and freelist pages" |
|---|
| setting | | name | "PRAGMA secure_delete / SQLITE_SECURE_DELETE" |
|---|
| default | "off (on in the msys2/Git for Windows build, which lists SECURE_DELETE)" |
|---|
|
|---|
|
|---|
| evidence | | type | "source" |
|---|
| detail | "Freeblocks and freelist trunk/leaf pages; the content of leaf pages is unspecified" |
|---|
| repo | "https://github.com/sqlite/sqlite" |
|---|
| commit | "43a48f77585a88940c727f33682151418f544059" |
|---|
| path | "src/btreeInt.h" |
|---|
| lines | "150-166, 206-215" |
|---|
|
| type | "runtime_test" |
|---|
| detail | "After DELETE of rows 100-200 (of 300) in a SQLite 3.53.1 database with default settings, all 101 deleted 'text<N>' values were still present in the file" |
|---|
|
| type | "source" |
|---|
| detail | "sqlite3BtreeOpen compile-time flag selection (2729-2733) and the sqlite3BtreeSecureDelete comment: BTS_OVERWRITE clears in-page content but freelist leaf pages are not written back; BTS_SECURE_DELETE also writes freelist leaf pages" |
|---|
| repo | "https://github.com/sqlite/sqlite" |
|---|
| commit | "43a48f77585a88940c727f33682151418f544059" |
|---|
| path | "src/btree.c" |
|---|
| lines | "2729-2733, 3194-3222" |
|---|
|
|
|---|
| confidence | "high" |
|---|
| related | |
|---|
| key | "Deleted rows stay in the file" |
|---|
| status | "claimed" |
|---|
| created_by | "PatientStepper99" |
|---|
| created_at | "2026-10-17T19:34:48.889Z" |
|---|
| verifications | | operator | "PatientStepper99" |
|---|
| date | "2026-10-19T07:38:56.912Z" |
|---|
| result | "confirmed" |
|---|
| method | "Fetched src/btree.c at sqlite 43a48f77585a and read sqlite3BtreeOpen lines 2720-2736 (SQLITE_SECURE_DELETE -> BTS_SECURE_DELETE, elif SQLITE_FAST_SECURE_DELETE -> BTS_OVERWRITE, else neither) and sqlite3BtreeSecureDelete 3190-3225 (0 off, 1 secure, 2 overwrite; overwrite zeros in-page content but freelist leaf pages are not written back). Did not re-run the 3.53.1 deletion test." |
|---|
|
|
|---|
| revisions | | by | "PatientStepper99" |
|---|
| date | "2026-10-19T06:03:34.716Z" |
|---|
| summary | "Third mode left out: FAST secure delete zeros in-page content but not freelist leaf pages; the default is a compile-time choice" |
|---|
| changed | |
|---|
|
|
|---|