What Hides in the WAL — SQLite Forensics with crush

Moin! 👋

SQLite is everywhere in digital forensics. iOS, Android, macOS, Windows — virtually every app that needs to store structured data uses it. Messages, call logs, browser history, location data, app state — if it is on a mobile device, there is a good chance it lives in a SQLite database. If you want to go deeper on the file format itself, the official SQLite file format documentation is the right starting point.

With v0.6.0, the crush SQLite viewer has grown significantly. This post walks through what it can do — and more importantly, what the features actually mean for your investigation.

Opening a Database

crush opens SQLite databases directly from inside ZIP or TAR archives, from folders, or as standalone files. No extraction needed. When you select a SQLite file, the SQLite viewer opens automatically. The combo box at the top lets you switch between tables, views, and the generated entries described below.

One important detail: crush opens all SQLite connections in read-only mode. This matters because closing a WAL-mode database that was opened with a read-write connection triggers an automatic checkpoint — which commits and clears the Write-Ahead Log, potentially destroying forensic data. More on WAL below.

The Summary View

The default view when opening a database is the Summary. It shows all tables and views with their row counts, and a status label giving the full schema object count — tables, views, indexes, and triggers at a glance.

This is useful for orientation before diving in: how many tables are there, which ones have data, and are there any views worth looking at?

DB Structure

The DB Structure entry in the combo box lists every schema object in the database — tables, views, indexes, and triggers — with structural details: column definitions for tables, CREATE SQL for views, index definitions, and trigger signatures.

When I open an unfamiliar database, this is where I start, direclty after taking a look on the summary. What tables are there? What do the columns tell me about what the app stores? I also pay attention to indexes — they often hint at what the app queries frequently — and triggers, which can automatically update timestamps or delete records without the user doing anything. Understanding the schema is the foundation for everything that follows

DB Info — PRAGMA Settings

The DB Info entry shows 28 SQLite PRAGMA settings with their current values and plain-English descriptions. PRAGMA statements are essentially configuration queries — they reveal things like the page size, the journal mode, the encoding, and the application ID.

The journal mode is particularly relevant: a value of WAL means the database uses a Write-Ahead Log, which has significant forensic implications (see below). The application_id PRAGMA can sometimes identify the application that created the database. The user_version field is used by many apps to track database schema versions — useful for understanding which version of an app created or last modified the database.

Timestamp Decoding

Timestamps in SQLite databases are stored as raw numbers — but the epoch and unit vary wildly between apps and platforms. Right-clicking any column header and selecting "Decode column as timestamp" lets you tell crush how to interpret the values.

Supported formats:

• Unix seconds — seconds since 1970-01-01 UTC (most common on Android)
• Unix milliseconds — same epoch, millisecond precision
• Unix microseconds — same epoch, microsecond precision
• Mac Absolute Time — seconds since 2001-01-01 UTC (Apple's CoreData default)
• Windows FILETIME — 100-nanosecond intervals since 1601-01-01 UTC
• Chrome / WebKit time — microseconds since 1601-01-01 UTC (Chrome history databases)

The decoded values are displayed as YYYY-MM-DD HH:MM:SS UTC and the column header shows the active format as a suffix — for example created_at [mac abs]. Sorting still works correctly because the raw numeric value is preserved internally. Select "Clear timestamp format" to revert to the raw value.

If you are unsure which format a column uses, a quick sanity check is to look at the magnitude of the values: Unix seconds for recent dates are around 1.7 billion; Mac Absolute Time values for recent dates are around 750 million; Chrome time values are around 13 trillion.

The following pictures show one column converted, the other in original state.

Just to mention it: It is also possible to convert timestamps directly in the SELECT query - this is basic SQLite-Syntax one can use (Reference: https://sqlite.org/lang_datefunc.html ). 

Inspect Cell

BLOB cells are common in SQLite databases — and they often contain the most interesting data. Right-clicking any cell and selecting "Inspect cell…" opens a dedicated window that tries to auto-detect the content format. If it recognises something — a binary plist, xml, base64 (more format to come) — it opens the data directly in the appropriate viewer. If auto-detection does not find a match, I can manually select from the available formats or fall back to the hex view.

In practice I use this constantly. A BLOB that looks like noise in the table view often turns out to be an bplist or base64  — and Inspect Cell gets me there in two clicks without touching the filesystem.

There are currently not so many formats supported it the Inspect Cell command - but I will get there soon with more formats.

The SQL Bar

The SQL bar lets you run custom queries directly against the open database — useful when you want to filter, join, or aggregate data beyond what the table view shows. A few things worth knowing:

• PRAGMA statements are accepted alongside SELECT and WITH
• Selected text only — if you highlight a fragment of a query and press F5, only the selection is executed. Useful for debugging a complex query step by step
• Syntax highlighting — keywords, strings, numbers, and comments are highlighted
• Status feedback appears below the input field — red on error, normal on success
• The pane between the SQL bar and results table is resizable

WAL Forensics — Going Deeper

This is where things get forensically interesting. SQLite has two journal modes: rollback (the default) and WAL (Write-Ahead Log). In WAL mode, changes are not written directly to the database file — they are first written to a separate -wal file and later checkpointed (merged) back into the main database. Most modern iOS and Android apps use WAL mode.

The forensic value of the WAL comes from what survives in it. For a detailed explanation of the WAL format and its forensic implications, I highly recommend Sanderson Forensics' write-up on WAL forensics — it is the reference text on this topic. The book from him and some other great persons in our field is also a standard reference on SQLite forensic including wal-files - definitely worth a read in my opinion and still valid info after all the years.

Sanderson, Paul. SQLite Forensics. Independently Published, 2018. ISBN: 978-1980293071.

In crush, when a -wal companion is present alongside a database, a new WAL Frames entry appears in the combo box. It shows a full frame inventory with every frame classified:

• Active — the current version of a page, reflected in the live database view
• Superseded — an older version of a page that has since been overwritten by a newer frame. The older version of the data may still be readable here
• Uncommitted — frames that were written but never committed — the transaction was rolled back or the app crashed
• WAL slack — frames from a previous WAL cycle (salt mismatch). After a checkpoint, new WAL writes start from the beginning of the file, overwriting old frames — but until that happens, old frames from the previous cycle remain

Superseded and uncommitted frames are colour-coded amber and blue so you immediately see whether overwritten or in-flight data exists. The Table column shows which schema object owns each page.

Enabling the Show WAL history checkbox in the table toolbar appends rows decoded from Superseded, Uncommitted, and WAL-slack frames directly below the live data — colour-coded, with a row count showing how many additional records were recovered. Double-clicking any WAL frame row opens the raw page bytes in the hex viewer.

The DB Info view also shows a WAL summary when a companion is present: total frames, active / superseded / uncommitted / WAL-slack counts — a quick health check before diving into the frame inventory.

Paste & Decode

One more addition worth mentioning in the context of SQLite work: Tools → Paste & Decode… lets you paste raw hex, base64, or plain text and open it in any supported viewer — including the SQLite viewer — without saving anything to disk. If you have been copying BLOB values out of a cell and creating temporary files just to inspect them, this saves a few steps.

A Note on WAL and Evidence Handling

To close with something practical: if you open a WAL-mode database with a standard SQLite tool in read-write mode, the automatic checkpoint will commit and clear the WAL before you see anything. This is silent and irreversible on your working copy.

crush prevents this by opening all connections with the mode=ro URI flag. If you are working with SQLite databases outside of crush, always use PRAGMA query_only = ON or open with a read-only URI to protect the WAL. The forensic test suite in v0.6.0 includes a dedicated test that verifies this behaviour on every build.

Happy examining. 🐢