What’s Up With Whatsapp

WhatsApp Messenger is a cross platform mobile application which allows you to exchange messages without paying for SMS. This information is taken from www.whatsapp.com that also describes that the application can be used on the iPhone, Android, Blackberry and Windows Phone. What it does not say is that this application has now 350 million active users each month. Users can share photos, chat and more all without SMS services of the cellular carrier. What this means to you the examiner is easy, the simple automated tool that extracts the SMS is going to miss a tremendous amount of information. Moreover, if your case hinges on a message that was sent or received you should be prepared to examine this application if it exists.

Taking a look at the database that we obtained from an iOS device running iOS 7 using AccessData’s MPE+ iLogical function you can quickly see it is a SQlite database typical to all applications on iOS and Android. Let’s look at the databases in the net.whatsapp.Whatsapp folder.

Figure 1 Filesystem view

Contained in the Documents folders are both the ChatStorage.sqlite file and Contacts.sqlite. Both are self-explanatory with ChatStorage containing chats and Contacts containing the contact lists. The Library folder contains the application data as well as the Snapshots folder. This folder will hold the last screens used and are stored in a png format. This can be some great information. Typically there will be one picture of the last chat and also the last contact screen. The Media folder is a treasure trove holding any audio, video or images shared and sent via the WhatsApp application. What is even better is this information is listed in subfolders with the Whatsapp user name. The Whatsapp user name is going to be the phonenumber associated with the user. An example is shown below. The net.whatsapp.Whatsapp.plist shows the user information for the device you are examining. This contains the username, status and associated times.

Figure 2 Media folder location

The real examination comes when we look inside of the databases. Let’s first look at the Contacts.sqlite file.

The Contacts.sqlite has several tables that correspond to the buttons in the application. The favorites table uses both the WACONTACT and WAPHONE table to identify the users. Using the PHONE column in both the WAFAVORITES and WAPHONE table you can ascertain the phone number associated in the WACONTACT table to determine the full name of the Whatsapp contact. The WACONTACT table is a duplicate of the iOS device contacts at the time of accepting the access requested by WhatsApp to access the devices contacts. The most important database to an investigation is going to be the ChatStorage.sqlite file which is also located in the same directory as the Contacts.sqlite.

The ChatStorage.sqlite contains several tables as well. For brevity I am going to only speak about a few. The WACHATSESSION table lists the active chats, the last date, the name of the user and their ID. The WAMEDIAITEM table lists the location in the filesystem, geolocation and a ton of metadata associated to the media stored. The WAMEDIALOCALPATH column points to the filesystem of the device showing where that attached media item is located. Using this table along with WAMESSAGE table you can link the media to the chat session and associated user. Speaking of the WAMESSAGE table let’s get into the most important table in my opinion.

The WAMESSAGE table contains several rows of importance but we are only going talk about ISFROMME, MESSAGESTATUS, MESSAGETYPE, MEDIAITEM, MESSAGEDATE, FROMJID, PUSHNAME, TEXT and TOJID. These tables can put together a complete picture for you as an investigator. Let’s get started.

ISFROMME – This column indicates if the message originated from the active account of the database you are examining. The column will contain a 0 or a 1. 1 indicating that the message originated from the database you are examining and it’s account and if a 0 the message did not.

MESSAGESTATUS – This column indicates the status of the message. If the message that has been sent or received has been read by both parties you will see a 2. If only one party as read the message it will have a 1. If there is a 0 this has been seen to indicate part of a group message or whatsapp message. If you look at the interface and see a check next to the message the table will reflect a 1; if there are two checks you will see a 2.

MESSAGETYPE – This column indicates if the message is a regular message, contains a file, is a message from whatsapp or has location attachment. If this column contains a 0 the message contains text, 1 will contain media, 6 is a whatsapp message and 5 indicates a location was sent. The location is a media file that can be found in the stored media folder as well.

CHATSESSION – This column indicates the chat session number. This table would be used to show the entire thread of the chat session.

LASTSESSION – This column will indicate the last message in the CHATSESSION. The number is corresponds to the CHATSESSION number and is in the column to indicate that message is the last message in the thread.

MEDIAITEM – This column give the media number that corresponds to the WAMEDIAITEM table.

MESSAGEDATE – This column gives the date of the message when sent and when read by the user. This format will depend on the OS whatsapp is running. For iOS it is a MAC Date and for Android I have seen microseconds.

FROMJID and TOJID can be used to get additional information on the whatsapp users.

PUSHNAME – This column will identify the name of the username of the sending party and can be tied to the contacts database for more information on the user.

TEXT – This column contains the chat text.

Of course with any release of an application the tables and associated markers can change, so please look into the data and make sure the information contained in this document is what you are seeing.

The best part about the database is the output when you put it all together. Using AccessData’s MPE+ I am able to select the database and then the associated tables and rows and create immediate output of the data into my report.This is all done without even leaving the application. Below is an example of pulling the data from whatsapp for the geolocation, date, URL in the database folder, the name of the user that sent the media and if any text was associated with the media. This can immediately be published into a report.

Figure 3 Pulling whatsapp data sample

Another example is using AccessData’s MPE to pull the content from the message table, associate with the user and get the date of the message. What is fantastic is the fact these queries are all saved and can allow reuse over and over in your examinations.

Figure 4 Pulling message data from whatsapp

Locating and analyzing application data on smart devices is of paramount importance in today’s digital examinations. Whatsapp is only one application in a sea of millions of iOS and Android applications available for smart device users.

If you are relying on the simple automated solution to pull data from the standard locations you are missing valuable data that can be easily obtained using tools built to handle the analysis of this type of digital data.