Investigating Skype cloud-based media_cache/image sharing with the Forensic Browser for SQLite
Without going into the pros and cons of this, from a forensic point of view it is irrelevant anyway, the move has had the effect of introducing a new set of artefacts and in particular a new location for stored/cached image files (pictures).
More information here: https://support.skype.com/en/faq/FA1…t-is-the-cloud
This article deals with the SQLite tables that reference to these pictures, the locations of the pictures themselves and how to join the relevant tables, decode the data held in certain blob fields and create a report showing who sent what to whom including the pictorial evidence where possible.
At the end of the article, I will have shown how the different tables fit together and will provide a Browser extension that will create the necessary tables and import the cached pictures; you will be able to run a report that shows who sent an image and when. Alongside this, it will display the original image (if sent from the machine we are investigating) and will display the cached image. From the information, if the sender is the owner of the machine we are investigating, we will be able to see if the image was sent from this machine or was sent from another device and synced with this machine. In certain cases, we will be able to see the original path on a remote users machine (i.e. when someone sends an image to us) and therefore potentially glean information re the remote users operating system.
Note: This article was prepared after looking at a small test set of Skype installations on Windows 7 and 8 PCs, as such the details within may need to be revised at a later date when more information comes to light.
While this article is quite lengthy and a little technical it is important to realise that to use the Forensic Browser for SQLite (part of the Forensic Toolkit for SQLite) to examine the Skype media cache you don’t need to understand SQL, all you need to be able to do is to apply it, and this can be done in just a few short steps that will be summarised at the end.
This particular investigation started off when Jimmy Weg from Montana DCI contacted me and asked if I knew anything about the Skype media cache. He said that the files in the cache were created when a user/Skype synced between devices and he wanted to know if there was a way to determine the sender and recipient of the files.
At the end of this process you will be able to run a simple script that prompts you for the relevant file locations and that then creates the necessary queries such that you can run an installed report in the Forensic Browser for SQLite that looks as below and can be exported directly as a HTML report. No knowledge of SQL is required by the user.
C:\Users\Paul\AppData\Roaming\Skype\r3scue193\medi a_messaging\media_cache
The content, as can be seen below, were cached image files with some odd naming conventions and associated files that looked from their names like they might be thumbnail images.
A root around in the asyncdb subfolder of media_cache shows a cache_db.db file that on examination is unsurprisingly an SQLite database. This database contains just one table “assets” the content of which is shown below in the Forensic Browser for SQLite.
The access_time field records the 100 nanosecond intervals since 1/1/970 and the Forensic Browser for SQLite can decode this (and apply a timezone offset should I desire) for me. The serialized_data field is a Binary Large OBject (BLOB) and contains what appears to be the file name from the cache (more on this later), blobs are displayed as hex by default in the Browser.
The Forensic Browser allows me to add additional databases (attach them) to the query designer and then perform cross-database queries, so I attached main.db to the Browser and started looking through the tables.
One of these tables jumped straight out at me, not least because I recognized the name of my bike (a Capra) and the picture I had taken at the Falmouth Tall Ships event last year, pictures of both appear above.
We still need to show who the sender and receiver are, so back to the tables in main.db. I know that Skype often stores system status information in the messages table so the first thing I did was to look in the messages table at the approximate times recorded in the table above, this came up trumps. There were a number of records that were related to my previous query, these records all had a type ‘201’, so I was able to quickly build a visual query on just type 201 records from the messages table. You can see the rows in the original_name column above appear in the screenshot below embedded in the body_xml column:
You can find more information on the SQLite core functions here:
https://www.sqlite.org/lang_corefunc.html
We can now join the messages table to the MediaDocuments table and The MediaDocuments table to the assets table. The only thing that remains to be done is to import the original images if they exist from the original folders (original_name) and the cached images from the media_cache folder. While this can be done using the built-in functionality of the Forensic Browser as I am providing a Browser extension to create the joins on the different tables and extract the cached filenames from the serialized_data column it makes sense for me to also import the pictures in the extension. This means that you just need to run a single program and follow a few prompts to create your report. So all that needs to be done by you is to run the extension (if you are a Forensic Browser user and haven’t got a copy of this browser extension then please get in touch).
Running the Browser extension:
- First, run the Browser and create a case file (you should create a case file whenever using extensions)
- Open the main.db (I suggest that you do not create a working copy at this time)
- Now run the Skype Media Cache parser extension from the Extensions menu.
Select run and choose the case file
This next step is optional and you can just hit cancel.
Choose the path to the root of your extracted data and choose an offset to ensure valid paths. In the dialogue below the first three “file names to find” are possible valid files from the local file system, we are investigating. When the file path (from character 3) is appended to the prefix then a valid path on the investigation machine is obtained – then the extension checks the file path for any existing matching files and shows them in the bottom memo. At this point (when valid file paths show in the bottom memo) you can select OK to continue.
When the Browser extension completes, hit “Close”, there will be two new SQL queries saved in the Query Manager as below:
The first query “Full Query” returns every row from the combined tables (as well as any pictures that were imported). The second query “Abbreviated Query” returns a subset of the main columns from the query. You are of course encouraged to modify these queries to get the report you would like.
The remaining three queries are the SQL for the VIEWS used by the two main report generating queries above. While a single compound query could be written it is a useful practice to break down complex queries in to smaller subqueries/views in order to simplify the problem.
An example of the output of the abbreviated query is shown below:
What does it mean?
There are some excerpts from the results shown below that help explain what we are seeing. The main.db file and the extracted profile image are all from my office Windows 7 PC.
First off, note there are two rows for each sent picture, this is because the media_cache folder holds two pictures. One full size and one thumbnail for each transfer.
The first two rows show a picture that was sent by a colleague in Canada to me, the orginal_name column contains the name of the picture on his device. The author and from_dispname columns contain his skype user name and “friendly” name. The dialog_partner column is also populated with his name.
The second two rows show a picture that I sent to him from my Surface Pro PC. Note that the dialog_partner column is not populated but my colleagues name does appear in the chatname column. The original_name column contains the file name on the surface pro. My user name is correctly shown in the author and from_dispname columns.
Rows 5 and 6 show a file that was sent from my iPhone to a second Skype test account I have that was running on a different machine (another laptop). Note the original_name column is empty, this may be because one or both devices does not support the new photo sharing functionality at this time.
Finally in this screenshot the bottom two rows show a picture sent from this PC (Windows 7 desktop) to another colleague Gary, in this case the original_name field contains the fully qualified path of the original picture on the Windows 7 PC, however the last two columns (original_filename and original_image) are not populated because the original picture has since been deleted – although helpfully Skype has maintained a cached copy for us.
For instance, I replace the following query with the view name “Messages201”
SELECT Messages.”timestamp”,
Messages.chatname,
Messages.author,
Messages.from_dispname,
Messages.dialog_partner
FROM Messages
WHERE Messages.type = 201
I can then use either the full query or just “SELECT * FROM messages201” to get the same results. The three VIEWS I create are available for use by the Forensic Browser user as follows:
The row in the messages201 view has an ID of 5743 (all records in the Skype main.db have a unique ID irrespective of what table they reside in), the record in MediaDocuments has an ID of 5742, i.e. one previous. The edited_timestamp in Messages201 is 2015/03/05 20:20:03 and the access_time in assets is 2015/03/06 20:20:04.
If any Forensic Browser users need help with any of the SQL referred to above or installed into the Query Manager by the browser extension (or indeed any SQL query at all) then please get in touch and I’ll do what I can to help.