Analyze Sqlite Database via Sqlite Database Browser

Sqlite database makes an important place in forensic analysis because most of the application consists of Sqlite database. These applications have become necessary to every person in today’s life; some of them are Browsers, applications based on android platform, etc. A wide range of applications use Sqlite database to store their data. Forensic analysis based on Sqlite data refers to open a database file in another database viewer.

Types and Need of Sqlite Files in Forensic Investigation:

Sqlite database consist of various data, various types of files in binary encoded format. The most important files of Sqlite are .db and .sqlite made up of database of 100 byte including schema tables and header. These files of Sqlite database used in forensic investigations.

• Deleted Data or Free list– Sqlite database files consist of all deleted browsing data, chats, messages, browsing history and all other data that becomes crucial for the purpose of digital investigations. If we talk about the deleted data from hard disk, Sqlite database records are not completely deleted until the free space is overwritten. This deleted data is more helpful for investigators to analyze Sqlite database because the suspect always tries to delete all previous history or data that can work as an evidence to prove him as criminal.

• Write Ahead Logs – The size of WALs is very low as compared to Sqlite database since it stores the new and altered data. Every time, the new and altered pages are written, is stored in a file called Write Ahead Log file instead of the main database. This process continues until the file consists of 1000 pages (by default) or committed by Checkpoint via the database. This type of file helps to find out if we are talking about web browsing or chatting because the whole chat session can never set off an individual checkpoint.

• Sqlite Carving – It is the process of recovering the physically deleted Sqlite database files and if the system pointing to a file that is not currently available. The process of Sqlite carving is not an easy task as recovering the normal data because it requires special techniques to recover the deleted file. The next step is to recover the content of that file; it is done by using content-aware signature search and then calculates the file length analyzing its header. Carving technique becomes problematic if there is a Sqlite database file does not have the header part and this process is time – taking. So to carve & analyze Sqlite database experts need forensic tool like Sqlite Database Browser.

• Unallocated Space in Sqlite Database – Since the Sqlite database is divided into various pages, some of them are known as “leaf table b-trees” containing the data. These trees consist of cells placed by Sqlite and the new cells are added at the end of the b-tree page. These newly created cells contains random pieces of data i.e. do not contain valid data and ready to accept the new data. These new cells or unallocated space may work as fundamentals for digital forensic team because these cells may have previously deleted data stored in it.

• Roll Back Journals – It is a method used by Sqlite to, automatically store the information of actions performed by a user. This type of file helps the forensic investigators because it consists of the details about each transaction going to happen but due to any reason it could not take place. This file automatically deleted after the successful transaction is completed. If there is, any incomplete action performed by user does not store in the main database file but saves in these Roll Back Journals and helps in forensic investigators.

General Steps to Perform Digital Forensics:

1. Acquire the digital data.
2. Preserve the data for future use.
3. Analyze it to get the evidence.
4. Generate a report in text format.

Analyze Sqlite Database on Altered Locations:

The location of Sqlite file is different for different applications. Here, I am going to give you the information about the location of Sqlite files of some applications.

Steps to Use Sqlite Database Browser:

Install the software and Run with the help of following steps:

The initial screen looks like –

Click on the Add File button on the top of the left corner of the window. Now you will have a window looks like –

Select the path of Sqlite File and its associated Journal file by clicking the blue button in front of them respectively.

Now, select the Journal File in the section File Type if you want to change the encoding of Journal File and then select the Encoding type with the help of dropdown menu then click the Add button to add the selected file.

The files will be scanned automatically. Now you can view all the data saved in your selected Sqlite database file.

Select what data you want to view such as history, URL, downloads, tables etc.

You can view various forms of these data such as Hex view, and Tabular format of data.

In tabular format, you can see the visit time, duration of visit etc.

Deleted data can be viewed by clicking on the Deleted button.

SQL Editor Option is also available which is applicable if you want to fetch the data with the help of SQL query.

To save or export your data, click on the Export option available on the top of the window. This option prevents you to load a data again for further investigation process.

We have discussed the needs of digital forensic in this technological world. Since crimes are growing rapidly in our country so Sqlite Database Browser tool assists the investigators to analyze and extract evidence from Sqlite files without any data loss. The discussed types of Sqlite files are necessary for the forensic team and how can this software prove itself best to fetch all the files as per the requirement. The second half of this page discusses the working of tool in steps so that you will not have to face problems during analysis of Sqlite database and the usage of this software.