skype: how to read chat message using main.db

Question

I have tried upto a level but after that I am stuck. Let share steps I followed:

1. I can see main.db file at this location C:\Users\Admin\AppData\Roaming\Skype\paul.lowry198.
2. To open this main.db file I have installed SQL Lite BB Browser application.
3. This application can show all existing tables in DB. There is one table chats. On this table I am firing a query select * from Chats where friendlyname = 'Jimmy Trevor'; It resulted 69 results.
4. Now after this how to read the message that he wrote but deleted(or any message) before I could read it.

What could be the correct query that can show me the message?

Answer 1

I've done this 4 years ago and today I need that again.

So after opening main.db file using SQLite Browser you need to:

Determine id of conversation (one person could be in multiple conversations):

select conv_dbid, * from Chats where friendlyname like '%Friendly Name%';

OR you can find desired conversation id using

select * from Conversations where identity like '%accountname%';

Finally, SQL Query to get all messages from desired conversation:

select body_xml, datetime(timestamp, 'unixepoch'), edited_by, edited_timestamp 
from Messages where convo_id=YOUR_CONVERSATION_ID;

Unfortunately I discovered that main.db fields body_xml, edited_by and edited_timestamp changed every time person editing/deleting message and there is no backup of body_xml in main.db.

---

But don't worry! There is a folder chatsync near main.db database (in your skype account folder /AppData/Roaming/Skype/Account_Name_Folder).

All messages in chatsync is in Skype binary format, to read them you can use lightweight free utility http://www.nirsoft.net/utils/skype_log_view.html

Here is the start dialog of SkypeLogView, it automatically selects your skype directory (better close your skype application, but it is not necessary). Choose dates to faster up search process.

Voila!