Searching Browser Client-Side Storage

Is there anything you don't know? Google it! Bing it! Ask Jeeves! We sometimes forget how ubiquitous internet search is in our lives - Google in particular. We use Google Maps to find our way around, we use Google's search engine to find a good restaurant, we use Google for almost everything. That's why Google is great for finding information about a wayward spouse.

Web Browser Client-Side Storage

A lot of what internet browsers retain for data is kept in "client-side" storage. Most browsers retain a SQLite database in the user's directory. From our previous discussions we know a few tricks to get information from these SQLite sources already. Grab a SQL browser and we'll dive into the rabbit hole of what's on a hard drive.

File Location - Where's it at?

Firefox:
On Win7:  C:\Users\[UserName]\AppData\Roaming\Mozilla\Firefox\Profiles\[Profile]
On WinXP:  C:\Documents and Settings\[UserName]\Local Settings\Application Data\Mozilla\Firefox\Profiles\[ProfileName]

Chrome:
On Win7:  C:\Users\[UserName]\AppData\Local\Google\Chrome\User Data\[Profile]
On WinXP:  C:\Documents and Settings\[UserName]\Local Settings\Application Data\Google\Chrome\User Data\[Profile]

RockMelt:
On Win7:  C:\Users\[UserName]\AppData\Local\RockMelt\User Data\[Profile]

Internet Explorer (IE):
This is a different kind of beast. We'll discuss this later but it is important to note they do not have a SQLite db structure.
On Win7:  C:\Users\[UserName]\AppData\Local\Microsoft\Windows\Temporary Internet Files
On WinXP:  C:\Documents and Settings\[UserName]\Local Settings\Temporary Internet Files

Types of Storage

The following table is sourced from a Chrome Browser extension called Click&Clean. If their product is anywhere near as good as their chart on client-side storage - it should be excellent!

Below you can see Cookies, Local Storage, Web Databases (SQL), IndexedDB, File System, Application Cache, Flash Cookies, and Silverlight Cookies...

From Click&Clean

Cookies
I know what you're saying - "But everyone blocks cookies!" True. Everyone blocks 3rd Party Cookies. These are the cookies advertisers put on your system to track your behavior. What people usually don't block are 1st Party Cookies. These are cookies that are directly linked to the domain you choose to visit with your browser. They remember what's in your shopping cart, your login info (if you authorize autofill), your browser session state, etc. These cookies are delicious.

Local Storage,Web Databases (SQL)
The real big deal is in the local storage databases on your computer. These tables track downloads, search terms, archived history of URLs visited and more. See below for the schema and descriptions of the data present.

Application Cache
Nirsoft has a cache reader for you! Chrome, Firefox, Internet Explorer, Opera included. Even (gasp) Safari.

The cache is a set of already downloaded images and media from webpages that is stored on your hard drive. The browser uses these cached elements to speed the loading of webpages. The cache readers will recover images and the URLs that reference them. You will see a lot of elements like the graphic files for navigation buttons for a site, images for headers and/or other elements of a web page. If you are lucky... You may be able to retrieve images from online dating sites, Facebook images, and thumbnails of other incriminating evidence.

I strongly recommend reviewing the cache of every browser on the computer and recovering/saving files you find. The cache is fluid and can be flushed at any time or the relevant data may be overwritten by newer cached data. 

Flash Cookies & Silverlight Cookies
I haven't found anything worth noting in Flash or Silverlight Cookies. There may be data present that is interesting to an investigator but I haven't found it yet.

Local Storage, Web Databases (SQL)

Data Schema and Relevant Files
Here's a list of databases in the User's directory and a list of the data files contained within. There are many SQLite databases as well as flat files and cache files. There may be different tables present depending on the browser (Chrome, Firefox) and the browser version.

For a complete list of data files I've encountered, go here or click on the database you want to investigate below.

