Skype

Where would the wayward spouses of this world be without Skype?

Skype Primer

Skype is an IM/chat/voice/webcam platform now belonging to Microsoft. There are no public chat rooms, ala AOL, but users can create a group and invite other users to join. Files can be shared between users and groups and desktops can be shared.

Skype Guts

Skype is a platform based on SQLite3 databases (like Chrome and Firefox). The files for Skype in Windows Vista and 7 are normally located at C:\Users\USER\AppData\Roaming\Skype. In this directory, you'll see the Skype user name set up as a folder. You'll have one such folder for every Skype user to log in through that computer. Clicking on the Skype user name, you are presented with the data files for that user.

You will see the following folders:

• chatsync
• httpfe
• voicemail

You will also see the following files:

• bistats.db
• config.xml
• dc.db
• griffin.db
• keyval.db
• main.db
• msn.db

You may also see several .db-journal files. These are temporary files SQLite uses to perform a rollback.

Brass Tax

The main.db file is the primary focus here. 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.

Richard Drinkwater, blogger at Forensics from the Sausage Factory wrote a brilliant article in April 2011 titled Carving SQLite Databases from Unallocated Sectors. He posited it could be possible to write a parsing program to retrieve these "inactive" pages in a systematic manner utilizing the SQLite header information. That's beyond my skills... but like any good caveman with a blunt instrument, it just takes the right amount of brute force to get through it.

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.

From windows explorer, right click the target main.db file and select Open With... Pick HxD Hex Editor to open the file. In HxD, click View then Select Visible Columns, check Text Only. Click File, then Export and select to export the "Editor View". You now have a more-or-less human readable record of all the IMs, chats, file sharing and records of web cam sessions. Import into Excel and search for your favorite keyword.

You may want to use a tool called Windows Grep to pull out all references to a user, keyword, or conversation code. This is a shareware app and they request you purchase a license after evaluating the tool.

Inside Main.db

For user-to-user messages, the syntax used in main.db to identify the sender of a message is the hash (#), then Skype user name, then slash (/). The receiver is preceded by the dollar ($). A 16 digit alphanumeric conversation code is present as well. If you only see the conversation code then it came from a user that has since been blocked/removed or is a message sent to a group.

The leading two characters of the conversation code also appear as sub-folders within the Chatsync folder. Additional data is presumably stored in these files related to the conversation. The conversation folders in Chatsync may be empty where a conversation has been deleted by the user.

Other Handy Tools

Since main.db is a SQLite database, you can open and copy tables using SQLite Database Browser. Unfortunately, only active records are read by the database viewer. You will not see all of the records the user tried to delete.

SkypeChatsyncReader
This application reads the .dat files in the Chatsync folder and tries to reintegrate the conversations. Unfortunately, I have not had much luck with the application reading all of the deleted data. I have found that the delete process in Skype appears to clean up the .dat files before addressing main.db.

One useful aspect of this tool will provide the members belonging to a group conversation. That is, if the Chatsync files are present.

SkypeLogView
This application will provide a handy list of all messages in and out of the account. It includes voice and video. Unfortunately, it doesn't read the deleted records in the unallocated memory.

Video Chat/Voice

There is nothing able to retroactively recall the contents of a video chat or a voice call. The chat session is done on a peer-to-peer basis and the content of the session is not stored. The metadata surrounding the session (participants, time, video quality, etc) may be found in main.db but there are no saved video or audio files.

There are several add-ons around that allow a Skype user to record calls and video but it is user-initiated and is not a stealthy operation. There are rumors of a trojan virus which does record audio. This is far beyond the scope of this blog entry. I wouldn't ever recommend intentionally installing a virus on any machine.

Conclusion

Get a copy of main.db now! Run it through HxD and see what kind of deleted data you can retrieve. If your partner hasn't deleted data, SkypeChatsyncReader and SkypeLogView are excellent tools to review the contents of their Skype history.

Please post questions or comments. Also, let me know if you found a tool to read from the unallocated memory of that pesky Skype main.db file.

---DNS