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):
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:
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):
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:
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.
krypted February 24th, 2017
Posted In: Mac OS X, SQL
App Store, apps, enumeration, GUID, inserting information, MAC, reverse engineering, select statements, sqlite, view headers
These days, new services get introduced in OS X Server during point releases. OS X now has a Software Caching server built to make updates faster. This doesn’t replace Apple’s Software Update Server mind you, it supplements. And, it’s very cool technology. “What makes it so cool” you might ask, given that Software Update Server has been around for awhile. Namely, the way that clients perform software update service location and distribution with absolutely no need (or ability) for centralized administration.
Let’s say that you have 200 users with Mac Minis and an update is released. That’s 200 of the same update those devices are going to download over your Internet connection, at up to 2 to 3 gigs per download. If you’re lucky enough to have eaten at the Varsity in Atlanta, just imagine trying to drink one of those dreamy orange goodnesses through a coffee stirrer. Probably gonna’ be a little frustrating. Suck and suck and suck and it’ll probably melt enough to make it through that straw before you can pull it through. For that matter, according to how fast your Internet pipe is, there’s a chance something smaller, like an update to Expensify will blow out that same network, leaving no room for important things, like updates to Angry Birds!
Now, let’s say you have an OS X Server running the new Caching service. In this case, the first device pulls the update down and each subsequent device uses the WAN address to determine where the nearest caching service is. If there’s one on the same subnet, provided the subnet isn’t a Class B or higher, then the client will attempt to establish a connection to the caching service. If it can and the update being requested is on that server then the client will pull the update from the server once the signature of the update is verified with Apple (after all, we wouldn’t want some funky cert getting in the way of our sucking). If the download is stopped it will resume after following the same process on a different server, or directly from Apple. The client-side configuration is automatic so provides a seamless experience to end users.
Pretty cool, eh? But you’re probably thinking this new awesomeness is hard as all heck to install. Well, notsomuch. There are a few options that can be configured, but the server is smart enough to do most of the work for you. Before you get started, you should:
- Be running Mountain Lion with Server 2.2 or better.
- Install an APNS certificate first, described in a previous article I wrote here.
- Have an ethernet connection on the server.
- Have a hard drive with at least 50GB free in the server.
- The server must be in a Class C or smaller LAN IP scheme (no WAN IPs can be used with this service, although I was able to multihome with the WAN off while configuring the service)
Once all of the requirements have been met, you will need to install the actual Caching Service. To do so, open Server.app from the /Applications directory and connect to the server with which you would like to install the Caching service.
Click on Caching from the SERVICES section of the Server sidebar. Here, you have 3 options you can configure before starting the service. The first is which volume with which to place updates. This should typically be a Pegasus or other form of mass storage that is not your boot volume. Use the Edit… button to configure which volume will be used. By default, when you select that volume you’ll be storing the updates in the Library/Server/Caching/Data of that volume.
The next button is used to clear out the cache currently used on the server. Click Reset and the entire contents of the aforementioned Data directory will be cleared.
Next, configure the Cache Size. Here, you have a slider to configure about as much space as you’d like, up to “Unlimited”. You can also use the command line to do some otherwise unavailable numbers, such as 2TB.
Once you’ve configured the correct amount of space, click on the ON button to fire up the service. Once started, grab a client from the local environment and download an update. Then do another. Time both. Check the Data folder, see that there’s stuff in there and enjoy yourself for such a job well done.
Now, let’s look at the command line management available for this service. Using the serveradmin command you can summon the settings for the caching service, as follows:
sudo serveradmin settings caching
The settings available include the following results:
caching:ReservedVolumeSpace = 25000000000
caching:SingleMachineMode = no
caching:Port = 0
caching:SavedCacheSize = 0
caching:CacheLimit = 0
caching:DataPath = "/Volumes/Base_Image/Library/Server/Caching/Data"
caching:ServerGUID = "FB78960D-F708-43C4-A1F1-3E068368655D"
caching:ServerRoot = "/Library/Server"
Don’t change the caching:ServerRoot setting on the server. This is derived from the root of the global ServerRoot. Also, the ServerGUID setting is configured automatically when connecting to Apple and so should not be set manually. When you configured that Volume setting, you set the caching:DataPath option. You can make this some place completely off, like:
sudo serveradmin settings caching:DataPath = "/Library/Server/NewCaching/NewData"
Now let’s say you wanted to set the maximum size of the cache to 800 gigs:
sudo serveradmin settings caching:CacheLimit = 812851086070
To customize the port used:
sudo serveradmin settings caching:Port = 6900
The server reserves a certain amount of filesystem space for the caching service. This is the only service I’ve seen do this. By default, it’s about 25 gigs of space. To customize that to let’s say, ‘around’ 50 gigs:
sudo serveradmin settings caching:ReservedVolumeSpace = 50000000000
To stop the service once you’ve changed some settings:
sudo serveradmin stop caching
To start it back up:
sudo serveradmin start caching
Once you’ve started the Caching service in OS X Server and familiarized yourself with the serveradmin caching options, let’s look at the status options. I always use fullstatus:
sudo serveradmin fullstatus caching
Returns the following:
caching:Active = yes
caching:state = "RUNNING"
caching:Port = 57466
caching:CacheUsed = 24083596
caching:TotalBytesRequested = 24083596
caching:CacheLimit = 0
caching:RegistrationStatus = 1
caching:CacheFree = 360581072384
caching:StartupStatus = "OK"
caching:CacheStatus = "OK"
caching:TotalBytesReturned = 24083596
caching:CacheDetails:.pkg = 24083596
The important things here:
- An Active setting of “yes” means the server’s started.
- The state is “STARTED” or “STOPPED” (or STARTING if it’s in the middle).
- The TCP/IP port used 57466 by default. If the caching:Port setting earlier is set to 0 this is the port used by default.
- The CacheUsed is how much space of the total CacheLimit has been used.
- The RegistrationStatus indicates whether the server is registered via APNS for the service with Apple.
- The CacheFree setting indicates how much space on the drive can be used for updates.
- The caching:TotalBytesRequested option should indicate how much data has been requested from clients while the caching:TotalBytesReturned indicates how much data has been returned to clients.
Look into the /Library/Server/Caching/Config/Config.plist file to see even more information, such as the following:
There are also a number of other keys that can be added to the Config.plist file including CacheLimit, DataPath, Interface, ListenRanges, LogLevel, MaxConcurrentClients, Port and ReservedVolumeSpace. These are described further at http://support.apple.com/kb/HT5590
As you can see, this provides the host name of the server and path on that server that the Caching server requires access to, the last port connected to and the last date that the contents were flushed.
In the Data directory that we mentioned earlier is a SQLite database, called AssetInfo.db. In this database, a number of files are mentioned. These are in a file hierarchy also in that Data directory. Client systems access data directly from that folder.
Finally, the Server app contains a log that is accessed using the Logs option in the Server app sidebar. If you have problems with the service, information can be accessed here (use the Caching Service Log to access Caching logs).
The Caching Service uses the AssetCache service, located at
then starts as the new user _assetcache user. It’s LaunchDaemon is at
Note: In my initial testing it appeared that after rebooting devices, that iOS updates were being cached; however, several have reported that this is not yet possible. I’ll try and replicate and report my findings later.
krypted December 17th, 2012
Posted In: iPhone, Mac OS X, Mac OS X Server, Mac Security, Mass Deployment, Network Infrastructure
Caching Server, caching service, Config.plist, Flush, ios, iPad, LastPort, logs, MAC, mountain lion server, OS X Server 2.2, server.app, serveradmin, Software Update Server, Software Updates, sqlite
LaunchPad is the OS X Lion version of the old Launcher, or the iOS home screen, according to how you look at these things. A few notes on issues I’ve seen with LaunchPad. First, I’ve had to nuke LaunchPad and have it rebuild. To do so, delete the database.
rm ~/Library/Application Support/Dock/*.db
You might also need to kill the dock:
In a deployment scenario, I’ve started doing both as post flight tasks. Getting to the point where you’re granularly adding and removing items is done by editing the .db file in ~/Library/Application Support/Dock. In here is a generatedID followed by .db that makes up a SQLite database. This database can be managed using a number of SQLite management tools, such as Base or SQLite Inspector
The management of databases from within these tools is pretty straight forward. You browse the apps, locate the offending rule and delete it. Then killall on the Dock (shown earlier) to actually have it disappear. You can also use the sqlite3 command line (where the string that begins with BF is the generated ID of the database):
sqlite3 ~/Library/Application Support/Dock/BF222CEA-E6B2-4804-BAA2-DED0428E6C90.db "select * from apps"
Assuming you see a row in the output that you’d just love to get rid of, you can look at the bundleID and then get rid of it using a command like this:
sqlite3 ~/Library/Application Support/Dock/BF222CEA-E6B2-4804-BAA2-DED0428E6C90.db "DELETE from apps WHERE bundleid LIKE 'org.videolan.vlc'"
Don’t forget to kill the Dock afterwards. That’s basically it. If you install an app and then want to toss items from Launchpad as a post flight use the bundleID, run sqlite3 and then a second query to verify that the item is gone. As you don’t know the generatedID for the database name, you can also replace it with * in scripts:
sqlite3 ~/Library/Application Support/Dock/*.db "DELETE from apps WHERE bundleid LIKE 'com.microsoft.uploadcenter'"
Oh, and the easy way to clean up LaunchPad is to use something like Launchpad Cleaner
krypted September 20th, 2011
Posted In: Mac OS X, Mac OS X Server, Mass Deployment
bundleid, delete from apps where, launchpad, Mac OS X, package, post flight, remove item, remove row, Scripting launchpad, sqlite, sqlite3
Each time you sync an iOS based device, a backup is made (unless you disable the option). These are stored in ~/Library/Application Support/MobileSync/Backup. Here you will find a number of folders, each beginning with the UDID of the iPhone, iPad or iPod Touch that has been backed up. The contents of these folders can be used to restore a device in the event that the device falls outside your control. Within the folders are a bunch of files with alphanumeric names that look garbled, even though some can be viewed using a standard text or property list editor (while others are binary). But there are also a bunch of other files in here. These can be parsed using a script, such as this one
(which parses the database files), or you can use a GUI tool to put Humpty Dumpty back together again, such as iPhone Backup Extractor
When you open iPhone Backup Extractor, click on the Read Backups button and you will be shown a list that should correspond (albeit using prettier names) to the entries in the Backup directory. Select the one that you would like to extract and then click on Choose. From here, click on iOS Files and then click on Extract. You will then be asked where to extract the file to. Choose a location.
Once extracted you can find out a lot of information about the apps you use and how they interact with your data. Most useful applications are going to cache your data (that’s what makes most of them useful) so don’t be surprised to see data such as conversations, contacts and even passwords in raw text or sqlite databases that you might not have thought so easily accessed (even without your phone). Keep in mind, the iTunes backup is considered secure to your iOS based device and if a user profile shouldn’t be considered secure then there is an Encrypt iPhone backup option available in iTunes that makes this whole process a moot point…
Anyway, back to finding that pincode… Next, browse into the extracted iOS Files and then into the Library/Preferences directory. Here you will find a file called com.apple.springboard.plist with a SBParentalControlsPin key. I extracted my files on my test device to my desktop, so I can see this with a quick defaults command:
defaults read /Users/seldon/Desktop/iOS Files/Library/Preferences/com.apple.springboard SBParentalControlsPIN
If a iPhone backup has been encrypted then it can be decrypted only if you know the correct password to decrypt. Once you have the passcode, you can safely manage the device again. There are also a lot of other things that you can enjoy playing around with if you’re interested to see what kind of data is stored where, either in the operating system or for each application (eg – com.apple.mobilephone.speeddial.plist is why I can never seem to remember my wifes phone number).
While much of the data for an iOS based device is stored in property lists, some is also stored in a sqlite database (typically in .sqlitedb files). You can interact with these via the sqlite3 command, built into Mac OS X or using a tool such as http://sourceforge.net/projects/sqlitebrowser
if you’re not into SELECT commands in sqlite3. Overall, there is a lot of information that can be learned playing around with this stuff. If you haven’t given it a shot yet, I’d recommend it. However, again, don’t be alarmed about any of the security impacts of this stuff, just encrypt the backups and it’s not an issue.
krypted April 10th, 2011
Posted In: iPhone, Mac Security
defaults, disable, forgot password for iPhone, iPad, iPhone, ipod, ipod touch, passcode, pin, reset, sbparentalcontrolspin, sqlite, sqlite3