SQLite Forensics

by Paul Sanderson, Dr. Richard Hipp (Editor), Brett Shavers (Editor)

SQLite is a self-contained SQL database engine that is used on every smartphone (including all iOS and Android devices) and most computers (including all Macs and Windows 10 machines). Each computer or phone using SQLite often has hundreds of SQLite databases and it is estimated that there are over one trillion SQLite databases in active use. Given the above, the importance of examining all of the data held in these databases in an investigation is paramount, and of course this includes examining deleted data whenever possible.In this book we cover the format of the SQLite database, and associated journal and Write-Ahead Logs (WAL) in great detail. We show how records are encoded, how to decode them manually and how to decode records that are partially overwritten. We also describe how the workings of SQLite, and in particular the journal and WAL, can be used to ascertain what has happened in a manner that cannot be determined from the data alone. We cover basic SQL queries and how they can be used to create a custom report that includes data from different tables, and we show how we can use SQL queries to test hypothesises about the relationships of data in different tables.This book is aimed mainly at forensic practitioners, and it is assumed that the reader has some basic knowledge of computer forensics; it will also be of interest to computer professionals in general particularly those who have an interest in the SQLite file format.

315 pages, Paperback

Published May 12, 2018

Reviews

[Brett Shavers · Rating 5 out of 5]

I had the pleasure of being one of the tech editors for Paul's book. For anyone and everyone working anywhere in the DFIR field (digital forensics/incident response), this book is a mandatory read. The material is as fundamental to the job as much as knowing about file systems and file formats.

The book is written clearly and concisely, yet encompasses more than I have ever seen in regards to SQLite databases and forensic analysis. This is not a book to read if you don't have anything to do with SQLite. Conversely, if you have anything to do with SQLite and forensics, then this book has become a required reading for you.

Nicely done, Paul. Nicely done.

[Scar]

SQLite forensics is an important part of many digital forensic investigations. Most smartphones and computer operating systems use SQLite, with each device often including hundreds of databases. Despite this extreme proliferation, SQLite forensics is often overlooked in conversations about current trends in digital forensics. Paul Sanderson’s book attempts to redress the balance and bring attention to the importance of SQLite forensics.

The book opens with an introduction to SQLite forensics: what it covers, and how SQLite differs from most other databases. Astonishingly, there are over one trillion SQLite databases in circulation, a fact that the reader is introduced to on the first page – which definitely sets the scene for this being an important book!

Although the book does assume some knowledge of forensics in general and SQLite in particular, it begins with a short introduction to the basics of SQLite as a refresher for those who may need it. This covers creating tables and running simple queries, and if you’re looking for a bit more information there are several resources listed at the end of the chapter which should help you to find out more. Further resources are included at the end of every section, so if there’s a subject you’re particularly interested in, it’s easy to find out more.

SQLite Forensics is a brilliant resource and a necessary addition to the library of any investigator who might come across SQL databases - which, as we learn in the book's introduction, is everyone!