This promises to be an interesting article, covering a few different aspects not only of MapsSync, but locations, and databases in general. Hopefully, I will do it justice and this will be as useful as I hope. Lets begin.

MapsSync

No doubt most people are aware of the MapsSync database; the database behind Apple Maps. Despite being a fairly standard SQLite database, there are some design choices which we don't commonly see across other databases.

You have probably seen that in many iOS databases, there is a Z_ENT field (that never really seems to add much value to the table) and that typically, the Z_ENT field will be the same in every row.
If we look at knowledgeC as an example, the ZOBJECT table shows the value Z_ENT value 11 in all rows.

If we look at ZSTRUCTUREDMETADATA we see all rows show 15 in Z_ENT.

If we look at the Z_PRIMARYKEY table, these values start to make sense.

You can see here that the Z_ENT type 11 has the name “Event”, which makes sense for the items in the ZOBJECT table. The Z_ENT value 15 has the name “StructuredMetaData” which again makes sense for items in the STRUCTUREDMETADATA table. (Best guess is that Z_ENT is for “ENTITY”).

Moving back to the MapsSync database, and looking specifically at the ZHISTORYITEM table, we see that the Z_ENT field contains a range of values (15, 18 and 20). This is pretty different to what we are used to.

Again, these ENTITY Names are listed in the Z_PRIMARYKEY table to let you know what item you are looking at.

So we now know that Entity 15 is HistoryDirections, 18 is HistoryPlace and 20 is HistorySearch. That means we could filter the ZHISTORYITEM table to just those with Z_ENT value 20 to see what the user has searched for, or filter to Z_ENT 18 to see Places.

But it's the HistoryDirectionsItem (Type 15) that the rest of this post will focus on.

Think about creating a route in iOS. You open the app, search for a place and then request directions to it. You will get 2 results here with very close timestamps; the search and the route.

Visited Location

One common question about the Directions History is whether the user was actually at either the start or end location.

It's probably obvious that the end location would likely be interesting to us because it's a location of interest to the user. It was somewhere they planned a route to. But that doesn't mean that they actually followed those directions or ever arrived at the destination.

The Starting location is a little more interesting. And if you think back earlier when I asked you to think about planning a route, where did the route begin? I would bet that the starting location of the route was your actual location at the time you planned the route.

This would be a safe bet most of the time, but it is possible to plan a route between location B and location C while you are in Location A, and for us to make assumptions could be dangerous.

I have tested thoroughly and have found that in every record where I planned a location from my current location, a flag exists in the database. And in every case where I plan a route from an arbitrary location, the flag does not exist. I use this as the basis for determining if the user was at the starting point or not.

The flag I am referring to can be found in the ZROUTEREQUESTSTORAGE field of the DirectionHistory row. This is the protobuf blob that stores the directions themselves.

What I noticed is that every route planned from my current location had a Node 100 item, but every route planned from an arbitrary location did not.

This logic was working fine for me without issue for a while. That is, until 2023's CTF.

CTF - Abe

If you played, you may have seen this artifact on Abe's device:

Before we go any further, I will spoil part of the fun and say that this is not incorrect. It's just odd and requires some thought.

So, what is the issue with this record? Well. There's a few.

• Firstly, the From Point is 15 minutes earlier than the To Point.
• Secondly, these 2 locations are so extremely close to each other I questioned why would you even conduct this search?
• Thirdly, even ignoring the fact that the timestamps are the wrong way round, they are still almost 15 minutes apart, for a distance of about 40m.

From Point shown in Green. To Point shown in Red.

It just didn't make sense.

I checked the ZROUTEREQUESTSTORAGE blob and found that node 100 existed. So, by my previous logic, that would mean that the device was at this location at the time that the route was planned.

Luckily, in this case, the cache database was still populated, which looked like this:

The black arrow points to the device's location according to the cache database at 3:23PM. The rest of the cache locations can be seen as gold dots and make perfect sense travelling along the road to the top left of the map which is where the MapsSync data was located.

Even more interesting though, was that when I looked at the MapsSync database without including the WAL file, I got a different Start Location and it also contained Node 100.

