Thunderbird Artifacts [FREE COURSE CONTENT]

In this excerpt from our Ubuntu Forensics course we will take a closer look at Thunderbird artifacts. Thunderbird is a popular email client, and all forensic investigators should know what information it can hold - so here's a handy list for you! Let us know if you have ever used one of these Thunderbird artifacts in an investigation :)!

global-messages.db.sqlite Database

The global-messages.db.sqlite is the SQLite database used by Thunderbird to index and search messages.

Although the SQL analysis of the Thunderbird mailbox can take a long time and other solutions are preferred, we will see how to set up some useful queries to understand the mechanism underlying the e-mail client.

First of all, let’s see some useful tables among those present: contacts, identities and messages, messagesText_content.

contacts, identities

We can extract the list of contacts and associate it with the email addresses stored in the table identities with the following syntax:

SELECT contacts.name, identities.value FROM contacts, identities WHERE contacts.id=identities.id;

We can also filter the output. Let’s try to search some results related to bank:

SELECT contacts.name, identities.value, FROM contacts, identities WHERE contacts.id=identities.id AND contacs.name LIKE '%bank%';

messages, messagesText_content.

Let’s explain the messagesText_content table which stores the text messages:

Table	Column	Description
messagesText_content	Docid	Unique identifier
	c0body	The stored text.
	c1subject	Subject of the mail
	c2attachmentNames	Name of the attachments
	c3author	Sender
	c4recipients	Receiver

Filter by sender/receiver

We can extract some useful information, even using this table only. For example, all the messages received from a certain contact. Let’s take Amazon as an example.

SELECT DISTINCT docid, c1subject, c3author
FROM messagesText_content
WHERE c3author LIKE '%amazon%';

Note that we’ve added: DISTINCT after SELECT to avoid duplicate,.

We can check all the messages sent to a certain contact just by exchanging c3author with c4recipients:

SELECT DISTINCT docid, c1subject, c4recipients
FROM messagesText_content
WHERE c4recipients LIKE '%amazon%';

We see that’s a reply to an Amazon.com Order message.

Body of the message

We can check the content of a message specifying its docid in the query adding SELECT c0body and, at the end of the query, AND docid=[docid-number].

Let’s take messages 2314 as an example.

SELECT DISTINCT docid, c1subject, c2attachmentNames, c0body
FROM messagesText_content
WHERE c3author LIKE '%amazon%' AND docid=2314;

Attachments

We can check all the attachments received from a certain contact using AND c2attachmentNames <> '';

In this way, we filter out messages with no files attached.

SELECT DISTINCT docid, c1subject, c2attachmentNames
FROM messagesText_content 
WHERE c3author LIKE '%amazon%' AND c2attachmentNames <> '';

Attachments are not stored in the global-messages.db.sqlite, so we can’t extract it via SQL. Later we will see how to export and rebuild a complete Thunderbird mailbox with attachments with Thunderbird Email Parser.

Date

We can associate a message to its sending or receiving date by crossing the id value from the table messages and docid value from the messagesText_content table adding AND messages.id=messagesText_content.docid.

SELECT DISTINCT datetime(messages.date/1000000, 'unixepoch', 'localtime'), docid, c1subject, c2attachmentNames
FROM messages, messagesText_content 
WHERE c3author LIKE '%amazon%'
AND c2attachmentNames <> ''
AND messages.id=messagesText_content.docid;

folderLocations

We can list all the email folders using the columns id and name from the folderLocations:

SELECT id, name FROM folderLocations;

We can see which messages are stored crossing the id value from the table folderLocations and folderID value from the messages table using AND folderLocations.id=messagesText_content.docid.

We look for the Amazon folder.

Again, we can associate a message to its sending or receiving date by crossing the id value from the table messages and docid value from the messagesText_content table adding AND messages.id=messagesText_content.docid.

We can also use the messagesText_content folder for subjects, senders and receivers.

SELECT DISTINCT folderLocations.id, name, c1subject, docid
FROM folderLocations, messages, messagesText_content
WHERE folderLocations.id=messages.folderID
AND folderLocations.name='Amazon'
AND messages.id=messagesText_content.docid;

Again, we can take a look at the content of the message using their docid value:

SELECT DISTINCT docid, c1subject, c2attachmentNames, c0body
FROM messagesText_content
WHERE docid=7505;

Thunderbird e-mail parser

A great tool for Thunderbird mailbox acquisitions is Mari De Grazia’s Thunderbird Email Parser, a python script which parses the raw e-mails, including deleted e-mails, in files created by Thunderbird and located in the user’s Thunderbird directory at the path /home/[username]/.thunderbird/ $PROFILE.default/.

According to the developer, “these files do not have a file extension and contain emails in MIME format. It will parse the Header information (To, from, CC, BC, Date and Subject) into an Excel file and create a link to the .eml file. It will also list the attachments”.

Before cloning the tool, it is advisable to download the xlwt library:

sudo easy_install xlwt

Then we can clone the parser from Git-Hub:

git clone https://github.com/mdegrazia/Thunderbird-Email-Parser

Now, let’s choose a user folder previously extracted. In this case, we’ve copied the user’s directory at the path /home/luca/evidences/thunderbird

sudo python2 /home/luca/thunderbird_parser.py -d /home/luca/evidences/thunderbird/art90cuz.default/ImapMail/in.alice.it -o /home/luca/evidences/thunderbird/parsed_emails

Now, the reconstructed mailbox is ready to be analysed in the parsed_emails folder as .eml files.


[IMG]

For further information about the tool, you can check Mari De Grazia’s blog:

http://az4n6.blogspot.com/2014/04/whats-word-thunderbird-parser-that-is.html

logins.json

The .json file logins.json stores information about previously memorized Firefox and Thunderbird passwords.

We can take a look at a more human-readable version of the file using json.tool:

cat logins.json | python -m json.tool > formatted.json
cat formatted.json

Note that, although username and password are encrypted, we’ve collected some interesting information about time values related to password creation, use and change. Furthermore, we see how many times the password was used (1) and, most important, the logins site at the line hostname and formSubmitUrl.

To decrypt the stored values, we need to collect and put in the same directory of the file logins.json two other files from the Firefox/Thunderbird profile folder:

cert9.db

An NSS certificate database.

key4.db

An NSS key database.