443-451-7330
Posted By admin
An increasing number of programs are employing SQLite to store data that can be of relevance in an investigation. Forensic practitioners who become familiar with SQLite and learn how to interpret these files will be in a better position to obtain the most usable information from available digital evidence. We cover this and other useful forensic techniques in our Mobile Device Forensics course (SANS SEC563).
Backup files from an iPhone or iPod Touch provide an excellent example of SQLite databases that digital forensic examiners can exploit with relative ease, provided they are not encrypted. Data backed up from an iPhone using iTunes such as call logs, contacts, multimedia, and other files are, by default, stored in SQLite database files under “~/Library/Application/Support/MobileSync/Backup” Mac. On Windows XP these backup files are stored in the user’s profile under “C:\Documents and Settings\[userprofile]\Application Data\Apple Computer\MobileSync\Backup” and Windows Vista has a “Roaming” subfolder in this path.
SQLite databases can be examined using a command line tool like sqlite3.exe (http://www.sqlite.org/) or with a GUI tool like SQLite Database Browser (http://sqlitebrowser.sourceforge.net/) shown here with the call log backed up from an iPhone.
The dates are in Unix string format and can be converted using Perl as shown here:
$ perl -e "print scalar(gmtime(1247848584))" Fri Jul 17 16:36:24 2009
The use of SQLite databases gives forensic practitioners the ability to query the available data directly using the SQL database language. Although a full treatment of SQL is beyond the scope of this discussion, simple examples are provided here to get you started.
C:\>sqlite3.exe E:\iPhoneBackup\call_history.db SQLite version 3.6.16 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .tables _SqliteDatabaseProperties call sqlite> select * from call WHERE address like '%868%'; 2|+186835xxxxx|1247848584|60|4|-1 3|+186835xxxxx|1247853361|0|5|-1 4|+186835xxxxx|1247854453|0|5|-1 9|+186831xxxxx|1247895923|60|4|-1 10|+186835xxxxx|1247936960|60|5|-1 11|+186835xxxxx|1247941792|0|4|-1 12|+186835xxxxx|1247941827|0|4|-1 13|+186835xxxxx|1247941920|0|4|-1 14|+186835xxxxx|1247942844|0|4|-1 16|+186835xxxxx|1248015352|60|4|-1 17|+186835xxxxx|1248015674|0|4|-1 18|+186835xxxxx|1248016092|0|5|-1 26|+186835xxxxx|1248177103|0|5|3
The Symbian operating system for mobile devices also makes use of SQLite databases, and other computer applications store investigatively useful information in SQLite databases, including Firefox 3 and Skype. For instance, the moz_places table in the places.sqlite file from Firefox 3 is shown below.
This file can also be queried using SQL, as shown here being queried for all URLs containing the cmdLabs web site.
C:\tools>sqlite3 E:\firefox\places.sqlite SQLite version 3.6.16 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .tables moz_anno_attributes moz_favicons moz_keywords moz_annos moz_historyvisits moz_places moz_bookmarks moz_inputhistory moz_bookmarks_roots moz_items_annos sqlite> select * from moz_places WHERE url like '%cmdlabs%'; 621|http://www.cmdlabs.com/|Home|moc.sbaldmc.www.|1|0|1||2000 622|http://www.cmdlabs.com/page11/page11.html|Blog|moc.sbaldmc.www.|1|0|0||100 623|http://www.cmdlabs.com/services/services.html|Services|moc.sbaldmc.www.|1|0|0||100 624|http://www.cmdlabs.com/services/services/services-4.html|Training and Education|moc.sbaldmc.www.|1|0|0||100
Programs like Firefox that maintain usage records in these databases may leave remnants of deleted items that may be recoverable from unallocated disk space as detailed in Murilo Tito Pereira’s article “Forensic analysis of the Firefox 3 internet history and recovery of deleted SQLite records” (www.digitalinvestigation.net).
This entry was posted on Friday, August 21st, 2009 at 3:05 pm and is filed under Forensic Analysis. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.