Authors: TJ O'Connor
In this section we will examine application artifacts, namely data stored in SQLite Databases by two popular applications. The SQLite database is a popular choice for local/client storages on several different applications, especially web browsers, because of the programming-language-independent bindings. As opposed to a database that maintains a client/server relationship, SQLite stores the entire database as a single flat file on the host. Originally created by Dr. Richard Hipp for his work with the US Navy, SQLite databases continue to grow usage in many popular applications. Applications built by Apple, Mozilla, Google, McAfee, Microsoft, Intuit, General Electrics, DropBox, Adobe and even Airbus utilize the SQLite database format (
SQLite, 2012
). Understanding how to parse SQLite databases and automating the process using Python is invaluable during forensic investigations. The next section begins by examining the SQLite database format used in the popular Skype voice-over-ip, chat client.
As of version 4.0, the popular chat utility Skype changed its internal database format to use SQLite (
Kosi2801., 2009
). Under Windows, Skype stores a database named
main.db
in the C:\Documents and Settings\
Data\Skype\
SELECT tbl_name FROM sqlite_master WHERE type==”table”
The SQLite database maintains a table named sqlite_master; this table contains a column named tbl_name, which describes each of the tables in the database. Executing this SELECT statement allows us to see tables in the Skype
main.db
database. We can now see that this database holds tables containing information about contacts, calls, accounts, messages, and even SMS messages.
investigator$ sqlite3 main.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> SELECT tbl_name FROM sqlite_master WHERE type==”table”;
DbMeta
Contacts
LegacyMessages
Calls
Accounts
Transfers
Voicemails
Chats
Messages
ContactGroups
Videos
SMSes
CallMembers
ChatMembers
Alerts
Conversations
Participants
The table
Accounts
contains information about the Skype account used by the application. It contains columns that include information about the user’s name, Skype profile name, the location of the user, and the creation date of
the account. To query this information, we can create a SQL statement that SELECTs these columns. Notice that the database stores the date in unixepoch time and requires conversion to a more user-friendly format. Unixepoch time provides a simple measurement for time. It records the date as a simple integer that represents the number of seconds since January 1st, 1970. The SQL method
datetime()
can convert this value into an easily readable format.
sqlite> SELECT fullname, skypename, city, country, datetime(profile_timestamp,‘unixepoch’) FROM accounts;
TJ OConnor|
While connecting to the database and executing a SELECT statement proves easy enough, we would like to be able to automate this process and extra information from several different columns and tables in the database. Let’s write a small Python program that utilizes the sqlite3 library to do this. Notice our function printProfile(). It creates a connection to the database
main.db
. After creating a connection, it asks for a cursor prompt and executes our previous SELECT statement. The result of the SELECT statement returns an array of arrays. For each result returned, it contains indexed columns for the user, skype username, location, and profile date. We interpret these results and then pretty print them to the screen.
import sqlite3
def printProfile(skypeDB):
conn = sqlite3.connect(skypeDB)
c = conn.cursor()
c.execute(“SELECT fullname, skypename, city, country, \
datetime(profile_timestamp,‘unixepoch’) FROM Accounts;”)
for row in c:
print ‘[∗] -- Found Account --’
print ‘[+] User: ’+str(row[0])
print ‘[+] Skype Username: ’+str(row[1])
print ‘[+] Location: ‘+str(row[2])+’,’+str(row[3])
print ‘[+] Profile Date: ’+str(row[4])
def main():
skypeDB = “main.db”
printProfile(skypeDB)
if __name__ == “__main__”:
main()
Running the output of printProfile.py, we see that the Skype
main.db
database contains a single user account. For privacy concerns, we replaced the actual account name with
investigator$ python printProfile.py
[∗] -- Found Account --
[+] User: TJ OConnor
[+] Skype Username:
[+] Location: New York, NY,us
[+] Profile Date: 2010-01-17 16:28:18
Let’s further the investigation into the Skype database by examining the stored address contacts. Notice that the table
Contacts
stores information such as the displayname, skype username, location, mobile phone, and even birthday for each contact stored in the database. All of this personally identifiable information can prove useful as we investigate or attack a target, so let’s gather it. Let’s output the information that our SELECT statement returns. Notice that several of these fields, such as birthday, could be null. In these cases, we utilize a conditional IF statement to only print results not equal to “None.”
def printContacts(skypeDB):
conn = sqlite3.connect(skypeDB)
c = conn.cursor()
c.execute(“SELECT displayname, skypename, city, country,\
phone_mobile, birthday FROM Contacts;”)
for row in c:
print ‘\n[∗] -- Found Contact --’
print ‘[+] User: ’ + str(row[0])
print ‘[+] Skype Username: ’ + str(row[1])
if str(row[2]) != ’’ and str(row[2]) != ‘None’:
print ‘[+] Location: ’ + str(row[2]) + ‘,’ \
+ str(row[3])
if str(row[4]) != ‘None’:
print ‘[+] Mobile Number: ’ + str(row[4])
if str(row[5]) != ‘None’:
print ‘[+] Birthday: ’ + str(row[5])
Up until now we have only examined extracting specific columns from specific tables. However, what happens when two tables contain information that we want to output together? In this case, we will have to join the database tables with values that uniquely identify the results. To illustrate this, let us examine
how to output the call log stored in the skype database. To output a detailed Skype call log, we will need to use both the
Calls
table and the
Conversations
table. The
Calls
table maintains the timestamp of the call and uniquely indexes each call with a column named
conv_dbid
. The
Conversations
table maintains the identity of callers and indexes each call made with a column named
id.
Thus, to join the two tables we need to issue a SELECT statement with a condition WHERE calls.conv_dbid = conversations.id. The result of this statement returns results containing the times and identities of all Skype calls made and stored in the target’s Skype database.
def printCallLog(skypeDB):
conn = sqlite3.connect(skypeDB)
c = conn.cursor()
c.execute(“SELECT datetime(begin_timestamp,‘unixepoch’), \
identity FROM calls, conversations WHERE \
calls.conv_dbid = conversations.id;”
)
print ‘\n[∗] -- Found Calls --’
for row in c:
print ‘[+] Time: ’+str(row[0])+\
‘ | Partner: ’+ str(row[1])
Let’s add one final function to our Skype database scrapping script. Forensically rich, the Skype profile database actually contains all the messages sent and received by a user by default. The database stores this in a table named Messages. From this table, we will SELECT the timestamp, dialog_partner, author, and body_xml (raw text of the message). Notice that if the author differs from the dialog_partner, the owner of the database initiated the message to the dialog_partner. Otherwise, if the author is the same as the dialog_partner, the dialog_partner initiated the message, and we will print from the dialog_partner.
def printMessages(skypeDB):
conn = sqlite3.connect(skypeDB)
c = conn.cursor()
c.execute(“SELECT datetime(timestamp,‘unixepoch’), \
dialog_partner, author, body_xml FROM Messages;”)
print ‘\n[∗] -- Found Messages --’
for row in c:
try:
if ‘partlist’ not in str(row[3]):
if str(row[1]) != str(row[2]):
msgDirection = ‘To ’ + str(row[1]) + ‘: ’
else:
msgDirection = ‘From ’ + str(row[2]) + ‘: ’
print ‘Time: ’ + str(row[0]) + ‘ ’ \
+ msgDirection + str(row[3])
except:
pass
Wrapping everything together, we have a pretty potent script to examine the Skype profile database. Our script can print the profile information, address contacts, call log, and even the messages stored in the database. We can add some option parsing in the main function and use some of the functionality in the os library to ensure the profile file exists before executing each of the functions to investigate the database.
import sqlite3
import optparse
import os
def printProfile(skypeDB):
conn = sqlite3.connect(skypeDB)
c = conn.cursor()
c.execute(“SELECT fullname, skypename, city, country, \
datetime(profile_timestamp,‘unixepoch’) FROM Accounts;”)
for row in c:
print ‘[∗] -- Found Account --’
print ‘[+] User: ’+str(row[0])
print ‘[+] Skype Username: ’+str(row[1])
print ‘[+] Location: ’+str(row[2])+’,’+str(row[3])
print ‘[+] Profile Date: ’+str(row[4])
def printContacts(skypeDB):
conn = sqlite3.connect(skypeDB)
c = conn.cursor()
c.execute(“SELECT displayname, skypename, city, country,\
phone_mobile, birthday FROM Contacts;”)
for row in c:
print ‘\n[∗] -- Found Contact --’
print ‘[+] User: ’ + str(row[0])
print ‘[+] Skype Username: ’ + str(row[1])
if str(row[2]) != ’’ and str(row[2]) != ‘None’:
print ‘[+] Location: ’ + str(row[2]) + ‘,’ \
+ str(row[3])
if str(row[4]) != ‘None’:
print ‘[+] Mobile Number: ’ + str(row[4])
if str(row[5]) != ‘None’:
print ‘[+] Birthday: ’ + str(row[5])
def printCallLog(skypeDB):
conn = sqlite3.connect(skypeDB)
c = conn.cursor()
c.execute(“SELECT datetime(begin_timestamp,‘unixepoch’), \
identity FROM calls, conversations WHERE \
calls.conv_dbid = conversations.id;”
)
print ‘\n[∗] -- Found Calls --’
for row in c:
print ‘[+] Time: ’+str(row[0])+\
‘ | Partner: ’+ str(row[1])
def printMessages(skypeDB):
conn = sqlite3.connect(skypeDB)
c = conn.cursor()
c.execute(“SELECT datetime(timestamp,‘unixepoch’), \
dialog_partner, author, body_xml FROM Messages;”)
print ‘\n[∗] -- Found Messages --’
for row in c:
try:
if ‘partlist’ not in str(row[3]):
if str(row[1]) != str(row[2]):
msgDirection = ‘To ’ + str(row[1]) + ‘: ’
else:
msgDirection = ‘From ’ + str(row[2]) + ‘: ’
print ‘Time: ’ + str(row[0]) + ‘ ’ \
+ msgDirection + str(row[3])
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)
elif os.path.isdir(pathName) == False:
print ‘[!] Path Does Not Exist: ’ + pathName
exit(0)
else:
skypeDB = os.path.join(pathName, ‘main.db’)
if os.path.isfile(skypeDB):
printProfile(skypeDB)
printContacts(skypeDB)
printCallLog(skypeDB)
printMessages(skypeDB)
else:
print ‘[!] Skype Database ’+\
‘does not exist: ’ + skpeDB
if __name__ == ‘__main__’:
main()
Running the script, we add the location of a Skype profile path with the –p option. The script prints out the account profile, contacts, calls, and messages stored on the target. Success! In the next section, we will use our knowledge of sqlite3 to examine the artifacts stored by the popular Firefox browser.
investigator$ python skype-parse.py -p /root/.Skype/not.myaccount
[∗] -- Found Account --
[+] User: TJ OConnor
[+] Skype Username:
[+] Location: New York, US
[+] Profile Date: 2010-01-17 16:28:18
[∗] -- Found Contact --
[+] User: Some User
[+] Skype Username: some.user
[+] Location: Basking Ridge, NJ,us
[+] Mobile Number: +19085555555
[+] Birthday: 19750101
[∗] -- Found Calls --
[+] Time: 2011-12-04 15:45:20 | Partner: +18005233273
[+] Time: 2011-12-04 15:48:23 | Partner: +18005210810
[+] Time: 2011-12-04 15:48:39 | Partner: +18004284322
[∗] -- Found Messages --
Time: 2011-12-02 00:13:45 From some.user: Have you made plane reservations yets?
Time: 2011-12-02 00:14:00 To some.user: Working on it…
Time: 2011-12-19 16:39:44 To some.user: Continental does not have any flights available tonight.
Time: 2012-01-10 18:01:39 From some.user: Try United or US Airways, they should fly into Jersey.
More Information…
Other Useful Skype Queries…
If interested, take the time to examine the Skype database further and make new scripts. Consider the following other queries that may prove helpful:Want to print out only the contacts with birthdays in the contact list?SELECT fullname, birthday FROM contacts WHERE birthday > 0;Want to print a record of conversations with only a specific