The Duck Hunters Guide - Blog #2 - DuckDuckGo Browsing History (Android)

Over the past few days, I started combing through the Android version of DuckDuckGo. The version I have been testing is the latest version (5.222.0).

In this blog post I want to talk specifically about the history.db. This SQLite database stores all the browsing history. This includes back/forward navigations in tabs similar to other web browsers that we see during examinations. 

I will start by talking about the structure of the database and its contents and then discuss how the history clearing options and its impact on this database. The History.db is a very basic database, just 2 tables of interest with very minimal information about the URL's being visited.

History.db Location: data\data\com.duckduckgo.mobile.android\databases\

As it is an SQLite database, I first get information about the database settings:

Page Size: 4096

Journal_Mode: WAL

Auto_Vacuum: On

 

The useful information this gives me is primarily for record recovery purposes. The database uses Write-Ahead Logging so we can potentially find old versions of the SQLite pages in the main database file and the WAL file. Auto_Vacuum is enabled so Freelist pages won't exist.

The database has 4 application tables. I don't count sqlite_sequence as it is an internal table for tracking primary keys that auto increment. Of the 4 application tables, only the history_entries and visits_list are of interest.

history_entries Table

• id: unique identifier (Primary Key)
• url: url for the site being visited
• title: title for the url
• query: if the url was a duckduckgo search the query will be listed
• isSerp: This is a flag to determine whether the history item was a regular web page or a DuckDuckGo search. Serp stands for Search Engine Results Page

visits_list Table

• historyEntryID: id for the associated URL in the history_entries table
• timestamp: Text datetime stamp for when the URL was visited (Local Time). This field is also the table primary key

Like I mentioned a very simple database, so creating a query to pull information together wasn't difficult at all.

SELECT  

    visits_list.rowid AS 'Visit ID',

    history_entries.id AS 'URL ID',

    history_entries.url AS 'Visited URL',

    history_entries.title AS 'URL Title', 

    visits_list.timestamp AS 'Visit Date (Local)',

CASE history_entries.isSerp 

    WHEN 1 THEN 'DuckDuckGo Search'

    WHEN 0 THEN 'Web Page Visit' 

END AS 'History Type', 

history_entries.query AS 'Search Query' 

FROM visits_list 

LEFT JOIN history_entries ON visits_list.historyEntryId = history_entries.id  

The Downside

Having the ability to parse this out is great, but we can't forget the infamous Fire button. When a user hits the fire button the browsing history is cleared. 

But wait there is more, a user can configure Automatic Clearing of Tab's and Data after a certain condition. This means that the chances of find "allocated" browsing history are fairly slim.

The Automatic Clearing settings can be accessed via: Settings > Fire Button

The Automatically Clear.. option controls what is automatically cleared:

• 
  None - Browsing History persists
• Tabs - Browsing History persists
• Tabs and data - Browsing History is cleared (equivalent to fire button)

When Automatically clear is enabled, the Clear on.. option becomes available:

• App exit only
• App exit, inactive for 5 minutes
• App exit, inactive for 15 minutes
• App exit, inactive for 30 minutes
• App exit, inactive for 1 hour

The Automatic clearing settings are found in the com.duckduckgo.app.settings_activity.settings.xml file

File Location: data\data\com.duckduckgo.mobile.android\shared_prefs\

By default, the automatic clearing options are disabled, but if they are enabled by the user, you will find key value pairs for AUTOMATICALLY_CLEAR_WHAT_OPTION and AUTOMATICALLY_CLEAR_WHEN_OPTION

In this example, Tabs and Data are automatically cleared when the App is exited or when the is inactive for 30 mins (example being if the app was pushed to background).

If you see these Key Value pairs present, and the AUTOMATICALLY_CLEAR_WHAT_OPTION is CLEAR_NONE then this means the user changed the settings at some point but then disabled it.

Interestingly, this xml file has a key value pair stating whether the app has been used since it was last cleared. I need to do further research what is classed as app "use".

Recovering Browsing History

The user has used the Fire Button, what now? Can we recover browsing history?

History.db is an SQLite database so there are options for us to potentially recover browsing history. So, let's explore them...

From an SQLite perspective we could potentially recover browsing history from:

1. Freelist pages
2. Freeblocks
3. Page Unallocated Space
4. Journal File   

The database uses auto_vacuum, which means there will not be any freelist pages so we can knock that off our list.  

