Addressing the iOS6 Address Book and SQLite Pitfalls

Now that I have a basic handle on the iOS6 sms.db, it's time to look at the iOS6 Address Book.  After all, I now know what's being said when I examine the sms.db, but I don't have a real good picture, other than the phone number, of who's sending the message.  The iOS AddressBook.sqlitedb is the place to look for data related to the phone number.

A brief look at AddressBook.sqlitedb

The iOS6 AddressBook.sqlitedb database has 29 tables:


ABAccount                        ABPersonFullTextSearch_segdir  
ABGroup                          ABPersonFullTextSearch_segments
ABGroupChanges                   ABPersonFullTextSearch_stat    
ABGroupMembers                   ABPersonLink                   
ABMultiValue                     ABPersonMultiValueDeletes      
ABMultiValueEntry                ABPersonSearchKey              
ABMultiValueEntryKey             ABPhoneLastFour                
ABMultiValueLabel                ABRecent                       
ABPerson                         ABStore                        
ABPersonBasicChanges             FirstSortSectionCount          
ABPersonChanges                  FirstSortSectionCountTotal     
ABPersonFullTextSearch           LastSortSectionCount           
ABPersonFullTextSearch_content   LastSortSectionCountTotal      
ABPersonFullTextSearch_docsize   _SqliteDatabaseProperties  

The abperson table seems to be the obvious target for the data we want.  Here's its schema:


CREATE TABLE ABPerson (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, First TEXT, Last TEXT, Middle TEXT, FirstPhonetic TEXT, MiddlePhonetic TEXT, LastPhonetic TEXT, Organization TEXT, Department TEXT, Note TEXT, Kind INTEGER, Birthday TEXT, JobTitle TEXT, Nickname TEXT, Prefix TEXT, Suffix TEXT, FirstSort TEXT, LastSort TEXT, CreationDate INTEGER, ModificationDate INTEGER, CompositeNameFallback TEXT, ExternalIdentifier TEXT, ExternalModificationTag TEXT, ExternalUUID TEXT, StoreID INTEGER, DisplayName TEXT, ExternalRepresentation BLOB, FirstSortSection TEXT, LastSortSection TEXT, FirstSortLanguageIndex INTEGER DEFAULT 2147483647, LastSortLanguageIndex INTEGER DEFAULT 2147483647, PersonLink INTEGER DEFAULT -1, ImageURI TEXT, IsPreferredName INTEGER DEFAULT 1)

If you bothered to read through that, you'll notice there is nothing about phone numbers or email addresses.  We need to find the table containing the phone numbers, and somehow join that to this table that has the name data.

Take a look at a sample (fictitious) row:

                  ROWID = 1

                  First = Some

                   Last = Name

                 Middle = 

          FirstPhonetic = 

         MiddlePhonetic = 

           LastPhonetic = 

           Organization = SomeCompany

             Department = 

                   Note = 

                   Kind = 0

               Birthday = 

               JobTitle = 

               Nickname = 

                 Prefix = 

                 Suffix = 

              FirstSort = -'- 1'?7=W +'= 17I/ )'MM'=7CA +57/1

               LastSort = 1'?7=W -'- +'= 17I/ )'MM'=7CA +57/1

           CreationDate = 350880583

       ModificationDate = 369882045

  CompositeNameFallback = 

     ExternalIdentifier = 

ExternalModificationTag = 

           ExternalUUID = 68516E99-7C39-4C4D-8871-BDE114EDD6B4

                StoreID = 0

            DisplayName = 

 ExternalRepresentation = 

       FirstSortSection = -

        LastSortSection = 1

 FirstSortLanguageIndex = 0

  LastSortLanguageIndex = 0

             PersonLink = -1

               ImageURI = 

        IsPreferredName = 1

A little more digging, we find the ABMultivalue table contains phone numbers, email addresses, URLs to social networking sites, and potentially more.  There are only six fields (making it a little easier on the eyes), but there's a minor issue.  Reading the table flat (that is, not related to any other tables) we don't know whom the numbers and email addresses belong to, nor to we know what type of data it is.  For example, is it a home, work, mobile, or FAX phone number? Take a look at its makeup and some sample rows and you'll see what I mean:

CREATE TABLE ABMultiValue (UID INTEGER PRIMARY KEY, record_id INTEGER, property INTEGER, identifier INTEGER, label INTEGER, value TEXT)

       UID = 643

 record_id = 1

  property = 4

identifier = 0

     label = 3

     value = someguy@somedomain.com

       UID = 1026

 record_id = 1

  property = 3