Looking back at the overview map, you can see that this location is pretty close to the location shown in the cache database at the time the Journey was created. This certainly makes more sense.

I also checked the origins of the timestamps and found the following:

So immediately, a difference can be seen in the ZModificationTime, but the ZCreateTime is the same.

Another notable difference was the Z_OPT field which also gets overlooked way more than it probably should. We will look at this field in more detail momentarily.

In this case, without the WAL, the Z_OPT value was 5, but with the WAL, the Z_OPT value was 15.

While I admit I'm not entirely sure what the OPT stands for, I can describe it as showing the number of times the record has been updated. (It's possible that OPT means Option? Optimal? :shrug)

I decided to look at the WAL in a bit more detail and look for every other version of this page that I could find.

Within ArtEx, I ran the Advanced SQL Recovery. Before I show those results, I would like to dive more into what it is doing and how you could do this manually if Advanced Recovery failed.

Advanced Recovery & The Explorers

The SQL and WAL Explorers are a great way to see what is happening during the Advanced Recovery process.

You can find the SQL and WAL Explorers by simply opening a database you are interested in. Here, we will look at the MapsSync database.

Immediately upon opening the database, you can see three tabs at the top; SQL Viewer, SQL Explorer and WAL Explorer.

I can use the SQL Viewer as a normal SQL tool and find the record that I'm interested in. In this case, it's the last record in the table; Z_PK = 36.

I can then move to the SQL Explorer tab which will show the database header and the contents of the first page.

SQL Explorer will parse out the Page Header, and assuming it is a supported page type (ie. Not an overflow page) will parse the contents of this single page into the table view at the bottom of the window.

I can use the arrow buttons in the left pane move forward or backwards page at a time, or press the blue “Page” label to enter a specific page or offset.

The Explorer processes every page in isolation. It doesn't understand (or care) about the rest of the database, and that includes the field names. Hence all fields are named Field 0, Field 1, Field 2 etc.

As I pointed out earlier, I know that it is record 36 that I am interested in, and I eventually found it on page 413 of the database You can see the only record here shows 36 in Field 0.

In this case, I simply scrolled through the pages until I found record 36, but if I'd had known the offset (by using hex search for example), I could have jumped straight to it.

Note that at this time, the SQL and WAL Explorers don't have timestamp conversion or Blob viewer capabilities, but it's still useful.

We can see that Field 2 has a value of 5 and since we already know that Field 2 is the Z_OPT field, we know that this record is the 5th update.

I'm now going to jump over to the WAL Explorer tab.

This shows the File Header, the Frame Header and the Page Header, along with the hex view of the Frame and the parsed page (if applicable).

Before we go further, I want to very briefly explain databases or else the next part may not make sense.

The main database file is made up of PAGES. These pages contains the rows of data you see in the tables.

In contrast, the WAL is made up of FRAMES. Each FRAME contains a PAGE and a header.

If you read the database without the WAL, the Live Page (in my case, page 413) will be displayed. But if you include the WAL, the most recent version of the page will be shown but there may be multiple.

Going back to ArtEx, the blue text on the left column shows me that Frame 1 (or 360) contains a copy of Page 100. And if I click on the “Page 100” label, I can then type in the page number I'm interested in (ie. 413) and then press “Find All Page 413”.

ArtEx will take a minute or two to scan each Frame of the WAL file, looking for all instances of Page 413 and by the time it's finished, it's found 9 instances of this page.

This represents the 9 versions of Page 413 in the WAL file. So in total, I have 10 Page 413 instances between the WAL and Live Database.

I can clicking on any of the results in the list to jump to that specific frame.

You can see here that as I click down the results, the main frame updates, the page is parsed and the value in Field 2 (Z_OPT) increases.

It's not only the data in Field 2 that gets updated though. The value in the timestamps and blob gets updated too.

This is essentially how Advanced Recovery works in ArtEx. It parsers each page/frame independently and then looks at the database schema to see if it can work out where the data belongs.

