performing-sqlite-database-forensics

name	performing-sqlite-database-forensics
description	Perform forensic analysis of SQLite databases to recover deleted records from freelists and WAL files, decode encoded timestamps, and extract evidence from browser history, messaging apps, and mobile device databases.
domain	cybersecurity
subdomain	digital-forensics
tags	["sqlite","database-forensics","freelist","wal","write-ahead-log","browser-history","mobile-forensics","deleted-records","b-tree","unallocated-space"]
version	1.0
author	mahipal
license	Apache-2.0
nist_csf	["RS.AN-01","RS.AN-03","DE.AE-02","RS.MA-01"]

Performing SQLite Database Forensics

Overview

SQLite is the most widely deployed database engine in the world, used by virtually every mobile application, web browser, and many desktop applications to store user data. In digital forensics, SQLite databases are critical evidence sources containing browser history, messaging records, call logs, GPS locations, application preferences, and cached content. Forensic analysis goes beyond simple SQL queries to examine the internal B-tree page structures, freelist pages containing deleted records, Write-Ahead Log (WAL) files preserving transaction history, and unallocated space within database pages where recoverable data may persist after deletion.

When to Use

• When conducting security assessments that involve performing sqlite database forensics
• When following incident response procedures for related security events
• When performing scheduled security testing or auditing activities
• When validating security controls through hands-on testing

Detection Gaps & Validation

A plain SELECT * on the live table is the shallowest possible SQLite exam and misses most recoverable evidence. Cover these or you will under-report:

• The deleted rows live outside the main tables. Querying the database with sqlite3 shows only active records. Deleted data persists in freelist pages, in unallocated space between the cell-pointer array and cell-content area, and in overflow pages. Parse these raw (as the freelist/slack code here does) — "no such message" from a SQL query is not proof it was never there.
• WAL and journal hold uncommitted/rolled-back state. A -wal file can contain newer rows not yet checkpointed into the main DB, and older versions of pages superseded by later frames; a -journal holds pre-transaction images. NEVER open the DB read-write (it triggers a checkpoint and destroys this evidence) — copy db, -wal, -shm, and -journal together, and parse WAL frames page-by-page for multiple versions of the same row.
• Secure-delete and VACUUM erase the freelist. If PRAGMA secure_delete was on, or the app ran VACUUM/auto-vacuum, deleted-record recovery yields little — note this as a coverage limitation, not a clean device. Carve surrounding unallocated disk space and check for prior DB copies/backups.
• Validate timestamps by epoch, then corroborate. Decode against the correct base: Chrome/WebKit (µs since 1601), Mozilla PRTime (µs since 1970), Unix (s/ms), Mac Absolute (s since 2001). A mis-decoded timestamp can shift events by decades. Cross-check recovered rows against indices, app logs, and a second artifact before relying on them.