• Archived History
• Meta
• URLs
• Visits
• Visit Source
• Keyword Search Terms
• Cookies
• Meta
• Cookies
• Extension Cookies
• Meta
• Cookies
• Favicons
• Meta
• Favicons
• Icon Mapping
• History
• Meta
• Downloads
• Presentation
• URLs
• Visits
• Visit Source
• Keyword Search Terms
• Segments
• Segment Usage
• History Index YYYY-MM
• Meta
• Pages
• Pages Content
• Pages Segments
• Pages SegDir
• Info
• Login Data
• Meta
• Logins
• Shortcuts
• Omni Box Shortcuts
• Top Sites
•  Meta
• Thumbnails
• Web Data
• Meta
• Keywords
• Autofill Profile
• Autofill Profile Emails
• Autofill Profile Phones
• Autofill Profile Trash
• Credit Cards
• Web Intents
• Keywords Backup
• Logins
• IE7 Logins

The following are flat files (non-database files) in the User's directory. These open with Notepad and display readable contents. 

• Bookmarks
• Current Session
• Current Tabs
• Last Session
• Last Tabs
• Preferences
• Visited Links

So.... Where do I start?

I've presented a lot of information. So naturally you want to know where's the best information for web behaviors found.

• Look into the History Index YYYY-MM, History, Archived History and Web Data databases first.
• Get a cache reader and review what is in the cache for each browser on the computer.

Analyzing this data should help you confirm or deny your suspicions.

 

What's in a URL?

You are very likely to recover URLs. Sometimes these are neat and clean to read but a lot of times the URL is a jumble of variables and escaped characters.

Google Search URL
Here are some common variables you will find in a Google search string:

• q = search term
• as_sitesearch = searches specified domain only
• sort = sorting parameter for results

The authoritative source for variables would be Google. They've posted a helpful, lengthy page on search parameters here.

URL Encoding - Escaped Characters
URLs often have characters in them (:, /, ., etc) which would cause problems for other parsing engines like Java or Flash. The characters are often replaced by a '%' and the two digit hexadecimal code for the character. For example ':' = %3A and '?' = %3F.

Here's a list of characters that are often replaced in a URL with a more script-friendly value. I generally use the substitute function in Excel to replace the hex value with the ASCII character.

 

TL;DR - Summary

1) Find the local storage on your machine
2) Open a SQLite files with a database browser
3) Save file as HTML, close the file, re-open with a spreadsheet program (Excel)
4) Repeat until you've opened all files and saved them all into one workbook
5) Utilize the pivot table function to investigate the data
6) Have a beer

V/r - DNS

Skype Main.db

I hope you've read the Skype primer I posted a while back... Because now it is time to roll up our sleeves and get dirty!

Main.db

The main.db SQLite 3 file is the heart of Skype. The file is located in Windows 7 at C:\Users\WindowsUserName\AppData\Roaming\Skype\SkypeUserName and in Windows XP at C:\Documents and Settings\WindowsUserName\Application Data\Skype\SkypeUserName.

In my Computer Psuedo-Forensics & Tools post, I pointed out some helpful software; a SQLite database browser called, appropriately, SQLite Database Browser and a raw hexadecimal file editor called HxD Hex Editor. Both are free. The discussion below utilizes these tools.

SQLite is cool

The Skype tool (also Firefox and Chrome) utilize a lightweight SQL engine called SQLite 3. This enables the software to have a back-end platform which is light enough to be used on mobile devices. The PC based tools are using the same engine as the software is relatively standardized on the back-end to reduce the cost of maintenance and reducing development cost and time across platforms.

SQLite is an open standard and has extensive documentation. All SQLite formatted files have a header beginning "SQLite format 3".

From my earlier post on Skype: 

"According to the SQLite3 documentation at www.sqlite.org:

A database file might contain one or more pages that are not in active use. Unused pages can come about, for example, when information is deleted from the database. Unused pages are stored on the freelist and are reused when additional pages are required.

This means when the user deletes records, they're marked inactive. NOT removed from the main.db file. The deleted records sit in unallocated memory. They are, however, overwritten with new data. This means you should copy that file ***now*** before any old data is truly gone. Once overwritten, it cannot be retrieved.

