Blog

Accomplish Forensics Analysis Using Sqlite Forensic Tool!

Dexter Morgan | August 25th, 2015 | Updates

SQLite has recently got popular and its implication has been seen nearly everywhere. Be it storage architecture of iOS & Android, desktop/mobile internet browsers like Chrome or Mozilla, or messaging applications like Skype or WhatsApp, SQLite file format is been used almost everywhere. This database can be viewed for the examination using an external SQLite database viewer. It is important to know how these files store the details and information and how investigators should view it to investigate.

Important Files While Performing SQLite Forensics

SQLite Databases:  These are open-source, lightweight database which allow a swift processing of the details stored in it. Data is processed through an in-process library which acts like a database engine. There are various file extensions used for SQLite like; .db, .sqlite, etc. which is the main database file and comprises of one or more pages. The initial page of the main database comprises of 100-byte database header and the schema tables. The header of the database stores the structure information and the schema table comprises details of tables, triggers, indices and views. Rest of the page has B-tree index and B-tree table to store the entries.

Roll Back Journals: SQLite uses a method in order to apply atomic commit and rollback. This will generate a copy of actual unchanged content as a rollback journal file before making any changes to the main database file. When the transaction is finished, this file is generally deleted or removed. In order to speed up the process and improve concurrency, SQLite has adapted new method to back up these entries using WAL or Write-ahead log.

Write-ahead Logs: These WAL files are source of investigation for the data or transactions which were not committed. It offers a medium to access the records which were not entered in the main database. SQLite 3.7.0 started new journaling mechanism and the WAL was included. This file holds the data for a longer period and can be a crucial source of information (prior the Checkpoint event). Examining these uncommitted records can bring out the hidden details and can be forensically important.

Steps to Explore & Analyze SQLite Database

Step 1: Download the SQLite Forensic tool and launch in your Windows System. The first window will appear as mentioned below. Click on Add File option of the SQLite Forensic Explorer, in order to add the SQLite database. Click on SQLite File and Journal File under File options for adding the SQLite database and Journal file. Once done, click Add.

SQLite Forensic Tool

 

download

  • Here we have added History file of Chrome browser which is saved to the C:\Users\admin\AppData\Local\Google\Chrome\User Data\Default The SQLite Forensic tool will display the data as; Tables of Meta, downloads, downloads_url_chain, “urls”, visits, visit source, keyword_search_term, segments, segment_usage, etc. One can click on respective tables to view the data in an organized manner. Here URLs are displayed in detail with information like; id, url, title, visit_count, types_count, last_visit_ime, etc.

SQLite Forensic Explorer

 

  • Users can view the information in Hex view in order to analyze how data is stored in raw format. The Hex value can be converted to decimal to depict the information in detail. This decimal number can be converted to UTC yields to extract the date and time of access from the hex code. The data is also represented as Unallocated, Deleted, Active or Secured deleted which will be flagged into various colors.

SQLite Forensic Browser

  • Keywords which were searched (since this is an internet browser file) can be also viewed using this SQLite Forensic Explorer tool with the keyword_id, url_id, lower_term, and tem.

SQLite Forensic Browser

 

Conclusion

Collection of SQLite files is the primary step; users can acquire the databases from the systems belonging to the applications or the browsers. In order to fetch the storage files from the Smart Phones, it must be rooted. Rooting is a procedure used for overcoming the securities or restrictions applied by the manufacturers of Smart Phones. There is different procedure which is different for different OS versions and different Smart Phone. Once the SQLite databases are collected, it is important that the databases are in healthy format. SQLite forensic tool is an exceptional SQLite forensic browser application which displays the healthy as well as corrupted Sqlite databases and also displays deleted data.