Database Forensics

What it is and why it matters

Most of the facts in a modern organisation live in a database. The ledger, the order book, the attendance register, the customer list and the access control log are all rows in tables. So are the messages in a phone's chat app and the history in a browser. When a question turns on whether a record was altered, when it was created or what was deleted, the examiner has to go beneath the application's screens and read the database engine's own files.

Two kinds of database dominate forensic work. SQLite is a single-file engine embedded in almost every mobile app, browser and desktop program. It is small, well documented and leaves deleted records lying in its pages for a surprisingly long time. Server databases such as Microsoft SQL Server, MySQL, PostgreSQL and Oracle run on their own machines, serve many users at once and keep transaction logs so that every committed change can be replayed. The transaction log, written for crash recovery, is the examiner's best friend because it records changes the application never meant to show anyone.

Database work also demands care about consistency. A copy taken while the engine is running may be mid-write and unreadable. Querying a live production system changes caches and logs and can be noticed. The examiner plans the acquisition with the database administrator, under authority, and prefers a consistent backup, a snapshot or a file-level copy of a stopped instance.

Types of digital evidence

Data Files
The main storage files such as a SQLite database file, SQL Server .mdf and .ndf files, MySQL InnoDB tablespace files and PostgreSQL's per-table files in the base directory, holding the live rows and often the remains of deleted ones.
Transaction and Redo Logs
SQL Server's .ldf log, MySQL's binary log and InnoDB redo log, PostgreSQL's write-ahead log in pg_wal and Oracle's redo and archive logs, which record each change in order.
SQLite Journals
The rollback journal or the write-ahead log and shared memory files beside a SQLite database, which hold recent changes and sometimes the previous content of changed pages.
Freelist and Unallocated Pages
Pages a database has released but not yet reused, where deleted rows survive until the space is overwritten or the file is vacuumed.
Audit Records
SQL Server Audit output, MySQL general and audit logs, PostgreSQL statement logging and Oracle's audit trail, which record logins, queries and schema changes when enabled.
Schema and Metadata
Table definitions, triggers, stored procedures, views and user accounts, which show how the application worked and who had which rights.
Backups and Dumps
Full and differential backups, log backups and logical dumps such as mysqldump output, each a snapshot of the data at a known time.
Server and Operating System Logs
Database error logs, connection logs and the host's own logs, which place database activity beside logins and file changes on the server.

Devices and data sources

SQLite databases inside Android and iOS app data, browser profiles and desktop applications
Microsoft SQL Server instances on Windows servers and in Azure
MySQL and MariaDB servers behind web applications
PostgreSQL servers in corporate and cloud deployments
Oracle Database in banking, telecom and enterprise systems
NoSQL stores such as MongoDB and Redis, with their own file formats and logs
Managed cloud databases reached through provider snapshots, exports and audit logs rather than file copies
Application servers whose logs show the queries they sent and the users who triggered them

How an examination runs

The order matters. Each step protects the one after it.

01
Authority and Coordination
Confirm who owns the system and who may authorise access. Agree with the administrator how the acquisition will be done so that production is not damaged and the examiner's actions are logged.
02
Identify the Engine and Layout
Record the product, version, data file locations, log file locations, backup schedule and whether auditing or statement logging was enabled.
03
Preserve the Logs First
Transaction logs and binary logs are truncated or rotated on a schedule. Copy them, and any log backups, before anything else, and stop the rotation if the owner agrees.
04
Acquire Consistently
Prefer a full disk image of the server or a storage snapshot. Where only a file copy is possible, stop the instance or use the engine's own backup command so that the copy is consistent. For SQLite, copy the database with its journal, WAL and shared memory files.
05
Hash and Record
Hash every file and backup, record the server time and time zone and note the database's own clock settings, since timestamps in rows come from the application or the server, not the examiner.
06
Analyse at Two Levels
Query the data in a copy loaded on an analysis system, and read the raw files and logs with forensic tools to find deleted rows, earlier page versions and log entries that the live view does not show.
07
Report
Present the rows and log entries with their page, offset or log sequence number, explain what the engine's behaviour makes recoverable and what it does not and keep the application's meaning of each column separate from the raw value.

Tools used in this discipline

Independent profiles: what each tool does, where it stops and what you need to know before using it.

Browse the full tools directory

Terminology

Page: The fixed-size block in which a database stores rows, typically 4 KB in SQLite and 8 KB in SQL Server and PostgreSQL. Deleted rows are often still present in a page until it is rewritten.
B-Tree: The structure that organises pages into tables and indexes. Knowing how a SQLite B-tree lays out its cells is what lets an examiner carve rows from raw pages.
Freelist: The list of pages a SQLite database no longer uses. Their content stays intact until the page is reused or the database is vacuumed.
Write-Ahead Log: A file that records changes before they reach the main data file. SQLite's -wal file, PostgreSQL's pg_wal and the general idea behind SQL Server's transaction log.
Transaction Log: SQL Server's record of every modification, kept for recovery and replication. It can be read to see the before and after images of changed rows.
Binary Log: MySQL's record of data-changing statements or row changes, written for replication and point-in-time recovery and readable with the engine's own utility.
Log Sequence Number: The identifier that orders entries in a transaction log, which gives an exact sequence of changes even when their timestamps are coarse.
Vacuum: The operation that rebuilds a database file and discards freelist pages. SQLite's VACUUM and PostgreSQL's VACUUM both destroy recoverable remnants.
Secure Delete: A SQLite setting that overwrites deleted content with zeros. Some messaging applications enable it, which is why deleted chats cannot always be recovered.

