I’ve had a few questions recently about the BrowserState.db database on iOS that caused me to dig a little deeper into this source and this blog will share the findings along with demonstrating a feature of the upcoming version of ArtEx (18.104.22.168).
The request has always revolved around the accuracy of the timestamp that can be found in the last_viewed_time field of the TABS table of the BrowserState.db database.
This timestamp value is being mistakenly used as an indication of the visit time of the URL in the same record.
And why wouldn’t it be? We have a record with a URL and Page Title and Timestamp.. It's even called last_visited_time. It makes perfect sense.
Except.. that's not what this database is used for.
This entire database is related to Sessions, not individual page visits, and this can be seen by the names of the tables that exist.
This led me to do some digging because some of the results I was seeing were clearly not correct.
Note that some records were perfectly correct and that’s what makes this source even harder to understand. And it also seems to vary by iOS version to add to the fun!
So the good news is that if you have relied on this timestamp before, it doesn’t necessarily mean it was wrong. In fact, it probably wasn't (not by much anyway).
But let's take a look at simple example of the issue:
The important bits to know are that the title of the page (ie the query) is the time that I performed it.
This query was made at 1201. But you can see the last_viewed_time is 11:47:12, ~13 minutes before I performed the search.
This is not a huge difference, as I have seen this difference be far more than 24hrs in real cases. But regardless of size of the delta, the fact that it IS wrong could be an important detail to the case.
To research this, I used Live Connection via ArtEx to my Jailbroken devices and opened BrowserState.db. For anyone unfamiliar, I discussed this feature in an earlier blog post (http://doubleblak.com/ArtExtraction). In a nutshell, it is a way to connect to a device via SSH and see the results in near real-time and it saves me having to extract the device multiple times.
First, lets take a quick look at the database schema which, as we saw earlier, has only 3 tables (I’m ignoring the standard ‘sqlite_sequence’ table here).
TABS will be the table we focus on mostly and is the real source of the confusion. Although, I will bring TAB_SESSION into the mix too as it can add some additional important detail.
Lets start with iOS14...
I’ll start with a clean slate, where I have deleted all cache history.
It’s worth noting that if I clear history via Safari itself, it doesn’t seem to really clear this database, but if I do it via settings > Safari > Clear, the database itself is totally deleted. Which is perfect.
So with no browserstate.db database at all, lets begin...
At 0941 I opened Safari and used the main Safari Search Box to search for 0941.
I could see in ArtEx that the BrowserState.db data was immidiately created and upon opening it and viewing the tabs table, I can see my search record.
You can see the title shows the 0941 search query and the last_viewed_time is about right.
A few minutes went by and I used the Google Search bar to now search for 0944.
ArtEx allows me to use the button in the top right of the window to download the latest version of the BrowserState.db database from the device and load it to screen with the same query. This really speeds up the research.
Immediately, you can see the Title reflects the 0944 time but the last_viewed_time still shows 09:41.
I repeated the test again at 0947.
Again, the title is updated (so is the URL but I’m not showing that in the screenshot) but the last_viewed_time is the still 09:41..
OK, so lets try a new tab.
At 0949, I opened a new tab and searched for 0949.
Reloading the table now shows a second record:
Record 1 is unchanged, and record 2 shows 0949 for both the title and last_viewed_time.
For good measure, I tested this second tab the same way I’d tested the first, and searched for 0950.
As expected, the title changed but the last_viewed_time did not.
At 0951, I returned to the first tab and searched again.
Now, we see that the last_viewed_time has changed to reflect the time the tab took focus (remember, we are in the tab table here).
Repeating the same test as earlier should mean that the title is still updated but the last_viewed_time remains the same then:
And that is exactly what happened.
Final test for this then, is to move back to the second tab and search. This should change the last_viewed_time... And it did.
So by now, I am confident that the last_viewed_time is the time that the tab takes focus and is irrelevant to the time the search was conducted. But I wanted to push it a little further.
At 0957 I returned to the first tab but did not perform a search. Nothing happened to the database.
Simply moving to a different tab didn’t update the database.
I waited a little and then did a search. And the last_viewed_time got the update from the time I switched focus.
At 1000, I minimized Safari. I wanted to see if bringing Safari itself back into focus had an affect too.
At 1002, I brought Safari back to the foreground and performed a search.
Well, that was less than expected. Neither tab’s last_viewed_time was updated to reflect the either the minimize or maximize event, but tab 2 inherited the last_viewed_time from tab 1.
At 1004, I minimized Safari again.
At 1005, I maximized, but didn’t perform a search. Nothing changed.
I waited a minute and searched.
The title was updated but the last_viewed_time was not.
So minimizing & maximizing did have an affect, but not on the tab being viewed? I wanted to test this again.
At 1007, I switched to tab 2 and performed a search. As expected by now, the last_viewe_time was updated to reflect the tab switch.
At 1008, I minimized Safari and at 1009 I maximized and performed a search...
It happened again:
At 1010, I closed Safari fully, reopening it at 1012 without performing a search.
OK, so opening the app from closed will update the last_viewed_time even though I didn’t search.
I tried it again at 1013 only for the same thing to happen.
This actually makes perfect sense though, as loading Safari from a closed state causes a reload of the tab, just as if I’d done a search.
OK, so what did we learn?
Firstly, and most importantly, the last_viewed_time does not necessarily relate to the URL that is shown. It is in fact time the tab took focus. This could mean taking focus from another tab, being generated as a new tab or loading Safari from closed. It could also be relate to a tab taking focus when the currently selected tab is closed.
The time inheritance between tabs was still a confusion to me that I wanted to test some more, so I opened a few more tabs.
I minimized Safari at 1021 and maximized it at 1022 while tab 5 was in focus.
Note that only tab 4 inherited the time from tab 5.
But also note the other timestamp changes:
Tab 1 remained the same.
Tab 2 inherited the time from tab 3.
Tab 3 inherited the time from tab 4.
Tab 4 inherited the time from tab 5.
Tab 5 remained the same.
Odd and I don’t know that I can explain the logic to this, if there is any. But it’s worth bearing in mind.
OK, so what else can we learn from this database?
If we take the UUID from the tabs table, we should be able to find the same value in the tab_sessions table.
And we can see there is a session_data blob there so lets take a look at this bplist.
We see here a list of entries made during the session.
Without going through them all, here is a sample:
In fact there is 7 entries here that relate to searches for 0941, 0944, 0947, 0951, 0953, 0959, 1002, 1006.
Note that it's not just searches that is stored here, it's any navigation.
This could be a gold mine to show how a page was navigated to or what was searched/viewed as part of the session. Shame there are no timestamps*, but great information nonetheless.
*We’ll discuss Google EI timestamps later on.
Before finishing of iOS14, I wanted to show a new feature of ArtEx which is related to improved FreePage support.
Hitting this button in the top bar will run my custom FreePage code and this will give you a look at all instances of a record.
Here, we can see many instances of Tab 1 from all of the different free pages. I may do a blog post specific to this feature in the coming weeks.
And for extra fun, I can take the offset from one of them and dig even deeper.
I took the offset for record 1 which was 82179.
I then went to the WAL Explorer tab
By clicking on the blue “Page 1” label, I can jump to the frame & page of the database that contains this offset.
From here, I can see that this is Page 5. And I can then Click on the blue "Page 5" label and easily find all Page 5’s.
This can be really useful to see changes made to a page over time.
Note that the data in the table view is changing as the pages are navigated in the left table.
Moving on to iOS15
All Browser History was again wiped so that I could start with a clean slate and this meant that the BrowserState.db was deleted.
I opened Safari and performed my first search at 1338. I then tried to view the database.
Surprisingly, the database did not exist. What?!
At 1339, I performed a second search and looked for the database again but it was still not present.
At 1340, I opened a new tab and searched for 1340. But still nothing.
At 1341, I returned to the original tab. But STILL nothing!
I closed the tab at 1342.
And looky what showed up.
Note that the creation time matches the time I closed the tab.
When I opened the database and the tabs table, I saw just one record.
The last_viewed_time was related to the time I gave this tab focus and the URL was related to the search that had been conducted earlier.
At 1344, I performed a search for 1344, but this is now in the only tab I had open, (technically tab 2) which had yet to show up in the database.
A reload changed nothing. Maybe I had to close the tab again?
At 1345 I closed the tab. This would have left zero tabs open, and so Safari created a new, empty tab on my behalf.
Refreshing the database again, the second tab had now appeared.
This was already exhibiting some significant differences to iOS14 in terms of when the record was updated. So lets see what else is different.
At 1347 I searched for 1347 and at 1348, I minimized the Safari.
At 1349, I maximized Safari again.
At 1350, I searched for 1350 and then closed the tab.
Minimizing and Maximizing the tab had zero affect on any of the timestamps.
Tab 3’s last_viewed_timestamp was essentially the time that I’d closed the tab and Safari created me a new one. I have it listed at 1345 above, but it was right on the cusp of 1346 so this makes sense.
I tried a couple more times but only found the same result.
At 1354 I searched for 1354 and at 1355 closed Safari altogether.
I opened Safari back up at 1356 to find that nothing had changed. Although I did see that Safari reloaded the page on loading again.
At 1357, I closed the only open tab.
This caused the record to be written, and the last_viewed_time synced with the time I reopened Safari.
OK, so the biggest takeaway here is WHEN the record is written to the database, but the timestamps essentially mean the same thing.
Returning to the tab_session table, this still appears the same, with the session history being accessible via the session_data blob.
Moving on to iOS 16
As with the previous two tests, I wiped the Safari history resulting in the entire database being deleted.
At 1406, I opened Safari and searched for 1406.
As with iOS15, no browserstate.db database was created.
At 1407, I closed the tab and again, this was the catalyst for the database to be created.
As before, the last_viewed_time reflected the time the tab was opened.
At 1413, Safari was minimized and it was maximized again at 1414.
A search was conducted at 1415 followed by the tab being closed.
At 1416 a search was conduced followed by Safari being closed.
At 1417 Safari was opened and the page reloaded itself.
At 1418, the tab was closed and the record was written.
The last_viewed_time is indicative of the time Safari was opened.
Finally, at 1420 a new tab was opened and a search performed before the device was locked.
At 1431 the device unlocked and Safari was front and center. The tab was closed but it was seen that the locking of the device had no affect.
iOS17 was a little different, as I don’t have a jailbroken device. So I needed to do everything I wanted to in one go.
All Safari data was deleted as normal.
At 1434, Safari was opened and searched.
Further searches were performed at 1436 and 1437 in the same tab.
At 1438, a new tab was opened for searching “1438”.
At 1439, the first tab was closed.
At 1440 a new tab was opened and searched.
At 1445, the new tab was closed, leaving just tab 2.
At 1446 a search was done in tab 2 before the tab was closed.
A new tab was opened at 1447 and search conducted.
We can see with EI value is Z39aZaitKOXv0PEPmfOqgA so we can run our conversion on it to get the timestamp of 19 Nov 2023 14:34:31 (UTC -7:00).
But we can also see the time that I entered as the search query is 1436.
So hold up... If I submitted the search at 1436, why is the EI time 90 seconds earlier?
It's actually a really simple answer.
As soon as I go to google.com, I can see in the Developer Tools that the timestamp is recorded.
And if I convert this value (8-ZfZYnbDrTK9APmpozICg) to EI Time, I get 23 Nov 2023 16:57:39 (UTC -7:00) which is the time the page loaded.
If I check the source code for the page, I can find this value again in a hidden text box on the page.
I can then perform a search at 1659 and that EI value is found within the querystring.
If I check the source of this page, I find the same Hidden EI text box, but with a new value:
a-dfZfWwMMO-0PEPxeu5-A0 = 23 Nov 2023 16:59:39 (UTC -7:00)
And when I do another search, this time at 1701, it is that value that shows in the querystring.
So ultimately, while this IE Timestamp value can be indicative of when a URL was visited, it is actually the time that the page before loaded.
For normal navigation, it’s probably not more than a few seconds away from reality.
But likewise, I can also visit a google.com page and not actually submit a search for days. Weeks even. When I finally do submit a search, the EI Timestamp will reflect the time that the previous page loaded, not the time I performed this specific search.
This is a great example of not only how these files can change over time, but how their use can change over time too. And that something that seems obvious, such as a field called "last_visited_timestamp" may be anything but.
So while this timestamp (or the timestamp from the querystring) may not be far away from the truth, it can only really be treat as an approximation of the time the page was visited, as the possibility that it could actually be much further from reality.