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.

Obtaining and displaying Skype IP addresses (with maps) from ChatSync files

by | Feb 27, 2015 | Forensic Browser for SQLite, Skype

I had reason recently to look at Skype ChatSync files to recover the IP addresses held within and I needed to get these into a report. For those of you that aren’t aware when Skype is syncing data between two different accounts, it uses ChatSync files to transfer this data. The data held within is, for the most part, duplicated in the main.db file (after all that is what the sync part of ChatSync refers to). However, and most interestingly for forensic purposes, usernames and IP addresses are also stored within these files.

I have therefore written a Forensic Browser for SQLite extension that parses the folder containing these files and for every file records the following information in a new SQLite database:

  • The filename
  • The last written date
  • The user names
  • And for each username, the Lan and Wan IP addresses that are stored

It struck me when writing this application that I could also obtain some location information from an online service and display this information within a Skype report and further I could use the built-in mapping functions of the Forensic Browser for SQLite to display maps related to the latitude and longitude fields obtained from my IP lookup service.

Of course, location information based on IP addresses needs to be carefully considered as IP addresses will often be the of a service provider. Nevertheless on examination of the IP addresses and particularly associated maps for my own Skype username quickly revealed some interesting locations.

The screenshot below shows the output of this process with three maps at different scales shown alongside the details from the ChatSync files.

The rest of the article will show how easy it is to create these reports yourself.

In order to follow these instructions you will need to obtain the ChatSync parser program from me (it needs a current Forensic Toolkit for SQLite licence) and if you want to obtain the location information you will need to have:

  1. A free account (and associated key) from IPInfoDB
  2. Internet connectivity when running the ChatSync parser program and Forensic Browser for SQLite.

So, first, visit IPInfoDb and create a free account at this page http://www.ipinfodb.com/register.php you need to provide an IP address of the “server used to connect to the API gateway” I used the IP address of my router (also conveniently displayed on the page above) and all seems to work OK. You need to acknowledge an email in the normal fashion and then to wait 10 minutes after the acknowledgement before you can use the service. When the service is created you will be provided with a long alphanumeric key – you will need this later.

You can now run ChatSync (make sure it is in the folder alongside The Forensic Browser for SQLite so that it can pick up the licence file). If you have obtained a key form IPInfoDB then select the checkbox and enter your key in the edit box (this will be retained in the registry for future use).
Click on the parse button and when prompted choose the Skype ChatSync folder you want to examine
and then again when prompted choose a database to write the results to.
When the parsing has completed, this may take a minute or two, you can open the saved database in the Forensic Browser for SQLite.
Now we have created and viewed the database in the Forensic Browser it is a simple process to use the built-in feature to create a new table containing three geolocated maps for each IP address.

Select “Create geolocated images” from the “Tools” menu

In the following dialog choose the table that contains the latitude and longitude information, in this case “decodedchatsync”. You also need to specify the key, lat and long fields, but these should be filled in for you in this instance. You will need to provide (or accept) the name for the output table.

The zoom levels specific the scale for each of the three created maps (0 disables a map) with 16 being the maximum “zoom in” level (i.e. street level) and 1 the minimum.

Press OK and the table will be created, this may take a few minutes as the maps are created and downloaded via the open street map server.

The resulting geodata1 table looks as follows:
However, in order to make sense of it, we want to display the maps alongside the data from the decodedchatsync table, to do this add the decodedchatsync table to the query designer and then the geodata1 table. Create a link by selecting the id field in the decodedchatsync table and dragging it to the id field in the geodata1 table. Finally, select all columns (*) from the decodedchatsync table and just the three maps from the geodata1 table. Now execute the query.
You can then use the column filter condition, should you wish, to filter on specific users:
The resulting query can be saved to HTML/XLSX, PDF etc.

Using group_concat to amalgamate the results of queries

by | Feb 9, 2015 | Forensic Browser for SQLite, Skype

Recently one of our users contacted me and asked for help creating a custom report for a Skype database after other forensic software had failed to include some very relevant data in their reports’.

In a nutshell, the messages report he had produced using the other software only included the author of a given message and did not list the other person(s) who were part of that conversation. This information is maintained in different places in a Skype main.db SQLite database. This article discusses how to include this information in a user-friendly format.

