Accessing data from defunct iOS apps
I had an app called “TankPro” in which I tracked all my car expenses, including - and this was the crucial part - each and every refueling and service cost. Now with the latest update of iOS that app stopped working - “This app is too old, the developer has to update”. I had that fear, but I never got around to export and look for something new. So now - boom, you’re fucked, no more data for you today.
Internet to the rescue.
Assumptions
Since a fuel cost tracking app is basically something for which you’d use a database, I assumed that developer did the same and used the Apple-provided database API internally. Which is good since this is backed by SQLite. (none of this I knew for sure, this is all stuff I just picked up over time - yet I was just hoping to be right).
Now the question is how to access this data.
Get access to the data
Accessing the data using iTunes (now Music) did not work, the developer never added this functionality. Export did not work because the app didn’t start any longer. The developer’s web page was also quite discouraging. Finally I found a blog post which gave me hope, describing how to access the application’s files in an iOS backup performed by Music to the local Mac. Boom.
So first step, connect the iPhone to the Mac and perform a backup using the ‘Music’ app (yes, this is as stupid as it sounds, it should be a separate app now if you ask me).
Now find what you’re looking for …
After the backup is complete, all the backup data will be available here:
# the last part MIGHT change
$HOME/Library/Application Support/MobileSync/Backup/746acd60cc3e4674ab61071e390c4bce601c259f
The directory contents look like this:
# DIRS | # FILES
0a/ | Info.plist
0b/ | Manifest.db
0c/ | Manifest.db-shm
[...maaany more directories...] | Manifest.db-wal
fe/ | Manifest.plist
ff/ | Status.plist
Turns out the file called Manifest.db is itself a SQLite database. Let’s analyze:
$ sqlite3 Manifest.db
sqlite> .schema
CREATE TABLE Files (fileID TEXT PRIMARY KEY, domain TEXT, relativePath TEXT, flags INTEGER, file BLOB);
CREATE INDEX FilesDomainIdx ON Files(domain);
CREATE INDEX FilesRelativePathIdx ON Files(relativePath);
CREATE INDEX FilesFlagsIdx ON Files(flags);
CREATE TABLE Properties (key TEXT PRIMARY KEY, value BLOB);
Hmmh. One table, with content of files. The domain field looked interesting:
sqlite> select distinct domain from Files;
[...]
AppDomainPlugin-net.whatsapp.WhatsApp.TodayExtension
AppDomainPlugin-org.linphone.phone.linphoneExtension
AppDomainPlugin-org.linphone.phone.msgNotificationContent
AppDomainPlugin-org.linphone.phone.msgNotificationService
AppDomainPlugin-org.whispersystems.signal.NotificationServiceExtension
AppDomainPlugin-org.whispersystems.signal.shareextension
AppDomainPlugin-org.wordpress.WordPressAllTimeWidget
AppDomainPlugin-org.wordpress.WordPressDraftAction
AppDomainPlugin-org.wordpress.WordPressIntents
[...]
All right. It seems those are the apps. Now the defunct app’s name is “TankPro” by “osxwerk”, let’s see if it’s in there.
sqlite> select distinct domain from Files where domain like '%werk%';
AppDomain-de.osxwerk.TankPro
I’m on to something. More digging lead to this:
sqlite> select fileID, relativePath from Files where domain = 'AppDomain-de.osxwerk.TankPro';
607abd5ade35b2839a2fbd83378797ef6a66e74e|
521dd6d864d15473f598c8376e22177654039d17|Library
83a94dda1e6957a7dd823e07b76a117605912688|Library/Preferences
c3c18f8333773453136410a4210f1aea0b9fe199|Library/Preferences/com.apple.PeoplePicker.plist
ff0500ead582024057d7b4202da23d13ba9aee39|Library/Preferences/.GlobalPreferences.plist
078e4d9f26a620de79bd950cac5ffbb41db6f07f|Library/Cookies
e5aa547333c2d26ee8866db1ec4d6ab4826751cc|Documents
1c70ac9b8b843a69c96b29b202958834a12f159a|Documents/tankbuch.sqlite
967d44623183390c36989f8574a946222e5dee63|Documents/tankpro.sqlite.old
787938b3c7f21fe178d59f4c030e3451f2f3dd23|Library/Preferences/de.osxwerk.TankPro.plist
Very promising, right?? Let’s see what 1c70ac9b8b843a69c96b29b202958834a12f159a might be … :
$ find . -iname 1c70ac9b8b843a69c96b29b202958834a12f159a
./1c/1c70ac9b8b843a69c96b29b202958834a12f159a
$ ll ./1c/1c70ac9b8b843a69c96b29b202958834a12f159a
Permissions Size User Date Modified Name
.rw-r--r-- 77k tm 30 Apr 18:56 ./1c/1c70ac9b8b843a69c96b29b202958834a12f159a
$ file ./1c/1c70ac9b8b843a69c96b29b202958834a12f159a
./1c/1c70ac9b8b843a69c96b29b202958834a12f159a: SQLite 3.x database, last written using SQLite version 3032003
$ sqlite3 ./1c/1c70ac9b8b843a69c96b29b202958834a12f159a
sqlite> .schema
CREATE TABLE ZCAR ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZACTIVE INTEGER, ZCONSUMPTIONDISPLAYUNIT INTEGER, ZFUELCAPACITYUNIT INTEGER, ZFUELTYPE INTEGER, ZODOMETERUNIT INTEGER, ZIMAGE INTEGER, ZCREATEDAT TIMESTAMP, ZUPDATEDAT TIMESTAMP, ZLICENSEPLATE VARCHAR, ZMAKE VARCHAR, ZMODEL VARCHAR, ZNAME VARCHAR, ZREMARK VARCHAR );
CREATE TABLE ZCARIMAGE ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZCAR INTEGER, ZCREATEDAT TIMESTAMP, ZUPDATEDAT TIMESTAMP, ZIMAGE BLOB );
CREATE TABLE ZCOST ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZODOMETER INTEGER, ZREFUND INTEGER, ZCAR INTEGER, ZAMOUNT FLOAT, ZCREATEDAT TIMESTAMP, ZDUEDATE TIMESTAMP, ZUPDATEDAT TIMESTAMP, ZCOMMENT VARCHAR, ZREASON VARCHAR );
CREATE TABLE ZFILLUP ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZFUELTYPE INTEGER, ZODOMETER INTEGER, ZQUANTITYUNIT INTEGER, ZTYPE INTEGER, ZCAR INTEGER, ZCREATEDAT TIMESTAMP, ZDATE TIMESTAMP, ZLATITUDE FLOAT, ZLONGITUDE FLOAT, ZPRICE FLOAT, ZQUANTITY FLOAT, ZUPDATEDAT TIMESTAMP, ZCOMMENT VARCHAR );
CREATE TABLE Z_PRIMARYKEY (Z_ENT INTEGER PRIMARY KEY, Z_NAME VARCHAR, Z_SUPER INTEGER, Z_MAX INTEGER);
CREATE TABLE Z_METADATA (Z_VERSION INTEGER PRIMARY KEY, Z_UUID VARCHAR(255), Z_PLIST BLOB);
CREATE TABLE Z_MODELCACHE (Z_CONTENT BLOB);
CREATE INDEX ZCAR_ZIMAGE_INDEX ON ZCAR (ZIMAGE);
CREATE INDEX ZCARIMAGE_ZCAR_INDEX ON ZCARIMAGE (ZCAR);
CREATE INDEX ZCOST_ZCAR_INDEX ON ZCOST (ZCAR);
CREATE INDEX ZFILLUP_ZCAR_INDEX ON ZFILLUP (ZCAR);
… aaaand I got my receipts back. Boom baby!! Happy day.