performing-sqlite-database-forensics对 SQLite 数据库执行取证分析，从空闲列表（Freelist）和 WAL 文件中恢复已删除记录，解码编码时间戳，并从浏览器历史、即时通讯应用和移动设备数据库中提取证据。
Independent GitHub snapshot · Automated classification · Original documentation governs
SQLite 是全球部署最广泛的数据库引擎，几乎每个移动应用、Web 浏览器和许多桌面应用都使用它存储用户数据。在数字取证（Digital Forensics）中，SQLite 数据库是包含浏览器历史、消息记录、通话日志、GPS 位置、应用偏好设置和缓存内容的关键证据来源。取证分析超越了简单的 SQL 查询，需要检查内部 B 树（B-tree）页面结构、包含已删除记录的空闲列表（Freelist）页面、保存事务历史的写前日志（WAL，Write-Ahead Log）文件，以及数据库页面中删除后仍可能保留可恢复数据的未分配空间。
| 偏移量 | 大小 | 描述 |
|---|---|---|
| 0 | 16 | 魔数字符串："SQLite format 3\000" |
| 16 | 2 | 页面大小（512-65536 字节） |
| 18 | 1 | 文件格式写入版本 |
| 19 | 1 | 文件格式读取版本 |
| 24 | 4 | 文件变更计数器 |
| 28 | 4 | 数据库页面数 |
| 32 | 4 | 第一个空闲列表主干页面编号 |
| 36 | 4 | 空闲列表总页面数 |
| 52 | 4 | 文本编码（1=UTF-8，2=UTF-16le，3=UTF-16be） |
| 96 | 4 | 版本有效性编号 |
| 类型 | ID | 描述 |
|---|---|---|
| B 树内部节点 | 0x05 | 内部表节点 |
| B 树叶节点 | 0x0D | 包含实际记录的表叶页面 |
| 索引内部节点 | 0x02 | 内部索引节点 |
| 索引叶节点 | 0x0A | 索引叶页面 |
| 空闲列表主干 | - | 跟踪已释放页面 |
| 空闲列表叶 | - | 包含可恢复数据的已释放页面 |
| 溢出页 | - | 大型记录的延续 |
删除记录时，SQLite 可能将其页面放入空闲列表而非立即覆盖。
import struct
import sqlite3
import os
def analyze_freelist(db_path: str) -> dict:
"""分析 SQLite 空闲列表，识别包含已删除数据的页面。"""
with open(db_path, "rb") as f:
# 读取头部
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
# 遍历空闲列表主干链
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 in range(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
def extract_freelist_content(db_path: str, output_dir: str):
"""提取空闲列表页面的原始内容以供分析。"""
info = analyze_freelist(db_path)
os.makedirs(output_dir, exist_ok=True)
with open(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")
with open(output_file, "wb") as out:
out.write(page_data)
return len(info["leaf_pages"])
WAL 文件包含尚未检查点写回主数据库的待处理事务。
def parse_wal_header(wal_path: str) -> dict:
"""解析 SQLite WAL 文件头部和帧清单。"""
with open(wal_path, "rb") as f:
header = f.read(32)
magic = struct.unpack(">I", header[0:4])[0]
file_format = struct.unpack(">I", header[4:8])[0]
page_size = struct.unpack(">I", header[8:12])[0]
checkpoint_seq = struct.unpack(">I", header[12:16])[0]
salt1 = struct.unpack(">I", header[16:20])[0]
salt2 = struct.unpack(">I", header[20:24])[0]
wal_info = {
"magic": hex(magic),
"format": file_format,
"page_size": page_size,
"checkpoint_sequence": checkpoint_seq,
"frames": []
}
# 解析帧（每帧 24 字节头部 + page_size 数据）
frame_offset = 32
frame_num = 0
file_size = os.path.getsize(wal_path)
while frame_offset + 24 + page_size <= file_size:
f.seek(frame_offset)
frame_header = f.read(24)
page_number = struct.unpack(">I", frame_header[0:4])[0]
db_size_after = struct.unpack(">I", frame_header[4:8])[0]
wal_info["frames"].append({
"frame_number": frame_num,
"page_number": page_number,
"db_size_pages": db_size_after,
"offset": frame_offset
})
frame_offset += 24 + page_size
frame_num += 1
return wal_info
活跃 B 树页面内已删除的单元在单元指针数组和单元内容区域之间的未分配区域留有数据。
def analyze_unallocated_space(db_path: str, page_number: int) -> dict:
"""分析特定 B 树页面内的未分配空间。"""
with open(db_path, "rb") as f:
header = f.read(100)
page_size = struct.unpack(">H", header[16:18])[0]
if page_size == 1:
page_size = 65536
offset = (page_number - 1) * page_size
f.seek(offset)
page_data = f.read(page_size)
# 解析页面头部（根据类型为 8 或 12 字节）
page_type = page_data[0]
first_freeblock = struct.unpack(">H", page_data[1:3])[0]
cell_count = struct.unpack(">H", page_data[3:5])[0]
cell_content_offset = struct.unpack(">H", page_data[5:7])[0]
if cell_content_offset == 0:
cell_content_offset = 65536
header_size = 12 if page_type in (0x02, 0x05) else 8
cell_pointer_end = header_size + cell_count * 2
unallocated_start = cell_pointer_end
unallocated_end = cell_content_offset
unallocated_size = unallocated_end - unallocated_start
return {
"page_number": page_number,
"page_type": hex(page_type),
"cell_count": cell_count,
"unallocated_start": unallocated_start,
"unallocated_end": unallocated_end,
"unallocated_size": unallocated_size,
"unallocated_data": page_data[unallocated_start:unallocated_end].hex()
}
| 应用 | 数据库文件 | 关键表 |
|---|---|---|
| Chrome | History | urls, visits, downloads, keyword_search_terms |
| Firefox | places.sqlite | moz_places, moz_historyvisits |
| Safari | History.db | history_items, history_visits |
| msgstore.db | messages, chat_list | |
| Signal | signal.sqlite | sms, mms |
| iMessage | sms.db | message, handle, chat |
| Android SMS | mmssms.db | sms, mms, threads |
| Skype | main.db | Messages, Conversations |
from datetime import datetime, timedelta
def decode_chrome_timestamp(chrome_ts: int) -> datetime:
"""将 Chrome/WebKit 时间戳转换为 datetime（自 1601-01-01 的微秒数）。"""
epoch_delta = 11644473600
return datetime.utcfromtimestamp((chrome_ts / 1000000) - epoch_delta)
def decode_unix_timestamp(unix_ts: int) -> datetime:
"""将 Unix 时间戳转换为 datetime。"""
return datetime.utcfromtimestamp(unix_ts)
def decode_mac_absolute_time(mac_ts: float) -> datetime:
"""将 Mac 绝对时间（自 2001-01-01 的秒数）转换为 datetime。"""
mac_epoch = datetime(2001, 1, 1)
return mac_epoch + timedelta(seconds=mac_ts)
def decode_mozilla_timestamp(moz_ts: int) -> datetime:
"""将 Mozilla PRTime（自 Unix 纪元的微秒数）转换为 datetime。"""
return datetime.utcfromtimestamp(moz_ts / 1000000)