10:05 Skype Main.db | |
Main.dbThe 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 coolThe 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:That's why SQLite is cool :)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 Main.dbThe 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.SchemaThe 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 indexesand a link to a spreadsheet containing the tables and data fields. TablesThe 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...
TimestampsThe 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 withNot 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. ForensicsReading 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 ToolsThe other helpful tools to user are SkypeLogView and SkypeChatsyncReader. I discussed these in my earlier post:
ConclusionThe 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. | |
|
| |
| Всего комментариев: 0 | |