The Duck Hunters Guide - Blog #5 - Bookmarks & Favorites (Android)
Favorite sites are bookmarks that user has favorited. The favicon for a favorited site will show on a tile on a new browser tab to enable quick navigation to the URL.
The main artifact for Bookmarks and Favorites is the app.db sqlite database.
app.db location: data\data\com.duckduckgo.mobile.android\databases\
This database has a lot of tables, including tables called bookmarks, bookmark folders and favorites. One would think that these tables would contain all the information about Bookmarks and Favorites; however, they are empty.
In actual fact the Bookmark and Favorite information goes into the entities table and the relations table.
Entities Table
The entities table contains the core information for Bookmarks and Folders.
- entityId - Type 4 UUID (Random) unique identifier
- title - Title for the Bookmark/Folder
- url - URL for Bookmarks
- type - What type of entity it is (Bookmark/Folder)
- lastModified - When the entity was last updated (UTC)
- deleted - flag to determine if the entity is deleted
The relations table maps bookmarks to folders and determines whether a bookmark is favorited.
- id - Unique Identifier
- folderId - Id for the associated folder in the entities table
- entityId- Id for the Entity in the entities table
Bringing it Together
We can pull all this information together to get a full picture of the users bookmarks
-- CTE to identify folder name using the folderId
SELECT
entities.entityId AS folderId,
entities.title AS folderTitle
FROM entities
WHERE entities.type = 'FOLDER'
),
bookmark_favorites AS (
SELECT
relations.entityId
FROM relations
WHERE relations.folderId = 'favorites_root'
)
SELECT
entities.rowid,
entities.entityId,
folder_titles.folderTitle AS 'Parent Folder Title',
CASE
WHEN bookmark_favorites.entityId IS NOT NULL THEN 'YES'
ELSE 'NO'
END AS Favorited,
entities.title,
entities.url,
entities.type,
entities.lastModified,
CASE entities.deleted
WHEN 1 THEN 'YES'
ELSE 'NO'
END AS Deleted
FROM entities
LEFT JOIN relations ON entities.entityId = relations.entityId
LEFT JOIN folder_titles ON relations.folderId = folder_titles.folderId
LEFT JOIN bookmark_favorites ON entities.entityId = bookmark_favorites.entityId
1 of the 5 bookmark entries is flagged as deleted (My script converts 0-1 to NO-YES). With this deleted bookmark we get the entityId and when it was last modified, but not the Bookmark Title or URL.
Folder Path: NULL
Title: NULL
URL: NULL
LastModified: 2025-04-02T16:21:03.727596Z
I already established that this database uses secure_delete when we went through closed tab information, but just to confirm I did go the physical location of the record. As expected, it's just a standard record modification and as less data is being written it fit into the existing space in the cell content area and the left over space turned into a freeblock.
Cached Data
The other location we can look for artifacts associated with bookmarks is the cache.
Within the favicons folder will be the png favicons for bookmarks
Favicons Location: data\data\com.duckduckgo.mobile.android\cache\
In the example above the favicon is recognizable (at least to me) and I can tie this back to the bookmark entry for https://www.formula1.com/; however, that's not necessarily a scientific method especially if the favicon is not recognizable.
To truly tie this back to the bookmark entry we have to look at the filename for the favicon.
File name: 40d5ac0812593c0cfdb8e38918d2525396263c4b7d9c92d93bf6f97a93e76d6d
Its 64 characters in length, so the first thing that may spring to mind is a SHA-256 hash value, which is correct. This is actually the hash of the Bookmark URL, but a "normalized" version of the URL. Basically, the url is stripped of http://, https://, www., any trailing /
Bookmark URL: https://www.formula1.com/
DDG Normalized URL: formula1.com
SHA256 Hashing formula1.com gives us 40d5ac0812593c0cfdb8e38918d2525396263c4b7d9c92d93bf6f97a93e76d6d which is the filename for the favicon file in the example above.
The methodology here is to take all the Bookmark URLs from the entities table, normalize the URLs and then sha256 hash them. Of course, you don’t want to do this one by one, so I wrote a little python script to do it in bulk.
DuckDuckGo_BookmarkURL_SHA256.py
With my database, I can map 3 favicons back to a bookmark. I also have an additional favicon, and 1 bookmark entry in the app.db that doesn't have a favicon.
My initial assumption was this was the favicon for the bookmark entry in the app.db that was flagged as deleted, which is true, but it only exists due to a bug. I did a lot of testing and found that when a bookmark is deleted, the favicon is deleted from the cache folder immediately; however, if the bookmark was favorited the favicon will persist forever even after using the fire button.
If you do encounter an "unassociated" favicon you could attempt cracking the SHA256 hash by creating a word list of the normalized urls extracted from browsing history (history.db) but this may not be fruitful.
Impact of Clearing Data
When the user clears their data (aka Fire Button), as expected Bookmarks and Favorites are not cleared as they are designed to be persistent. This also includes the Favicons cache folder.
That being said, any entries in the entities table (app.db) that are flagged as deleted will be secure_deleted. If you do find an entry that has been flagged as deleted, it would have been deleted since the last time data was cleared. The datetime when data was last cleared is stored in the com.duckduckgo.app.fire.unsentpixels.settings.xml in the shared_prefs folder.
The datetime stamp is stored as Unix Milliseconds.
Conclusion
Whilst bookmarks and favorites aren't the most exciting artifacts, sometimes this might be all we have to go on if browsing history was cleared. Bookmarks and Favorites shows knowledge of a specific site, and I consider it user browsing activity as you need to go to that site to Bookmark it.
Another successful day of hunting Android DuckDuckGo artifacts 🍻
Comments
Post a Comment