1 / 34

SQLite Forensics

SQLite Forensics. David Dym G-C Partners. 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!.

rhys
Download Presentation

SQLite Forensics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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!

More Related