Friday, July 20, 2012

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.

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

26 comments:

  1. Thank you very much for this information. This helped me to confirm some bad news I was suspicious about.

    Perhaps the harder part is to actually get the log files, without warning your significant other. This can be particularly difficult if you do not have frequent access to her/his computer and you need to analyze the information somewhere else. Maybe you are not allowed enough time to navigate through the folders and copy the contents, or he/she is looking at you when you are trying to do it.

    I would like to share two "programs" that might facilitate copying the log files. While not 100% stealth, they are rather more silent and fast than doing a manual copy.

    The first one http://pastebin.com/X0Ra6bXM is a MS-DOS script. It opens a black screen for a fraction of a second, then minimizes and copies the all the skype data files to a directory in the pendrive while the windows is minimized. Save it in a file with ".cmd" extension in the root of your pendrive, and double click it. (Line 3 opens a minimized console of the same script, and the window is closed in line 6; line 10 actuallly copies the files). Tested with Windows XP, should work in later versions.

    The second one http://pastebin.com/n8JspZwH is a Java program that you can launch with http://pastebin.com/4yUgeY0g. Note that Java is normally installed on most computers that are connected to the internet. The companion script is similar to the first one, but the copy is performed by the Java program, which is executed without a window (hence there is no indication in the task bar while the copy is in progress). You must save the contents of the Java program into a file named A.java, and compile it. Deploy both the generated .class file and the DOS script in the root of your pendrive. (tested with Windows XP and 7, should work in Vista)

    Try them on your own computer until you are confident with their operation. The copied logs will be in a folder named "files", located in the root of your pendrive. Since this are custom files that do not use exploits, it is unlikely that an antivirus would detect them.

    ReplyDelete
    Replies
    1. Thank you for the feedback and contribution!

      However, I am very sorry to hear your suspicions were confirmed. Take a trip to www.survivinginfidelity.com. Lots of wonderful people are there and they can help you out - even if you just want to curse and scream.

      To gain access to my spouse's computer I had used a bit of trickery. I banned her MAC address from our home router and she asked me to fix her machine when she couldn't connect. While I was "fixing it", I took a peek at the Skype main.db file with a hex editor. What I saw drove me to do a more thorough investigation.

      At the point when she asked me to fix her computer I was considering installing Spyware but I didn't want to take that much of a leap at the time. I was merely suspicious and trying to see if the situation warranted more investigation or if I could chalk it up to paranoia, insecurity, or self-delusion. In the end my suspicions were confirmed. It's sad but it seems that most, if not all, suspicions get confirmed when betrayed spouses decide to investigate.

      Personally, I could have done a better job of investigating if I knew what was possible, where to look, and how to process the data. I figured I'd give this hard-won knowledge back to help those who might need a hand.

      -- DNS

      Delete
  2. this is very helpful and useful . thank you.

    btw i need to recover one deleted message, my friend deleted it and it is very important. we had a conversation 23. januar 2013. and tomorrow i browsed chatsync folder and i found that message in .dat file, but since i was stupid i didn't saved that jkk4sd45dfsfskdd54.dat file. now, for over 3 days i'm searching that .dat file which is missing. i found out that file has deleted by it self and conversation too.
    i found conversation in main.db file, but there is everything except that important message. please dude, please help me to recover that .dat file.
    will system recovery help me to get back that .dat file?

    i changed time on my comp 23. januar 2013 and typed "test123" in the same day hoping that will create .dat file again, but i think this will not happen :(

    please, please help me

    ReplyDelete
    Replies
    1. Please contact me at dead.nt.sleeping [at] gmail [dot] com. There may be something I can do to help.

      Unfortunately, no. Most system backup software programs and window's own recovery ability do not monitor the default file locations for the Skype main.db and .dat files. This is something you can modify your system to do going forward but it won't help you recover something that has already been deleted.

      Delete
  3. Hi Dead Not Sleeping, thank you for this post. I didn't think any of this was possible to do but you made it very simple and us, so thank you.
    I wanted to ask if there is a way to translate the HxD into more of a "human language" even after turning it into a txt file on notepad. I am still having a hard time reading it and understanding like when video sessions are starting. Do you have any further tips?

    ReplyDelete
    Replies
    1. Human readable?

      I tried learning Perl and some other parsing languages... but I'm not a developer so it was/is/will be an uphill battle.

      Using WinGrep did help me read the data a bit. Grep stands for global search and replace. It will search for a text string you put in and pull back the line of the file where it was found. You can look for "/$SkypeName" and "#SkypeName" to get messages from that user. You could also look for the conversation ID you're interested in and pull back all comments in the file attributed to that ID.

      Hope it helps ---DNS

      Delete
  4. SQLLiteSpy works great for digging into the messages, highly recommend it if you can copy the main.db to somewhere you can access it at your leisure.

    Suggestion on your SQL for the TimeStamp, use the following instead :
    select datetime(timestamp, 'unixepoch', 'localtime') as timestamp

    This will show the correct datetime, offset by the local timezone.

    Here's a SQL I've been using to follow my SO's tracks (she's cheating, says she's trying to end it, she's lying). If you open & browse the messages table, you can find the CONVO_ID - this remains constant for all messages between the same two people. So find that and change the value of 2000 to whatever you need.

    select datetime(timestamp, 'unixepoch', 'localtime') as timestamp, author, dialog_partner, body_xml from messages
    where convo_id = 2000
    order by timestamp desc

    ReplyDelete
    Replies
    1. Thanks for the time stamp correction! The issue many of us have to deal with is trying to parse out messages from the main.db file which are seen by SQL programs as "deleted" and don't show in queries of the database. They are just floating around in free memory. These can be accessed by using a hex editor program to read the plain language of the messages but finding out their convo_id and time stamp is a huge task. Most of the hex editor output is either machine language, encoded or parts of jpegs chopped up in the free memory space. I've been trying my hardest to make sense of these but my Perl and other parsing language skills are woefully inadequate. There is a professional product ($600+) that may help a user carve out and link up these free memory records but I haven't seen it in action... And likely won't unless I come upon a windfall of rather large size.

      Delete
  5. Can somebody tell me how I can read all there has been said between my boyfriend and his contacts in skype. I have his main.db but i can't work with SQLite

    ReplyDelete
  6. Thank you for these hints. I am sorry, to be compelled to doing such things, but my wife was betraying me before and I am not sure, that she stopped contacting him after I found out. This will give me the information I need to do the right thing (leave or stay).

    ReplyDelete
  7. Take a look at the tool called undark. It does what you indicate in terms of recovering records in unvacuumed sqlite database files. Just google for undark and sqlite.

    ReplyDelete
  8. I have opened up the main.db file in SQLite database browser and am able to see the timestamps to all the conversations with the specific person that I want to see the text to in the 'chats' section and the 'conversations' section. I have opened up the main.db file in HexEdit and put it into text edit. When I search for the timestamps in text edit I am able to find some of the conversations that I am looking for but not all of them. Sadly the more important ones cant be found in the text edit file. I am confused as to why the timestamps appear in SQLite but not in the text edit file. Can someone please help? I am able to trace back conversations from years gone by, just not some of the ones I want to find.

    ReplyDelete
    Replies
    1. additional details: the way that i transported the main.db file from one computer was by attaching it to an email and emailing it to myself. is it possible that some of the data could have been lost this way? i just dont see the timestamps appear in sqlite but not hexedit or textedit

      Delete
  9. This is interesting. I have a feeling it may have something to do with either the way Skype uses the .dat files I the chatsync folder or that the time stamps are stored in a non-standard data type in the main.db file. Have you tried the Nirsoft chatsync reader? This may be beneficial to you if the time stamps are partially stored in the chatsync folders. If it is a result of the data being saved as a blob instead of text, UTC, or date data type... The translation of that to a time stamp is waaaay over my head ;). Good luck, friend.

    ReplyDelete
    Replies
    1. yes i tried that awhile ago but as the messages had been removed from the users skype account they did not show up on there. thanks for your help!

      Delete
  10. Does password is saved anywhere in the log or db file? Can I archive password for particular skypename db or log files?

    ReplyDelete
  11. Does skype password can be retrieve through db or log file? Does password is saved anywhere in skypename/db/log file?

    ReplyDelete
    Replies
    1. No. You can't retrieve the password. You would need to utilize another method for getting it (asking your spouse, keylogger, etc).

      Delete
  12. Hi the Main.db can be viewed easily for windows .... but if someone uses Android Tablet where can we find the main.db file

    ReplyDelete
  13. Does anybody know how can we get Main.db file from an android tablet

    ReplyDelete
  14. Saw ur blog at betrayed spouse website.... had to discuss little thing... I am unable to see any main.db file for skype at an Android IBM LENOVO Tablet.... the skype massage retrieval you suggested was quite helpful... but she is no more using my PC and have got a tablet ..... i have had a bit of conversation done by her on my PC and have retrieved it successfully but still i am in half doubt.... no she dont use my PC anymore.... Q. How can I retrieve massages from tablet... that is Android ??? I searched alot for the main.db file on tablet but did not get it....

    ReplyDelete
    Replies
    1. Unfortunately, Android is outside of my experience.

      Delete
  15. This comment has been removed by the author.

    ReplyDelete
  16. If you re-install Skype and it deletes the original main.db database. Is the original recoverable ?

    ReplyDelete
    Replies
    1. I believe you are entering the world of computer forensics here. So yes BUT it will be difficult and require some expertise with data carving. The OSForensics tool is the only toolset I've found that is free and I've not had much luck with it. This is most likely due to user issues rather than software issues.

      Delete