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!
