Transcript SQLite Forensics - EasyMetaData.com

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 InfoSec 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
•
iOS
•
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 SQL92 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
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 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.
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
DataTypes
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 iOS 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 ePoch
•
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
Page 16
SQLite Forensics
Datetime Converting
Chrome – Top_Sites
SELECT last_updated,
datetime(((last_updated -11644473600000000)/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
MacOSX+
Important Databases
QuickLook
Document
Revisions
Page 19
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
Filename
Path
Tables
db.sqlite
/.DocumentRevisions-V100/db-V1
files, generations, storage
Page 20
SQLite Forensics
MacOSX+
Quicklook
•
Cached thumbnails for file previews in Finder
•
Thumbnails for files with associated viewers
Filename
index.sqlite
Path
/private/var/folders/<dynamic>/<dynamic>_<dynamic>/C/com.apple.QuickLook.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
SQLiteDiver
Page 26
SQLite Forensics
SQLite Tools
Database Managers
•
Sqliteman – database
manager
•
SQLiteManager 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://static01.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%3A%2F%2Fwww.nytimes.com%2F20
14%2F01%2F31%2Ftechnology%2Famazons-shares-fall-as-revenuedisappoints.html%3Fnl%3Dtodaysheadlines%26emc%3Dedit_th_20140131%26_r
'http://static01.nyt.com/favicon.ico'
%3D0'
'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/s2/oz/images/faviconr3.ic
o'
'http://ow.ly/t9y7h
'
'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: [email protected]
Twitter: @dave873
Phone: (214) 377-1363
My Blog: www.easymetadata.com/news
Page 34