SQLite Forensics by Paul Sanderson

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.


Get The Latest DFIR News

Join the Forensic Focus newsletter for the best DFIR articles in your inbox every month.


Unsubscribe any time. We respect your privacy - read our privacy policy.

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.

Leave a Comment

Latest Videos

Digital Forensics News Round-Up, February 21 2024 #digitalforensics #dfir

Forensic Focus 21st February 2024 6:19 pm

Alan Platt, Professional Services Consultant at MSAB, discusses his experience as a former UK police officer working in digital forensics. He talks about the different levels of digital forensics capabilities within police forces and how MSAB products like XAMN and XEC Director are used by frontline officers versus lab analysts. 

The discussion covers how MSAB partners with law enforcement to develop custom workflows for mobile device acquisitions that facilitate ISO compliance. Alan explains MSAB's managed service offering, where approved MSAB staff can remotely access a customer's XEC Director server to assist with software updates and troubleshooting. He emphasizes the strict data segregation policies enforced by customers to prevent MSAB from accessing any sensitive case data.

Looking ahead, Alan mentions MSAB's new CEO and hints at some exciting developments coming down the pipeline. He spotlights recent enhancements to XEC Director's speed and database functionality for managing large estates of networked Kiosks. Alan also plugs the new XEC Director training he created to help users fully leverage the platform's capabilities.

00:00 – Introduction to Alan Platt
07:00 – Training
12:00 – Workflows
17:20 – Ensuring a secure environment
19:45 – Customer training
20:35 – Helping customers comply with ISO accreditation
25:00 – Validation and verification
27:30 – ISO standards
30:00 – MSAB’s pipeline plans
32:40 – XEC Director 
43:45 – Privacy of user data

Alan Platt, Professional Services Consultant at MSAB, discusses his experience as a former UK police officer working in digital forensics. He talks about the different levels of digital forensics capabilities within police forces and how MSAB products like XAMN and XEC Director are used by frontline officers versus lab analysts.

The discussion covers how MSAB partners with law enforcement to develop custom workflows for mobile device acquisitions that facilitate ISO compliance. Alan explains MSAB's managed service offering, where approved MSAB staff can remotely access a customer's XEC Director server to assist with software updates and troubleshooting. He emphasizes the strict data segregation policies enforced by customers to prevent MSAB from accessing any sensitive case data.

Looking ahead, Alan mentions MSAB's new CEO and hints at some exciting developments coming down the pipeline. He spotlights recent enhancements to XEC Director's speed and database functionality for managing large estates of networked Kiosks. Alan also plugs the new XEC Director training he created to help users fully leverage the platform's capabilities.

00:00 – Introduction to Alan Platt
07:00 – Training
12:00 – Workflows
17:20 – Ensuring a secure environment
19:45 – Customer training
20:35 – Helping customers comply with ISO accreditation
25:00 – Validation and verification
27:30 – ISO standards
30:00 – MSAB’s pipeline plans
32:40 – XEC Director
43:45 – Privacy of user data

YouTube Video UCQajlJPesqmyWJDN52AZI4Q_ifoHVkjJtRc

How MSAB Is Managing The Digital Forensics Challenges Of Frontline Policing

Forensic Focus 21st February 2024 3:07 pm

Podcast Ep. 80 Recap: Empowering Law Enforcement With Nick Harvey From Cellebrite

Forensic Focus 20th February 2024 11:49 am

This error message is only visible to WordPress admins

Important: No API Key Entered.

Many features are not available without adding an API Key. Please go to the YouTube Feed settings page to add an API key after following these instructions.

Latest Articles