by RSS Axelle Apvrille  |  Apr 21, 2011  |  Filed in: Security Research

Some time ago, a security researcher, Alex Levinson, found out the iPhone was keeping a SQLite database of the iPhone's location (wifi-based location, cell-based or GPS) and a few other information.

The file, located in /private/var/root/Library/Caches/locationd/consolidated.db, is easily accessible on jailbroken phones (ssh or any file transfer tool) and readable by any SQLite3 tool.

This issue has recently re-surfaced as two researchers, Pete Warden and Alasdair Allan, wrote a MacOS tool to generate maps from the locations recorded in that database, and are presenting this at Where 2.0 in San Francisco today.

If you don't have a Mac, then there is an online tool here (in French) or you can use Firefox4 SQLiteManager plugin + Google Fusion to do the trick (which is actually the solution I used for the maps below).

I would also encourage you read Mikko Hypponen's post. It offers an interesting explanation as to why Apple designed such a database. In short, Hypponen's idea is that it reduces the costs of renting an external location database.

The few things I would like to add to the story are:

  • the consolidated.db is a 'standard' SQLite3 database, so you can query it like any SQLite database, there is no need for sophisticated tools (but they are cool). Data is directly usable:

    sqlite> .dump CellLocation PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE CellLocation (MCC INTEGER, MNC INTEGER, LAC INTEGER, CI INTEGER, Timestamp FLOAT, Latitude FLOAT, Longitude FLOAT, HorizontalAccuracy FLOAT, Altitude FLOAT, VerticalAccuracy FLOAT, Speed FLOAT, Course FLOAT, Confidence INTEGER, PRIMARY KEY (MCC, MNC, LAC, CI)); INSERT INTO "CellLocation" VALUES(208,10,49802,21036492,314034125.866114, 43.60604608,7.06016272,1211.0,0.0,-1.0,-1.0,-1.0,70); ...

  • The WifiLocation table tries to make up your location based on the wifi access points your iPhone sees, and for which Apple knows the location. If your iPhone sees a wifi access point known to be located by the Eiffel Tower, well, you probably are located close to the Eiffel Tower. This is done without using GPS.

  • The CellLocation table does basically the same, but based on the GSM access points your phone sees.

Now, in my case, I noticed neither table mentioned I had gone to Poland with the iPhone. Why ? Well, obviously, when you restore an old image of your phone, you overwrite the database :) By the way, the iPhone also made a poor estimation of my altitude and thinks I work at sea level (which is not the case).

  • Comparing the cell location with the wifi location (see maps below) may release interesting information. First of all, it shows that Apple does successfully associate our workplace wifi with its physical location (I believe the several locations in Sophia Antipolis - where we are located - are just various approximations). It also shows that our lab iPhone (well, the backup I restored) only accessed wifi from our office , that we did a trip to Toulon, but that we did not use wifi there.



  • On a security point of view, it should be noted [thanks Guillaume for raising the point] that consolidated.db's integrity is not guaranteed at all. It is easy to modify it to say I was in Greenland last month. Or I could hack into someone's else iPhone and alter it so as to show that this person was on a crime scene when the crime happened. Thus, this should be handled carefully by forensics experts.

  • The 'untrackerd' application cleans the database regularly.

  • Finally, you might have noted the iPhone stores the MCC (Mobile Country Code) and MNC (Mobile Network Code) of the SIM. It is interesting to note it did notice I sometimes use a fake SIM (208/30). This is when I use a local OpenBTS replication jail I will talk about at VB 2011 - patience :) In that case, it is unable to locate my position as it is not aware of this fake operator (as it is only valid within the walls of our lab) :)

    INSERT INTO "CellLocation" VALUES(208,30,1000,10,314034365.532726, 0.0,0.0,-1.0,0.0,-1.0,-1.0,-1.0,0);

by RSS Axelle Apvrille  |  Apr 21, 2011  |  Filed in: Security Research