O'Reilly logo

Hacking and Securing iOS Applications by Jonathan Zdziarski

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

SQLite Databases

Apple iOS devices make heavy use of database files to store information such as address book contacts, SMS messages, email messages, and other data of a sensitive nature. This is done using the SQLite database software, which is an open source, public domain database package. SQLite databases typically have the file extension .sqlitedb, but some databases are given the .db extension, or other extensions as well.

Whenever an application transfers control to one of Apple’s preloaded applications, or uses the SDK APIs to communicate with other applications’ frameworks, the potential exists for data to leak, as these databases are used extensively through Apple’s software. Consider an enterprise Exchange server with confidential contact information. Such data could potentially be compromised simply by storing this data in the iOS address book, which will expose the otherwise-encrypted data to an attacker.

In order to access the data stored in these files, you’ll need a tool that can read them. Good choices include:

  • The SQLite command-line client, which can be downloaded at http://www.sqlite.org.

  • SQLite Browser, a free open source GUI tool for browsing SQLite databases. It is available at http://sqlitebrowser.sourceforge.net. This tool provides a graphical interface to view SQLite data without issuing direct SQL statements (although knowledge of SQL helps).

Mac OS X includes the SQLite command-line client, so we’ll use command-line examples here. SQLite’s command-line utility can easily access the individual files and issue SQL queries against a database.

Note

The basic commands you’ll need to learn will be explained in this chapter. For additional information about Structured Query Language (SQL), read Learning SQL by Alan Beaulieu (O’Reilly).

Connecting to a Database

To open an SQLite database from the command line, invoke the sqlite3 client. This will dump you to an SQL prompt where you can issue queries:

$ sqlite3 filename.sqlitedb
SQLite version 3.4.0
Enter ".help" for instructions
sqlite>

You are now connected to the database file you’ve specified. To disconnect, use the .exit command; be sure to prefix the command with a period. The SQLite client will exit and you will be returned to a terminal prompt:

sqlite> .exit
$

SQLite Built-in Commands

After you connect to a database, there are a number of built-in SQLite commands you can issue to obtain information or change behavior. Some of the most commonly used commands follow. These are SQLite-specific, proprietary commands, and do not accept a semicolon at the end of the command. If you use a semicolon, the entire command is ignored.

.tables

Lists all of the tables within a database. This is useful if you’re not familiar with the database layout, or if you’ve recovered the file through data carving and are not sure which database you’ve connected to. Most databases can be identified simply by looking at the names of the existing tables.

.schema table-name

Displays the SQL statement used to construct a table. This displays every column in the table and its data type. The following example queries the schema for the mailboxes table, which is found inside a database named Protected Index on the device. This file is available once decrypted using the protection class keys, which will be explained in Chapter 5. This database is used to store email on the device:

sqlite> .schema messages
CREATE TABLE messages (message_id INTEGER PRIMARY KEY,
                         sender,
                         subject,
                         _to,
                         cc,
                         bcc);
.dump table_name

Dumps the entire contents of a table into SQL statements. Binary data is output as long hexadecimal sequences, which can later be converted to individual bytes. You’ll see how to do this later for recovering Google Maps cached tile images and address book images.

.output filename

Redirects output from subsequent commands so that it goes into a file on disk instead of the screen. This is useful when dumping data or selecting a large amount of data from a table.

.headers on

Turns display headers on so that the column title will be displayed whenever you issue a SELECT statement. This is helpful to recall the purpose of each field when exporting data into a spreadsheet or other format.

.exit

Disconnects from the database and exits the SQLite command shell.

Issuing SQL Queries

In addition to built-in commands, SQL queries can be issued to SQLite on the command line. According to the author’s website, SQLite understands “most of the SQL language.” Most of the databases you’ll be examining contain only a small number of records, and so they are generally manageable enough to query using a simple SELECT * statement, which outputs all of the data contained in the table. Although the proprietary SQLite commands we saw in the previous section do not expect a semicolon (;), standard SQL queries do, so be sure to end each statement with one.

