SQLite Forensics David Dym GC Partners www encase

SQLite Forensics David Dym G-C Partners www. encase. com/ceic

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

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

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

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

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

SQLite Forensics What is SQLite • 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 Write SQLite Database Read Page 7

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 4 c 69 74 65 20 66 6 f 72 6 d 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. Page 8

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 Page 9

SQLite Forensics Data. Types Page 10

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 Page 11

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

SQLite Forensics BLOB fields BLOB - storing binary plist in “properties” field of an i. OS sms database Page 13

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 Offset 18 19 Size 1 1 Description File format write version. 1 for legacy; 2 for WAL. File format read version. 1 for legacy; 2 for WAL. Page 14

SQLite Forensics Datetimes Handling Page 15

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

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

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()” Page 18

SQLite Forensics Mac. OSX+ Important Databases Quick. Look Document Revisions Page 19

SQLite Forensics Mac. OSX+ Document. Revisions • 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 Filename Path Tables db. sqlite /. Document. Revisions-V 100/db-V 1 files, generations, storage Page 20

SQLite Forensics Mac. OSX+ Quicklook • Cached thumbnails for file previews in Finder • Thumbnails for files with associated viewers Filename index. sqlite Path /private/var/folders/<dynamic>_<dynamic>/C/com. apple. Quick. Look. thumbnailcache Tip to Locate find /var/folders –name “Quicklook*” Page 21

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

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

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

SQLite Forensics SQLite Tools Encase: enscript – sqlitequery Page 25

SQLite Forensics SQLite Tools SQLite. Diver Page 26

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

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

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

SQLite Forensics SQLite Scripting Python • Run the script Page 30

SQLite Forensics SQLite Scripting Converted to Datetime! Python Here’s what we get as output 'http: //static 01. nyt. com/favicon. ico' 'http: //www. nytimes. com/2014/01/31/technology/amazons-shares-fall-asrevenue-disappoints. html? nl=todaysheadlines&emc=edit_th_20140131' '2014 -01 -31 08: 31: 39 'http: //www. nytimes. com/glogin? URI=http%3 A%2 F%2 Fwww. nytimes. com%2 F 20 14%2 F 01%2 F 31%2 Ftechnology%2 Famazons-shares-fall-as-revenuedisappoints. html%3 Fnl%3 Dtodaysheadlines%26 emc%3 Dedit_th_20140131%26_r 'http: //static 01. nyt. com/favicon. ico' %3 D 0' '2014 -01 -31 08: 31: 39 'http: //www. schaeffersresearch. com/favicon. ico' 'https: //lyris. schaeffer. com/t/113127/6595615/8359/50/' '2014 -01 -31 09: 32: 07 'http: //www. southwest. com/assets/images/favic on. ico' 'http: //www. southwest. com/' '2014 -03 -19 10: 01: 18 'https: //ssl. gstatic. com/s 2/oz/images/faviconr 3. ic o' 'http: //ow. ly/t 9 y 7 h ' '2014 -03 -12 21: 40: 37 ' ' ' Page 31

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

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 Page 33

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