Violent Python: A Cookbook for Hackers, Forensic Analysts, Penetration Testers and Security Engineers (15 page)
Authors: TJ O'Connor
Running our script against a Firefox user profile under investigation, we see the results. In the next section, we will use the skills learned in the two previous sections, but expand our knowledge of SQLite by searching through a haystack of databases to find a needle.
investigator$ python parse-firefox.py -p ∼/Library/Application\ Support/Firefox/Profiles/5ab3jj51.default/
[∗] --- Files Downloaded ---
[+] File: ANONOPS_The_Press_Release.pdf from source: http://www.wired.com/images_blogs/threatlevel/2010/12/ANONOPS_The_Press_Release.pdf at: 2011-12-14 05:54:31
[∗] -- Found Cookies --
[+] Host: .mozilla.org, Cookie: wtspl, Value: 894880
[+] Host: www.webassessor.com, Cookie: __utma, Value: 1.224660440401.13211820353.1352185053.131218016553.1
[∗] -- Found History --
[+] 2011-11-20 16:28:15 - Visited: http://www.mozilla.com/en-US/firefox/8.0/firstrun/
[+] 2011-11-20 16:28:16 - Visited: http://www.mozilla.org/en-US/firefox/8.0/firstrun/
[∗] -- Found Google --
[+] 2011-12-14 05:33:57 - Searched For: The meaning of life?
[+] 2011-12-14 05:52:40 - Searched For: Pterodactyl
[+] 2011-12-14 05:59:50 - Searched For: How did Lost end?
In April 2011, security researcher and former Apple employee Pete Warden disclosed a privacy issue with the popular Apple iPhone/Ipad iOS operating system (
Warden, 2011
). After a significant investigation, Mr. Warden revealed proof that the Apple iOS operating system actually tracked and recorded the GPS coordinates of the device and stored them in a database on the phone called
consolidated.db
(
Warden, 2011
). Inside this database, a table named CellLocation contained the GPS points the phone had collected. The device determined the location information by triangulating off the nearest cell-phone towers in order to provide the best service for the device user. However, as Mr. Warden suggested, this same data could be used maliciously to track the entire movements an iPhone/iPad user. Furthermore, the process used to backup and store a copy of the mobile device to a computer also recorded this information. While the location-recording information has been removed from the Apple iOS operating system functionality, the process Mr. Warden used to discover the data remains. In this section, we will repeat this process to extract
information from iOS mobile device backups. Specifically, we will extract all the text messages out of an iOS backup using a Python script.
When a user performs a backup of his iPhone or iPad device, it stores files in a special directory on his or her machine. For the Windows operating system, the iTunes application stores that mobile device backup directory under the user’s profile directory at C:\Documents and Settings\
Examining the directory that stores our mobile directory backup, we see it contains over 1000 unhelpfully named files. Each file contains a unique sequence of 40 characters that provide absolutely no description of the material stored in the specific file.
investigator$ ls
68b16471ed678a3a470949963678d47b7a415be3
68c96ac7d7f02c20e30ba2acc8d91c42f7d2f77f
68b16471ed678a3a470949963678d47b7a415be3
68d321993fe03f7fe6754f5f4ba15a9893fe38db
69005cb27b4af77b149382d1669ee34b30780c99
693a31889800047f02c64b0a744e68d2a2cff267
6957b494a71f191934601d08ea579b889f417af9
698b7961028238a63d02592940088f232d23267e
6a2330120539895328d6e84d5575cf44a082c62d
<..SNIPPED..>
To get a little more information about each file, we will use the UNIX command
file
to extract the file type of each file. This command uses the first identifying bytes of a file header and footer to determine the file type. This provides us slightly more information, as we see that the mobile backup directory contains some sqlite3 databases, JPEG images, raw data, and ASCII text files.
investigator$ file ∗
68b16471ed678a3a470949963678d47b7a415be3: data
68c96ac7d7f02c20e30ba2acc8d91c42f7d2f77f: SQLite 3.x database
68b16471ed678a3a470949963678d47b7a415be3: JPEG image data
68d321993fe03f7fe6754f5f4ba15a9893fe38db: JPEG image data
69005cb27b4af77b149382d1669ee34b30780c99: JPEG image data
693a31889800047f02c64b0a744e68d2a2cff267: SQLite 3.x database
6957b494a71f191934601d08ea579b889f417af9: SQLite 3.x database
698b7961028238a63d02592940088f232d23267e: JPEG image data
6a2330120539895328d6e84d5575cf44a082c62d: ASCII English text
<..SNIPPED..>
While the
file
command does let us know that some of the files contain SQLite databases, it does very little to describe the content in each database. We will use a Python script to quickly enumerate all the tables in each database found in the entire mobile backup directory. Notice that we will again utilize the sqlite3 Python bindings in our example script. Our script lists the contents of the working directory and then attempts to make a database connection to each file. For those that succeed in making a connection, the script executes the command
SELECT tbl_name FROM sqlite_master WHERE type==‘table’
Each SQLite database maintains a table named sqlite_master that contains the overall database structure, showing the overall schema of the database. The previous command allows us to enumerate out the database schema.
import os, sqlite3
def printTables(iphoneDB):
try:
conn = sqlite3.connect(iphoneDB)
c = conn.cursor()
c.execute(‘SELECT tbl_name FROM sqlite_master \
WHERE type==\”table\”;’)
print “\n[∗] Database: “+iphoneDB
for row in c:
print “[-] Table: “+str(row)
except:
pass
conn.close()
dirList = os.listdir(os.getcwd())
for fileName in dirList:
printTables(fileName)
Running our script, we enumerate the schema of all the databases in our mobile backup directory. While the script does find several databases, we have snipped the output to show a specific database of concern. Notice that the file d0d7e5fb2ce288813306e4d4636395e047a3d28 contains a SQLite database with a table named
messages
. This database contains a listing of the text messages stored in the iPhone backup.
investigator$ python listTables.py
<..SNIPPED…>
[∗] Database: 3939d33868ebfe3743089954bf0e7f3a3a1604fd
[-] Table: (u’ItemTable’,)
[∗] Database: d0d7e5fb2ce288813306e4d4636395e047a3d28
[-] Table: (u’_SqliteDatabaseProperties’,)
[-] Table: (u’message’,)
[-] Table: (u’sqlite_sequence’,)
[-] Table: (u’msg_group’,)
[-] Table: (u’group_member’,)
[-] Table: (u’msg_pieces’,)
[-] Table: (u’madrid_attachment’,)
[-] Table: (u’madrid_chat’,)
[∗] Database: 3de971e20008baa84ec3b2e70fc171ca24eb4f58
[-] Table: (u’ZFILE’,)
[-] Table: (u’Z_1LABELS’,)
<..SNIPPED..>
Although we now know that the SQLlite database file d0d7e5fb2ce288813306e4d4636395e047a3d28 contains the text messages database, we want to be able to automate the investigation on different backups. To execute this, we write a simple function named
isMessageTable()
. This function will connect to a database and enumerate the information schema of the database. If the file contains a table named messages, it returns True. Else, the function returns False. Now we have the ability to quickly scan a directory of thousands of files and determine which specific file contains the SQLite database that contains the text messages.
def isMessageTable(iphoneDB):
try:
conn = sqlite3.connect(iphoneDB)
c = conn.cursor()
c.execute(‘SELECT tbl_name FROM sqlite_master \
WHERE type==\”table\”;’)
for row in c:
if ‘message’ in str(row):
return True
except:
return False
Now that we can locate the text message database, we want to be able to print the data contained in the database—specifically the date, address, and text messages. To do this, we will connect to the database and execute the command
‘select datetime(date,\‘unixepoch\’), address, text from message WHERE address>0;’
We can then print the results of this query to the screen. Notice, we will use some exception handling. In the event that isMessageTable() returned a database that is not our actual text message database, it will not contain the necessary columns: data, address, and text. If we grabbed the wrong database by mistake, we will allow the script to catch the exception and continue executing until the correct database is found.
def printMessage(msgDB):
try:
conn = sqlite3.connect(msgDB)
c = conn.cursor()
c.execute(‘select datetime(date,\’unixepoch\’),\
address, text from message WHERE address>0;’)
for row in c:
date = str(row[0])
addr = str(row[1])
text = row[2]
print ‘\n[+] Date: ‘+date+’, Addr: ‘+addr \
+ ‘ Message: ‘ + text
except:
pass
Packaging the functions
isMessageTable() and printMessage()
together, we can now construct the final script. We will add some option parsing to the script to include parsing the iPhone backup directory as an option. Next, we will list the contents of this directory and test each file until we find the text message database. Once we find this file, we can print the contents of the database to the screen.
import os
import sqlite3
import optparse
def isMessageTable(iphoneDB):
try:
conn = sqlite3.connect(iphoneDB)
c = conn.cursor()
c.execute(‘SELECT tbl_name FROM sqlite_master \
WHERE type==\”table\”;’)
for row in c:
if ‘message’ in str(row):
return True
except:
return False
def printMessage(msgDB):
try:
conn = sqlite3.connect(msgDB)
c = conn.cursor()
c.execute(‘select datetime(date,\’unixepoch\’),\
address, text from message WHERE address>0;’)
for row in c:
date = str(row[0])
addr = str(row[1])
text = row[2]
print ‘\n[+] Date: ‘+date+’, Addr: ‘+addr \
+ ‘ Message: ‘ + text
except:
pass
def main():
parser = optparse.OptionParser(“usage%prog “+\
“-p
parser.add_option(‘-p’, dest=’pathName’,\
type=’string’,help=’specify skype profile path’)
(options, args) = parser.parse_args()
pathName = options.pathName
if pathName == None:
print parser.usage
exit(0)
else:
dirList = os.listdir(pathName)
for fileName in dirList:
iphoneDB = os.path.join(pathName, fileName)
if isMessageTable(iphoneDB):
try:
print ‘\n[∗] --- Found Messages ---’
printMessage(iphoneDB)
except:
pass
if __name__ == ‘__main__’:
main()
Running the script against an iPhone backup directory, we can see the results against some recent text messages stored in the iPhone backup.
investigator$ python iphoneMessages.py -p ∼/Library/Application\ Support/MobileSync/Backup/192fd8d130aa644ea1c644aedbe23708221146a8/
[∗] --- Found Messages ---
[+] Date: 2011-12-25 03:03:56, Addr: 55555554333 Message: Happy holidays, brother.
[+] Date: 2011-12-27 00:03:55, Addr: 55555553274 Message: You didnt respond to my message, are you still working on the book?
[+] Date: 2011-12-27 00:47:59, Addr: 55555553947 Message: Quick question, should I delete mobile device backups on iTunes?
<..SNIPPED..>
