Nest camera app (DFRWS2018 Challenge)

Last February, I started playing the DFRWS 2018 challenge:

• http://dfrws.org/dfrws-forensic-challenge
• https://github.com/dfrws/dfrws2018-challenge

This is a brief description of the challenge taken from the website:

"The DFRWS 2018 challenge is about Internet of Things (IoT), defined generally to include network and Internet connected devices usually for the purpose of monitoring and automation tasks. Consumer-grade “Smart” devices are increasing in popularity and scope. These devices and the data they collect are potentially interesting for digital investigations, but also come with a number of new investigation challenges."

Unfortunately for me, I soon had to dedicate my free time to something else and forgot about the challenge. Since the submission window is now closed (the submission deadline was March 20, 2019), I'd like to share my findings about an extension-less SQLite database named frame_database belonging to the Nest app for Android installed on the mobile device found at crime scene. This post is not a full analysis of the app.

The file is located here:

• Evidence: Samsung GSM_SM-G925F Galaxy S6 Edge
• Physical image: blk0_sda.bin
• Database path: P18\data\com.nest.android\cache\f315c6e2b5434a5381f1f5be6f73b4b3

The frame_raw_data_table table in the database caught my attention due to its name and I thought it could contain pictures or videos. The table contains 4548 records and its column names are:

frame_time | chunk_id | chunk_version | gop_start_rowid | sps_bytes | pps_bytes | frame_bytes | chunk_complete

A bit of googling made me find out that gop, sps and pps are parameters related to H.264 videos and stand for:

• GOP = Group of pictures
• SPS = Sequence Parameter Set
• PPS = Picture Parameter Set

This post at stackoverflow was also useful to understand that a stream could look like this:

(AUD)(SPS)(PPS)(I-Slice)(PPS)(P-Slice)(PPS)(P-Slice) ... (AUD)(SPS)(PPS)(I-Slice)

The explanation of all these terms is beyond the scope of this article. The only thing to care about is that there's a data sequence that needs to be rebuilt in order to get meaningful data to analyze.

Having said that, this is a data snippet of what is stored in the database:

The chunk_id field is a timestamp. Frames belonging to the same group have the same chunk_id. The last frame of each group has the chunk_complete value set to 1. The frame_time field is a timestamp too, but I haven't investigated the relationship between this field and the chunk_id. I noticed there's a significant time difference between the two fields. For instance:

• frame_time 1526289810036 translates to 14/05/2018 09:23:30 Unix milliseconds
• chunk_id 1526288836 translates to 14/05/2018 09:07:16 Unix seconds

After several failed data combination attempts, I found out that the following fields containing blob data have to be concatenated in this order to build a working group of frames:

sps_bytes || pps_bytes || frame_bytes (first) || frame_bytes (second) || ... || frame_bytes (last)

I achieved this operation by using a SQL query built with the concatenation operator || and the GROUP_CONCAT function:

SELECT chunk_id,
       ( Hex(sps_bytes) || Hex(pps_bytes)|| frame_bytes ) AS frame_bytes
FROM   (SELECT chunk_id,
               sps_bytes,
               pps_bytes,
               GROUP_CONCAT(Hex(frame_bytes), '') AS frame_bytes
        FROM   frame_raw_data_table
        GROUP  BY chunk_id) 

Then I wrote a quick Python script to automate the data extraction and write data to binary files. To try the script, just copy the code below and customize the paths of db and output_path.

To make Python 3.7.2 correcly execute the SQL query, I had to download the latest version of the SQLite dll from here (I used 64-bit DLL x64 for SQLite version 3.27.2) and save it to C:\Python37\DLLs and overwrite the existing one.

from datetime import datetime
import binascii
import sqlite3

db          = "C:\\temp\\frame_database"
output_path = "C:\\temp\\"

conn = sqlite3.connect(db)
conn = conn.execute("SELECT chunk_id,(hex(sps_bytes) || hex(pps_bytes) || 

frame_bytes) AS frame_bytes FROM (SELECT chunk_id,sps_bytes,pps_bytes,

GROUP_CONCAT(hex(frame_bytes),'') AS frame_bytes FROM 

frame_raw_data_table GROUP BY chunk_id)")

rows = conn.fetchall()

for row in rows:
    chunk_id    = row[0]
    frame_bytes = row[1]
    timestamp   = datetime.utcfromtimestamp(float(chunk_id))
    timestamp   = str(timestamp).replace(" ","_").replace(":","")
    filename    = output_path + str(chunk_id) + "_" + timestamp + ".h264"
    video       = open(filename,"wb")
    video.write(binascii.unhexlify(frame_bytes))

The very small video files (9KB/83KB) can be played with VLC media player (3.0.6):