If the display headers are turned on prior to issuing the query, the first row of data returned will contain the individual column names. The following example queries the actual records from the mailboxes table, displaying the existence of an IMAP mailbox located at http://imap.domain.com. This mailbox contains three total messages, all of which have been read, with none deleted.

sqlite> SELECT * FROM mailboxes;
1|imap://user%40yourdomain.com@imap.yourdomain.com/INBOX||3|0|0

Important Database Files

The following SQLite databases are present on the device, and may be of interest depending on the needs of the attacker.

Note

These files exist on the user data partition, which is mounted at /private/var on the iPhone. If you’ve extracted the live filesystem from a tar archive using the DataTheft payload example in Chapter 3, you’ll see a private folder in the current working directory you’ve extracted its contents. If you’re using a raw disk image you’ve recovered using the RawTheft payload, the image will be mounted with the name Data and will have a root relative to /private/var.

Address Book Contacts

The address book contains individual contact entries for all of the contacts stored on the device. The address book database can be found at /private/var/mobile/Library/AddressBook/AddressBook.sqlitedb. The following tables are primarily used:

ABPerson

Contains the name, organization, department, and other general information about each contact

ABRecent

Contains a record of recent changes to properties in the contact database and a timestamp of when each was made

ABMultiValue

Contains various data for each contact, including phone numbers, email addresses, website URLs, and other data for which the contact may have more than one. The table uses a record_id field to associate the contact information with a rowid from the ABPerson table. To query all of the multivalue information for a particular contact, use two queries—one to find the contact you’re looking for, and one to find their data:

sqlite> select ROWID, First Last, Organization, Department, JobTitle, CreationDate, ModificationDate from ABPerson where First = 'Jonathan';
ROWID|Last|Organization|Department|JobTitle|CreationDate|
ModificationDate
22|Jonathan|O'Reilly Media|Books|Author|234046886|234046890

sqlite> select * from ABMultiValue where record_id = 22;
UID|record_id|property|identifier|label|value
57|22|4|0|7|jonathan@zdziarski.com
59|22|3|0|3|555-555-0000
60|22|3|1|7|555-555-0001

Notice the property field in the example. The property field identifies the kind of information being stored in the field. Each record also consists of a label to identify how the data relates to the contact. For example, different numbers in the label field of the previous output indicate whether a phone number is a work number, mobile number, etc. The meaning of each number in the label field can be found in the ABMultiValueLabel table. The following output shows the rowid field of that table, which contains the label numbers shown in the previous output, along with its definition. Because rowid is a special column, it must be specifically named; the general SELECT * from command would not return it:

sqlite> select rowid, * from ABMultiValueLabel;
rowid|value
1|_$!<Work>!$_
2|_$!<Main>!$_
3|_$!<Mobile>!$_
4|_$!<WorkFAX>!$_
5|_$!<HomePage>!$_
6|mobile
7|_$!<Home>!$_
8|_$!<Anniversary>!$_
9|other
10|work
ABMultiValueEntry

Some multi-value entries contain multiple values themselves. For example, an address consists of a city, state, zip code, and country code. For these fields, the individual values will be found in the ABMultiValueEntry table. This table consists of a parend_id field, which contains a value matching a rowid of the ABMultiValue table.

Each record in the ABMultiValueEntry table consists of a key/value pair, where the key is a numerical identifier describing the kind of information being stored. The individual keys are indexed starting at 1, based on the values stored in the ABMultiValueEntryKey table as shown here:

sqlite> select rowid, * from ABMultiValueEntryKey;
rowid|value
1|Street
2|State
3|ZIP
4|City
5|CountryCode
6|username
7|service
8|Country

Putting it all together

