Book Review: SQLite Forensics by Paul Sanderson

One of the two books I've been the most excited for came out over the weekend.* SQLite Forensics by @sandersonforens is available now and can be purchased via Amazon.

After arriving home from work I went straight to my mailbox and read the first 3 chapters in one sitting. The rest I finished the next day. Don't get me wrong, this book is not a beginners book by any means but the  way the book is structured makes learning easy even though it is impossible to get it all in one reading. The conceptual explanations are succinct and highly visual with many examples. As long as the reader knows a little about offsets and hex it is possible to follow along with no problems.

Author and Technical Editors - Do note!

As can be seen in the cover the technical editors are of the highest quality. Most folks in the DFIR space have either studied technical articles, read blog posts, taken courses from, or used tools developed by these individuals. The author of SQLite itself is here so the technical support comes straight from the source. The author himself is extremely well known in the community for his expertise and for his development of the Forensic Browser for SQLite, one of the best if not the best tool to forensically deal with the subject matter at hand. For me this book couldn't be more timely considering that my caseload is becoming more and more mobile forensics heavy as time goes by. As mentioned in the book and as experience has shown me, there can easily be over a hundred SQLite databases in just one mobile phone. There is a real need for reference material on this topic and it is great for practitioners to now have a book that pools all this knowledge in one place. It feels to me like the SQLite version of Brian Carrier's File System Forensics Analysis book. In depth and accessible all at the same time.

The book starts with a breakdown of SQLite SQL basics. The target audience for this book should be intimately familiar with SQL itself hence the explanations are short and to the point but still with enough detail to carry readers with little experience through the book. From database pages and b-trees to record recovery and schema definition the author gives us a low level understanding of all these topics.

Regarding the style of the book I appreciate the time taken to explain the process used to reach conclusions on how to interpret the data. The constant reinforcement of testing, validation, and the need to avoid hasty assumptions is made throughout. For example when dealing with record recovery Sanderson states, "With an understanding of what was present we can then provide methods for recovering data..." (83) This to me is the heart of forensics. The understanding of data we know and how it behaves in all states serving as the foundation for understanding datasets generated by others in those already understood states. In the book Sanderson proceeds to demonstrate all the cases to be considered in the record recovery process for SQLite databases. Both beginner and experienced forensicators will be well served by following such an approach in all things.

If I were to pick my favorite 3 sections I would choose the following:

• Journals & Write-Ahead Logs
• It is my opinion that the most used forensic applications in the mobile forensics space lack a clear way of presenting WAL file content and corresponding context. As a result plenty of possibly pertinent data goes unexamined. I fully expect to use the knowledge and the tools in this section for case work and teaching/trainer duties.
• Time Conversions
• Although I have been acquainted with the different time conversions used in SQLite for a while now this book is the best central repository of them I've come across. Forensic Browser for SQLite datetime SQL extensions and display functions make conversion easy work.
• Case Study
• Having a case study putting it all together is not only the best way of making sure the concepts are understood but also serves as a template the reader can use when approaching SQLite databases for analysis. Super useful.

For me the biggest take-away from the book is the detailed understanding of what the tools we use do and how they do it. There is no practical way to do all the correlation and analysis shown in the book by hand on every single database on every single case. Yet with the knowledge gained by this book I am able to validate any particular tool finding that is relevant to a case I might be working on. The why of the data can be just as important as the fact that data has been found. Maybe even more. This books give you the tools to dig and uncover both.

Bottom line: Highly recommended. 10/10 would buy again.

For any questions or comments I can be reached via twitter here: @AlexisBrignoni

---------------
* The other book I'm looking forward to will be on Bitcoin/cryptocurrency forensic investigations by Brett Shavers. There is no publication date set that I know of.