#Sqlite for mac series#
#Sqlite for mac free#

In the last post, when we played with sysdiagnose, you may recall that one location the utility scraped logs from was /var/db. Aside from the fact that they are littered all over the user and system folders, they can also move around from one version of macOS to another and have also been known to change structure from time to time. There’s a few challenges when investigating user activity on the Mac, and the first is actually finding the databases of interest. We’ll look at an example of doing this below. schema command allows you to understand what columns each table contains and what kind of data they hold. This command is essential to understand the structure of the tables in the database. Sqlite3 /path to db/ 'select * from 'Īnother one of my go-to commands, this is equivalent of doing a “dump” on a specific table. Tables gives you a sense of the different kinds of data stored and which might be most interesting to look at in detail. You can grep that output, too, if you’re looking for specific kinds of things like filepaths, email or URL addresses, and piping the output to a plain text file can make it easy to save and review if you don’t want to work directly on the command line all the time. It’s a great way to quickly look at what kind of info the database might contain. This is my go-to command, which just pumps out everything in one (potentially huge) flood of data. Here are some very basic commands that serve me well. I tend to use the command line for quick, broad-brush looks at what a database contains and turn to the SQLite Browser if I really want to dig deep and run fine-grained queries.

#Sqlite for mac free#

You can use the free DB Browser for SQLite if you want a GUI front end, or you can use the command line. I am certainly no expert with SQL, but we can very quickly extract interesting data with a few simple commands and utilities. A Quick Review of SQLiteĪlthough some data we will come across is in Apple’s property plist format and less occassionally plain text files, most of the data we’re interested in is saved in sqlite databases. By looking at these databases, what they contain, and when they were accessed, we can get a sense of what data the company might have lost in an attack, from everything from personal communications, to contacts, web history, notes, notifications and more. While Apple have made some efforts recently to lock these down, many are still scrapable by processes running with the user’s privileges, but not necessarily their knowledge. Third, a lot of confidential and personal user data is stored away in hidden or obscure databases on macOS. Who has accounts on the device, when have they been accessed, and do those access times correlate with the pattern of behaviour we would expect to see from the authorized users? These are all questions that we would want to be able to answer. Second, ‘user behavior’ isn’t necessarily restricted to the authorized or designated user (or users), but also covers unauthorized users including remote and local attackers. What has the user been using the device for, what have they accessed and who have they communicated with? First, there’s the possibility of either unintentional or malicious insider threats. There’s a few reasons why we might be interested in user behavior. Today, in the second post we’re going to take a look at retrieving data on user activity and behavior.

#Sqlite for mac series#

In the previous post in this intro series on macOS Incident Response, we looked at collecting device, file and system data.