Freeblocks are created when a record is deleted and there are still allocated cells on the b-tree page. With the DuckDuckGo app, the Fire Button clears all history, and from what I can tell there is no option to clear history for a specific timeframe or individual website visits. As all records on the page are deleted, the cell content area will be absorbed into page unallocated space which means there should never be any freeblocks in this database, as by definition a freeblock is 4 or bytes of unallocated space in the cell content area which in this case there is no cell content area.

In the example below is page 4 which is the root page for the history_entries table. I used the fire button to clear history and as expected I got a leaf page with 0 freeblocks, 0 cells, and the start of the cell content area as page offset 4096 (entire page is unallocated).

Immediately after the b-tree leaf page header, is the cell pointer array. As there are no reported cells on the page, we shouldn't have anything here, but we do have some cell references. This is promising!

In my example I got references for page offset 3588 (0E 04) and page offset 1642 (06 6A)

Note: When you see repeating cell references this is an indication of records being deleted and the cell pointer array shrinking.

Going to these page offsets, I found the structure for a freeblock with its 4-byte header followed by the freeblock content (deleted data). Unfortunately, the content was zero'd out which means the database uses secure_delete. This is not entirely unexpected as typically privacy focused apps use secure_delete to ensure no traces of deleted records

So far, we are 0 - 3 on SQLite data recover techniques, this is not looking good. 

Last thing we can check is the journal file. The database uses Write-Ahead Logging, so all browsing history is committed to the write-ahead log first, and then when a WAL checkpoint occurs the most recent copy of the page is committed to the history.db file. This means that we can potentially walk backward through the wal frames and pull out the deleted history.

After rigorous testing I found that a checkpoint occurs when the WAL file reaches 100 pages in size. So, we are not going to recover an enormous amount of browsing history, but some is better than none.  

I used the Fire Button, then just went to Formula1.com to populate new browsing history. I extracted the data from my test phone then ran my SQBite tool against the database and WAL file. As expected, I was able to recover my browsing history.

Note: Your forensic tools 'should' do this, I'm using SQBite for demonstration purposes as it provides Page and Frame information.

My visit to Formula1.com after hitting the fire button was at Frame Number 85, therefore every frame that came before it in the WAL file contains deleted browsing history. 

I used my dev version of SQBite to rebuild the database with the records from the WAL so I could query all the parsed data using the same query as before but with the addition of the source field. When all frames are parsed out, I got 520 records for just 17 URL visits. 

To really make sense of the data and what action occurred for a page to get added to the WAL, we need to understand what the transactions look like in this database. Once you understand the transactions you can start finding the patterns in the WAL frames.

Thankfully this a very simple database and that I have been able to determine, there are 3 different types of transactions relating to browsing history.

• User visits a URL
• User visits a URL that already exists in the history_entries table
• User clears browsing history

When the user visits a brand-new URL, the following happens:

1. URL information is inserted into the history_entries table
2. sqlite_sequence table is updated with the new historyEntryID assigned to the record. This is because the historyEntryID is set to auto_increment
3. index_history_entries_url index is updated to include the new url
4. URL id and visit time is inserted into the visits_list table
5. sqlite_autoindex_visits_list_1 is updated

If the user visits a URL that already exists in the history_entries table such as using the back/forward buttons the following happens:

1. URL id and visit time is inserted into the visits_list table
2. sqlite_autoindex_visits_list_1 is updated

If the user clears their browsing history i.e. uses the Fire button:

1. URL information is secure_deleted in the history_entries table
2. index_history_entries_url index is cleared
3. URL id and visit time is secure_deleted the visits_list table
4. sqlite_autoindex_visits_list_1 is cleared

Each time a user visits a URL whether it's existing or not the page is duplicated, and the associated entries are added. Below is an example of the patterns that you will see in the history.db-wal file. The pages in frames 81 - 84 contained 0 records and the previous contents were secure_deleted.

Going back to the data parsed out of the WAL, I can use a SELECT DISTINCT to clean up the output to remove duplicative records. This gives me my 17 website visits. 

I observed a few things at this point:

1. As the timestamp field in the visits_lists table is a non-integer primary key, the ROWID gets reset back to 1 when browsing history is cleared so if you include the rowid field in your query and sort by visit date you can see when the Fire button was used.

2. The id field in the history_entries does not reset, so you can get an idea of how many unique URL's are visited.

So, there we have it, you can recover browsing history for the history.db file by using the WAL file. In my example the whole database was in the WAL file as a checkpoint hadn't occurred yet, but real-world you would have old versions of pages in the main database file and WAL file that you can potentially recover browsing history from.

Hope you find this information useful if you are looking at browsing history for the DuckDuckGo App on Android during your examinations.

Happy Hunting 🍻