Challenges and limits

What makes it hard

A running database is never at rest, and a file copied while the engine writes may be inconsistent and unreadable
Transaction logs are truncated as part of normal operation, and the window in which a change can be replayed may be short
Auditing and statement logging are often disabled on production systems for performance, so there may be no record of who ran a query
Deleted rows survive only until their pages are reused or vacuumed, and busy databases reuse space quickly
Encryption at rest, transparent data encryption and encrypted SQLite databases such as those used by some messaging applications block raw examination without the keys
The meaning of a column lives in the application, not the database, and a raw value can be misread without the application's documentation or source
Managed cloud databases offer no file access, so the examiner depends on the provider's snapshots, exports and logs

Keeping the evidence intact

Work on a copy loaded in an isolated analysis instance. Never run queries on the evidential files or the production system beyond what is agreed and logged
Hash every data file, log file and backup at acquisition and after each transfer
Keep SQLite databases together with their journal, WAL and shared memory files, and note whether the WAL was checkpointed
Record the engine version and settings such as page size, secure delete and auto-vacuum, since they decide what recovery is possible
Log every query the examiner runs during analysis so that the examination can be repeated

Standards and legal considerations

ISO/IEC 27042:2015
Guidelines for the analysis and interpretation of digital evidence. Database work is mostly interpretation, and this standard sets out how to keep analysis and conclusions separate and repeatable.
Read the guide
NIST SP 800-86
The examination and analysis phases and the general treatment of application data.
Read the guide
ISO/IEC 27037:2012
Collection and preservation, including the handling of live systems where a database cannot simply be switched off.
Read the guide
Bharatiya Sakshya Adhiniyam, 2023
Database records produced in court are electronic records and need the certification the Act requires.
Read the guide
Digital Personal Data Protection Act, 2023
Databases under examination usually contain personal data of many people, and the examination must stay within its lawful purpose and scope.
Read the guide

In practice

A Backdated Ledger Entry
An accounts application shows an entry dated the first of the month. The row's primary key is higher than entries dated three weeks later, the SQL Server transaction log shows the insert on the twenty-second with a different date value that was updated an hour later and the audit log records the login that ran both statements.
Deleted Chat Messages in SQLite
A phone extraction includes a messaging application's database with no messages for a two-day period. The database's write-ahead log file contains frames with rows for those days, and several freelist pages hold cells with the same thread identifier. The examiner recovers the rows, reports their offsets and states that the application's secure delete setting was off.
How Much Was Read in a Breach
After a web application compromise, the examiner finds that MySQL's general query log was off but the binary log and the web server logs exist. The binary log shows no data changes. The web logs show thousands of requests with a SQL injection pattern against the customer table over four hours, which gives the owner a defensible estimate of the records exposed.

This page is educational material, not legal advice. Laws and standards change, commence on different dates and are interpreted differently across jurisdictions. Always read the official text linked on this page and take advice from a qualified professional for a real matter.

Learn it at DFRTI

Free pages explain. These courses teach it on real devices with a mentor.

StorageDigital Storage ForensicsFile System AnalysisRead NTFS, FAT, exFAT, ext4 and APFS at the structure level and build timelines.IntermediateDCFA

MobileMobile & IoT ForensicsAndroid ForensicsExtract and examine Android phones, from ADB pulls to WhatsApp databases and ALEAPP reportsIntermediateDCMFE

MobileMobile & IoT ForensicsiOS ForensicsBFU and AFU states, iTunes and Finder backups, the keychain and iLEAPP for iPhone casesIntermediateDCMFE

LegalFoundation & Legal CoursesBasics of Cyber ForensicsStart here. Evidence handling, imaging, hashing, chain of custody and your first analysis.BeginnerDCFA

Standards for this area

CCPA / CPRACalifornia's privacy law applies to for-profit businesses that meet revenue or volume thresholds, and it reaches their vendors by contract. It is a state law, enforced by the Attorney General and a dedicated agency, and it is not the law of the rest of the United States.Act

Questions people ask

Can deleted database records be recovered?

Often in SQLite, where deleted cells stay in freelist pages and the WAL until overwritten. In server databases the transaction or binary log may hold the deleted row's content for as long as the log is retained. Nothing is certain, and the engine's settings decide.

Is it safe to query a live production database?

Only with the owner's agreement and a plan. Queries consume resources, can lock tables and are themselves logged. Where possible the examiner works from a consistent backup, a snapshot or a stopped copy.

What is the difference between the data file and the transaction log?

The data file holds the current state of the tables. The log holds the sequence of changes that produced it. The data file answers what is there now. The log answers how it got there.

Why does the application show one thing and the database another?

Applications filter, format and hide rows through flags such as a deleted marker. The raw row may still be present with that marker set. The examiner reports the raw data and the application's treatment of it as two separate facts.