So now, making life easier, I can return to the SQL Viewer and press the Advanced Recovery button found in the top right corner, above the SQL Query input.

It will prompt if you want to view all duplicates and in this (and most) cases, we do.

If I answer no to this question, ArtEx will still recover data, but it will only include the records that have unique PK numbers and that's not what I want to see here.

By Showing Duplicates, every instance of every record should be shown, and we can now see that 46 records were found, 10 records more than before the Advanced Recovery ran.

We can also see that 11 of the records have a Z_PK value of 36, and that the Z_OPT values range from 5 through 15.

If I run a query to only see the Creation and Modification times of records with Z_PK 36 then I can see that although the Created Date remains the same on every record, the Modification date is updated every few minutes.

Recap

To recap before we go any further;

• We know the Journey was created at 15:23:41.
  We know this due to what we see in the ZCREATETIME of every recovered record.


• We know where the device actually was at 15:23:41 and that it's nowhere near the location shown as the Start Location.
  We know this due to the cache.sqlite records.
  


• We know that the Start Location is different if you use the WAL or not.

• We know that neither of these Start Locations is where the device actually was at the Creation Time.
  Despite both Start Locations having a Node 100 which I believe indicates the starting location to be the user's actual location.
• We know where the device was at 15:24:17 (the Modification Time of Z_OPT 5)
  We can compare this to the device's actual location according to cache.sqlite and see that they match.

• We know where the device was at 15:37:50 (the Modification Time of Z_OPT 15)
  We can compare the device's actual location according to cache.sqlite to the Start Location in MapsSync blob at Z_OPT 15.

• We can also see that Z_OPT 14 was the same location but a slightly different time and we can compare that with cache.sqlite too.

So what is going on? I had an hypothesis but needed to do some testing.

The Hypothesis

The hypothesis was that the blob was updated whenever the route was recalculated. ie, whenever the device noticed that the direction being taken was not the direction being suggested.

So, at 15:23:21, when the Journey was created, the device was at the location shown on the map as item A and this was likely used as the starting point (Although since we don't have the original record, this cannot be proven).

At 15:24:17, the device recalculated the route and used the user's current location (location B) as the new starting point.

At 15:37:13, the device recalculated the route again, and again used the devices current location as the start point (Location C).

Finally, when the location was ended, the modification time was updated, to 15:37:51 but the route blob didn't need to be updated.

This would mean that my understanding of Node 100 is still correct, and perfectly explains both why the From Point and To Point are so close together and why the From time is later than the To time.

The Testing

I tested this by taking a short drive with a test phone and my laptop, pulling the MapsSync database every 60 seconds for later comparison.

This is the result:

This map shows the elements of importance.

The starting point is shown as a red arrow towards the bottom left of the image.

The original route I planned called for me to follow the orange line to the left of the map and down.

The first green arrow is the first time that I deviated from the planned route and caused my device to replot. This new route is shown in a dark blue and suggested I do a u-turn and rejoin the orange line.

This change was saved as Z_OPT 5 and correctly logged my location, at that time, as the start of the route.

The second green arrow is the second time I deviated from the planned route. This showed as Z_OPT 7 and the new suggested route is shown in red.

The third and forth green dots didn't result in a record in the database that I could see. They occured within very close proximity (both in time and distance).

The fifth green arrow is the last time that I deviated my route before ending the test. It was saved as Z_OPT 11 and is shown in cyan.

Clearly, I wasn't recovering all records here, and I was missing record 6, 8, 9, and 10.

But I was satisfied of the following;

• The record is updated periodically and the modified timestamp updated.
• If the user deviates from their plotted route, their current location will be used to replot, and the blob will be updated.

Of course, this makes perfect sense looking back at it now.

Wrapping Up

This is yet another artifact that appears so simple on the surface. But when you start digging into it, you can find layers of complexity and understanding how it is working may help you find additional location data, understand which map locations were actually visited, and how to explain odd time discrepancies.

Despite initially banging my head on my desk when I found this issue, I have to admit, I love this type of problem for the opportunity to learn several new things all at once. Hopefully, this will be useful for you too.