The following query can be used to cross-reference the data discussed in the previous sections by dumping every value that is related to any other value in another table (this dump is known in mathematics as a Cartesian product). This may be useful for exporting a target’s contact information into a spreadsheet or other database. Use the following commands to dump the address book into a field-delimited text file named AddressBook.txt:

$ sqlite3 AddressBook.sqlitedb
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .headers on
sqlite> .output AddressBook.txt
sqlite> select Last, First, Middle, JobTitle, Department,
   ...>    Organization,  Birthday, CreationDate,
   ...>    ModificationDate, ABMultiValueLabel.value,
   ...>    ABMultiValueEntry.value, ABMultiValue.value
   ...> from ABPerson, ABMultiValue, ABMultiValueEntry,
   ...>    ABMultiValueLabel
   ...> where ABMultiValue.record_id = ABPerson.rowid
   ...>     and ABMultiValueLabel.rowid = ABMultiValue.label
   ...>     and ABMultiValueEntry.parent_id = ABMultiValue.rowid;
sqlite> .exit

Address Book Images

In addition to the address book’s data, each contact may be associated with an image. This image is brought to the front of the screen whenever the user receives an incoming phone call from the contact. The address book images are stored in /private/var/mobile/Library/AddressBook/AddressBookImages.sqlitedb and are keyed based on a record_id field corresponding to a rowid within the ABPerson table (inside the AddressBook.sqlitedb database). To extract the image data, first use SQLite’s .dump command, as shown in the following example:

$ sqlite3 AddressBookImages.sqlitedb
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .output AddressBookImages.txt
sqlite> .dump ABFullSizeImage
sqlite> .exit

This will create a text file containing the image data in an ASCII hexadecimal encoding. In order to convert this output back into binary data, create a simple Perl script named decode_addressbook.pl, as shown in Example 4-1.

Note

Perl is a popular scripting language known for its ability to easily parse data. It is included by default with Mac OS X. You may also download binaries and learn more about the language at http://www.perl.com.

Example 4-1. Simple ascii-hexadecimal decoder (decode_addressbook.pl)

#!/usr/bin/perl

use strict;