identifier = 0

     label = 1

     value = (###) ###-####

That's a little easier on the eyes.  You can see that the 'value' field at the end of the row contains the phone numbers and email addresses we're looking for.  The problem comes from the fact its the only text field in the table.  All other values are integers.  So, how do we relate the names in the ABPerson table to the numbers in the ABMultivalue?  I made it pretty obvious here, but ABperson.ROWID = ABMultivalue.record_id.

So, what about that other problem: what kind of data is that?  Here comes the ABMultiValueLabel table to the rescue!  Take a look at this:

value = iPhone

value = _$![MOBILE]!$_

value = _$![HOME]!$_

value = mobile1

value = mobile2

value = DAD

value = MOM

If that looks a bit strange, its because the iOS user can create their own labels.  Some, like mobile1 and mobile2, are going to be default.  Others like Mom and DAD are custom.  But something else should catch your eye, too: there are no values in the ABMultiValueLabel table with which to match the label integer in the  ABMultivalue table!  Now what?

I introduced the SQLite rowid field in an earlier article.  In sum, every SQLite table has a rowid field, which is a unique integer, whether or not it was specified when the table was created.  In the case of the ABMultiValueLabel table, the rowid is the relation to the label integer in the ABMultivalue table to inform us of the data type.  Observe:

$ sqlite3 -line AddressBook.sqlitedb 'select rowid, value from abmultivaluelabel where rowid = 1 or rowid = 3'

rowid = 1
value = iPhone

rowid = 3
value = _$![Home]!$_

Now that we know how these tables interrelate, we can compose a query to generate a simple contacts list.

A Simple Contact List

To quickly recap, the ABPerson, ABMultivalue, and ABMultivalueLabel tables are interrelated: ABPerson contains the contact names, ABMultivalue houses the phone numbers, email addresses, and potentially other values (e.g. URLs), and ABMultivalueLabel defines the values.  We can create a list of contacts, sorted by contact identification number, thusly:

$ sqlite3 -header AddressBook.sqlitedb 'select p.rowid, p.first, p.last, p.organization, l.value as Type, m.value from abperson p, abmultivalue m, abmultivaluelabel l where p.rowid=m.record_id and l.rowid=m.label order by p.rowid;'

ROWID|First|Last|Organization|Type|value

1|Some|Guy|SomeCompany|_$![Home]!$_|someguy@somedomain.com
1|Some|Guy|SomeCompany|iPhone|(###) ###-####

(Note: Keep reading! Though it might not be apparent, this query above is flawed.)

Checking our work

While the query above works nicely, it does not return all the data available in the tables.  Let's take a look some database stats and you'll see what I mean:

$ sqlite3 AddressBook.sqlitedb 'select count(*) from abperson;' 

542

$ sqlite3 AddressBook.sqlitedb 'select count(*) from abmultivalue;'

1588

$ sqlite3 AddressBook.sqlitedb 'select count(*) from abmultivaluelabel;'

14

The ABPerson table has 542 entries.  We'd expect at least 542 lines in our query, assuming one phone number, email or other value in the ABMultivalue table.  Considering there are 1588 ABMultivalue rows, we actually expect an average of three rows per contact.  But, how many rows are returned with our "Simple Contact List" query?

$ sqlite3 AddressBook.sqlitedb 'select count(*) from abperson p, abmultivalue m, abmultivaluelabel l where p.rowid=m.record_id and l.rowid=m.label;'

305

"Ruh-row, Raggy!"  What happened?  Maybe there are contacts in ABPerson not found in ABMultivalue?  

$ sqlite3 AddressBook.sqlitedb 'select count(*) from abperson where rowid not in (select record_id from abmultivalue);' 

0

No, all 542 rowids from ABPerson are found in the record_ids of ABMultivalue.  We certainly want all the ABPerson records represented in our output, and clearly we're not getting them with the query as written.  But how many of the ABMultivalue records relate to the ABPerson records?

$ sqlite3 AddressBook.sqlitedb 'select count(*) from abmultivalue where record_id in (select rowid from abperson);' 

1588

 All of them.  So, now we know the score: our query won't be correct until we have 1588 rows of data.  So, where's the breakdown?  To find out, we need to look carefully at our original query:

select              # here we a just selecting the columns we desire

    p.rowid, 

    p.first, 

    p.last, 

    p.organization, 

    l.value as Type, 

    m.value 

from                # here we list the source tables, and assign an alias

    abperson p, 

    abmultivalue m, 

    abmultivaluelabel l 

where               # here we filter the data, and this is our danger area

    p.rowid=m.record_id 

    and l.rowid=m.label 

order by            # here we are simply ordering the results

    p.rowid;'

So, it would appear we need to concentrate on the where clause which has the effect of filtering our data.  We are doing two things here:

1. p.rowid=m.record_id - ABPerson's rowid must match a ABMultivalue record_id, which we have already shown occurs in all cases
2. l.rowid=m.label - There must be a matching rowid in ABMultivalueLabel for each ABMultivalue label.

It appears that the second statement in where clause is our culprit.  Can we demonstrate this to be the case and verify our assessment of the original query?

$ sqlite3 AddressBook.sqlitedb 'select count(*) from abperson, abmultivalue where rowid=record_id;' 

1588

Replacing our count function with the original field names (replacing the label name with the integer because we don't call to the ABMultivalueLabel table in this instance) doesn't change the count because the select statement is the location where we define the fields to be displayed, not filter the results.  I'll demonstrate:

$ sqlite3 AddressBook.sqlitedb 'select p.rowid, p.first, p.last, p.organization, m.label as Type, m.value from abperson p, abmultivalue m where p.rowid = m.record_id;' | wc -l

1588

So, it was the final filter that resulted in the reduced data set.  That means there must be undefined labels in the ABMultivalue table (and in fact, 1283 of the records have NULL label values)!  So, can we just delete that filter from our original query and call it good?

$ sqlite3 AddressBook.sqlitedb 'select p.rowid, p.first, p.last, p.organization, l.value as Type, m.value from abperson p, abmultivalue m, abmultivaluelabel l where p.rowid=m.record_id order by p.rowid;' | wc -l

22233

What?!  Over 22,000 rows are returned?  How does that happen?  It's simple, but its also easily misunderstood.  Without our last filter, our select statement says to show the ABMultivaluelabel value field.  There are 14 rows in that table, so we get 14 rows for each of the 1588 records in the ABMultivalue table.  I'm not a math genius, but 542+1588+14= ... two plus eight plus four is fourteen, carry the one... no where near 22K!

So, we need a way to display label name if it exists in the ABMultivaluelabel table, otherwise display the ABMultivalue label integer.  Even if we don't know what it means, we don't want to ignore an ABMutlivalue entry because we may come to understand its meaning through another phase of our investigation.  We can accomplish this with a pair of nested select statements in the primary select statement.

select 

    p.rowid, 

    p.first, p.last, 

    p.organization, 

    case 

        when m.label in (select rowid from abmultivaluelabel) 

        then (select value from abmultivaluelabel where m.label = rowid) 

        else m.label 

    end as Type, 

    m.value  

from 

    abperson p, 

    abmultivalue m, 

where 

    p.rowid=m.record_id 

order by 

    p.rowid;

What we have going on in the highlighted portion is a case statement.  Case is the if/then/else of SQLite.  What we are saying above is, if the ABMultivalue label is in the ABMultivalueLabel table, then print the english translation from the ABMultivalueLabel table.  Otherwise, just print the label integer.  The 'as Type' suffix just labels the column 'Type' in the output.

Let's see what this does to our results:

$ sqlite3 AddressBook.sqlitedb 'select p.rowid, p.first, p.last, p.organization, case when m.label in (select rowid from abmultivaluelabel) then (select value from abmultivaluelabel where m.label = rowid) else m.label end as Type, m.value  from abperson p, abmultivalue m where p.rowid=m.record_id order by p.rowid;' | wc -l

1588

Bingo!  We now have our expected results.  One more tiny addition to our query cleans up our output.  It turns out, that a lot of the entries in the ABMultivalue table have NULL values and are useless for our purpose.  We can remove them with a filter in the where clause and get a final product:

$ sqlite3 AddressBook.sqlitedb 'select p.rowid, p.first, p.last, p.organization, case when m.label in (select rowid from abmultivaluelabel) then (select value from abmultivaluelabel where m.label = rowid) else m.label end as Type, m.value  from abperson p, abmultivalue m where p.rowid=m.record_id and m.value not null order by p.rowid;' | wc -l

725

So, we improved our results greatly over the initial 305 records that "seemed right".  

The Larger Lesson Learned

You likely tuned into this article because of the iOS6 AdressBook.sqlitedb topic.  And I hope I've helped you extract the results you sought.  But the bigger takeaway comes from the analysis after the initial query that resulted in 305 records.  And, it is this: SQLite will do what you tell it, so long as you use proper syntax, no matter how stupid your query is.  In otherwords, you ask the question, it answers that question, not the one you were thinking, the one you typed.  Make sure you really understand the question before you rely on the answer!

---

Addendum: What About Street Addresses?

It's great that we've figured out the phone numbers email addresses, etc. of our contacts, but what if we want to find their physical addresses?  The ABMultivalue table I examined did not have street addresses in its values.  Instead, I found Street addresses in the ABMultivalueEntry and ABPersonFullTextSearch_content tables.  I would not preclude addresses from existing ABMultivalue, however, since we have seen there can be custom labels and the value field can contain any text data.  But, I digress.

The ABPersonFullTextSearch_content has the following structure:

CREATE TABLE 'ABPersonFullTextSearch_content'(docid INTEGER PRIMARY KEY, 'c0First', 'c1Last', 'c2Middle', 'c3FirstPhonetic', 'c4MiddlePhonetic', 'c5LastPhonetic', 'c6Organization', 'c7Department', 'c8Note', 'c9Birthday', 'c10JobTitle', 'c11Nickname', 'c12Prefix', 'c13Suffix', 'c14DisplayName', 'c15Phone', 'c16Email', 'c17Address', 'c18SocialProfile', 'c19URL', 'c20RelatedNames', 'c21IM', 'c22Date')

At first glance, the structure of this table might cause you to think you can simply dump the data from this table for a very complete list of contacts.  But this table was intended for searching if you take its name at face value.  Looking at the data, this assertion is supported by the fact that phone numbers are merged into one field and then recorded in a variety of formats (i.e., international, with/without area code, with/without punctuation, etc).  Thus, it is difficult to read and there is no distinction in the type of data (i.e, home/work phone number, etc).

EDIT:
I have discovered how the ABMultivalueEntry relates to the ABMultivalue table and solved the mystery of the blank ABMultivalue "values" fields.

ABMultivalueEntry contains physical addresses which are distinguished a "key" integer.  The key is is given its meaning, such as "Country," "Street," "Zip," "City," etc., by the ABMultivalueEntryKey table.  The key in ABMultivalueEntry relates to the rowid in ABMultivalueEntryKey.  The address data itself is stored in the "value" field of the ABMultivalueEntry table, which in turn relates by the "parent_id" field to the ABmultivalue table by a matching UID field.  Confused?

Let me try it this way:  Contact names are in the ABperson table.  Contact phone numbers, emails addresses, and URLs are located in the ABmultivalue table.  Physical addresses are located in the ABMultivalueEntry table.  The definitions of the values in the '*value*' tables are located in the "*label" and "*key" tables discussed.

What is the query to produce a complete contact list?  Still working on that one...

Getting to know the Relatives: SQLite Databases

I was contacted by a colleague who needed some help analyzing a SQLite database.  It was the myspace.messaging.database#database located in the "\Users\\appdata\local\google\chrome\userdata\default\plugin data\google gears\messaging.myspace.com\http_80\" folder.  I didn't and still don't know a whole lot about this file, but it appears to contain myspace email messages.

The Challenges of SQLite

Let's face it: SQLite is everywhere.  Understanding it is essential to good examinations, and a big part of that understanding come from learning SQL statements. There are many good online sources for learning SQL, and one of my favorites is w3schools.com.

But, for digital forensics practitioners, there is another challenge beyond understanding SQL commands--understanding the construction and relationships of the tables.  SQLite is a relational database, and the tables are meant to be related to one another to produce a result no possible or impractical from a single table.  Knowing how the table was intended to be used can be very difficult... after all, a SQLite database is more akin to a file cabinet, not a secretary who uses the file cabinet.

For example, the secretary can place company bank records in a file called "Financial Records" or she can put them in a file called "Artichokes".  It really doesn't matter, because she knows what goes in the file.  Someone coming along behind her won't have much trouble finding the bank records in the Financial Records file, but might overlook them them entirely in the Artichokes file.  The point is, without the secretary, it might be very hard to understand the filing system.

SQLite databases can be a lot like that.  You can see the structure, or the schema as it is called, very easily.  But what is not so easily understood is how the structure is intended to be used.  That mystery is usually locked up in the application that utilizes the database, but it is not explained in the database itself.

Getting a Clue

To be sure, there can be hints about how tables in a database interrelate.  Table and field names often speak volumes.  A database called "AddressBook.db" with two tables called "Names" and "Addresses" that have a field in common called "SubjectID" isn't too hard to fathom.  If we are lucky enough to be able to run the application that uses the database and test our inferences based on the applications output, our confidence grows (and our understanding, if supported by the outcome, would now be considered reliable).

My favorite hints by far are SQL 'view' statements.  These are virtual tables that draw their data from other tables in the database (or an attached database).  By studying a view statement, you get insight from the database creator how the database was intended to be used... at least in one capacity.  Think of a view as a macro: it saves the database user the trouble of repeated typing a frequently used query.  And, if the query is frequently used, then you have a good sense of how the database was intended to be used.

What if There are No Clues?
What about circumstances in which there are no clues in the database to help us understand its use.  Well, if there are really no clues, then the only safe answer is we look at the data flat, that is to say, we look at the tables individually and we don't relate them in any way.  But, there are often less obvious clues than can reveal an underlying relationship... which brings me to the point of this article.

Latent Rows

Latent fingerprint examiners know the term "latent" means hidden or invisible.  Latent fingerprints must be revealed to be seen by some external method, such as fingerprint powder.  SQLite tables have a latent field, so to speak.  And, we can reveal it to help us form relations in a SQLite database.

Consider the myspace.messaging.database#database I mentioned in the open paragraph. It has the following schema:

CREATE VIRTUAL TABLE AuthorData USING fts2(AuthorDisplayName, AuthorUserName); 
CREATE TABLE AuthorData_content(c0AuthorDisplayName, c1AuthorUserName); 
CREATE TABLE AuthorData_segdir(  level integer,  idx integer,  start_block integer,  leaves_end_block integer,  end_block integer,  root blob,  primary key(level, idx)); 
CREATE TABLE AuthorData_segments(block blob); 
CREATE TABLE AuthorMetaData (AuthorId INTEGER PRIMARY KEY, AuthorImageUrl TEXT); 
CREATE VIRTUAL TABLE MessageData USING fts2(Subject, Body); 
CREATE TABLE MessageData_content(c0Subject, c1Body); 
CREATE TABLE MessageData_segdir(  level integer,  idx integer,  start_block integer,  leaves_end_block integer,  end_block integer,  root blob,  primary key(level, idx)); 
CREATE TABLE MessageData_segments(block blob); 
CREATE TABLE MessageMetaData (MessageId INTEGER PRIMARY KEY, RecipientId INTEGER, AuthorId INTEGER, Folder INTEGER, Status INTEGER, CreatedDate INTEGER); 
CREATE TABLE UserSettings (UserId INTEGER PRIMARY KEY, MachineId TEXT, Enabled INTEGER, TimeStamp INTEGER, LastSyncTimeStamp INTEGER, FirstRunIndexPass INTEGER, FirstRunIndexTargetCount INTEGER, OldestMessageId INTEGER, LastServerTotalCount INTEGER); 
CREATE INDEX AuthorIdIndex ON MessageMetaData (AuthorId, RecipientId); 
CREATE INDEX StatusIndex ON MessageMetaData (Status, CreatedDate);

Now look more closely at two tables of interest,MessageMetaData and MessageData_content:

CREATE TABLE MessageMetaData (MessageId INTEGER PRIMARY KEY, RecipientId INTEGER, AuthorId INTEGER, Folder INTEGER, Status INTEGER, CreatedDate INTEGER);

CREATE TABLE MessageData_content(c0Subject, c1Body)

It would seem from the table names that MessageMetaData contains information about the messages, and MessageData_content contains the messages themselves.  But, they don't share any fields that allow the two tables to be related. In other words, which rows of the metadata table correspond to which row of the content table?  Do they even correspond at all?

Let's look at our first hint or correspondence:

$ sqlite3 myspace.messaging.database#database.db 'select count(*) from MessageMetaData;'

1358 
$ sqlite3 myspace.messaging.database#database.db 'select count(*) from MessageData_content;'
1358

Both tables have the same number of records.  Hmm, a clue?  Quite likely, especially upon study of the table content and the remaining tables contents.  In fact conducting a similar study, we find another set of table correspondence: AuthorMetaData and AuthorData_content also have an equal number of records (172, to be exact) but no obvious, interrelated fields.

Unless you've studied SQLite construction in any depth, you probably don't know that it creates a 'rowid' field for every table to act as a primary key.  If a table is created with a defined primary key, that primary key is just a alias to the builtin rowid (with one exception outside the scope of this discussion).  But the rowid is not represented in the table or database schema, which is probably why you didn't know about it (at least, I didn't until I bought a SQLite book).

Knowing about the rowid, i can now check to see if the two tables have matching rowid fields:

$ sqlite3 myspace.messaging.database#database.db 'select count(*) from MessageMetaData m, MessageData_content c where m.rowid = c.rowid'
1358 

We don't have to trust the count function, take a look for yourself:

$ sqlite3 myspace.messaging.database#database.db 'select m.rowid, c.rowid from MessageMetaData m, MessageData_content c where m.rowid = c.rowid'
...
81407357|81407357
81416917|81416917
81504605|81504605
81505714|81505714
81530947|81530947
81569294|81569294

Well, now this is even more interesting.  We not only have two tables with the same number of rows, but we have two tables with fields in relation, i.e., rowid!  

Understand that rowid is simply an autoincrementing, unique, 64-bit integer unless specifically declared otherwise by insert and update commands.  But is this just a coincidence?  Let's consider: we have non-sequential rowids throughout both tables.  That might be explained by dropped rows from the tables.  But two tables, each with 1358 rows, and each row having a matching rowid in the other table?  That is more than coincidence--it's programatic.  The application populating the tables is assigning the rowids.

The Proof is in the Pudding

My assertion is that the myspace.messaging.database#database.db is assigning the rowids as it populates the related tables and links the rows by matching rowid.  Let me demonstrate how rowid can be assigned:

sqlite> create table numbers(digit integer);
sqlite> insert into numbers (digit) values(1);
sqlite> insert into numbers (digit) values(2);
sqlite> insert into numbers (digit) values(3);
sqlite> select rowid, digit from numbers;
1|1
2|2
3|3
4|3
sqlite> insert into numbers (rowid, digit) values (1000, 4);
sqlite> select rowid, digit from numbers;
1|1
2|2
3|3
4|3
1000|4

I created at table called "numbers" with one field called "digit."  I then inserted three rows in the table with the values 1, 2, and 3 respectively.    If you've been following along, you now know that every SQLite table also has a rowid field, even if not expressly created in the table by the user.  The first select statemnt shows the autogenerated rowid and along with the digits I inserted.

The final insert statement is different.  Here I assign the rowid, rather than let it be automatically populated by the SQLite engine.  And, as you an see in the final select statement, I succeed in setting an non-sequential rowid.

Putting it All Together

I've demonstrated a "hidden" way that tables in SQLite databases can be related.  It takes some knowledge in SQLite structure and the SQL query language to unveil this data, however.  If you are in the habit of relying on SQLite browsers and looking at tables without relating them, then you are really missing out on a wealth of data.

Again, let me illustrate using the myspace.messaging.database#database.  Lets look at one row in each of the tables I mentioned previously:

$ sqlite3 -header myspace.messaging.database#database.db 'select * from MessageMetaData limit 1;'
MessageId|RecipientId|AuthorId|Folder|Status|CreatedDate
1289081|544962655|41265701|0|2|1280870820000 

$ sqlite3 -header myspace.messaging.database#database.db 'select * from MessageData_content limit 1;'
c0Subject|c1Body
Hi|Hey, what's up? 

$ sqlite3 -header myspace.messaging.database#database.db 'select * from AuthorMetaData limit 1;'
AuthorId|AuthorImageUrl
-1930729470|http://some_url/img/some_image.png 

$ sqlite3 -header myspace.messaging.database#database.db 'select * from AuthorData_content limit 1;'
c0AuthorDisplayName|c1AuthorUserName
A User|auser

The only hint of relationship, besides table names, is the AuthorID field in MessageMetaData and AuthorMetaData.  But there is still no obvious way to tie the metadata to the content we are most interested in.  Your favorite GUI browser maybe make the display prettier, but its just as impotent.

But, now that you have knowledge of the rowid, and have a link to a tutorial on SQLite statements, you're not too far from being able to do this:

sqlite3 -header myspace.messaging.database#database.db 'select messageid, datetime(createddate/1000, "unixepoch", "localtime") as Date, mm.AuthorID, c0AuthorDisplayName as "Author Display Name", c1AuthorUserName as "Author Username", c0subject as Subject, c1Body as Body from  MessageMetaData mm, MessageData_content mc, AuthorData_Content ac, AuthorMetaData am where mm.AuthorID = am.AuthorID and am.rowid = ac.rowid and mm.rowid = mc.rowid limit 2;'
MessageId|Date|AuthorId|Author Display Name|Author Username|Subject|Body1289081|2010-08-03 14:27:00|41265701|A User|auser|Hi|Hey, what's up?

I ask you, on which output would you rather examine and report?

Addendum

That last query is really not so scary.  It's just long because we're grabbing seven fields from four tables, and converting a date stamp.  But, in reality, it's very straight forward.

Let's take a look:

select
     messageid,
     datetime(createddate/1000, "unixepoch", "localtime") as Date,
     mm.AuthorID,
     c0AuthorDisplayName as "Author Display Name",
     c1AuthorUserName as "Author Username",
     c0subject as Subject,
     c1Body as Body 
from 
     MessageMetaData mm,
     MessageData_content mc,
     AuthorMetaData am,
    AuthorData_Content ac 
where
     mm.AuthorID = am.AuthorID
     and am.rowid = ac.rowid
     and mm.rowid = mc.rowid;

The select clause simply picks the fields we want to display.  The datetime function converts the unixepoch time, which is recorded in milliseconds, to local time.  The 'as' statements are naming the columns something more user friendly and are not required.

The from statement simply declares what tables to query for the fields we are trying to display.  Each table is followed by an alias I chose to make easier reference to field names common to more than one table.  For example, AuthorID is found in both the MessageMetaData and AuthorMetaData tables.  By giving MessageMetaData the alias of mm, I can now reference the MessageMetaData.AuthorID field as mm.AuthorID.

The where statement is a filter.  It keeps the tables 'aligned,' so to speak.  It ensures that only the correct author content and message content is returned for each row.  This post is a lot long in the tooth, so I won't go into detail describing how it works.  But, very succinctly, the MessageMetaData record is matched to a AuthorMetaData record by AuthorID.  The the AuthorMetaData record is matched to its corresponding AuthorData_Content record by rowid.  Finally, the MessageMetaData record is matched to its corresponding MessageData_content, also by rowid.

Who's Texting? The iOS6 sms.db

It didn't take long from the release of iOS6 on 09/14/12 until I got my first iOS6 device to process: 18 days.  In this case, it was an iPhone4 with iOS6.  In so short a time, there are not too many commercial or open source tools out there prepared to analyze the new OS or its new data formats.

Fortunately for me, the phone was not locked.  To process the phone, in which the prime interest was text messages and contacts, I used the latest iTunes to create a backup of the phone.  Fortune was again on my side: the phone was not set for encrypted backups.

iTunes Backup

If you are not familiar with iTunes backups, then allow me to introduce them generally.  iTunes copies user data (media, databases, settings, applications, etc.) to a directory in the operating system.  The location of the directory varies by operating system and version.  Since this is primarily a discussion about the new iOS6 sms.db, I'll let you find the location yourself.  What's most important to know is this:

1. the backups are flat:  all the files are dumped into one directory and are not preserved in their original tree structure.
2. the files are renamed: the file names are sha1 values calculated from the domain, path, and filename of the original file.

The nature of hash digest is that they produce unique, fixed length values based on the data present in the data structure being analyzed.  This means that a 1 byte file and a 200gb data stream both produce a 40-byte sha1 hash value.  This also means that the data on which the hash value was calculated cannot be reverse engineered from the value alone.  Why did I just state all this?  Certainly not to ignite another hash controversy by anything I might just misstated, but instead to point out that we can't really know the original file name, path, and domain from the sha1 value file names found in the backup.  Sure, you'll find postings that tell you the sms.db is renamed "3d0d7e5fb2ce288813306e4d4636395e047a3d28", but you won't find a file name for every file in your backup.  And you won't know any metadata about any of the backup files from the hash value, either.

Now, Linux helps us here.  On the command line, the file command will tell us the file type of each of the backup files, and most Linux file managers will render thumbnails for known file types.  This means its quite easy to view media files and identify databases just by opening a file browser pointed at the backup directory.  Its good for low hanging fruit, but how do you differentiate between the databases, for example?  Keyword search on a table name, maybe?  Sure, that might narrow the field, but it's a less than perfect solution.

Identifying Files in the Backup

Keeping the discussion general, know one more thing about iTunes backups before we proceed with the sms.db discussion: there are database files included in the backup that identify the files by domain, path, and file name.  In fact, the file metadata such a MAC times, ownership, permissions, and file size are included in the databases.

Now, the databases have changed with each iOS evolution--at least since iOS3 when I started examining iTunes backups.  Well, more accurately, I should they changed with each evolution except iOS6.  iOS6 retains the format introduced in iOS5.  That was again fortunate, because it just so happens that I coded a tool, based on a python script posted on stackoverflow posted by user galloglass, to 'unback' the iTunes backup based on the information in the backup databases.  The iOS5-unback.py tool works just fine on the iOS6 backup.

The sms.db

Where the backup format did not change in iOS6, the sms.db changed markedly.  Compare, if you will, the database schema side by side with an iOS5 sms.db.

	iOS6	iOS5
Number of tables:	10	8
Table Names:	SqliteDatabaseProperties, message, sqlite_sequence, chat, attachment, handle, message_attachment_join, chat_handle_join, chat_message_join, sqlite_stat1	_SqliteDatabaseProperties, message, sqlite_sequence, msg_group, group_member, msg_pieces, madrid_attachment, madrid_chat
Number of triggers:	9	7
Trigger Names:	set_message_has_attachments, update_message_roomname_cache_insert, delete_attachment_files, clean_orphaned_attachments, clean_orphaned_handles, clear_message_has_attachments, clean_orphaned_messages, update_message_roomname_cache_delete, clean_orphaned_handles2	insert_unread_message, mark_message_unread, mark_message_read, delete_message, insert_newest_message, delete_newest_message, delete_pieces
Number of indexes:	16	18
Index Names:	sqlite_autoindex__SqliteDatabaseProperties_1, sqlite_autoindex_message_1, sqlite_autoindex_chat_1, sqlite_autoindex_attachment_1, sqlite_autoindex_handle_1, sqlite_autoindex_handle_2, sqlite_autoindex_message_attachment_join_1, sqlite_autoindex_chat_handle_join_1, sqlite_autoindex_chat_message_join_1, message_idx_is_read, message_idx_failed, message_idx_handle, chat_idx_identifier, chat_idx_room_name, message_idx_was_downgraded, chat_message_join_idx_message_id	sqlite_autoindex__SqliteDatabaseProperties_1, madrid_attachment_message_index, madrid_attachment_guid_index, madrid_attachment_filename_index, madrid_chat_style_index, madrid_chat_state_index, madrid_chat_account_id_index, madrid_chat_chat_identifier_index, madrid_chat_service_name_index, madrid_chat_guid_index, madrid_chat_room_name_index, madrid_chat_account_login_index, message_group_index, message_flags_index, pieces_message_index, madrid_guid_index, madrid_roomname_service_index, madrid_handle_service_index

For anyone familiar with the iOS5 sms.db database structure, the most apparent change in the iOS6 sms.db is the lack of "madrid" tables.  In iOS5, the iMessage app was introduced, and iMessage texting and SMS texting were unified in the sms.db.  This is still true in iOS6, but it is handled quite differently, and by "differently," I mean "better."

Time Stamps

When parsing the iOS5 database, it was necessary to convert two different date formats: Unix epoch time and Mac Absolute Time.  The dates of SMS messages sent through the wireless carrier were recorded in unix epoch time in the date field of the message table.  iMessages, on the other hand, were recorded in Mac Absolute Time in the same table and field as the SMS message date!  

What's the difference between unix epoch and Mac Absolute Time?  Exactly 978307200 seconds.  Unix epoch starts on 01/01/1970, while Mac Absolute time starts on 01/01/2001.  SQLite queries that integrated the SMS and iMessage texts had to account for these differences.  Some examiners with automated tools for parsing the sms.db were likely blissfully ignorant of this issue, but it exited none-the-less.  But it presented challenges to those of use extracting data from the database with the SQLite command line program or a SQLite GUI browser.

The iOS6 sms.db simplifies the date issue.  All text message dates, whether SMS or iMessage are recorded in Mac Absolute time.  The datetime function in SQLite can be used to convert that time by adding 978307200 seconds to the date data.  This converts the time to unix epoch which is one of the 'modifiers' the datetime function was coded to handle (Mac Absolute Time is not a valid datetime modifier):

sqlite> select datetime(370884516 + 978307200, 'unixepoch');
datetime(370884516 + 978307200, 'unixepoch')
2012-10-02 22:28:36

While on the topic of time stamps, two additional time stamps are possible in each record: date_read and date_delivered.  The date_read field defaults to 0 until the message is opened in the iMessage application.  The date_sent is populated with a date when the message is sent through the iMessage service but not SMS.  This meas it is possible to differentiate between the time an iMessage was initiated by the device user and when it was actually sent.

Addresses 

Another significant change in the database is the address field.  In the iOS5 version, the "address" was the phone number of the remote contact in the text conversation.  The address field has been replaced with the handle_id in iOS6.  The handle_id corresponds to the ROWID in the handle table, which contains the phone number of the the contact in the id field.

Flags

I'll quickly mention a couple of other changes.  In the previous versions of the sms.db, fields such as flags and read were used to mark the type (sent, received, etc) and status (read, unread, etc) of the message.  New fields exist for these attributes including is_from_me, is_empty, is_delayed, is_auto_reply, is_prepared, is_read, is_system_message, is_sent.  The values 0 and 1 are used as "no" and "yes" respectively in interpreting these fields.

I mentioned earlier that the service used to transmit the text, SMS or iMessage, resulted in different time stamp type.  It also resulted in different fields being populated in the message table.  In iOS6, the messages share the same flags regardless of the service used to send them.  The service utilized is recorded in the the new service field of the message table.

Putting it All Together

I did not come anywhere close to describing all the data in the new sms.db table, nor how to related all the tables.  I only intended to alert investigators that there are differences in the new database that must be considered.

That said, I'd be remiss if I did not provide a sample query to produce a basic chat list.  The following query produces a list with several important fields (RowID, Date, Phone Number,Service|Type|Date Read/Sent|Text):

SELECT m.rowid as RowID, DATETIME(date + 978307200, 'unixepoch', 'localtime') as Date, h.id as "Phone Number", m.service as Service, CASE is_from_me WHEN 0 THEN "Received" WHEN 1 THEN "Sent" ELSE "Unknown" END as Type, CASE WHEN date_read > 0 THEN DATETIME(date_read + 978307200, 'unixepoch') WHEN date_delivered > 0 THEN DATETIME(date_delivered + 978307200, 'unixepoch') ELSE NULL END as "Date Read/Sent", text as Text FROM message m, handle h WHERE h.rowid = m.handle_id ORDER BY m.rowid ASC;


I'll make that a little easier to read:

SELECT 
  m.rowid as RowID, 
  DATETIME(date + 978307200, 'unixepoch', 'localtime') as Date, 
  h.id as "Phone Number", m.service as Service, 
  CASE is_from_me 
    WHEN 0 THEN "Received" 
    WHEN 1 THEN "Sent" 
    ELSE "Unknown" 
  END as Type, 
  CASE 
    WHEN date_read > 0 then DATETIME(date_read + 978307200, 'unixepoch')
    WHEN date_delivered > 0 THEN DATETIME(date_delivered + 978307200, 'unixepoch') 
    ELSE NULL END as "Date Read/Sent", 
  text as Text 
FROM message m, handle h 
WHERE h.rowid = m.handle_id 
ORDER BY m.rowid ASC;

Your results, when imported to a spreadsheet, should look like this:

ROWID	Date	Phone Number	Service	Type	Date Read/Sent	Text
2484	2012-10-02 08:28:36	11231234567	iMessage	Sent	2012-10-02 08:28:39	Hey
2485	2012-10-02 08:45:17	11231234567	iMessage	Sent	2012-10-02 08:46:11	Call me when you get this
2486	2012-10-02 08:46:06	13217654321	SMS	Received	2012-10-02 08:47:21	Can I borrow some bucks?
2487	2012-10-02 08:47:10	1321765321	SMS	Sent		No! I don't have any doe.

Rubbing Sticks: Lighting The Kindle Fire

I recently obtained a Kindle Fire on which to experiment.  It was booted to the operating system and unlocked.  Naturally, I thought, "Enable USB Debugging and have a go at it with adb."  I looked at the settings and to my surprise: no USB debugging option!  A little research revealed that USB debugging is enabled by default on the Kindle Fire and it runs on a custom Android 2.3.  "Fantastic!" I thought, "This is going to be a piece of cake."

Dashed Hopes

Boy was I wrong.  It's not that the Kindle Fire is particularly hard to deal with, it's just very different from my normal Android experience.  Normally, for the Android Debug Bridge (adb) to connect to an Android device in Linux, the vendor ID needs to be defined in a udev rule.  A quick look at the vendor IDs listed on the Android Developer site reveals the definite lack of a definition for the Kindle.  So, how do we find one?

$ lsusb
Bus 001 Device 001: ID 1d6b:0002 Linux Foundation 2.0 root hub
Bus 002 Device 001: ID 1d6b:0002 Linux Foundation 2.0 root hub
Bus 001 Device 002: ID 8087:0020 Intel Corp. Integrated Rate Matching Hub
Bus 002 Device 002: ID 8087:0020 Intel Corp. Integrated Rate Matching Hub
Bus 001 Device 004: ID 1949:0005 Lab126

The lsusb command displays information about USB buses on the system.  From the output, we see the vendor ID for the Kindle Fire is 0x1949.  Yes, that "Lab126" is the Kindle and yes, the ID is hexadecimal.  And in the event you were wondering, the second hex value after the colon, 0x0005, is the Prodoct ID.

False Hopes

OK, now we're in business, right?  I append the Kindle Fire to my android.rules file in /etc/udev/rules.d/ with the following lines:

#Amazon Kindle
SUBSYSTEM=="usb", ATTR{idVendor}=="1949", MODE="0666"

Now I start the adb server and read the device with:

$ adb devices
* daemon not running. starting it now on port 5037 *
* daemon started successfully *
List of devices attached

Nothing!  How can that be?  I've added every new device in this way!  I should see the serial number of the device or at least a series of question marks (indicating I need to restart the server as root because I lack permission to read the device).

Discovering Fire

It took some poking around and some experimentation, but it turns out the only way I could get adb to see the Kindle fire was to add the Vendor ID to the adb_usb.ini file.  "An '.ini' file?  Isn't that for Windows?" you ask.  In a strict sense, an ini file is a text file used for application settings, and it is commonly found on Windows systems, but not exclusively so.

You may recall that when you installed the Android SDK, you launched the android application in the ./android-sdk-linux/tools/ directory.  When you selected and installed the "Android SDK Tools" and "Android SDK Platform Tools", a hidden .android directory was created in your user account, or in the root user account if you ran the tool as administrator (recommended).  In the directory is the adb_usb.ini file, which by default, or after an android update usb command, has the following content:

# cat /root/.android/adb_usb.ini
# ANDROID 3RD PARTY USB VENDOR ID LIST -- DO NOT EDIT.
# USE 'android update adb' TO GENERATE.
# 1 USB VENDOR ID PER LINE.

To connect to the Kindle Fire with adb, we need to append the Vendor ID, as a hexadecimal expression, to the adb_usb.ini file:

# echo 0x1949 >> $HOME/.android/adb_usb.ini

You can see the effect of your command with 'cat':

# cat adb_usb.ini
# ANDROID 3RD PARTY USB VENDOR ID LIST -- DO NOT EDIT.
# USE 'android update adb' TO GENERATE.
# 1 USB VENDOR ID PER LINE.
0x1949

Now, to connect the Fire, all that remains is to restart the adb server:

# adb kill-server
# adb devices
* daemon not running. starting it now on port 5037 *
* daemon started successfully *
List of devices attached
0123456789ABCDEF device

Success!  

To recap, the udev rule does not help us in the case of the Kindle Fire.  The presence of the Vendor ID in the rule or lack there of has no effect on connecting to the device with the Android Debug Bridge in Linux.  The only effective method for connecting to the Fire is to place the Vendor ID in the adb_usb.ini file.

Facebook Search History

A digital forensics investigator is often asked to answer the question, "What did the user search for?"  Usually, the question revolves around Internet search engines, and producing a list depends on the browser in play as much as the search engine.  With the 250+ search engines and the 130+ Internet browsers referenced in Wikipedia, where you will find these histories and in what format can vary wildly.  Add to that the various tool bars that maintain data independent of the browser, and you're probably ready to surrender jump to another post on a topic less daunting!

But, you probably figured out from the title of this post that I'm not going to discuss the over 33,800 browser/search engine possibilities you need to consider when producing a "search term" history.  Instead, I'm going to discuss something I just discovered, Facebook search history.  And like so many web browsing sessions, I begin the discussion with a Google search....

Google Instant Predictions

I'll wager that most Internet users that have conducted an Internet search in the last two years have done so at least once using Google.  If so, they have likely experienced Google Instant Predictions. Launched around September, 2010, Instant Predictions produces "instant" search results as you type.  Search results begin populating based on Google's prediction of your search terms as you type them.

From a forensics point of view, this produces a lot more Internet history.  Every time Google populates results, its sending a webpage and elements.  And unless the connection is slow or the typist is particularly fast, Google refreshes the search results page for every letter typed by the user!  Consider a Google search using the Safari web browser for the term: "car wash" (obtained from the Safari Cache.db).

http://clients1.google.com/complete/search?client=safari&q=c
http://clients1.google.com/complete/search?client=safari&q=ca
http://clients1.google.com/complete/search?client=safari&q=car
http://clients1.google.com/complete/search?client=safari&q=car+
http://clients1.google.com/complete/search?client=safari&q=car+w
http://clients1.google.com/complete/search?client=safari&q=car+wa
http://clients1.google.com/complete/search?client=safari&q=car+was
http://clients1.google.com/complete/search?client=safari&q=car+wash

It is easy to see that an html page was cached for each of these URLs!  What can be interesting, and entertaining, is that you can even follow typing errors such as typos, spelling errors, and the resulting backspaces!

Facebook Type Ahead Search

I've recently discovered that Facebook produces a similar URL history for searches conducted through its website.  Observe the following:

http://www.facebook.com/ajax/typeahead/search.php?__a=1&value=sl&viewer=##########&filter%5B0%5D=page&filter%5B1%5D=user&context=mentions&dark_launch=true&rsp=mentions
http://www.facebook.com/ajax/typeahead/search.php?__a=1&value=slo&viewer=##########&filter%5B0%5D=page&filter%5B1%5D=user&context=mentions&dark_launch=true&rsp=mentions
http://www.facebook.com/ajax/typeahead/search.php?__a=1&value=slo.&viewer=##########&filter%5B0%5D=page&filter%5B1%5D=user&context=mentions&dark_launch=true&rsp=mentions
http://www.facebook.com/ajax/typeahead/search.php?__a=1&value=slo.sl&viewer=##########&filter%5B0%5D=page&filter%5B1%5D=user&context=mentions&dark_launch=true&rsp=mentions
http://www.facebook.com/ajax/typeahead/search.php?__a=1&value=slo.sleuth&viewer=##########&filter%5B0%5D=page&filter%5B1%5D=user&context=mentions&dark_launch=true&rsp=mentions

The response speed of Facebook does not appear to be that of Google, but again, it is easy to see that facebook sent several responses that were cached by Safari related to a single search by way of the "typeahead" mechanism.

Now, Facebook searches might be recorded in browser history databases, but consider that these databases may be destroyed through anti-forensics techniques or otherwise.  Searching for URLs might be the only method at your disposal for producing search term histories.  And now, like me, you know what to seek to reconstruct Facebook searches!

Mate: Your Forensics Desktop Buddy

There has been a casualty in the Linux Desktop over the past year or two: forensics.  Now, I'm not saying forensics can't be conducted from a modern Linux desktop environment like Gnome3 or Unity, but those environments do make it more difficult.  First, they are less configurable and second, they make it difficult to display more than one window at a time.  What might be a simple, clean interface to a desktop user is a hindrance to a forensics practitioner.

As a result, I switched to XFCE.  It's always been attractive to me because of its simplicity and size, and it does a lot of things right.  But it's a little rough around the edges.  The various settings windows are not well integrated, and a new user can easily become lost and frustrated.  More importantly, Thunar, the default file manager, is a little weak for exploring file systems from digital discovery point-of-view.  While

Meet Mate

Fortunately, forensics gurus are not the only people dissatisfied with the direction of the Linux desktop.  Gnome2 was a staple for many users, and it was common on Linux forensics boot disks, too.  The Mate project arose from the ashes of Gnome2 and serves as a drop in replacement for the popular but deprecated classic.

If you are a longtime or former Ubuntu user, Mate will look very familiar.  There is a little bit of translating to do, however, as some of the application names to which you've grown accustomed:

	MATE	GNOME 2	Type	Notes
	Atril	Evince	Document viewer	“lectern, reading desk”
	Caja	Nautilus	File manager	“box”
	Engrampa	File-Roller	File archive manager	“clip together”
	Eye of MATE (EOM)	Eye of GNOME (EOG)	Image viewer
	Marco	Metacity	Window manager	“framework, frame”
	MateCalc	gcalctool	Calculator
	MateConf	GConf	DE configuration system
	MateDialog	Zenity	GTK+ command-line dialog boxes
	MDM	GDM	Display manager (graphical login)
	Mozo	Alacarte	Menu editor
	Pluma	Gedit	Text editor	“pen”

The application gconf-editor was default GUI settings editor for Gnome2.  It was an important tool to know, because with it, the forensicator could configure his/her system to not automount devices.  The table above and a wiki entry at the Mate website indicate that gconf-editor will be replaced with, predictably, mateconf-editor, but as of Mate v1.40, no such application exists.

Configuring Mate

This brings me to the purpose of this post.  There is a command line tool that can be used to configure the Mate desktop environment called mateconftool-2, but its use is not intuitive.  I'll demonstrate its use to alter the media auto-mount setting.

First, its important to understand that the Mate settings expressed as key-value pairs and are stored in a series of XML files.  Editing the XML files directly is not recommended (settings are read and applied by the mateconfd daemon), and if you try, you'll see its quite difficult to chase down the correct file.  The mateconftool-2 tool makes navigating and editing the settings straight forward.

If you choose to think of the settings as being stored in a file system-like hierarchy, and I think you'll have little problem.  Observe, to view the settings directories at the root of the settings tree, issue the command:

$ mateconftool-2 --all-dirs /
 /system
 /desktop
 /schemas
 /apps

We see that there are settings in four categories: system, desktop, schemas, and apps.  We are seeking to change the behavior of the caja application, the file manager responsible for mounting attached devices.  We can see that the caja settings are not the only Mate desktop environment settings that can be configured:

$ mateconftool-2 --all-dirs /apps
 /apps/mate-dictionary
 /apps/procman
 /apps/mate-system-log
 /apps/timer-applet
 /apps/control-center
 /apps/pluma
 /apps/marco
 /apps/panel
 /apps/baobab
 /apps/engrampa
 /apps/caja
 /apps/mate-terminal
 /apps/mate-screenshot
 /apps/mate-screensaver
 /apps/notification-daemon
 /apps/eom
 /apps/mate-session
 /apps/mate-power-manager
 /apps/mate_settings_daemon
 /apps/mate-search-tool
 /apps/stickynotes_applet
 /apps/mate-volume-control

To review the caja settings, we examine the categories with the all-directories option and learn there is a preferences directory.  To view the preferences key-value pairs, we switch to the all-entries option:

$ mateconftool-2 --all-entries /apps/caja/preferences
 ...
 media_automount = true
 search_bar_type = search_by_text
 media_autorun_never = false
 media_autorun_x_content_start_app = [x-content/software]
 mouse_back_button = 8
 show_image_thumbnails = local_only
 desktop_is_home_dir = false
 media_autorun_x_content_ignore = []
 start_with_sidebar = true
 thumbnail_limit = 10485760
 directory_limit = -1

Now, this might seem cumbersome.  That's because it is.  If you know the path to the setting in which you are interested, you can address it directly:

$ mateconftool-2 --get /apps/caja/preferences/media_automount

true

To change a setting, you must specify its type: integer, boolean, float, or a string.  In our case, its pretty evident that we are dealing with a boolean value, and we want to set it to "false".  We must specify the action (set) and the type (false).  Silence means success, but we follow our change by reading the key for verification:

$ mateconftool-2 --set --type=bool /apps/caja/preferences/media_automount false

$ mateconftool-2 --get /apps/caja/preferences/media_automount

false

Now, when a device is plugged into the a Linux system running the Mate desktop, it will not be auto-mounted.  This is particularly important in Linux forensic boot discs designed to be inserted in the device to be examined/imaged or where a write-blocker is not an option or available.  None-the-less, we'll all  be happier when mateconf-editor GUI is added to the Mate line-up!