Monday, April 25, 2011

iSpyPhone - Your iPhone Knows Where You've Been

You’ve heard how your iPhone keeps track of where you’ve been, right?  Would you like to see exactly which locations it has been logging? 

If you back up your iPhone to a Windows computer, here’s how to access that data in just three easy (well, slightly technical) steps.  Note: if you enabled backup encryption in iTunes, this isn’t going to work.  Encrypted files are secure, and no longer readable.

The paper that started the controvery can be found at  Though the discussion is Mac-based, it’s a great reference for Windows developers.  The authors guide you through finding the Manifest.mbdb file, referencing a Python script to read it, searching for a file named consolidated.db, then translating that file to the alphanumeric file names Apple uses to obfuscate the data.

Where they elegantly used a scalpel to find the location file, sometimes an ax is so much more efficient:

Step 1: Find the File that Stores the Locations

  • Search for a folder called MobileSync (Start à Search à MobileSync).
  • Open it (you’ll probably just see a folder named Backup).
  • Search from here for files containing the text CellLocation

That’s it; you found it!  If there are multiple, use the most recent one.  If you didn’t find it, try searching for the text SQLite.  Apple stores data in SQLlite databases, where each file is an independent database; there should be several dozen.  If you don’t find any of these, you need to change search settings in your Content Indexing Service (see

Step 2: Extract the Location Coordinates

The file name is a string of numbers and letters like 4096c9ec676f2847dc283405900e284a7c815836.  Copy it to a file with a friendlier name, like consolidated.db.

Next, extract the geo-location data.  You’ll need a SQLite database reader:
  • Go to
  • Download the command-line shell program:
  • Unzip it, and you’ll have the file sqlite3.exe (no installer needed)
  • From a DOS Command Prompt, enter:
    sqlite3 consolidated.db "select * from CellLocation;" > CellLocation.txt

Now you have all your information in a pipebar-delimited text file.  There are no column headers, so:
  • Open CellLocation.txt and add this string as the first line:
  • Import it into Excel, and save it as CellLocation.xls

You should have 13 columns of iPhone data, and two empty columns: datetime and kml.

SQLite stores the Timestamp (Column E) as ‘seconds since 1/1/2001’.  Excel stores date and time as ‘days since 1/1/1900’.  To convert, change seconds to days (divide by 86400), set to the same start dates (add 36892), and adjust for your timezone (I’m 8 hours behind UTC so I’ll subtract 8/24th of a day).
  • Copy this string into cell N2 (datetime):
  • Format the cell to display the date and time
  • Copy this cell down the entire column

Step 3: Display the Location Coordinates

The final step is to create a kml file that can be displayed in Google Earth.  (You do have Google Earth installed, right?  If not, go get it.)
  • Copy this string into cell O2 (kml):
      ="<Placemark><name></name><description>"&TEXT(N2, "dddd mm/dd/yyyy hh:mm AM/PM")& "</description><Point><coordinates>" &G2&","&F2&"</coordinates></Point></Placemark>"
  • Copy this cell down the entire column

Now to make a file that Google Earth can read, in KML format:
  • Create and open a text file called CellLocation.kml
  • Insert these two lines into CellLocation.kml:
<kml xmlns=""><Document>
  • Copy all your kml statements from the kml column (Column 0)
  • Paste them between these two statements

Double-click CellLocation.kml and Google Earth should start up, with yellow pushpins showing each position your iPhone has logged.  Click on a pushpin to see the date and time it was recorded.

Note how the locations can vary drastically  I have points over ten miles apart with identical timestamps.  This may be related to Apple geolocating WiFi networks in the area, rather than determining exactly where I was standing … so take all results as approximations only.

If you’re concerned about someone accessing your PC to determine where you’ve been, set your backup security to Encrypted in iTunes.  Apple will still know, but no one else will be able to snoop on you.

You now have the tools to find, extract and process the iPhone’s SQLite files.  There are dozens of other ones on your system - go explore, and discover more ways your iPhone is keeping tabs on you!

11,000 points collected in the last 6 months