Learn · Mobile forensics

SQLite

One file, cut into equal pages, organised as b-trees. Rows are packed into cells with variable-length integers. Deleting a row mostly just unlinks it.

2 Page 1: the schema

After the header, page 1 holds the root of the sqlite_master table (also called sqlite_schema). Each row describes one table or index: type, name, tbl_name, rootpage and the sql that created it. rootpage is how you find a table: the messages table starts at page 2.

Look at the unallocated space of page 1: it still contains CREATE TABLE drafts (…). The app had a third table, drafts, which was dropped. Its schema row is gone from sqlite_master, but its bytes were never wiped. Section 4 finds the table's content too.

3 B-tree pages, cells, varints and records

Every table is a b-tree keyed by the rowid. Small tables fit on one leaf page (type 0D). Larger ones get interior pages (type 05) that hold rowid keys and child page numbers. Indexes use types 0A and 02. Every b-tree page has the same layout:

Page header8 bytes (leaf), 12 (interior)
Cell pointers2 bytes per cell, sorted by key
Unallocatedgrows from both sides
Cell content areacells written from the end of the page downwards, plus freeblocks

Varints

Lengths, rowids and types inside cells are varints: 1 to 9 bytes, big-endian, 7 bits per byte. A set top bit (0x80) means "another byte follows"; the 9th byte, if reached, contributes all 8 bits. So 05 is 5, and 81 1D is 1 × 128 + 29 = 157.

Varint calculator

Records and serial types

A table leaf cell is: payload length (varint), rowid (varint), then the record. The record starts with a header: its own length (varint), then one serial type (varint) per column. The values follow, in column order, with no separators. The serial type says how many bytes each value takes:

