Accomplish Forensics Analysis Using Sqlite Forensic Tool!
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 databases that allow swift processing of the details stored in it. Data is processed through an in-process library which acts as 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. The rest of the page has a 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 adopted a new method to back up these entries using WAL or Write-ahead log.
Write-ahead Logs: These WAL files are a 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 a 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 to 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 it 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.
- Here we have added the History file of the 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 the 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.
- 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 in various colours.
- Keywords that 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.
Conclusion
Collection of SQLite files is the primary step; users can acquire the databases from the systems belonging to the applications or the browsers. Smart Phones need to root, in order to fetch the storage files from them. Rooting is a procedure used for overcoming the securities or restrictions applied by the manufacturers of Smart Phones. There is a different procedure which is different for different OS versions and different Smart Phone. Once the SQLite databases collection completes, it is important that the databases are in a healthy format. SQLite forensic tool is an exceptional SQLite forensic browser application that displays the healthy as well as corrupted SQLite databases and also displays deleted data.
