Reviewed by Scar de Courcier, Forensic Focus
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.
File format is the next subject of discussion, following a description of the structure of the database and the database header, which is necessary before diving into the deeper realms of SQLite forensics. In this chapter and throughout the rest of the book there are useful images, tables and diagrams to illustrate the points being made. This is helpful both to show the reader real-life examples of the subjects under discussion, and to break up the text: digital forensics is one of those disciplines that can easily become very dense and text-heavy when one gets under the hood.
I particularly enjoyed the illustration on page 57, which uses an arrow to demonstrate where the freeblock chain can be found so that the reader doesn’t have to work too hard to locate it themselves. This is demonstrative of the general tone of the book: while it does assume a knowledge of forensics, it doesn’t make the reader work too hard – a perfect balance between being easily comprehensible yet still thought-provoking.
The short summary paragraphs at the end of each chapter are very helpful as reference points. Although each chapter’s heading should be sufficiently descriptive to allow a reader to flick through and find the section they need, it can also be useful to have a slightly longer summary which solidifies the information you’ve just taken in and serves as a reminder should you need to check something later.
Chapter three deals with recovering SQLite records, whether these are intact, orphaned or partial. There is also a brief description about secure_delete and how it may be temporarily thwarted.
SQLite has failsafes in place to help ensure database integrity when errors occur. These are called rollback journals, and they are dealt with in chapter four of SQLite Forensics. Firstly the chapter describes how rollback journals work and what data may be found within them, and then we are shown how they may play a part in a forensic investigation.
A newer approach to database integrity comes in the form of write-ahead logs (WALs), which are the subject of chapter five. This section covers the salient points relating to WALs, including determining where in the database they belong; understanding the provenance of records recovered from WALs; and showing how WALs can help an investigator to see how a database has been modified over time. An important sub-section beginning on page 141 details why it is important not to use non-forensic tools to investigate WALs, and what you should do instead.
Chapter six switches perspectives slightly to take a higher-level look at SQLite forensics. It looks at the database schema and how forensically useful information can be gleaned from tables, indexes, foreign keys, triggers and views.
The following chapter deals with SQL itself, providing an introduction to the most common SQL queries and how they can be useful in forensic investigations. Throughout the book Sanderson’s wealth of experience shines through, and this is one of the things that makes it accessible for readers on many levels, whether you’ve recently started in forensics or you’re established already but looking for a SQLite refresher.
Possibly my favourite chapter is the penultimate one, entitled ‘Odds And Ends’, which is exactly what it sounds like: a place for all the bits and pieces that didn’t neatly slot into any of the preceding sections. This covers many subjects including Full-Text Search modules; R*Trees; BLOBs and unstructured data; and that bane of every investigator’s life: encryption.
The book concludes with an in-depth case study involving the sms.db from an iOS device. The chapter is set up in a question and answer fashion, encouraging the reader to think for themselves and use some of what they’ve learned in the book to talk themselves through the case study to its resolution.
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!
Find out more and buy the book at sqliteforensics.co.uk or on Amazon.
