Base64 is an encoding scheme that is commonly used for data transport; it is not considered encryption because it has a known method for decoding, and does not require a unique key to decode the data. Base64 contains ASCII-printable characters, but the underlying data is binary (which will make our output somewhat messy!). An example from the payload column in the messages table of tc.db looks like this:

EhZtQzVtUFVQWmgxWnNRUDJ6aE44cy1nGAAiQldlbGNvbWUgdG8gVGFuZ28hIEhlcmUncyBob3cgdG8gY29ubmVjdCwgZ2V0IHNvY2lhbCwgYW5kIGhhdmUgZnVuIYABAKoBOwoFVGFuZ28SABoWbUM1bVBVUFpoMVpzUVAyemhOOHMtZyILCgcKABIBMRoAEgAqADD///////////8BsAHYioX1rym4AYKAgAjAAQHQAQDoAdC40ELIAgTQAgDqAgc4MDgwODg5yAMA2AMA2AXTHw==

For example, consider the following table:

You can see that the 12-byte input (divisible by 3) has no padding, while the other two input do have padding because they are not divisible by 3. This is important because it shows that while the equal signs are a strong indicator of Base64, the lack of an equal sign does not mean it isn't Base64!

Now that we understand a little about Base64, and recognize that our payload column is very likely encoded in Base64, we need to decode it. There are websites that will allow the user to paste in encoded data, and it will be decrypted (such as www.base64decode.org), but this is inconvenient for large amounts of data as each message must be input individually (and putting evidentiary data on the internet is also frowned upon in most cases). Likewise, it can be decoded on the command line of Linux-based systems, but is equally inconvenient for large amounts of data. Our solution was to build a Python script that pulls the Base64 data from the database, decodes it, and writes it back out to a new file:

import sqlite3
import base64
conn = sqlite3.connect('tc.db')
c = conn.cursor()
c.execute('SELECT msg_id, payload FROM messages')
message_tuples = c.fetchall()
with open('tcdb_out.txt', 'w') as f:
   for message_id, message in message_tuples:
         f.write(str(message_id) + 'x09')
         f.write(str(base64.b64decode(message)) + '
')

To run the code, simply paste this code into a new file, named tcdb.py, place the script in the same directory as tc.db, and on the command line navigate to that directory and run the following:

python tcdb.py

The script will make a file named tcdb_out.txt in the same directory. Opening the file in a text editor (or importing it into Excel as a tab-delimited file) will show the msg_id value so that the examiner can correlate the message back to the messages table, and the decoded payload shows a plaintext message (noted as type 0 in the database):

There are other messages types worth looking at. Here is a decoded payload entry for a video message:

Note that with the video message, we can see two URLs. They are both public, meaning anyone with the link can access them. The URL ending in thumbnail is a thumbnail of the video, while the other URL will download the complete video in MP4 format. The path to the SD card and filename for the image is also shown.

Image and audio messages are stored in a very similar format, and contain URLs to either view or download the file, as well as the path to the file on the SD card.

Here is a sample location message:

This time, we can see the exact coordinates the user was at, as well as the address. Again, a path on the SD card is also present, and will show the map view of the location. As with the other message types, a received message would also show the sender's name.

Finally, let's take a look at the userinfo.xml.db database. Here is what it looks like before being decoded properly:

We wrote another script very similar to the first to parse the userinfo.xml.db database:

import sqlite3
import base64
conn = sqlite3.connect('userinfo.xml.db')
c = conn.cursor()
c.execute('SELECT key, value FROM profiles')
key_tuples = c.fetchall()
with open('userinfo_out.txt', 'w') as f:
   for key, value in key_tuples:
         if value == None:
               value = 'Tm9uZQ=='
         f.write(str(base64.b64decode(key)) + 'x09')
         f.write(str(base64.b64decode(value)) + '
')

The only difference in the code is that the filenames, table names, and values changed, and this time both of the columns in the database are base64-encoded. Again, it can be run by placing it in the same location as userinfo.xml.db and running it with the following:

python userinfo.py

Here is the relevant portion of the resulting output file, showing the personal data the user used to register the account:

Further down in the output, there is also a list of all of the user's contacts who use Tango, and it includes the contacts' names and phone numbers.