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
krypted February 23rd, 2017
Posted In: MacAdmins Podcast
My latest Inc.com piece is up. This one focuses on perfecting your sales pitch. It starts as follows:
So how do you incite interest rather than yawns? Here are six simple tips.
You can find the rest of the article here: http://www.inc.com/charles-edge/how-to-pitch-your-product-in-6-easy-steps.html.
krypted February 16th, 2017
Posted In: Articles and Books
So I comment a lot of lines out in my /etc/hosts file. This usually means that I end up with a lot of cruft at the top of my file. And while I write comments into files and scripts here and there, I don’t always want to see them. So I can grep them out by piping the output of the file to grep as follows:
cat /etc/hosts | grep -v "^#"
You could also do the same, eliminating all lines that start with a “v” instead:
cat !$ | grep -v "^v"
krypted February 13th, 2017
krypted February 10th, 2017
Posted In: MacAdmins Podcast
When you’re regression testing, you frequently just don’t want any delays for scripts unless you intentionally sleep your scripts. By default Safari has an internal delay that I’d totally forgotten about. So if your GUI scripts (yes, I know, yuck) are taking too long to run, check this out and see if it helps:
defaults write com.apple.Safari WebKitInitialTimedLayoutDelay 0
With a script I was recently working on, this made the thing take about an hour less. Might help for your stuffs, might not.
If not, to undo:
defaults delete com.apple.Safari WebKitInitialTimedLayoutDelay
krypted February 1st, 2017
Built a quick extension attribute for Jamf Pro environments to check if TouchID is enabled and report back a string in $result – this could easily be modified and so I commented a few pointers for environments that might need to modify it (e.g. to check for user-level as it’s currently system-level). To see/have the code, check https://github.com/krypted/TouchID_check.
krypted January 18th, 2017
The codesign command is used to sign apps and check the signature of apps. Apps need to be signed more and more and more these days. So, you might need to loop through your apps and verify that they’re signed. You might also choose to stop trusting given signing authorities if one is compromised. To check signing authorities, you can use
codesign -dv --verbose=4 /Applications/Firefox.app/ 2>&1 | sed -n '/Authority/p'
The options in the above command:
Then we pipe the output into a simple sed and get the signing chain. Or don’t. For example, if you’re scripting don’t forget a sanity check for whether an object isn’t signed. For example, if we just run the following for a non-signed app:
codesign -dv --verbose=4 /Applications/Utilities/XQuartz.app/
The output would be as follows:
/Applications/Utilities/XQuartz.app/: code object is not signed at all
krypted January 12th, 2017
Prepare for your network administrators to cringe… I’ve spoken on these commands but never really put them together in this way, exactly. So I wanted to find a coworker on a network. So one way to find people is to use a ping sweep. Here I’m going to royally piss off my switch admins and ping sweep the subnet:
Next, I’m going to run arp to translate:
Finally, if a machine is ipv6, it wouldn’t show up. So I’m going to run:
Now, I find the hostname, then look at the MAC address, copy that to my clipboard, find for that to get the IP and then I can flood that host with all the things. Or you could use nmap… :-/
krypted January 7th, 2017
macOS has keychains. Sometimes they’re a thing. When they are you might want to delete them. Let’s say you have an admin account. You want to keep the keychains for that account, but remove all the others. For this, you could do a shell operator to extglob. Or you could do a quick while loop as follows:
ls /Users | grep -v "admin" | while read USERNAME do; rm -Rf "/Users/$USERNAME/Library/Keychains/*" done;
If you borrow this, be careful.
krypted December 1st, 2016