In order to include the required information, the two tables that we need are the messages table and the participant’s table. Normally in a Skype installation, this table includes most of the information we want in the dialog_partner column, however in this database (from an HTC One phone) this column was blank.

In order to understand the problem more clearly have a look at this extract of selected columns from the participant’s table:

The convo_id column is a unique number that refers to each conversation; the identity column is the identity of the participants. Note that conversation 426 has 4 participants. Also note that r3scue193 (that’s me) appears in every conversation, as you would expect.

If we now look at the messages table we can see that there is a corresponding convo_id column and we can use this column to perform a join between the two tables so that we can include a list of all the participants.

So how do we get a list of all of the participants in a conversation?

If we simply do a LEFT join between the two tables on convo_id then for each row in the messages table we will get a join for each matching row in the participants table, i.e. for a conversation with two participants we would get a duplicated row for each participant (the identity column below), for three participants we would get three rows etc. – this would be very confusing:

The answer is the SQLite aggregate function group_concat, from the SQLite web site (https://www.sqlite.org/lang_aggfunc.html)

group_concat(X)
group_concat(X,Y)		 The group_concat() function returns a string which is the concatenation of all non-NULL values of X. If parameter Y is present then it is used as the separator between instances of X. A comma (“,”) is used as the separator if Y is omitted. The order of the concatenated elements is arbitrary.

The query we use is below and utilizes the group_concat function along with the GROUP BY expression:

Code:

SELECT Participants.convo_id,
Group_Concat(Participants.identity, ', ') AS ConversationParticipants
FROM Participants
GROUP BY Participants.convo_id
In English, this selects all of the convo_id and associated participants from the participants table and groups them by the convo_id, and then the identity of each participant in each group is concatenated into a single string with each identity separated by a comma.

This might be clearer if we review the original table:

And then look at the results of the query:

Remember you can use the Case Manager to save the query you have just created for re-use on another case.

We now want to use the query created above with the messages table so that we can list the participants alongside each message. But before we do that we will make a VIEW based on the above query. A VIEW can be thought of as a sort of temporary table and the Forensic Browser for SQLite allows you to create a VIEW by selecting the appropriate option from the Queries menu:

You then need to enter a name for the view

This view can now be used in the same way as any table by dragging it to the query designer. The screenshot below shows that:

Code:

SELECT Participants.convo_id,
Group_Concat(Participants.identity, ', ') AS ConversationParticipants
FROM Participants
GROUP BY Participants.convo_id

Can now be replaced with:

Code:

SELECT *
FROM Convo_participants

All that now remains is to create a JOIN on the messages table using our new view and select the columns we want to display:

Adding the same SQLite column to a report twice – raw and converted

by | Oct 9, 2014 | Skype, Forensic Browser for SQLite

In this short tutorial, I am responding to a request from a user to allow both the raw column and its converted values to be displayed side by side. i.e. in a Skype messages table, we might want a report that contains the message timestamp, author and the message body. The following screenshot shows the raw report with just the three required columns checked:
We now right click on the timestamp column and access the “View column as..” menu option so we can customise the display of this column.
The subsequent dialog (shown below) gives us a number of choices, for a Skype date we need to select the “Unix 10 digit timestamp” option. For the purposes of this demo, we will leave the timezone alone.
The converted column now looks as follows:
Now we get to the crux of the tutorial – clearly there is only one check box in the query designer for the timestamp, so how do we add the timestamp column to the report a second time?

With the Forensic Browser for SQLite this is straight forward. In empty row in the columns display click on the down arrow (this will become visible when the row is selected) and choose the messages.timestamp column again.

The Browser will automatically add an alias for this column to avoid duplicate names.
Alternatively, we can rely on the SQL query language itself and enter a new line manually so that the code looks as below (the columns display will be automatically updated if you modify the SQL in this way).
Then Execute the query.

The example shown chooses the timestamp column again and provides an alias for it (as mentioned SQL won’t allow the same column name in the output twice – if you don’t add an alias when you manually type a query you will be prompted with a “duplicate column name” error).

As we haven’t provided a conversion for the column the displayed date is displayed in its raw form.