Investigating Skype cloud-based media_cache/image sharing with the Forensic Browser for SQLite

by | Mar 17, 2015 | Forensic Browser for SQLite, Skype

Skype recently introduced cloud based operation and started moving away from peer-to-peer messaging with a view, to paraphrase Skype, of improving the service that we receive.

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.

So naturally the first thing I did was to look at the media_cache folder on my machine and see what was present. This folder on my machine is at the following path:

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 quick review of the content of the files confirms this (at a larger scale the duplicate images do include a thumbnail):
What is interesting here is that I can see pictures that I had received, pictures that I had sent and also pictures that I had sent from another device. So clearly from an investigation point of view, this is very interesting. What other information was present?

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.

While this looks interesting it doesn’t help us with our “who sent what to whom” question, so I needed to look further afield. Knowing that Skype maintains lots of useful data in its main database (main.db), I decided that my next step would be to see if I could link the media_cache cache_db.db with a table in main.db.

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.

Importantly I recognize all of the pictures and that the difference between the two groups is that those with a storage_document_id are pictures I sent and the others are pictures I received.

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:

It would seem logical to join these two tables (messages and MediaDocuments), but we need to work out how to perform our join, i.e. on what columns. It is clear that there are no exact matches where we can say col1 = col2 so we need to engineer one. We do this by extracting and matching the URI from the body_xml field in the messages table with the URI field in the MediaDocuments table using an SQLite core function “instr”. Some of the matching rows are highlighted below:

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:

This output can be saved to HTML/XLSX etc. as with any other query.

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.

In the screenshot below the two rows show a picture that was sent by me to my colleague Gary from my Windows 7 PC, in this case the original file still resides in the original “My Pictures” folder (note the fully qualified pathname) and as it is still present the browser extension has imported the original picture and displayed it in the query/report as well as the cached picture.
To simplify the SQL I make use of SQL VIEWS, views are akin to a virtual table and you can create a view that represents a complex SQL query and then just refer to that query by its view name in future.

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:

In the screenshot below on the Messages201 view there is a message with a type 201 that has no body_xml data. This is the remnants of a picture I sent to my Canadian colleague while he was offline and subsequently deleted before he had a chance to download it.
The next piece of this puzzle is the MediaDocuments table from main.db – this lists the original name of the file.
The actual image I sent is present in the assets table in the cache_db.db, the timestamp for this image shows the time at which I deleted the picture.
These three tables can be linked, after a fashion, by the dates and record ID’s.

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.