Using the HxD Hex Editor, you will be able to open that main.db file and translate the machine code to text. There will be plenty of garbage characters and the data will be in a somewhat random order. If you're lucky enough you will have been able to catch a good bit of data that the user believes they've deleted for good."

That's why SQLite is cool :)

Using Main.db

The main.db file is a SQLite database. Using SQLite Database Browser (or any other SQLite tools), you can open the "official" memory of the Skype program. The information you can retrieve from this file represent the data that has not been deleted or removed by the user.

Schema

The schema is the set of tables and indexes within the database file. It provides the structure of the database, the names of the tables, the fields (data items) within each table and a description of the field indicating what type of data is stored and whether it is a primary key (a unique, mandatory value). The indexes for the database are provided. These indexes are tools used by SQLite to speed up the execution of queries used to retrieve data.

Just because I like you here is a link to a spreadsheet containing the tables and indexes and a link to a spreadsheet containing the tables and data fields.

Tables

The following tables appear in the main.db schema. Each table on this list is linked to a spreadsheet with a description of the table's fields and data types.

• dbMeta
• Contacts
• Videos
• SMSes
• CallMembers
• ChatMembers
• Alerts
• Conversations
• Participants
• VideoMessages
• LegacyMessages
• Calls
• Accounts
• Transfers
• Voicemails
• Chats
• Messages
• ContactGroups

So what?

The following tables are where you will find useful data...

• Contacts
• You'll find all of the user's contacts with their SkypeName, their DisplayName and any other details they've provided in their Skype profile (location, mobile #, etc)
• Videos
• You'll find a list of the user's video chats and a Convo_ID. 
• The Convo_ID will map to the Convo_ID in the Conversations and Participants tables.
• SMSes
• I have no experience with this table but I believe it is used to store SMS texts sent via Skype to a cell phone (a paid feature for Skype users)
• CallMembers
• Provides a list of all parties to a call (grouped by Call_Name). 
• Duration is provided per participant and a Video_Status is provided (I believe 3 = video call)
• ChatMembers
• Provides a list of members taking part in a chat (grouped by Chat_Name). 
• The Adder field provides the SkypeName of the user who added the participant to the chat.
• Conversations
• Provides a list of the conversations in which the user participated. 
• The Identity column provides the SkypeName of the participant or the Chat_Name for group chats/rooms. 
• The DisplayName field provides the participant's displayed name in Skype or the displayed title of the group chat/room.  
• The Creator column notes the creator of the group chat/room.
• Timestamps are provided for Last_Activity, Inbox_Timestamp, Creation_Timestamp and more.
• Participants
• Provides a list of conversation participants grouped by the Convo_ID
• The Adder field notes the SkypeName of the user who added the participant to the conversation
• The Identity column provides the SkypeName of the participant.
• Text, Voice and Video status fields are provided.
• Calls
• Provides the calls in which the user has participated.
• Call_Name is provided and maps to the Call_Name column in the CallMembers table.
• Timestamps for the calls and the Host of the call are provided.
• Transfers
• Provides the details of files shared and downloaded by the user.
• Partner_Handle is the SkypeName of the other party, Partner_DisplayName is their DisplayName.
•  FileName and FileSize is provided along with the FilePath (uploads only I believe).
• Convo_ID is provided which maps to the Participants and Conversations tables.
• Timestamps are provided.
• Voicemails
• Lists voicemails sent to the user
• Partner_Handle is the SkypeName of the other party, Partner_DisplayName is their DisplayName.
• Path column displays a ".dat" file which is located in the ...\Skype\SkypeUserName\Voicemail folder
• Chats
• Provides the chats in which the user participated.
• The Name field follows this structure; #SkypeUserNameOfInitiator/$SkypeUserNameOfPartner;ChatIDString
• The ChatIDString is a 16-digit alphanumeric string that is unique to the Chat
• The SkypeNameOfPartner is replaced with the ChatIDString when the chat is a group chat/room. Ex: #SkypeUserNameOfInitiator/$ChatIDString
• The Friendly_Name column provides the display name of the partner or group chat/room.
• The Adder field notes the SkypeName of the user who added the participant
• Timestamps are provided
• The Posters field is a concatenated list of SkypeUserNames that posted to the chat
• The Participants field is a concatenated list of SkypeUserNames that received messages from the chat
• The ActiveMembers field is a concatenated list of SkypeUserNames of those who have recently posted
• The dbPath field displays a ".dat" file which is located in the ...\Skype\SkypeUserName\Chatsync folder
• The name of the ".dat" file is a 16-digit alphanumeric
• The first two characters indicate the subfolder where the ".dat" file is located under the Chatsync folder. Ex- if the dbPath field has the value of 01abc2345def6789.dat; the file is located at ...\Skype\SkypeUserName\Chatsync\01\
• Messages
• Provides a Convo_ID to map to Conversations table, Videos table and others.
• Provides ChatName to map to the Name field in the Chats table.
• Notes the SkypeName of the Author of the message.
• Provides the message detail in the Body_XML field. This is the most important field... this is the message sent from one party to another!

