I’ve written about SQLite databases here and there over the years. A number of Apple tools and third party tools for the platform run on SQLite and it’s usually a pretty straight forward process to get into a database and inspect what’s there and how you might programmatically interact with tools that store data in SQLite. And I’ll frequently use a tool like Navicat to quickly and visually hop in and look at what happens when I edit data that then gets committed to the database.
But I don’t always have tools like that around. So when I want to inspect new databases, or at least those new to me, I need to use the sqlite3 command. First, I need to find the databases, which are .db files, usually stored somewhere that a user has rights to alter the file. For example, /Library/Application Support/My Product. In that folder, you’ll usually find a db file, which for this process, we’ll use the example of Data.db.
To access that file, you’d simply run sqlite3 with the path of the database, as follows:
sqlite3 /Library/Application\ Support/My\ Product/Data.db
To see a list of tables in the database, use .tables (note that a tool like Postgress would use commands like /tr but in SQLite we can run commands with a . in front and statements like select do not use those):
.tables
To then see a list of columns, use .schema followed by the name of a table. In this case, we’ll look at iOS_devices, which tracks the basic devices stored on the server:
.schema iOS_devices
The output shows us a limited set of fields, meaning that the UDID is used to link information from other tables to the device. I like to enable column headers, unless actually doing an export (and then I usually do it as well):
.headers ON
Then, you can run a standard select to see what is in each field, which in the below example would be listing all information from all rows in the myapptable table:
select * from myapptable;
The output might be as follows:
GUID|last_modified|Field3|Field4
abcdefg|2017-01-26T17:02:39Z|Contents of field 3|Contents of field four
Another thing to consider is that a number of apps will use multiple .db files. For example, one might contain tables about users, another for groups, and another for devices in a simple asset tracking system. This doesn’t seem great at first, but I’ve never really judged it, as I don’t know what kind of design considerations they were planning for that I don’t know. If so, finding that key (likely GUID in the above example) will likely be required if you’re doing this type of reverse engineer to find a way to programmatically inject information into or extract information out of a tool that doesn’t otherwise allow you to do so.
