07 - iOS Forensics

Question 1

What are the six most important databases for iOS forensics?

Answer 1

• Contacts: Addressbook.sqlitedb
• Call History: call_history.db
• Chats: ChatStorage.sqlite
• Calendar: Calendar.sqlitedb / Extras.db
• SMS: sms.db
• Location: consolidated.db

Question 2

What are the two main databases for Contact details?

Answer 2

• AddressBook.sqlitedb: Information saved for each contact. Important tables: ABPerson and ABMultiValue.
• AddressBookImages.sqlitedb: Containing the images associated to given contact. Important tables: ABFullSizeImage.

Question 3

What are the four steps to retrieve Contact information?

Answer 3

1. Examine the schema of ABPerson table
2. Retrieve all information from the ABPerson
3. Examine the schema of other tables (ABMultiValue, ABMultiValueEntry, ABMultiValueLabel)
4. Retrieve information from the ABPerson, ABMultiValue, ABMultiValueEntry, ABMultiValueLabel tables

ModificationDate: NSDate Format (# of seconds since 2001). Convert to Unix epoch: +978’307’200

Question 4

Where are call history information stored?

Answer 4

Information are stored in call_history.db. Important table: call. Will only hold 100 calls (incl. FaceTime).

flags: 4=incoming, 5=outgoing, 8=blocked, 16: facetime. Everything else=dropped.

Question 5

How do you build a SQL CASE statement?

Answer 5

SELECT CASE field_name

WHEN field_value1 THEN “Some Text”
WHEN field_value2 THEN “Some Other Text”

ELSE “Some More Text”

END

FROM ;

SELECT CASE field1, field2

WHEN field2 = “1” THEN “Some Text”
WHEN field2 = “2” THEN “Some Other Text”

ELSE “Some More Text”

END

FROM ;

Question 6

Where are SMS information stored?

Answer 6

SMS information are stored in sms.db. Important tables are: message, sqlite_sequence, msg_group, group_member, msg_pieces.

• Most important tables is: message.*
• flags: 3 = sent, 2 = Received*
• Timestamps are in unixepoch*

Question 7

Where are calendar data stored?

Answer 7

Details are stored in two databases:

• Calendar.sqlitedb : Tables are: Alarm, AlarmChanges, Attendee, AttendeeChanges, Calendar, CalendarChanges, Event, EventChanges, EventExceptionDate, OccurrenceCache, OccurrenceCacheDays, Participant, Recurrence, RecurrenceChanges, Store, Task, TaskChanges…
  Most Interesting table: Event
• Extras.db : ZALARM, ZSETTING, Z_METADATA, Z_PRIMARYKEY

Question 8

Where are chat information stored?

Answer 8

Stored under …/Documents. File name: ChatStorage.sqlitedb. Contains chat information from third party applications, such as Viber, WhatsApp, etc. Important table: zwamessage.

Question 9

Where are iPhone location data stored?

Answer 9

In consolidated.db file stores GPS coordinates of WIFI and Cell sites. Consolidated.db can be used to track a user’s movements. Accuracy is within a few 100 meters.

• Main tables: CellLocation and WifiLocation.
• Timestamp: NSDate format.

Question 10

How can you only show the first three entries of a table?

Answer 10

SELECT * FROM table_name LIMIT 3

Question 11

How can you show 10 entries starting at entry 6?

Answer 11

SELECT * FROM table_name LIMIT 5,10

Question 12

How to convert NSDate format into Unixepoch and then into human readable format?

Answer 12

SELECT MAC, datetime(Timestamp+978307200, ’unixepoch’, ’localtime’) FROM WifiLocation