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.
Instalar com Codex ou Claude Copie este prompt, cole no Codex, Claude ou outro assistente e deixe que ele revise a página da skill e instale para você.
Um comando direto ignora o prompt de revisão. Verifique a origem antes de executá-lo.
Instruções da origem · Visualização somente leitura
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.
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.
_shm
Interpretation false positives. Carved freelist fragments may be stale/overwritten and mix bytes from unrelated records — a "recovered URL" can be a Frankenstein of two rows. Browser-history existence shows a page was rendered/visited, not that the user deliberately typed it (prefetch, redirects, ads). Confirm partial recoveries against record structure (valid serial types, rowid) before reporting them as fact.
Prerequisites
DB Browser for SQLite (sqlitebrowser)
SQLite command-line tools (sqlite3)
Python 3.8+ with sqlite3 module
Belkasoft Evidence Center or Axiom (commercial)
Hex editor (HxD, 010 Editor) for manual page inspection
Understanding of B-tree data structures
SQLite Internal Structure
Database Header (First 100 Bytes)
Offset
Size
Description
0
16
Magic string: "SQLite format 3\000"
16
2
Page size (512-65536 bytes)
18
1
File format write version
19
1
File format read version
24
4
File change counter
28
4
Database size in pages
32
4
First freelist trunk page number
36
4
Total freelist pages
52
4
Text encoding (1=UTF-8, 2=UTF-16le, 3=UTF-16be)
96
4
Version-valid-for number
Page Types
Type
ID
Description
B-tree Interior
0x05
Internal table node
B-tree Leaf
0x0D
Table leaf page containing actual records
Index Interior
0x02
Internal index node
Index Leaf
0x0A
Index leaf page
Freelist Trunk
-
Tracks freed pages
Freelist Leaf
-
Freed page with recoverable data
Overflow
-
Continuation of large records
Deleted Record Recovery
Method 1: Freelist Page Analysis
When records are deleted, SQLite may place their pages on the freelist rather than overwriting them immediately.
import struct
import sqlite3
import os
defanalyze_freelist(db_path: str) -> dict:
"""Analyze SQLite freelist to identify pages containing deleted data."""withopen(db_path, "rb") as f:
# Read header
header = f.read(100)
page_size = struct.unpack(">H", header[16:18])[0]
if page_size == 1:
page_size = 65536
first_freelist_page = struct.unpack(">I", header[32:36])[0]
total_freelist_pages = struct.unpack(">I", header[36:40])[0]
freelist_info = {
"page_size": page_size,
"first_freelist_page": first_freelist_page,
"total_freelist_pages": total_freelist_pages,
"trunk_pages": [],
"leaf_pages": []
}
if first_freelist_page == 0:
return freelist_info
# Walk the freelist trunk chain
trunk_page = first_freelist_page
while trunk_page != 0:
offset = (trunk_page - 1) * page_size
f.seek(offset)
page_data = f.read(page_size)
next_trunk = struct.unpack(">I", page_data[0:4])[0]
leaf_count = struct.unpack(">I", page_data[4:8])[0]
leaves = []
for i inrange(leaf_count):
leaf_page = struct.unpack(">I", page_data[8 + i * 4:12 + i * 4])[0]
leaves.append(leaf_page)
freelist_info["trunk_pages"].append({
"page_number": trunk_page,
"next_trunk": next_trunk,
"leaf_count": leaf_count,
"leaf_pages": leaves
})
freelist_info["leaf_pages"].extend(leaves)
trunk_page = next_trunk
return freelist_info
defextract_freelist_content(db_path: str, output_dir: str):
"""Extract raw content from freelist pages for analysis."""
info = analyze_freelist(db_path)
os.makedirs(output_dir, exist_ok=True)
withopen(db_path, "rb") as f:
page_size = info["page_size"]
for page_num in info["leaf_pages"]:
offset = (page_num - 1) * page_size
f.seek(offset)
page_data = f.read(page_size)
output_file = os.path.join(output_dir, f"freelist_page_{page_num}.bin")
withopen(output_file, "wb") as out:
out.write(page_data)
returnlen(info["leaf_pages"])
Method 2: WAL (Write-Ahead Log) Analysis
The WAL file contains pending transactions that have not yet been checkpointed back to the main database.
