When investigating artifacts from a potentially compromised system, we often start by focusing on system logs, records, or even the Master File Table (MFT). These sources provide essential information about the system’s activity and can be crucial for understanding what went wrong.
However, identifying the source of an infection or suspicious behavior often requires examining the user’s actions, as they are frequently the root cause of the issue.
For instance, if a user has unknowingly downloaded software with a malicious payload and hasn’t used incognito mode or cleared their browsing history, it becomes much easier to trace their activity. By analyzing browser history, we can quickly identify risky downloads, visited websites, and other digital breadcrumbs left behind, providing a clearer picture of how the system became compromised.
Chrome, Edge, and Firefox primarily use SQLite to store much of their data. SQLite is a lightweight, self-contained database that doesn’t require a separate database server. It’s widely used in applications that need to store data locally.
What interests us is the fact that browsing data is stored in this database. This is the database we’ll analyze to understand the behavior of our user.
DB Browser for SQLite is a free and open-source tool that allows users to visually view, create, modify, and manage SQLite databases without needing to write SQL queries manually. It is designed to simplify working with SQLite databases, which are lightweight and widely used in local applications such as browsers, mobile devices, and desktop apps.
In this case, we will use DB Browser for SQLite to open the files from internet browsers. Typically, these files will have extensions like .db or .sqlite.
Next, we will list the default file paths where browser data is typically stored.
If the user has more than one profile in the browser, we will have as many “database” files as there are profiles. For example, if the user has an additional profile besides the default one, the “history” file might be located in the “Profile 1” folder instead of the default folder.
When we open a “History” file using DB Browser for SQLite, we can see that the file contains many “tables” and “indexes.” It’s interesting to note how much information is stored in this file.
To identify user behavior, we usually focus on tables such as “downloads,” “urls,” and “keyword_search_terms.” However, there are many other tables that could be valuable depending on the nature of the investigation.
When analyzing browser data, it’s important to understand not only the structure of the tables but also how time is stored within them. Many tables in browser databases, such as “downloads” or “urls,” contain timestamps that track when certain activities occurred.
These time fields are often stored in formats like Unix Epoch time or Webkit time, which need to be converted into a human-readable date and time to gain meaningful insights. Understanding these time formats allows us to accurately reconstruct the user’s timeline of activity.
In the screenshot above, we can see four entries from the “downloads” table, which reveal several interesting fields. These include the path where the file is stored, the time the download started (in Webkit time format), and the referrer from which the download originated.
The entry highlighted in yellow shows the download of an Ubuntu .iso file. It is saved in the “VM” folder within “Documents,” and we can see that it was downloaded from ubuntu.com. The download time is recorded in Webkit time format as "13327592481955855".
We can use the website “https://www.epochconverter.com/webkit” to convert Webkit time to UTC. This tool allows us to easily interpret timestamps like "13327592481955855" and see when events such as downloads occurred.
The Webkit time 13327592481955855 converts to May 3, 2023, 13:01:21 UTC.
Another option for analyzing browser files is to use Jupyter Notebook along with the sqlite3 and pandas libraries. These libraries allow us to open the file and load its content into a DataFrame or multiple DataFrames.
This approach provides the ability to automate certain tasks in the future, such as quickly analyzing domains or files.
In the previous screenshot, we filtered the results for “ubuntu” directly in the SQL query. However, we could achieve the same result using the DataFrame. As you can see, with just a few lines of code, we can get the expected results.
Additionally, Jupyter Notebook makes it incredibly easy to implement time conversions, such as converting Webkit time to a human-readable format.
I hope you found this post helpful. More content will be coming soon!