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 :)!
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.
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%';
Let’s explain the messagesText_content table which stores the text messages:
|c0body||The stored text.|
|c1subject||Subject of the mail|
|c2attachmentNames||Name of the attachments|
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;
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.
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;
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.
For further information about the tool, you can check Mari De Grazia’s blog:
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:
An NSS certificate database.
An NSS key database.