Recently one of our users, Dan Saunders, was kind enough to write up his experience using the Forensic Browser for SQLite on a database that was not supported by any other forensics tools – this is his story:
SQLite databases are becoming more and more of a focus point for the present day Digital Forensics Specialist, with the increase of applications available on the app store providing a gold mine for digital evidence waiting to be discovered. Commercial forensic software companies are rapidly expanding their research and development departments, which are under constant pressure to keep up with the reverse engineering applications on the market and despite their best efforts, this is not feasible.
Because of this, we as investigators need to be able to delve deeper into the source of our data acquisitions, to ensure no stone is left unturned. A recent case study was a prime example of this, with the ‘3rd party’ application known Sleep Cycle being the centre of attention.
Case scenario: A serious crime has occurred on a specific date/time and using traditional forensic methods, a suspect was identified and detained for questioning. The defendant has claimed that they were not present at the crime scene and in fact were home in bed asleep at around 04:30am on 31/01/2015; furthermore they claim evidence contained within the application Sleep Cycle would prove this, as they initiated a session on their phone when they went to bed. One thing we must remember is that it is not possible to determine who had possession of the mobile device during a specific date/time and furthermore, the date/time cannot be regarded as accurate as it may have been incorrectly set during the notable date/time. In addition there is also no evidence suggesting the location of the mobile device in question. Cell site analysis may/may not prove the location of the handset; if a user was actively using the handset, i.e. Call Data Records (CDRs) could be analysed to determine cell towers utilised. However, it is our duty to analyse the data in an attempt to prove/disprove the account.
Sleep Cycle is a ‘3rd party’ application which is used to analyse your sleeping patterns via utilising Apple devices accelerometer. Apple devices accelerometer senses and monitors the devices physical movement and records this data in a format which can be interpreted and presented in a graph for conclusion via the application interface. The Sleep Cycle application version 4.7.1 was found to be installed on the Apple iPhone 5 (A1429) running iOS 8.1.2 and the data was acquired by utilising industry standard forensic software and the application data was extracted from the following file path:
/private/var/mobile/Applications/com.lexwarelabs.goodmorning
Various data files including but not limited to: SQLite db, PList & .dat files were stored within the aforementioned file path; of note was the ‘eventlog.sqlite’. By viewing the raw data in a HEX editor, it is clear that the SQLite 3 database was crucially not encrypted, therefore it can be viewed in its native format via a SQLite database browser with no issues.
Figure 1: HxD Hex Editor – ‘eventlog.sqlite’ database 16 byte signature represented in ASCII
The database itself was found to contain numerous tables, as displayed below:
Figure 2: Forensic Browser for SQLite – eventlog.sqlite table structure
Following closer inspection of the table structure, specific tables were identified which contained data relating to the recording of sessions and logging events which occurred during the course of the application being used, of note were: ‘ZSLEEPSESSION’ and ‘ZSLEEPEVENT’. ‘ZSLEEPSESSION’ was found to contain records of individual events, whilst ‘ZSLEEPEVENT’ contains more in-depth data in relation to triggered events/movements during sessions which a user has initiated.
Initially the SQLite database was reviewed using SQLite Expert Personal, however when dealing with multiple tables there are often instances where references link specific data fields from one table to another. Because of this, we opted to take a closer look at the contents using specialist forensic tools on the market purposely developed for analysing complex database files, one of which called Forensic Browser for SQLite is a prime example and was my tool of choice for examining the contents of the aforementioned file. This tool not only allows viewing of table structures, but also an advantage of this tool is that it can automatically create simple and complex SQL queries by selecting various tables and fields. SQL queries are commonly used to query database files to return results. Another great feature are the data conversions available; if different date/time encodings are encountered, these can be decoded, additionally if BLOB (Binary Large Object) is encountered, these can be displayed. Various other features are available, which make this the tool for the job.
Sleep sessions were first to be examined, however due to the amount of fields present within the ‘ZSLEEPSESSION’ table, only notable fields were requested using the following SELECT query:
SELECT ZSLEEPSESSION.Z_PK,
ZSLEEPSESSION.ZSESSIONEND,
ZSLEEPSESSION.ZSESSIONSTART,
ZSLEEPSESSION.ZSTATSLEEPQUALITY
FROM ZSLEEPSESSION
By executing the above query, the following results were returned:
Figure 3: Forensic Browser for SQLite – ‘ZSLEEPSESSION’ raw data
The raw data returned in Figure 3 contains various date/time stamps. These were found to be encoded in MAC absolute time (number of seconds since 1 January 2001 GMT); therefore these fields can now be decoded using a built in feature of Forensic Browser for SQLite. This allows the operator to format fields by their relevant encodings, for our example the end result being:
Figure 4: Forensic Browser for SQLite – ‘ZSLEEPSESSION’ decoded data
At this stage we now have a simple table, containing each individual event created by the user, including a start and finish date/time and furthermore a sleep quality statistics.
The ‘ZSTATSLEEPQUALITY’ column displays a decimal number, which represents a percentage for each individual sleep session. This percentage is based on the data results from the event logs for each session and whether or not the sessions were essentially good (minimal variables) or bad (many variables). Currently, no conversion has been applied to these entries, therefore the individual cells at present contain a lengthy decimal number which can be easily misinterpreted. Each percentage can provide a valuable insight as to whether, because of this we utilised the following SubStr (substring) and InStr (InString) function queries to interpret each decimal number and calculate the individual entry as a whole number:
SELECT ZSLEEPSESSION.Z_PK,
ZSLEEPSESSION.ZSESSIONEND,
ZSLEEPSESSION.ZSESSIONSTART,
ZSLEEPSESSION.ZSTATSLEEPQUALITY,
SubStr(ZSLEEPSESSION.ZSTATSLEEPQUALITY * 100, 0, InStr(ZSLEEPSESSION.ZSTATSLEEPQUALITY * 100, ‘.’)) || ‘%’ AS PERCENTAGE
FROM ZSLEEPSESSION
By executing the above queries, the following results were returned:
Figure 5: Forensic Browser for SQLite – ‘ZSTATSLEEPQUALITY’ raw data represented as a %
We now need to look into each event more thoroughly to identity the individual logs, which monitor the movement activity. Therefore, sleep events were next to be examined, again due to the amount of fields present within the ‘ZSLEEPEVENT’ table, only notable fields were requested using the following SELECT query:
SELECT ZSLEEPEVENT.Z_PK,
ZSLEEPEVENT.ZTYPE,
ZSLEEPEVENT.ZSLEEPSESSION,
ZSLEEPEVENT.ZINTENSITY,
ZSLEEPEVENT.ZTIME
FROM ZSLEEPEVENT
By executing the above query, the following results were returned:
Figure 6: Forensic Browser for SQLite – ‘ZSLEEPEVENT’ raw data
The raw data returned in Figure 5 yet again contains a date/time stamp, encoded in MAC absolute time, therefore this was also decoded using Forensic Browser for SQLite by formatting these fields by its relevant encoding, the end result being:
Figure 7: Forensic Browser for SQLite – ‘ZSLEEPEVENT’ decoded data
This is now where it starts to get interesting. In total we have 5 x sessions and 127 x event log entries. Each of the event log entries contained within ‘ZSLEEPEVENT’ contain a reference which corresponds with a link to a specific sleep session. As the sessions and logs are in separate tables, we can utilise the visual query designer to link the relevant reference within each notable table, by drawing an arrow to the corresponding column containing the reference (as seen in Figure 8):
Figure 8: Forensic Browser for SQLite – ‘Z_PK’ and ‘ZSLEEPSESSION’ arrow join to link event logs
When the link is created, this will trigger the software program to automatically create a LEFT JOIN SQL query, to connect the event log entries with their associated sessions:
SELECT ZSLEEPSESSION.Z_PK,
ZSLEEPSESSION.ZSESSIONEND,
ZSLEEPSESSION.ZSESSIONSTART,
ZSLEEPSESSION.ZSTATSLEEPQUALITY,
SubStr(ZSLEEPSESSION.ZSTATSLEEPQUALITY * 100, 0, InStr(ZSLEEPSESSION.ZSTATSLEEPQUALITY * 100, ‘.’)) || ‘%’ AS PERCENTAGE,
ZSLEEPEVENT.ZTYPE,
ZSLEEPEVENT.ZSLEEPSESSION,
ZSLEEPEVENT.ZINTENSITY,
ZSLEEPEVENT.ZTIME
FROM ZSLEEPSESSION
LEFT JOIN ZSLEEPEVENT ON ZSLEEPSESSION.Z_PK = ZSLEEPEVENT.ZSLEEPSESSION
By executing the above query, the following results were returned:
Figure 9: Forensic Browser for SQLite – ‘ZSLEEPSESSION’ and ‘ZSLEEPEVENT’ joined data
We now have an accurate log of the sleep sessions and their associated event log(s) (movement log). Even though the sleep quality field relates to overall experience, at specific time intervals different events occur and the intensity of these events are recorded within a separate column, ‘ZTYPE’.
The ‘ZTYPE’ column contains a numeric value aka flag, which relates to specific types of movement. The ‘ZTYPE’ essentially interprets how intense the movement was and logs this as an integer value. Following testing, we were able to determine what each of the recorded values represent, which then allowed us to translate this in text:
3 = Quick (High Intensity)
1 = Slow (Low Intensity)
9 = No Movement
We can utilise the following CASE query to interpret the numeric values as their true movement types:
SELECT ZSLEEPSESSION.Z_PK,
ZSLEEPSESSION.ZSESSIONEND,
ZSLEEPSESSION.ZSESSIONSTART,
ZSLEEPSESSION.ZSTATSLEEPQUALITY,
SubStr(ZSLEEPSESSION.ZSTATSLEEPQUALITY * 100, 0, InStr(ZSLEEPSESSION.ZSTATSLEEPQUALITY * 100, ‘.’)) || ‘%’ AS PERCENTAGE,
ZSLEEPEVENT.ZTYPE,
CASE ZSLEEPEVENT.ZTYPE WHEN 3 THEN ‘Quick (High Intensity)’ WHEN 1 THEN ‘Slow (Low Intensity)’ WHEN 9 THEN ‘No Movement’ END AS TYPEOFMOVEMENT,
ZSLEEPEVENT.ZSLEEPSESSION,
ZSLEEPEVENT.ZINTENSITY,
ZSLEEPEVENT.ZTIME
FROM ZSLEEPSESSION
LEFT JOIN ZSLEEPEVENT ON ZSLEEPSESSION.Z_PK = ZSLEEPEVENT.ZSLEEPSESSION
By executing the above query, the following results were returned:
Figure 10: Forensic Browser for SQLite – ‘ZTYPE’ integer flag(s) interpreted
The overall structure of the table at this point was specified to include only specific fields of note from the ‘ZSLEEPSESSION’ and ‘ZSLEEPEVENT’ tables which are relevant and these were as follows:
ZSLEEPSESSION = Sleep session id
ZSESSIONSTART = Sleep session start date/time
ZSESSIONEND = Sleep session end date/time
ZSTATSLEEPQUALITY = Overall % of sleep quality
TYPEOFMOVEMENT = Type of movement based on intensity level
ZINTENSITY = Intensity of movement event
ZTIME = Movement event log date/time
To clearly identity each of the columns identity, so that the OIC can review the data in a clear and concise format and more importantly so that the non-technical members of the jury can understand the contents, we utilised the following AS query to alter the alias to create a clear identity for each column category:
SELECT ZSLEEPSESSION.ZSESSIONEND AS Session_End,
ZSLEEPSESSION.ZSESSIONSTART AS Session_Start,
SubStr(ZSLEEPSESSION.ZSTATSLEEPQUALITY * 100, 0, InStr(ZSLEEPSESSION.ZSTATSLEEPQUALITY * 100, ‘.’)) || ‘%’ AS Overall_Sleep_Quality,
CASE ZSLEEPEVENT.ZTYPE WHEN 3 THEN ‘Quick (High Intensity)’ WHEN 1 THEN ‘Slow (Low Intensity)’ WHEN 9 THEN ‘No Movement’ END AS Movement_Type,
ZSLEEPEVENT.ZSLEEPSESSION AS Session_ID,
ZSLEEPEVENT.ZINTENSITY AS Log_Intensity_Value,
ZSLEEPEVENT.ZTIME AS Log_Date_Time
FROM ZSLEEPSESSION
LEFT JOIN ZSLEEPEVENT ON ZSLEEPSESSION.Z_PK = ZSLEEPEVENT.ZSLEEPSESSION
By executing the above query, the following results were returned:
Figure 11: Forensic Browser for SQLite – Alias changed to simplify column identity
The ‘Alias’ column becomes populated within the fields list panel below; this panel allows the user to edit and interpret not only columns titles, but furthermore interpretations of data sets via expressions:
Figure 12: Forensic Browser for SQLite – Alias changed to simplify column identity
Additional editing of the final results was conducted, using the built-in formatting features. The overall GUI of the software program at this stage was looking very interesting indeed (as seen in Figure 13) which displays a visual representation of the processes undertaken during the analysis of the application, including the final table structure displayed within the results panel.
Figure 13: Forensic Browser for SQLite – GUI at conclusion
We now have a very clear and concise table containing event logs recorded from the application, at specific date/time intervals. A graphical representation of an event (as seen in Figure 17) displays the processed data in a more simplified graph. If a high intensity event occurs, then this may indicate that a user was active/movement was strong, therefore may be regarded as awake. However, if a low intensity event occurs, then this may indicate that a user was still/movement was limited, therefore could be regarded as asleep.
Following further analysis, additional digital evidence contained within the ‘ZSLEEPSESSION’ table revealed ‘ZGPSLAT’ and ‘ZGPSLONG’ fields. These fields relate to location data, which if logged could assist in identifying device GPS location. It is important to note that these fields are only populated if the user opts to activate a specific setting within the application. When a user awakes, the application has the ability to check the weather for an update. When this occurs, the lat/long location of the device is logged to determine the weather for the specific location of the device. This could help prove where the device was, but only at the end of a sleep session. For this scenario the ‘ZGPSLAT’ and ‘ZGPSLONG’ fields were not populated with any data, as seen below:
Figure 14: Forensic Browser for SQLite – ‘ZSLEEPSESSION’ table GPS data fields
The following Figures 15-17 represent data from Sleep Session ID 1 in 3 different formats:
Figure 15: Sleep Cycle ‘ZSLEEPSESSION’ Table from ‘eventlogs.sqlite’ for Sleep Session ID 1
Figure 16: Sleep Cycle Optional ‘.csv’ Output Data from Application for Sleep Session ID 1
Figure 17: Sleep Cycle Graph for Movement Event/Intensity Logs from ‘App’ for Sleep Session ID 1
To conclude, we were able to create a report directly from the software program, containing all of the recorded sessions and associated data logs from ‘eventlog.sqlite’, which included encountered movement(s) of various intensities during specific date/time periods. From the decoded data, we were able to determine that during the notable time of 04:30am on 31/01/2015, the data indicated that high intensity movements were encountered and these were indicative of someone being active/awake, as opposed to someone being asleep. The session which the notable event log belongs to was ended at 06:58am on 31/01/2015 and within 5 minutes of the session ending, an outgoing SMS message was sent from the device by the suspect to their friend advising the deed was done. CDRs were obtained which confirmed the IMEI of the device which transmitted the message our exhibit.
As previously stated it is important to delve into application data if specific applications are notable to your case. More importantly, we have uncovered data which has not been automatically decoded on the fly and this may prove to be crucial to the investigation.
Software Tool References:
Sanderson Forensics – Forensic Browser for SQLite (SQLite Forensic Toolkit)
Coral Creek Software – SQLite Expert Personal
Maël Hörz – HxD
Cellebrite – UFED Physical Analyzer