SQLite Forensics

1. SQLite Forensics David Dym G-C Partners

2. SQLite Forensics Introduction • Who am I? • You may recognize me from • Contributing author for the Computer Forensics InfoSec Pro Guide by David Cowen. • Contributing author for Hacking Exposed Computer Forensics, Second Edition • Tools and scripts • My blog!

3. SQLite Forensics Objectives • SQLite introduction and basics • Help with date-time analysis • Stoke your curiosity • Scripting hands on • Q&A

4. SQLite Forensics Who is using SQLite? • Apple • Google • Mozilla • Dropbox • Adobe • Skype • G-C Partners • and more…

5. SQLite Forensics Where SQLite is used • Mobile • iOS • Android • Windows Mobile Apps • Web Browsers • Mac OSX+ • And many more!

6. SQLite Forensics Why SQLite? ? • Performance • Simplified Application Development • Cross-Platform and programming language agnostic • Atomic transactions • Supports familiar SQL92 features • Single file • Public domain

7. SQLite Forensics What is SQLite SQLite Database • Authored by Dwayne ‘Richard’ Hipp • Initial release in 2000 • Characteristics • Database is a cross-platform • No setup, administration or client-server • Light footprint • Handles large datasets • Multiple readers • Max database size up to 140 Terabytes • Dynamically typed data types Read Write Read

8. SQLite Forensics Header • Identifying a SQLite 3 Databases • SQLite format – Offset 0, Size 16 bytes • Magic Number • 1.2.1 Magic Header String - Every valid SQLite database file begins with the following 16 bytes (in hex): 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00. This byte sequence corresponds to the UTF-8 string "SQLite format 3" including the null terminator character at the end.

9. SQLite Forensics Header • Pages • Every SQLite database consists of pages • Page size is a factor of 2 and can be between 512 and 65536 • Default page size is usually 1024 bytes • Default size begins at offset 16 and is a 2 byte integer • Page size can be changed after creation

10. SQLite Forensics DataTypes

11. SQLite Forensics What you may find in SQLite databases • Your typical “Text” and Date-Time information - • Contacts, Messages, URL’s and more… • Geo Coordinates (GPS) Location data • Settings, preferences, etc… • Entire Files! • We call them BLOBS in database terminology

12. SQLite Forensics What you may find in SQLite databases • A BLOB field could contain • Icons • Images • Audio • Documents • Plists! • Any binary data

13. SQLite Forensics BLOB fields • BLOB - storing binary plist in “properties” field of an iOS sms database

14. SQLite Forensics WAL – Write Ahead Log • Introduced in version 3.7 • Not enabled by default • Improves concurrency – each writer has “end mark” tracked • Transactions append to the end of the WAL • Checkpoint causes WAL data to be written back to the database • Checkpoint occurs when the WAL reaches page size threshold • Header

15. SQLite Forensics Datetimes Handling

16. SQLite Forensics Datetime Formats • UnixtimeePoch • Begins 1 January 1970 • Mac ePoch • Begins 2001 rather than 1970. Thanks Steve • Increment typically in Seconds • Chrome (Webkit) ePoch • Begins 1 January1601 • Incremented in microseconds • Convert by subtracting 11644473600 and divide by a million • Firefox • Depends • Can be in Unixtime or Chrometime

17. SQLite Forensics Datetime Converting • Chrome – Top_Sites • SELECT last_updated, • datetime(((last_updated -11644473600000000)/1000000),'unixepoch','localtime') As ‘last_updated’ • FROM thumbnails;

18. SQLite Forensics Deleted Records • Deleted records can be recovered! (but not always) • Deleted records not overwritten • Deleted records are added to a “freelist” page • Deleted records are reassigned • Deleted records expunged by “vacuum()”

19. SQLite Forensics MacOSX+ • Important Databases QuickLook Document Revisions

20. SQLite Forensics MacOSX+ • DocumentRevisions • Stores previous versions of documents • Also stores chunks of changed documents • File path in database links to physical path in folder tree • Not user configurable

21. SQLite Forensics MacOSX+ • Quicklook • Cached thumbnails for file previews in Finder • Thumbnails for files with associated viewers

22. SQLite Forensics Browser SQLite databases • Chrome databases • Top Sites • Shortcuts • History • Favicons • Archived history • Cookies

23. SQLite Forensics Browser SQLite databases • Firefox databases • Cookies • Signons • Places • extensions

24. SQLite Forensics SQLite Tools • Way’s to review SQLite databases • Forensic tools • Database managers • Python

25. SQLite Forensics SQLite Tools • Encase: enscript – sqlitequery

26. SQLite Forensics SQLite Tools SQLiteDiver

27. SQLite Forensics SQLite Tools • Database Managers • Sqliteman – database manager • SQLiteManager Firefox extension • Navicat - commercial

28. SQLite Forensics SQLite Scripting • Python as a review tool • Build a script (to read “Favicons” database from Chrome) • Run the script • Review the output

29. SQLite Forensics SQLite Scripting Python Convert to datetime Linking the tables

30. SQLite Forensics SQLite Scripting • Python • Run the script

31. SQLite Forensics SQLite Scripting Convertedto Datetime! • Python • Here’s what we get as output

32. SQLite Forensics SQLite Lab • Lets get hands on with Python if time permits

33. SQLite Forensics Links and references • SQLite 3 Documentation: sqlite.org • OS X Lion Artifacts: by: Sean Cavanaugh, link • Recovering deleted records • Epilog • Oxygen Forensics • Another Forensics Blog, Python Parser

34. SQLite Forensics Q & A • David Dym • Email: ddym@g-cpartners.com • Twitter: @dave873 • Phone: (214) 377-1363 • My Blog: www.easymetadata.com/news Read our book!