Messing with the Skype 4.0 database

Beginning with version 4.0 Skype changed its internal database format to SQLite (at least the Windows-client, could not yet check other platforms). Besides from better performance it also allows now any curious person to dig a bit into the internals of the local Skype account.

At first, you need a SQLite client to connect to the database. SQLite Database Browser is a good choice for this purpose. Then you need to open the Skype database in it. Shut down Skype (you won't be able to open the file otherwise) and load the file main.db from your Documents and Settings\<User>\Application Data\Skype\<Skype-account> directory. You can browse around in the database structure, have a look into the tables contents and even use a SQL editor to query the database. That's what we're going to need.

Now the fun begins :) In the following statements replace the values in angle brackets eg. <skypeid> with the appropiate correct values. Let's assume, you want to display all messages you exchanged with someone. Beware, that this only works gets the messages you exchanged in a private chat with someone and does not work on chats with more than two participants (you included).

SELECT author, timestamp, body_xml 
FROM messages 
WHERE dialog_partner = '<skypename>'

Or you want to list all chats, where there have ever been more than you and your chat-partner. So to list all group-chats:

SELECT name, participants, posters, activemembers, last_change 
FROM chats
WHERE type = 4

You could also be interested in all previous topics which were set on your group chat:

SELECT chatname, timestamp, body_xml 
FROM messages
WHERE chatmsg_type = 5
AND chatname = <chatname> -- get names from the previous query

By now you should have recognized, that the timestamps and change-values are not really readable dates in the database. Don't worry, it's not encrypted. Its just stored as plain Unix timestamp values. You can easily convert it to a readable time and date using for example the online UNIX Timestamp To Standard Time Calculator

When we're already with timestamps, you maybe also want to know when someone has also been online the last time when you have been online as well:

SELECT skypename, given_displayname, lastonline_timestamp
FROM contacts

So far we have fetched some interesting information from the database. But now we want to leverage our l33tness a bit and modify our mood message in our profile to include some rich formatting:

UPDATE accounts
SET profile_timestamp = current_timestamp, rich_mood_text = 'I feel <b>bold</b> now.'
WHERE skypename = '<skypename>'

Don't forget to save your database afterwards! This statement updates the profile-timestamp with the current time and adds some rich formatting to our mood message. In this case there is some bold text inside. You can look up some more possibilities (like smileys, font coloring, etc.) directly in the Skype API Documentation (from). Additionally I've found out, that using hyperlinks with the <a>-tag works as well as modifying font-size with the size-attribute of the <font>-tag.

Update 2010-02-14: Meanwhile you can edit your mood-message more easily using my online Skype mood message editor.

You can also delete the chat-history of only certain skype-contacts using the following statement

DELETE FROM messages
WHERE skypename = '<skypename>'

Again, don't forget to save your changes afterwards.

Update 2010-10-29: Skype has dropped the support for "/htmlhistory" since Skype 4.0. An alternate way to get all the messages for a chat is:

SELECT timestamp, author, chatmsg_type, body_xml  
FROM messages 
WHERE chatname = '<chatname>' 

You can find out the name of a chat when you write the command "/showname" in its chat-window.

I'll continue to play around with the database and if I find more interesting stuff, I'll keep this posting updated. Or I'll write a new one depending on what I dig up :)


Similar entries


Do i leave the brackets > in sql statement or i let them out? I'm not familiar with program you're using.

This is great info, thanks!

Since I use my Skype at work (network PC), is history permanently deleted (tools, options, delete history), or is it somehow and somewhere still there...?


@Dinamitash: No, take the > out of your statement.

@Alma: The local history is deleted. If you're using your Skype account on more than one computer, there may still be entries from your history stored on the other ones. There may also happen some re-synching in that scenario so that one computer re-fetches the history from the other one. Delete the history from all your Skype-installations to be on the safe side.

GREAT. An alternative to SQLiteDatabaseBrowser is SQLiteSpy from It has the advantage that you can keep 2 SQL windows open at once: one to select distinct, another to run the delete query.

Love the Skype info you're providing here.

FYI, you can use an SQLLite function to convert unix epoch time to a more human readable form. E.g.

SELECT name, participants, posters, activemembers, datetime(last_change, 'unixepoch')
FROM chats
WHERE type = 4

I hav username n password of my wife id, but she use to call her x bf from skype so i cant hear her conversation, i want to know wat she want to do of us( me and our three children) if she cheating, i wil ask her to we wil workout on it for our children, i dont want to destroy thier life, could you please let me know is there any software that i can hear their conversation?? i wil really thankful to u. please help me

i have my gf's machine's main.db file i want to read it how can i read all her conversation with her ex-bf?

@John: Sorry, your question went completely under my radar. No sorry, I don't know of any software which wiretaps into Skype calls. And I wouldn't recommend that anyway, best thing I can tell you is to sort it out with your wife personally and calmly.

@puspak: Roughly same topic as John's, also something I'd rather not encourage. But read my posting again, the answer is in the first example.

hi, i would like to copy my chat history from a laptop to another one.. is it possible? could u plz hlp me if yes.. thnx

@Nash: Yes, this is possible, have done it a few times.
1.) shut down Skype on the old computer
2.) back up all database files from Skype on the old computer (probalby the whole Skype user files)
3.) install Skype on the new computer
4.) start up Skype on the new computer and log in with the same user as on the old computer
5.) shut Skype down again
6.) copy the backup files from the old computer over the created files on the new computer, overwriting existing files in the process
7.) start up Skype on the new computer again, your history should be present