Serial typeMeaningBytes
0NULL0
1 · 2 · 3 · 4 · 5 · 6signed big-endian integer1 · 2 · 3 · 4 · 6 · 8
7IEEE 754 float, big-endian8
8 · 9the integers 0 and 1, stored in the type alone0
N ≥ 12, evenBLOB(N − 12) ÷ 2
N ≥ 13, oddTEXT (in the database's encoding)(N − 13) ÷ 2

The contacts table shows all the parts. Cell 1 has a payload of 30 bytes. Its id is stored as NULL: a column declared INTEGER PRIMARY KEY is an alias for the rowid, so its value is not repeated in the record. last_seen has serial type 5, a 6-byte integer holding Unix milliseconds.

Try this: the sql column of the first schema row in section 2 has serial type 82 0D. Decode it in the calculator. How long is the text? Count the record header of the Sam cell by hand: why is it 5 bytes?

Values too large for the page spill into overflow pages. The cell then keeps the first part of the payload and a 4-byte page number of the first overflow page; each overflow page starts with the number of the next. The hex views show this as a note.

4 Why deleted rows survive

When a row is deleted, SQLite removes its pointer from the cell pointer array and links the cell's space into the page's freeblock chain. A freeblock reuses the first 4 bytes of the old cell: 2 bytes "next freeblock", 2 bytes "size". Everything after them stays as it was, unless secure_delete is on (it is off by default, and most apps never change it). If the deleted cell was the lowest one on the page, its space is added to the unallocated area instead, again without wiping it.

The messages page below has 5 live cells. The header's first freeblock field points to 0x02F1. Follow the chain: two deleted messages from the chat with Alex are still there.

freeblock = next (2 bytes) | size (2 bytes) | rest of the old cell old cell = payload length | rowid | header length | serial types … | values └────────── first 4 bytes overwritten ─────────┘

The overwritten bytes are the payload length, the rowid, the record header length and usually the first serial type. The body text and the timestamp behind it are intact. Carving tools rebuild the record by guessing the missing header bytes from the table's schema. Compare the rowids of the live cells: rowids 3 and 6 are missing, and their texts sit in the freeblocks.

Free pages

Whole pages that no table uses any more go on the freelist, which starts at the page named in the header. A freelist trunk page only rewrites its first 8 bytes (next trunk page, number of leaf pages) plus the list of leaf page numbers. The rest is the page's old content: here, the row of the dropped drafts table.

What makes old data disappear: VACUUM rebuilds the file and drops free space, auto-vacuum truncates free pages, secure_delete zeroes deleted content, and new rows reuse freeblocks and free pages. Look for deleted data early, before the app keeps writing.

Try this: in the freeblock that holds rowid 3, find the 6-byte timestamp behind the text and convert it (Unix milliseconds) with the converter on the Location page. Who sent the message? Hint: from_me is stored only as a serial type (8 = 0, 9 = 1). Find the surviving rest of the record header just after the 4 freeblock bytes: first the type of contact_id (here 09, the constant 1, i.e. Alex), then from_me, then the type of body. The hex view's "recovered" rows do the same guess for you.

5 Try it yourself

Answer from the hex views above. Numbers can be typed in decimal or hex (0x…). Remember: SQLite numbers are big-endian.

At which byte offset of the file does page 3 (the contacts table) start?

Read the page size at offset 0x10 of the header (2 bytes, big-endian), then use the formula from section 1. Pages are numbered from 1.

Bytes 0x10–0x11 are 04 00. Read big-endian: 0x0400 = 1,024 bytes per page.

(3 − 1) × 1,024 = 2,048 = 0x800. The page 3 view in section 3 starts exactly there.

Cell 1 of the messages page (rowid 1) stores its body with serial type byte 75. How many bytes long is the text?

Is 0x75 a one-byte varint? Convert it to decimal, check whether it is even or odd, and use the table in section 3.

The cell starts 41 01 07 00 09 08 75 05 09: payload length 65, rowid 1, record header length 7, then the serial types of id, contact_id, from_me, body, sent_at, is_read.

0x75 has the top bit clear, so it is a complete varint: 117. It is odd and ≥ 13, so TEXT of (117 − 13) ÷ 2 = 52 bytes: "Hi! Is the bike shop on Elm Street open on Saturday?"

Follow the freeblock chain of the messages page to its second freeblock. How many bytes does it cover?

The page header's "first freeblock" field is at page offset 1 (2 bytes). Each freeblock starts with 2 bytes "next freeblock" and 2 bytes "size", all big-endian page offsets and lengths.

Page header: 0D 02 F1 …, so the first freeblock is at page offset 0x02F1. Its first bytes are 03 61 00 29: next at 0x0361, size 41.

At 0x0361: 00 00 00 2C. Next = 0 (end of the chain), size = 0x002C = 44 bytes. They hold "The rear brake squeaks again."

Who wrote the deleted message "The rear brake squeaks again."?

After the 4 freeblock bytes, the rest of the old record header survives. The first surviving byte is the serial type of contact_id, the next one that of from_me. Serial types 8 and 9 are the constants 0 and 1.

The freeblock reads 00 00 00 2C | 09 08 47 05 09 | "The rear brake …". 09 = contact_id 1, which is Alex in the contacts table (page 3, rowid 1). 08 = from_me 0: the message was received from Alex.

(47 = 71 is the body: (71 − 13) ÷ 2 = 29 bytes; 05 is the 6-byte sent_at; 09 is is_read = 1.)

These 48 bytes are the last live cell of the messages page (rowid 7, at file offset 0x6D2) followed by the start of the first freeblock (at 0x6F1). Edit them and watch the decoded page below:

  • The record header of rowid 7 is 07 00 09 08 2D 05 09. Change from_me's type 08 (at 0x6D7) to 09. The message now looks sent by the owner, and no other byte moves. Why not?
  • Reset, then change the body type 2D (at 0x6D8, 16 bytes of text) to 2B. What happens to the text and to sent_at? Why does one wrong serial type spoil every column after it?
  • Reset, then set the "next freeblock" bytes 03 61 (at 0x6F1) to 00 00. The second freeblock vanishes from the view, although its bytes are unchanged. What does this mean for a tool that only follows the chain?