There's always something new to learn.

Thanks DFRWS for the challenge!

Calllog.db and SMS data on Android 7.0 Nougat

A few weeks ago, Jamie McQuaid at Magnet Forensics wrote an interesting article titled Android Messaging Forensics – SMS/MMS and Beyond. The article is a great overview of the different databases that Android uses to store SMS/MMS data.

Based on my recent findings, I think that another database should be mentioned: calllog.db. During the analysis of two Samsung smartphones (SM-G935F and SM-G930F) running Android 7.0, I've found out that calllog.db also contains SMS data.

I was able to find and analyze this db because I had created a physical dump of the two non-rooted smartphones by using UFED 4PC. I haven't verified if the file can be obtained with other methods like a ADB backup.

This sqlite database can be found here:

\data\com.android.providers.contacts\databases\calllog.db

The table calls within the database has a column named m_content. If the length of this field is greater than 0, it means that the record contains a text message (if zero, it's a phone call log).

At a quick glance, the most relevant fields in the table are:

• number: sender's or recipient's phone number;
• date: message date;
• type: its value indicates if it's a sent or received message; 
• name: contact name associated with the phone number;
• last_modified: message date (it usually matches the date of the field date);
• m_content: first 50 characters of SMS message body.

This is a quick query to view the contents of these fields:

SELECT number,datetime(date/1000,"unixepoch","utc") AS date,type,name,datetime(last_modified/1000,"unixepoch","utc") AS last_modified,m_content FROM calls WHERE m_content <>""

If I run the query SELECT DISTINCT type FROM calls WHERE m_content <>"", I can see that the field type has value "1" or "2". After some comparison with other messages, I deduced that "1" means "received" and "2" is "sent".

As of writing, by default the tools UFED Physical Analyzer (7.9.0.223) and Magnet AXIOM (2.5.1.11408 - with custom artifact plugin) only extract call logs from calllog.db.

This is how I adapted the tools to my needs.

UFED Physical Analyzer

By using the internal tool SQLite Wizard within the Physical Analyzer, I created a query to do the parsing:

Select calls.number,
  calls.date,
  calls.type,
  calls.name,
  calls.last_modified,
  calls.m_content
From calls
Where Length(calls.m_content) > 0

I then mapped the fields by drag&drop and customized the conditions of the field type.

I ran the query and successfully retrieved 500 additional SMS records that I added to my report.

The table shows the big difference in the number of SMS found before and after parsing the file calllog.db.

(before)

(after)

Some messages were duplicates, but many others were not. This is an example sorted by message body:

While creating the query, I tried with and without the "include deleted rows" option. In the end I decided to keep it unticked since I was getting too many false results.

Anyway you can download both query versions from here.

How to import and run the query: Physical Analyzer | Tools | SQLite Wizard | Open SQLite query manager | Import | select the query file to use | Run.


Magnet Axiom

When creating a new case with Magnet AXIOM Process, I recommend to use the Dynamic App Finder ("Find more artifacts" turned ON). It's a useful feature that allows to discover additional databases that may contain relevant data. Once the search is complete, a window pops up asking to select any of the found databases and to map the needed fields.

When done, click on "Save selected artifacts". Magnet AXIOM Examine will show these custom artifacts under the category "Custom".

My custom artifact can be downloaded from here. Just import it from the menu in Magnet AXIOM Process (Tools | Manage custom artifacts | Add new custom articact) or simply copy the file to the path "C:\Program Files\Magnet Forensics\Magnet AXIOM\AXIOM Process\plugins".

Decrypting WhatsApp crypt9

This is a quick tutorial on how to decrypt WhatsApp crypt9 databases.

Requirements

• an Android emulator (I used BlueStacks)
• WhatCrypt app by TripCode - WhatsApp Database Crypt Tool (I know there is a web version of this tool, but that's something I'm not allowed to use)

For the following steps, the two tools require no internet connection to work.


Steps

1. Run BlueStacks
2. Drag and drop the .apk file from your PC to the emulator window in order to install WhatCrypt
   
3. There's a shared folder between the host and the emulator located in: C:\ProgramData\BlueStacks\UserData\SharedFolder
4. Copy into this folder the WhatsApp key file and your crypt9 databases. Rename the key file to whatsapp.cryptkey otherwise WhatCrypt won't be able to detect it.
5. Run WhatCrypt
6. Click WhatsApp database
7. Double click on Encrypted Database Path
8. Navigate to the path /storage/sdcard/windows/BstSharedFolder and select the crypt9 db you need to decrypt
9. Double click Key File Path
10. Select the whatsapp.cryptkey file
11. Click Decrypt Database

Done! A SQLite database named msgstore.db will appear in the SharedFolder on your computer.