mkdir("./addressbook-output", 0755);
while(<STDIN>) {
    next unless (/^INSERT INTO/);
    my($insert, $query) = split(/\(/);
    my($idx, $data) = (split(/\,/, $query))[1,5];
    my($head, $raw, $tail) = split(/\'/, $data);
    decode($idx, $raw);
}
exit(0);

sub decode {
    my($idx, $data) = @_;
    my $j = 0;
    my $filename = "./addressbook-output/$idx.png";
    print "writing $filename...\n";
    next if int(length($data))<128;
    open(OUT, ">$filename") || die "$filename: $!";
    while($j < length($data)) {
        my $hex = "0x" . substr($data, $j, 2);
        print OUT chr(hex($hex));
        $j += 2;
    }
    close(OUT);
}

To decode the AddressBookImages.txt database dump, use the Perl interpreter to run the script, providing the dump file as standard input:

$ perl decode_addressbook.pl < AddressBookImages.txt

The script will create a directory named addressbook-output, containing a series of PNG images. These images can be viewed using a standard image viewer. The filename of each image will be the record identifier it is associated with in the AddressBook.sqlite database, so that you can associate each image with a contact.

Google Maps Data

The Google Maps application allows iOS to look up directions or view a map or satellite imagery of a particular location. If an application launched the maps application or used the maps interfaces to display a geographical location, a cache of the tiles may be recoverable from the device. The database file /private/var/mobile/Library/Caches/MapTiles/MapTiles.sqlitedb contains image data of previously displayed map tiles. Each record contains an X,Y coordinate on a virtual plane at a given zoom level, and a binary data field containing the actual image data, stored in PNG-formatted images.

The Google Maps application also stores a cache of all lookups performed. The lookup cache is stored at the path /private/var/mobile/Library/Maps/History.plist on the user partition, and can be easily read using a standard text editor. This lookup cache contains addresses, longitude and latitude, and other information about lookups performed.

Recovering the map tiles is a little trickier than retrieving the history, as the data resides in a SQLite database in the same fashion as the address book images. To extract the actual images, first copy the MapTiles.sqlitedb file onto the desktop machine and dump the images table using the command-line client, as follows. This will create a new file named MapTiles.sql, which will contain information about each map tile, including the raw image data:

$ sqlite3 MapTiles.sqlitedb
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .output MapTiles.sql
sqlite> .dump images
sqlite> .exit

Create a new file named parse_maptiles.pl containing the following Perl code. This code is very similar to the address book code used earlier, but it includes the X,Y coordinates and zoom level of each tile in the filename so that they can be pieced back together if necessary. See Example 4-2.

Example 4-2. Map tiles parsing script (parse_maptiles.pl)

#!/usr/bin/perl

use strict;
use vars qw { $FILE };

$FILE = shift;
if ($FILE eq "") {
    die "Syntax: $0 [filename]\n";
}

&parse($FILE);

sub parse {
    my($FILE) = @_;
    open(FILE, "<$FILE") || die "$FILE: $!";
    mkdir("./maptiles-output", 0755);
    while(<FILE>) {
        chomp;
        my $j = 0;
        my $contents = $_;
        next unless ($contents =~ /^INSERT /);
        my ($junk, $sql, $junk) = split(/\(|\)/, $contents);
        my ($zoom, $x, $y, $flags, $length, $data) = split(/\,/, $sql);
        $data =~ s/^X'//;
        $data =~ s/'$//;
        my $filename = "./maptiles-output/$x,$y\@$zoom.png";
        next if int(length($data))<128;
        print $filename . "\n";
        open(OUT, ">$filename") || die "$filename: $!";
        print int(length($data)) . "\n";
        while($j < length($data)) {
            my $hex = "0x" . substr($data, $j, 2);
            print OUT chr(hex($hex));
            $j += 2;
        }
        close(OUT);
    }
    close(FILE);
}

Use the parse_maptiles.pl script to convert the SQL dump to a collection of PNG images. These will be created in a directory named maptiles-output under the current working directory.

$ perl parse_maptiles.pl MapTiles.sql

Each map tile will be extracted and given the name X,Y@Z.png, denoting the X,Y position on a plane and the zoom level; each zoom level essentially constitutes a separate plane.

A public domain script, written by Tim Fenton, can be used to reassemble these individual tiles into actual map images. To do this, create a new directory for each zoom level you want to reassemble and copy the relevant tile images into the directory. Use the following script to rebuild each set of tiles into a single image. Be sure to install ImageMagick on your desktop, as the script makes extensive use of ImageMagick’s toolset. ImageMagick is an extensive collection of image manipulation tools. Install ImageMagick using MacPorts.

$ sudo port install imagemagick

You’ll also need a blank tile to represent missing tiles on the map. This image can be found in the book’s file repository, named blank.png, or you can create your own blank 64x64 PNG image. See Example 4-3 for a script to reconstruct your map tiles.

Example 4-3. Map tiles reconstruction script (merge_maptiles.pl)

#!/usr/bin/perl

# Script to re-assemble image tiles from Google maps cache
# Written by Tim Fenton; Public Domain

use strict;

my $i = 62;
my $firstRow = 1;
my $firstCol = 1;

my $j;
my $finalImage;

# do a directory listing and search the space
my @tilesListing = `ls −1 *.png`;
my %zoomLevels;
foreach( @tilesListing )
{
    my $tileName = $_;

    # do a string match
    $tileName =~ /(\d+),(\d+)[@](\d+).png/;

    # only key into the hash if we got a zoom level key
    if( $3 ne "" )
    {
        if ($2 > $zoomLevels{$3}{row_max} || $zoomLevels{$3}{row_max} eq "")
        {
            $zoomLevels{$3}{row_max} = $2;
        }

        if ($2 < $zoomLevels{$3}{row_min} || $zoomLevels{$3}{row_min} eq "")
        {
            $zoomLevels{$3}{row_min} = $2;
        }

        if ($1 > $zoomLevels{$3}{col_max} || $zoomLevels{$3}{col_max} eq "")
        {
            $zoomLevels{$3}{col_max} = $1;
        }

        if ($1 < $zoomLevels{$3}{col_min} || $zoomLevels{$3}{col_min} eq "")
        {
            $zoomLevels{$3}{col_min} = $1;
        }
    }
}

foreach( keys( %zoomLevels ) )
{
    print "Row max value for key: $_ is $zoomLevels{$_}{row_max}\n";
    print "Row min value for key: $_ is $zoomLevels{$_}{row_min}\n";
    print "Col max value for key: $_ is $zoomLevels{$_}{col_max}\n";
    print "Col min value for key: $_ is $zoomLevels{$_}{col_min}\n";
}

foreach( sort(keys( %zoomLevels )) )
{
    my $zoomKey = $_;


    # output file name
    my $finalImage = `date "+%H-%M-%S_%m-%d-%y"`;
    chomp( $finalImage );
    $finalImage = "_zoomLevel-$zoomKey-" . $finalImage . ".png";

    # loop over the columns
    for( $j = $zoomLevels{$zoomKey}{col_min};
         $j <= $zoomLevels{$zoomKey}{col_max}; $j++ )
    {
        # loop over the rows
        my $columnImage = "column$j.png";
        for( $i = $zoomLevels{$zoomKey}{row_min};
            $i < $zoomLevels{$zoomKey}{row_max}; $i++ )
        {
            my $fileName = "$j,$i\@$zoomKey.png";

            # check if this tile exists
            if( -e $fileName )
            {
                print "$fileName exists!\n";

                # we're past the first image and have something to join
                if( $firstRow == 0 )
                {
                    # rotate the image
                    `convert -rotate 270 $fileName Rot_$fileName`;
                    `convert +append $columnImage Rot_$fileName $columnImage`;
                }
                else # first row
                {
                    `cp $fileName $columnImage`;
                    $firstRow = 0;
                }
            }
            elsif( $firstRow == 1 ) # do this for the first non-existant row
            {
                print "$fileName doesn't exist\n";
                `cp blank.png $columnImage`;
                $firstRow = 0;
            }
            elsif( $firstRow == 0 )
            {
                print "$fileName doesn't exist\n";
                `cp blank.png Rot_$fileName`;
                `convert +append $columnImage Rot_$fileName $columnImage`;
            }
        }

        # now rotate the column we just created
        `convert -rotate 90 $columnImage $columnImage`;
        `rm Rot*`;

        if( $firstCol == 0 )
        {
            `convert +append $finalImage $columnImage $finalImage`;
        }
        else
        {
            `cp $columnImage $finalImage`;
            $firstCol = 0;
        }
    }

    # clean up the temorary files
    `rm column*`;
}

The resulting image will stitch together all of the map tiles based on the X, Y coordinates they were assigned. When loading this image in an image viewer, you may see tiles missing, which will be represented by the blank.png tile. Tiles can go missing for two reasons. If the tiles were never viewed in the map, you’ll notice large gaps of tiles in the areas that were never viewed. Single tiles missing from within a viewed region, however, suggest that the map was being viewed while the device was in motion along the given route. Because most mobile carriers’ networks have bandwidth limitations, gaps in tiles are likely to appear in increasing quantities as the vehicle moves faster. The resulting pattern not only suggests that the device’s user traveled the route (rather than simply viewing it on the device), but also gives broad hints as to the route and speed at which the user was traveling. In Figure 4-3, the device’s owner traveled along N. Amherst Rd. at about 35 miles per hour. The staggering of the tiles will change depending on speed, network (Edge vs. 3G), and signal strength. Only experimentation can determine the speed as it relates to missing tiles in a given area.

Reassembled map tile image with missing tiles consistent with motion

Figure 4-3. Reassembled map tile image with missing tiles consistent with motion

Calendar Events

Users and third-party applications may create calendar events and alarms. Data synchronized with Exchange can also synchronize calendar events, which can be leaked through the device’s calendar application. To extract all of the target’s calendar events, an attacker will look at /private/var/mobile/Library/Calendar/Calendar.sqlitedb.

The most significant table in this database is the Event table. This contains a list of all recent and upcoming events and their descriptions:

$ sqlite3 Calendar.sqlitedb
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> select rowid, summary, description, start_date, end_date from CalendarItem;

ROWID|summary|description|start_date|end_date
62|Buy 10M shares of AAPL||337737600.0|337823999.0

Each calendar event is given a unique identifier. Also stored is the event summary, location, description, and other useful information. An attacker can also view events that are marked as hidden.

Unlike most timestamps used on the iPhone, which are standard Unix timestamps, the timestamp used here is an RFC 822 timestamp representing the date offset to 1977. The date is, however, slightly different from RFC 822 and is referred to as Mac Absolute Time. To convert this date, add 978307200, the difference between the Unix epoch and the Mac epoch, and then calculate it as a Unix timestamp:

$ date -r `echo '337737600 + 978307200'| bc`
Wed Sep 14 20:00:00 EDT 2011

Call History

If your application initiates phone calls, each call is logged in the call history. The call history stores the phone numbers of the most recent people contacted by the user of the device, regardless of what application the call was initiated from. As newer calls are made, the older phone numbers are deleted from the database, but often remain present in the file itself. Querying the database will provide the live call list, while performing a strings dump of the database may reveal additional phone numbers. This can be particularly useful for an attacker looking for a log of a deleted conversation or if the call log was cleared by the user. The file /private/var/wireless/Library/CallHistory/call_history.db contains the call history:

$ sqlite3 call_history.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .headers on
sqlite> select * from call;
ROWID|address|date|duration|flags|id
1|8005551212|1213024211|60|5|-1

Each record in the call table includes the phone number of the remote party, a Unix timestamp of when the call was initiated, the duration of the call in seconds (often rounded to the minute), and a flag identifying whether the call was an outgoing or incoming call. Outgoing calls will have the low-order bit of the flags set, while incoming calls will have it clear. Therefore, all odd-numbered flags identify outgoing calls and all even-numbered flags identify incoming calls. It’s important to verify this on a different device running the same firmware version, as flags are subject to change without notice, given that they are proprietary values assigned by Apple.

In addition to a simple database dump, performing a strings dump of the file can recover previously deleted phone numbers, and possibly additional information.

$ strings call_history.db
2125551212H
2125551213H

Later on in this chapter, you’ll learn how to reconstruct the individual SQLite data fields for timestamps, or other values, based on the raw record data.

Email Database

All mail stored locally on the device is stored in a SQLite database having the filename /private/var/mobile/Library/Mail/Protected Index. Unlike other databases, this particular file has no extension, but it is indeed a SQLite database. This file contains information about messages stored locally, including sent messages and the trash can. Data includes a messages and a message_data table, containing message information and the actual message contents, respectively. The file Envelope Index, found in the same directory, contains a list of mailboxes and metadata, which may also be useful for an attacker. This data is also available if an Exchange server is synchronized with the device and mail is stored on the device.

All email that is synchronized to an Exchange server, or other compatible enterprise mail servers that integrate into the Mail application, use this database to store messages—making it a very lucrative target for those interested in stealing confidential email.

To obtain a list of mail stored on the device, query the messages table:

$ sqlite3 Protected\ Index
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> select * from messages;
message_id|sender|subject|_to|cc|bcc
1|"Zdziarski, Jonathan" <jonathan@zdziarski.com>|Foo|"Smith, John" <John.Smith@yourdomain.com>||

The message contents for this message can be queried from the message_data table.

sqlite> sqlite> select * from message_data where message_data_id = 1;
message_data_id|data
1|I reset your password for the server to changeme123. It's the same as everyone else's password :)

To dump the entire message database into single records, these two queries can be combined to create a single joined query:

sqlite> select * from messages, message_data where message_data.message_data_id = messages.rowid;

Note

The email database is another good candidate for string dumping, as deleted records are not immediately purged from the file.

Mail attachments and message files

In addition to storing mail content, mail attachments are often stored on the filesystem. Within the Mail directory, you’ll find directories pertaining to each mail account configured on the device. Walking down this directory structure, you may find a number of accounts whose folders have an Attachments folder, INBOX, folder, and others. When a passcode is used on the device, attachments are similarly encrypted using data protection. You’ll learn how to defeat this encryption in Chapter 5.

You may also find a number of Messages folders. These folders contain email messages downloaded from the server. While many messages are stored in the Protected Index file, you may also find the raw messages themselves stored as files with .emlx extensions in these directories.

Notes

The notes database is located at /private/var/mobile/Library/Notes/notes.sqlite and contains the notes stored for the device’s built-in Notes application. It’s one of the simplest applications on the device, and therefore has one of the simplest databases. Corporate employees often use the simplest and least secure application on the device to store the most sensitive, confidential information. With the advent of Siri, notes are even easier to create.

$ sqlite3 notes.sqlite
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> select ZCREATIONDATE, ZTITLE, ZSUMMARY, ZCONTENT
  ...>     from ZNOTE, ZNOTEBODY where ZNOTEBODY.Z_PK= ZNOTE.rowid;
ZCREATIONDATE|ZTITLE|ZSUMMARY|ZCONTENT
321554138|Bank Account Numbers|Bank Account Numbers|Bank Account Numbers<div><br></div><div>First Secure Bank</div><div>Account Number 310720155454</div>

In some cases, deleted notes can be easily recovered by performing a strings dump of this database:

$ strings notes.sqlite

Photo Metadata

The file /private/var/mobile/Library/PhotoData/Photos.sqlite contains a manifest of photos stored in the device’s photo album. The Photos table contains a list of photos and their paths on the device, their resolution, and timestamps when the photo was recorded or modified.

$ sqlite3 Photos.sqlite
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> select * from Photo;
primaryKey|type|title|captureTime|width|height|userRating|flagged|thumbnailIndex|orientation|directory|filename|duration|recordModDate|savedAssetType
1|0|IMG_0001|340915581.0|640|960|0|0|0|1|DCIM/100APPLE|IMG_0001.PNG|0.0|340915581.975359|0
2|0|IMG_0002|340915598.0|640|960|0|0|1|1|DCIM/100APPLE|IMG_0002.PNG|0.0|340915598.605318|0

The PhotoAlbum table also contains a list of photo albums stored on the device.

sqlite> select * from PhotoAlbum;
primaryKey|kind|keyPhotoKey|manualSortOrder|title|uid|slideshowSettings|objC_class
1|1000|0|130|saved photos|8+uXBMbtRDCORIYc7uXCCg||PLCameraAlbum

SMS Messages

The SMS message database contains information about SMS messages sent and received on the device. This includes the phone number of the remote party, timestamp, actual text, and various carrier information. The file can be found on the device’s media partition in /private/var/mobile/Library/SMS/sms.db.

$ sqlite3 sms.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .headers on
sqlite> select * from message;
ROWID|address|date|text|flags|replace|svc_center|group_id|association_id|
height|UIFlags|version
6|2125551234|1213382708|The password for the new cluster at 192.168.32.10 is root / changeme123. I forgot how to change it. That's why I send this information out of band. We should be safe since we have the 123 in the password.|3|0||3|1213382708|38|0|0

Like the call history database, the SMS database also has a flags field, identifying whether the message was sent or received. The value of the low-order bit determines which direction the message was going. Messages that were sent will have this bit set, meaning the flags value will be odd. If the message was received, the bit will be clear, meaning the flags value will be even.

The SMS messages database is also a great candidate for a strings dump, to recover deleted records that haven’t been purged from the file. An example follows of an SMS message that had been deleted for several days, but was still found in the SMS database:

$ strings sms.db
12125551234HPs
Make sure you delete this as soon as you receive it. Your new password on the server is poohbear9323.

Safari Bookmarks

The file /private/var/mobile/Library/Safari/Bookmarks.db contains a copy of the bookmarks stored in the Safari browser. These can be set inside the Safari application, or synced from a desktop machine. If your application opens remote resources inside a Safari browser window, a user may bookmark this data, and subsequently any confidential information stored in the URL.

$ sqlite3 Bookmarks.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .headers on
sqlite> select title, url from bookmarks;
O'Reily Media|http://www.oreilly.com

Safari bookmarks may have been set directly through the device’s GUI, or represent copies of the bookmarks stored on the target’s desktop machine.

SMS Spotlight Cache

The Spotlight caches, found in /private/var/mobile/Library/Spotlight, contain SQLite databases caching both active and long deleted records from various sources. Inside this folder, you’ll find a spotlight cache for SMS messages named com.apple.MobileSMS. The file SMSSeaerchdb.sqlitedb contains a Spotlight cache of SMS messages, names, and phone numbers of contacts they are (or were) associated with. The Spotlight cache contains SMS messages long after they’ve been deleted from the SMS database, and further looking into deleted records within the spotlight cache can yield even older cached messages.

Safari Web Caches

The Safari web browsing cache can provide an accurate accounting of objects recently downloaded and cached in the Safari browser. This database lives in /private/var/mobile/Library/Caches/com.apple.mobilesafari/Cache.db. Inside this file, you’ll find URLs for objects recently cached as well as binary data showing the web server’s response to the object request, as well as some binary data for the objects themselves. The cfurl_cache_response table contains the response data, including URL, and the timestamp of the request. The cfurl_cache_blob_data table contains server response headers and protocol information. Finally, the cfurl_cache_receiver_data table contains the actual binary data itself. Keep in mind that not all objects are cached here; primarily small images, JavaScript, and other small objects. It is a good place for an attacker to look for trace, nonetheless.

Web Application Cache

The file /private/var/mobile/Library/Caches/com.apple.WebAppCache/ApplicationCache.db contains a database of cached objects associated with web apps. These typically include images, HTML, JavaScript, style sheets, and other small, often static objects.

WebKit Storage

Some applications cache data in WebKit storage databases. Safari also stores information from various sites in WebKit databases. The /private/var/mobile/Library/WebKit directory contains a LocalStorage directory with unique databases for each website. Often, these local storage databases can also be found within a third party application’s Library folder, and contain some cached information downloaded or displayed in the application. The application or website can define its own local data, and so the types of artifacts found in these databases can vary. The Google website cache may, for example, store search queries and suggestions, while other applications may store their own types of data. It’s good to scan through WebKit caches to find any loose trace information that may be helpful to an adversary.

Voicemail

The voicemail database contains information about each voicemail stored on the device, and includes the sender’s phone number and callback number, the timestamp, the message duration, the expiration date of the message, and the timestamp (if any) denoting when the message was moved to the trash. The voicemail database is located in /private/var/mobile/Library/Voicemail/voicemail.db, while the voicemail recordings themselves are stored as AMR codec audio files in the directory /private/var/mobile/Library/Voicemail/.

$ sqlite3 voicemail.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .headers on
sqlite> select * from voicemail;
ROWID|remote_uid|date|token|sender|callback_num|duration|expiration|
trashed_date|flags 1|100067|1213137634|Complete|2125551234|2125551234|
14|1215731046|234879555|11
sqlite>

The audio files themselves can be played by any media player supporting the AMR codec. The most commonly used players include QuickTime and VLC.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required