Timestamps

The timestamps in main.db follow a format based on UNIX. You will need to convert these timestamps into a human-readable values. For example, the value '1325900664' represents January 6, 2012 8:44pm. I use the following equation in Excel to convert the dates (assuming the Timestamp value is in cell B3):

=IF(B3="","",(B3/86400)+25569+(-5/24))

The IF statement states that if there is no value for Timestamp, then no value should be returned. If there is a value in the Timestamp field, the equation should be executed.

In SQL queries the date is converted using the datetime function. The format of the function is as follows:

datetime(timestamp_field,'unixepoch')

A query to start off with

Not everyone can write SQL queries, but they should! Here is a simple sample query to get you on your way. This retrieves all contacts sorted by the number of times they appear in the participants table:

select c.skypename
         ,c.fullname
         ,c.country
         ,c.province
         ,c.city
         ,c.phone_home
         ,c.phone_office
         ,c.phone_mobile
         ,c.main_phone
         ,c.emails
         ,c.verified_email
         ,datetime(c.lastused_timestamp,'unixepoch') lastused_timestamp
         ,count(p.identity)
from contacts c
    ,participants p
where c.skypename = p.identity
group by c.skypename
         ,c.fullname
         ,c.country
         ,c.province
         ,c.city
         ,c.phone_home
         ,c.phone_office
         ,c.phone_mobile
         ,c.main_phone
         ,c.emails
         ,c.verified_email
         ,datetime(c.lastused_timestamp,'unixepoch')
order by count(p.identity) desc

What's the deal with Chatsync?

The Chatsync folders also contribute a great deal. The main.db database includes pointers to the ".dat" files in the Chat table. I believe that these folders within Chatsync are used to store nodes of information which is retrieved by the Skype application when the chat data is needed. It's sort of like an extended memory to save information about the chat. I do know the Chatsync files are not inclusive of all the information presented in the main.db Chat table and I have found data present in the Chatsync files not present in the main.db tables.

Forensics

Reading the Main.db file with a SQLite utility and the Chatsync files are of limited use when trying to recover data that has been deleted. As I stated in this earlier article, the best approach is to read Main.db with a hexadecimal reader to recover partial records of deleted messages, video calls, voice calls, chats and other data. The Chatsync files are of limited worth as these appeared, in my experience, to have been deleted with more regularity than the old data in main.db was overwritten by new data.

Other Tools

The other helpful tools to user are SkypeLogView and SkypeChatsyncReader. I discussed these in my earlier post:

SkypeLogView

The good people at NirSoft have provided a tool for analyzing the Skype database files for call logs, chats, etc. It is a very handy tool but much more data can be gathered using HxD.

SkypeChatsyncReader

A utility created by Rasmus Riis Kristensen from the Computer Crime Unit of Danish National Police. This tool reverse engineered the location of data in the .dat files of Skype.

Conclusion

The Main.db file is a useful source of data IF a deletion of data has not occurred. The presentation of files from a SQLite database browsing utility can provide a well-formatted view into Skype usage. Please remember that carving the Main.db file with a hex editor will yield data that has been deleted by the user. However the presentation of this deleted data is nowhere near the organized output of the Main.db file.

-- DNS