Reverse Engineering Evernote Penultimate (or: When is a picture not a picture?)

In this post Alex Caithness takes a look at “Penultimate” on the iPad and discovers that a picture paints a thousand words… but only once you work out how that picture is stored.

Recently we came up against an iPad note-taking application by the name of “Penultimate”. As a user it’s actually really quite a nice app to use: you can create multiple “notebooks” each with as many pages as you require on which you can jot down notes, draw diagrams or just doodle with a stylus or your finger. For a user it’s nice, simple and intuitive, for an intrepid forensic analyst, it’s not quite as straight-forward…

Penultimate Hello World Screenshot

Penultimate Screenshot

After installing the application on a test iPad, creating a couple of notebooks and having a doodle in each of them, I performed an extraction of the device. Poking through the application’s folder, this is the layout of the files which we can acquire with a backup-style acquisition:

 │     com.cocoabox.penultimate.plist
 └───Private Documents
 │     notebookList
 │     notebookListBackup
 │     100F53F2-BD1F-4046-8104-714E42264DAE

Now, ideally what we’d liked to have seen at this point are some image files, a nice friendly JPEG, GIF or PNG or two would have been just lovely. But no, it’s not going to be that easy (and, in fairness, if it had been I wouldn’t be bothering with this blog would I?).

So what do we actually have? Well, we can see the familiar “Library” folder in which we find the (also familiar) “Preferences” folder which contains a property list, the contents of which are mostly mundane (though we do have application launch counts and dates which may be of interest in some situations). More interesting is the “Private Documents” folder and the files and folders which are contained within it.

In the root of the Private Documents folder is a binary property list file named “notebookList”. It is a ‘NSKeyedArchiver’ plist, which means, as we discussed in a previous blog, it needs some additional processing in order for us to see the actual structure of the data. Luckily I had PIP and the “ccl_bplist” Python module to hand, both of which can perform that transformation. With the data structure untangled I proceeded with digging around to see what, if anything, we could extract from the “notebookList”.

Among some less interesting data, the root object contains a list of the notebook objects which are stored by the device, under the “notebooks” key.

notebookList in PIP

notebookList in PIP

Each of the notebook objects (which are represented by dictionaries in the property list) contain the keys: ‘name’, ‘title’, ‘pageNames’, ‘created’, ‘modified’,  ‘changeCount’, ‘blankPages’, ‘creatingDeviceId’, ‘editingPageName’, ‘pageDataIsCooked’, ‘versionMajor’, ‘versionMinor’, ‘pagePaperStyles’, ‘paperStyle’, ‘imported’ , ‘coverColor’, ‘originalName’.

The first few there are of immediate interest: the value of “title” does indeed contain the title of the notebooks that I created during my testing and the “created” and “modified” timestamps also ring true. The values of the “name” keys are familiar as well, albeit for a different reason; in my test data we see the “name” values “DD47D226-0CB3-460F-A0F9-3A7E2A795B3D” and “23FDA4FF-CE5E-4353-8D9A-B0C3E3E8AAE7” – matching the directory names that were extracted underneath the “notebooks” folder (see above). Beyond this, the “pageName” key contains a list of values which also match the names of files in each of the “name” directories.

So, with the “notebookList” file we have some useful metadata and a helpful guide to how the other files are organised, but there’s still no sign of the content of the notes themselves. Delving deeper into the folder structure, our next stop is one of the files which was named in the “pageName” list mentioned above.

Opening one of the “page” files we find another “NSKeyedArchiver” property list. After unravelling the structure of the file we find a top-level object containing further metadata (including a “blankDate” which appears to match the “created” timestamp reported in the “notebookList” and the dimensions of the note) along with a list of “layers”. Each of the “layer” objects (again represented by dictionaries) have keys for the layer’s colour (more on that later) the layer’s dimensions and a list of “layerRects” – sections of the layer where the user has drawn their notes; and that’s where we finally find the image itself.

Sort of.

Structure of the page object

Structure of the page object

Each of the “layerRects” objects are represented by (and this shouldn’t be a surprise by now) a dictionary. There are two keys: firstly “rect” which contains a string of two sets of “x,y” co-ordinates: the bounds of this layerRect on the page. The second key is “values” which requires a little extra explanation. As noted in my previous post on NSKeyedArchiver files, their function is to represent a serialisation of objects; the object under the “values” key is a “CBMutableFloatArray” which is programmer talk for a list of floating-point numbers. With this in mind we quite reasonably expect to see just that – a list of floating point numbers; but no, instead we get a data field containing binary data! As nothing else in this data had been straight-forward this was disappointing, but by this point didn’t surprise me. Floating-point numbers (like any numerical data) can be represented quite happily in binary, so I set about trying to turn the binary data into a list of floating-point numbers. I extracted one of these data fields and with a nice Python one-liner did just that:

>>> struct.unpack("<18f", data)
(282.0, 589.0, 5.506037712097168, 281.8666687011719, 588.2999877929688, 5.506037712097168, 281.73333740234375, 587.5999755859375, 5.506037712097168, 281.5999755859375, 586.9000244140625, 5.506037712097168, 281.4666442871094, 586.2000122070312, 5.506037712097168, 281.33331298828125, 585.5, 5.506037712097168)

So now we can see the numbers, but what did they mean? The meaning becomes clearer if we group them into threes:

282.0, 589.0, 5.506037712097168
281.8666687011719, 588.2999877929688, 5.506037712097168
281.73333740234375, 587.5999755859375, 5.506037712097168
281.5999755859375, 586.9000244140625, 5.506037712097168
281.4666442871094, 586.2000122070312, 5.506037712097168
281.33331298828125, 585.5, 5.506037712097168

What we have here is sets of x-y co-ordinates and another value (the meaning of which will become clearer later). So, here, finally is our drawing; stored as a list of co-ordinates rather than any conventional graphics format – the combination of the points in the “layerRects” in each of the layer objects gives you the full picture.

But how to present this data? A list of co-ordinates like this is of little to no use; we want to see the image itself. So I got to thinking: how does the application treat these co-ordinates? Well, when the user draws in the application they are essentially ‘painting’: moving a circular ‘brush’ around the screen making up the lines in the drawing, so if I was able to plot a circle at each of these co-ordinates perhaps I would see the picture? This thought process also gave me an idea as to what the third value in each of the co-ordinate groupings might represent: part of what makes the drawings look natural is that the “line” that is drawn is not of a uniform width, it grows and shrinks with the speed at which the finger or stylus moves, giving an impression of weight – perhaps the final value related to this?

So how to plot these co-ordinates? First of all I looked at various Python imaging libraries (PIL and PyGame both came on my radar), but there were issues with both (especially with PIL which still lacks a proper Py3k port) so I turned my attention to an alternative solution: SVG. SVG (Scalable Vector Graphics) is a graphics format specified by the W3C; it uses XML to describe 2D graphics. Because SVG just uses XML, no imaging libraries would be required; I could simply generate textual mark-up describing where each of my circular points should be plotted on the screen. Taking the data above extracted above as an example, the mark-up would be along the lines of:

<!DOCTYPE svg>
<svg height="865" version="1.1" viewBox="0 0 718 865" 
     width="718" xmlns="">
    <circle cx="282.0" cy="589.0" 
            fill="#000000" r="5.506037712097168"/>
    <circle cx="281.8666687011719" cy="588.2999877929688" 
            fill="#000000" r="5.506037712097168"/>
    <circle cx="281.73333740234375" cy="587.5999755859375" 
            fill="#000000" r="5.506037712097168"/>
    <circle cx="281.5999755859375" cy="281.5999755859375" 
            fill="#000000" r="5.506037712097168"/>
    <circle cx="281.4666442871094" cy="586.2000122070312" 
            fill="#000000" r="5.506037712097168"/>
    <circle cx="281.33331298828125" cy="585.5" 
            fill="#000000" r="5.506037712097168"/>

Each “circle” tag has an x and y co-ordinate (“cx” and “cy”) a fill colour (here expressed as an html colour code – black in this case) and a radius (“r”). Running some tests gave some good output; but things weren’t quite right, firstly, and most importantly, the image was mirrored in the y-axis, presumably caused by a difference of opinion between Penultimate and SVG as to where the y-axis originates, an easy fix (just subtract the ‘y’ value from the height of the image and use that value instead). Also, the lines in the writing all looked very chubby, making writing very tricky to read. Theorising that this was caused by Penultimate storing diameters and SVG requiring radii, I halved the value which improved things, but comparing the output with what I could see on the screen things still weren’t quite looking right so on a whim I halved the value again which made things look right (I’m not entirely sure why it should be the case that you have to quarter the value – it may be to do with penultimate adding a ‘feathered’ fade to the brush which increases it’s diameter though).

I created a proof-of-concept Python script to check that my thinking was correct and I was pleased to see that the output now matched what I could see on the iPad’s screen, save for the fact that the iPad was in glorious Technicolor and my script’s output was monochrome. I mentioned previously that the “layer” objects contained colour information under the unsurprisingly named “color” key.

The object stored in the “color” key has values for red, blue and green – each one a value between 0 and 1. Those readers who have had even the briefest dalliance with graphic manipulation programs will be familiar with colour-sliders: combining red, green and blue in different proportions in order to generate different colours, which is what these values represented. My SVG generated output was working with HTML colour codes which are made up of 3 bytes, each representing an amount of red, green and blue, this time using values from 0x00 to 0x0FF, to get colour into my output, all I had to do is multiply 0xFF by the correct value from the “layer” object’s colour fields and recombine those values into an HTML colour code. I modified my script and now the output reflected both the form and colour displayed by the App on the iPad.

Comparison between iPad and script output 1

Comparison between iPad and script output (1)

Comparison between iPad and script output 2

Comparison between iPad and script output (2)

Working on extracting data from Penultimate was a particularly enjoyable experience as it required a combination of a number of different concepts and led to the use of a number of different technologies to create a solution to automate the extraction of the data, which when it all boiled down was satisfyingly simple.

And as to the question: when is a picture not a picture? Well, quite simply: when it’s a series of serialised floating-point co-ordinate triplets representing points on a page, broken up into rectangles on a layer on a page which is stored in a NSKeyedArchiver property list file (obviously!).

If you have any questions, comments or queries regarding this post, as always you can contact us by dropping an email to or by leaving a comment below.

Update: As requested I’ve uploaded the script for research purposes. You can find it here: 

Alex Caithness, CCL Forensics


Parsing Apple System Log (ASL) files on iOS and OSX for Fun and Evidence (and a Python script to do it for you)

(If you’re dying to get stuck in and are only after the links to the Python scripts, they can be found at the bottom of the post!)

After every update to iOS I like to take a file system dump of one of our test iDevices and have a poke around to see what’s changed and what’s new. Recently, on one of my excursions around the iOS file system, I came across something that looked promising that I hadn’t dug into before: a bunch of files with the “.asl” extension which were located on the data partition in “log/DiagnosticMessages”. There were lots of them too –each with a file name referring to a particular date – they went back months!

DiagnosticMessages file listing

Log Files!

“Loads of lovely log files!” I thought to myself as I excitedly dropped one of the files into my current text editor of choice (Notepad++ if you’re interested) only to be disappointed by what was clearly a binary file format.

ASLDB File in a text editor


So I headed over to Google and entered some hopeful sounding search queries and came across a very useful blog post ( which described the role of ASL files on OSX and listed some ways for accessing the logs from within OSX, but I was interested in gaining a better understanding of the file format (besides, the nearest Mac to me was on a different floor!).

A little more digging revealed that the code that governed the ASL logging, and the files it generated were part of the Open Source section of OSX, as a result I was able to view the code that was actually responsible for creating the file – my luck was looking up!

The two files I was particularly interested in were “asl.h” (most recent version at time of posting: and “asl_file.h” (most recent version at time of posting: C header files are great; basically, their purpose is to define the data structures that are subsequently used in the functional code, so when it comes to understanding file formats, quite often they’ll tell you all you need to know without having to try and follow the flow of the actual program. Better yet, these files were pretty well commented. I know that not everyone reading this is going to want to read through a bunch of C code, so I’ll summarise the file format below (all numeric data is big endian):

First, the file Header:

Offset Length Data Type Description
0 12 String “ASL DB” followed by 6 bytes of 0x00
12 4 32bit Integer File version (current version is: 2)
16 8 64bit Integer File offset for the  first record in the file
24 8 64bit Integer Unix seconds timestamp, appears to be a file creation time
32 4 32bit Integer String cache size (not 100% sure what this refers to, may be maximum size for string entries in the records)
36 8 64bit Integer File offset for the last record in the file
44 36 Padding Should all be 0x00 bytes

So nothing too ominous there, although all of those pad-bytes at the end of the header suggest redundancy in the file spec in case apple ever fancy changing something. Indeed the fact that the header tells us that we’re on version 2 of the file format suggests that this has already happened.

The records in the file are arranged in a “doubly linked list”, that is, that every record in the file contains a reference (ie. the file offset of) the next and previous records.  From a high level, the records themselves are made up of a fixed length data section, followed by a variable length section which allows the storage of additional data in a key-value type structure, finally followed by the offset of the previous record. The table below explains the structure in detail.

NB: The string storage mechanism the records use is a little bit…interesting – I’ll explain in detail later in this post, but for now if you see a reference to an “ASL String”, I mean one of these “interesting” strings!

Offset Length Data Type Description
0 2 Padding 0x00 0x00
2 4 32bit Integer Length of this record (excluding this and the previous field)
6 8 64bit Integer File offset for next record
14 8 64bit Integer Numeric ID for this record
22 8 64bit Integer Record timestamp (as a Unix seconds timestamp)
30 4 32bit Integer Additional nanoseconds for timestamp
34 2 16bit Integer Level (see below)
36 2 16bit Integer Flags
38 4 32bit Integer Process ID that sent the log message
42 4 32bit Integer UID that sent the log message
46 4 32bit Integer GID that sent the log message
50 4 32bit Integer User read access
54 4 32bit Integer Group read access
58 4 32bit Integer Reference PID (for processes under the control of launchd)
62 4 32bit Integer Key-Value count: The total number of keys and values in the key-value storage of the record
66 8 ASL String Host that the sender belongs to (usually the name of the device)
74 8 ASL String Name of the sender (process) which send the log message
82 8 ASL String The sender’s facility
90 8 ASL String Log Message
98 8 ASL String The name of the reference process (for processes under control of launchd)
106 8 ASL String The session of the sender (set by launchd)
114 8 * Key-Value count ASL String[Key-Value count] The key-value storage: A key followed by a value, followed by a key followed by a value… and so on. All keys and values are strings

The level field mentioned above will have a numerical value which refers to the levels shown below:

Level Meaning
0 Emergency
1 Alert
2 Critical
3 Error
4 Warning
5 Notice
6 Info
7 Debug

As mentioned, the “ASL String” data type is a little odd. The ASL fields above take up 8 bytes, if the most significant bit in the 8 bytes is set (ie is 1), the rest of the most significant byte gives the length of the string, which occupies the remaining 7 bytes (unused bytes are set to 0x00). Conversely, if the top bit in the ASL String data type is not set (ie. Is 0) the entire 8 bytes should be interpreted as a 64bit Integer which gives the file offset where the string can be found. The string will be stored thusly:

Offset Length Data Type Meaning
0 2 Padding Padding bytes 0x00 0x01
2 4 32bit Integer String length
6 String length UTF8 String (nul-terminated) The string data

In order to get a better grip of what can be held in these files I decided to create a Python module to read these files and used it to dump out the contents of the ASL files I found on the iPhone.

Running the script

Running the script

Output from the script (iOS)

A snippet of the output produced by processing an iPhone’s ‘DiagnosticMessages’ folder

The first thing that struck me after running the script was the volume of messages: 16161 log messages spanning 10 months – and this was on a test handset which had lay idle for weeks at a time. The second thing was the prevalence of messages sent by the “powerd” daemon, over 87% of the messages had been sent by this process. The vast majority of these messages related to the device waking and sleeping – not through user interaction, but while the device was idle. Most of these “Wake” events occurred 2-5 minute apart, presumably to allow brief data connectivity to receive updates and push messages from apps.

Output from the script (iOS powerd messages)

Some powerd Wake and Sleep messages

The key thing that interested me about these messages was that they also noted the current battery-charge percentage in their text: this is the sort of data that just begs to be graphed, so I knocked up a little script which utilised the parsing module I had just written to extract just this data and present it in a graph-friendly manner.

Graph Friendly powerd Data

Graph Friendly Data

After graphing it (you want to use a scatter graph in Excel for this, not line as I discovered after some shouting at my screen) you are left with a graph which gives you some insight into the device’s use.

iOS Battery Use Graph

Some iPhone Power Usage (click for full-size)

The graph above shows around 3 weeks of battery usage data from the test handset. As noted previously, this test device would lay idle for days at a time (as suggested by the gentle downward gradients) but there were periods when the handset was in use, as shown by the steeper downward gradients on the 26th and 27th of April, which mostly took place within office hours. You can also clear see the points where the device was plugged in to be charged, suggested by the very steep upward gradients. As noted the power messages occur around ever 2-5 minutes, so the resolution is actually fairly good. The exception to this is while the device is plugged in as it no longer needs to sleep to preserve battery charge; typically I only saw an event when charging began and another when the device was unplugged and the battery began to discharge again.

There are a few other messages in the iOS ASL log that look interesting, but at this time I don’t have enough nice control data to make much of them. One thing that did hearten me somewhat was the fact that on the few extractions I’ve had the opportunity to take a look at from later revisions of iOS 5, there did seem to be some extra processes that were logging messages, so it’s my hope that we’ll see more and more useful data make its way into the ASL logs on iOS.

In addition to looking at iOS ASL files, I thought I’d take a look at some from an OSX installation. Pulling the logs from the “var/log/asl” on Lion (10.7.3) and running the parsing script across the whole directory brought back a far more varied selection of messages.

Output from the script (OSX)

Variety is the spice of life.

The number of records returned was actually far less than on iOS, partially due to the iOS “powerd” being so chatty, but more crucially because OSX tidies up its logs on a weekly basis. That’s not to say that you will only recover a week’s worth of logs though – on this test machine I recovered logs spanning 7 months. Rather, OSX has short-term log files (those with file names which begin with a timestamp) which have a shelf-life of a week and long term log files (those with file names which begin with “bb” followed by a timestamp). The “bb” in the long term log’s file name presumably stands for “best before” and the date, which is always in the future, is the date that the file should be cleared out. The short term log files tend to hold more “intimate” entries, often debug messages sent from 3rd party applications; the long term logs err more on the side of system messages. One particularly useful set of messages in the long term log are records pertaining to booting, shutting down, logins and logouts (hibernating, waking and failed logins are recorded too, but they end up in the short-term logs).

(As an aside:  one of my favourite things that I discovered when looking through these logs was the action of waking a laptop running OSX by wiggling a finger on the trackpad is recorded in the logs as a “HID Tickle”. Lovely.)

Like I did with the iOS power profiling, I put together a script which extracted these login and power records and timelines them.

OSX Login and power timeline

Login and power timeline

A couple of things worth noting beyond the basic boot/shutdown/login records: firstly when the device wakes it records why it happened – this can be quite specific: a USB device, the lid of a laptop being opened, the power button being pressed, etc. Secondly, you can see terminal windows (tty) being opened and closed as opening a terminal window involves logging in to a terminal session (OSX does this transparently, but it’s still logged).

We’ve released the scripts mentioned in this post to the community and they can be downloaded from The “ccl_asl” script is both a command line utility for dumping the contents of ASL files as well as a fully featured class module which you can use to write scripts along the lines of the battery profiler and login timeline scripts.

ASL files are, on the one hand, fairly dry system logs, but on the other, with a little work you can harvest some really insightful behavioural intelligence. As always if you have any questions, comments or suggestions you can contact us on or leave a comment below.

Alex Caithness

The Forensic Implications of SQLite’s Write Ahead Log

By Alex Caithness, CCL-Forensics

SQLite is a popular free file-based database format which is used extensively both on desktop and mobile operating systems (it is one of the standard storage formats available on both Android and iOS). This article sets out to examine the forensic implications, both pitfalls and opportunities, of a relatively new feature of the database engine: Write Ahead Log.

Before we begin, it is worth taking a moment to describe the SQLite file format. Briefly, records in the database are stored in file which in SQLite parlance is called the ‘Database Image’. The database image is broken up into “pages” of a fixed size (the size is specified in the file header). Each page may have one of a number of roles, such as informing the structure of the database, and crucially holding the record data itself. The pages are numbered internally by SQLite starting from 1.

Historically SQLite used a mechanism called “Rollback Journals” for dealing with errors occurring during use of the database. Whenever any data on a page of the database was to be altered, the entire page was backed up in a separate journal file. At the conclusion of a successful transaction the journal file would be removed; conversely if the transaction was interrupted for any reason (crash, power cut, etc.) the journal remained. This means that if SQLite accesses a database and finds that a journal is still present something must have gone wrong and the engine will restore the database to its previous state using the copies of pages in the journal, avoiding corrupted data.

From version 3.7.0 of the SQLite engine an alternative journal mechanism was introduced called “Write Ahead Log” (ubiquitously shortened to “WAL”). WAL effectively turned the journal mechanism on its head: rather than backing up the original pages then making changes directly to the database file, the database file itself is untouched and the new or altered pages are written to a separate file (the Write Ahead Log). These altered or new pages will remain in the WAL file, the database engine reading data from the WAL in place of the historic version in the main database. This continues until a “Checkpoint” event takes place, finally copying the pages in the WAL file into the main database file. A Checkpoint may take place automatically when the WAL file reaches a certain size (by default this is 1000 pages) or performed manually by issuing an SQL command (“PRAGMA wal_checkpoint;”) or programmatically if an application has access to the SQLite engine’s internal API.

Initial state: No pages in the WAL

Initial state: No pages in the WAL

Page Altered - new version written to WAL

Page 3 is altered. The new version of the page is written to the WAL and the database engine uses this new version rather than the old version in the database file itself.


A checkpoint operation takes place and the new version of the page is written into the database file.

It is possible to detect whether a database is in WAL mode in a number of ways: firstly this information is found in the database file’s header; examining the file in a hex editor, the bytes at file offset 18 and 19 will both be 0x01 if the database is using the legacy rollback journal or 0x02 if the database is in WAL mode. Secondly you can issue the SQL command “PRAGMA journal_mode;” which will return the value “wal” if the database is in WAL mode (anything else indicates rollback journal). However, probably the most obvious indication of a database in WAL mode is the presence of two files named as “<databasefilename>-wal” and “<databasefilename>-shm” in the same logical directory as the database (eg. if the database was called “sms.db” the two additional files would be “sms.db-wal” and “sms.db-shm”).

The “-wal” file is the actual Write Ahead Log which contains the new and updated database pages, its structure is actually fairly simplistic. The “-wal” file is made up of a 32 byte file header followed by zero or more “WAL frames”. The file header contains the following data:

Offset Size Description
0 4 bytes File signature (0x377F0682 or 0x377F0683)
4 4 bytes File format version (currently 0x002DE218 which interpreted as a big endian integer is 3007000)
8 4 bytes Associated database’s page size (32-bit big endian integer)
12 4 bytes Checkpoint sequence number (32-bit big endian integer which is incremented with every checkpoint, starting at 0)
16 4 bytes Salt-1 Random number, incremented with every checkpoint *
20 4 bytes Salt-2 Random number, regenerated with every checkpoint
24 4 bytes Checksum part 1 (for the first 24 bytes of the file)
28 4 bytes Checksum part 2 (for the first 24 bytes of the file)

* In testing it was found that although the official (and at the time of writing, up to date) command line version of SQLite v3.7.11 behaved correctly, when using SQLite Expert v3. this value appeared to be regenerated after each checkpoint (which is assumed by the author to be incorrect behaviour)

The WAL Frames that follow the header consist of a 24 byte header followed by the number of bytes specified in the file header’s “page size” field which is the new or altered database page. The Frame Header takes the following form:

Offset Size Description
0 4 bytes Database page number (32-bit big endian integer)
4 4 bytes For a record that marks the end of a transaction (a commit record) this will be a 32-bit big endian integer giving the size of the database file in pages, otherwise 0.
8 4 bytes Salt-1, as found in the WAL header at the time that this Frame was written
12 4 bytes Salt-2, as found in the WAL header at the time that this Frame was written
16 4 bytes Checksum part 1 – cumulative checksum up through and including this page
20 4 bytes Checksum part 2 – cumulative checksum up through and including this page

There are a number of potential uses and abuses for the WAL file in the context of digital forensics, but first, the behaviour of SQLite while in WAL mode should examined. A number of operations were performed on a SQLite database in WAL mode. After each operation the database file along with its “-shm” and “-wal” files were copied, audited and hashed so that their states could be examined.

Step 1: Create empty database with a single table:

8a9938bc7252c3ab9cc3da64a0e0e06a *database.db
b5ad3398bf9e32f1fa3cca9036290774 *database.db-shm
da1a0a1519d973f4ab7935cec399ba58 *database.db-wal

1,024       database.db
32,768      database.db-shm
2,128       database.db-wal

WAL Checkpoint Number:  0
WAL Salt-1: 3046154441
WAL Salt-2: 220701676

Viewing the database file using a hex editor we find a single page containing the file header and nothing else. As noted as well as creating a database file, a table was also created, however this data was written to the WAL in the form of a new version of this page. The WAL contains two frames, this new version of the first page in addition to a second frame holding an empty table page. When accessing this database through the SQLite engine this information is read from the “-wal” file transparently and we see the empty table, even though the data doesn’t appear in the database file itself.

Step 2: Force a checkpoint using PRAGMA command:

dd376606c00867dc34532a44aeb0edb6 *database.db
1878dbcefc552cb1230fce65df13b8c7 *database.db-shm
da1a0a1519d973f4ab7935cec399ba58 *database.db-wal

2,048       database.db
32,768      database.db-shm
2,128       database.db-wal

WAL Checkpoint Number:  0
WAL Salt-1: 3046154441
WAL Salt-2: 220701676

Using the pragma command mentioned above, the database was “checkpointed”. Accessing the database through SQLite we see no difference to the data but examining the files involved, we can clearly see that the database file has changed (it has different hash) furthermore it has grown. Looking inside the database file we can see the two pages from the “-wal” file have now been written into the database file itself and SQLite will be reading this data from here rather than the “-wal” file.

The WAL Checkpoint number and salts were not changed at this point, as we will see they are altered the next time that the WAL is written to.

Another interesting observation is that the “-wal” file was left completely unchanged during the checkpoint process – a fact that will become extremely important in the next step.

Step 3: Insert a single row:

dd376606c00867dc34532a44aeb0edb6 *database.db
6dc09958989a6c0094a99a66531f126f *database.db-shm
e9fc939269dbdbfbc157d8c12be720ed *database.db-wal

2,048       database.db
32,768      database.db-shm
2,128       database.db-wal

WAL Checkpoint Number:  1
WAL Salt-1: 3046154442
WAL Salt-2: 534753839

A single row was inserted into the database using a SQL INSERT statement. Once again we arrive at a situation where the database file itself has been left untouched, evidenced by the fact that the database file’s hash hasn’t altered since the last step.

The “-wal” file hasn’t changed size (so still contains two WAL frames) but clearly the contents of the file have changed. Indeed, examining the file in a hex editor we find that the first frame in the file contains a table page containing the newly inserted record as we would expect. What is interesting is that the second frame in the file is the same second frame found in the file in the previous two steps. After a checkpoint the “-wal” file is not deleted or truncated, it is simply reused, frames being overwritten from the top of the file.

Examining the Frame’s headers we see the following:

Frame Page Number Commit Size Salt-1 Salt-2
1 2 2 3046154442 534753839
2 2 2 3046154441 220701676

Both frames relate to the same page in the database but their salt values differ. As previously noted these two salt values are copied from the WAL file header as they are at the time of writing. Salt-2 is regenerated upon each checkpoint, but key here is Salt-1 which is initialised when the WAL is first created and then incremented upon each checkpoint. Using this value we can show that the page held in second frame of the WAL is a previous version of page held in the first frame: we can begin to demonstrate a timeline of changes to the database.

Step 4: Force a checkpoint using PRAGMA command:

704c633fdceceb34f215cd7fe17f0e84 *database.db
a98ab9ed82393b728a91aacc90b1d788 *database.db-shm
e9fc939269dbdbfbc157d8c12be720ed *database.db-wal

2,048       database.db
32,768      database.db-shm
2,128       database.db-wal

WAL Checkpoint Number:  1
WAL Salt-1: 3046154442
WAL Salt-2: 534753839

Once again a checkpoint was forced using the PRAGMA command.  As before the updated pages in the WAL were written into the database file and this operation had no effect on the contents of the “-wal” itself. Viewing the database using the SQLite engine shows the same data as in the previous step.

Step 5: Insert a second row, Update contents of the first row:

704c633fdceceb34f215cd7fe17f0e84 *database.db
d17cf8f25deaa8dbf4811b4d21216506 *database.db-shm
ed5f0336c23aef476c656dd263849dd0 *database.db-wal

2,048       database.db
32,768      database.db-shm
2,128       database.db-wal

WAL Checkpoint Number:  2
WAL Salt-1: 3046154443
WAL Salt-2: 3543470737

A second row was added to the database using a SQL INSERT statement and the previously added row was altered using an UPDATE statement.

Once again, and as is now fully expected, the database file is unchanged, the new data has been written to the WAL. The WAL contains two frames: The first holds a table page containing the original record along with our newly added second record; the second frame holds a table page containing the updated version of our original record along with the new, second record. Examining the frame headers we see the following:

Frame Page Number Commit Size Salt-1 Salt-2
1 2 2 3046154443 3543470737
2 2 2 3046154443 3543470737

In this case both frames contain data belonging to the same page in the database and the same checkpoint (Salt-1 is the same for both frames); in this case the order of events is simply detected by the order in which the frames appear in the file – they are written to the file from the top, down.

Step 6: Insert a third row:

704c633fdceceb34f215cd7fe17f0e84 *database.db
5ac6d9e56e6bbb15981645cc6b4b4d6b *database.db-shm
672a97935722024aff4f1e2cf43d83ad *database.db-wal

2,048       database.db
32,768      database.db-shm
3,176       database.db-wal

WAL Checkpoint Number:  2
WAL Salt-1: 3046154443
WAL Salt-2: 3543470737

Next, a third row was added to the database using an INSERT statement. Viewing the database logicaly with the SQLite engine we see all three records. While database file remains unchanged, the “-wal” file now contains 3 frames: the first two are as in the previous step with the third and final new frame holding a table page with all three records. The frame headers contain the following information:

Frame Page Number Commit Size Salt-1 Salt-2
1 2 2 3046154443 3543470737
2 2 2 3046154443 3543470737
3 2 2 3046154443 3543470737

We now have three versions of the same page, as before the sequence of events is denoted by the order they occur in the file.

Step 7: Force a checkpoint using PRAGMA command:

04a16e75245601651853fd0457a4975c *database.db
05be4054f8e33505cc2cd7d98c9e7b31 *database.db-shm
672a97935722024aff4f1e2cf43d83ad *database.db-wal

2,048       database.db
32,768      database.db-shm
3,176       database.db-wal

WAL Checkpoint Number:  2
WAL Salt-1: 3046154443
WAL Salt-2: 3543470737

As we have observed before the checkpoint results in to the up-to-date records being written into the database, the “-wal” file is unaffected.

Step 8: Delete A Row:

04a16e75245601651853fd0457a4975c *database.db
dca5c61a689fe73b3c395fd857a9795a *database.db-shm
3b518081a5ab4a7be6449e86bb9c2589 *database.db-wal

2,048       database.db
32,768      database.db-shm
3,176       database.db-wal

WAL Checkpoint Number:  3
WAL Salt-1: 3046154444
WAL Salt-2: 2798791151

Finally in this test, the second record in the table (the record added in Step 5) was deleted using an SQL DELETE statement. Accessing the database using the SQLite engine shows that the record is no longer live in the database.

As per expectations the database file is unaffected by this operation, the changes instead being written to the WAL. The “-wal” file contains three frames:  the first frame holds a table page with the second record deleted (the data can still be seen, and could be recovered using a tool such as Epilog, however the metadata on the page shows that the record is not live). The remaining two pages are identical to the final two frames in the previous step. Examining the frame headers we see the following:

Frame Page Number Commit Size Salt-1 Salt-2
1 2 2 3046154444 2798791151
2 2 2 3046154443 3543470737
3 2 2 3046154443 3543470737

Here we once again see three frames all containing data from the same database page, this time the most recent version of the page is found in frame 1 as it has the highest Salt-1 value; the other two frames have a lower Salt-1 value and are therefore older revisions; as they both share the same Salt-1 value we apply the “position in file” rule, the later in the file the frame occurs, the newer it is. So in order of newest to oldest the frames are ordered: 1, 3, 2.

Summarising the findings in this experiment:

  • Altered or new pages are written to the WAL a frame at a time, rather than the database file
  • The most up-to-date pages in the WAL are written to the database file on a Checkpoint event – this operation leaves the “-wal” file untouched
  • After a Checkpoint, the “-wal” file is reused rather than deleted or truncated,  with new frames
  • Multiple frames for the same database page can exist in the WAL, their relative ages can be derived by first examining the frame header’s Salt-1 value with newer frames having higher values. Where multiple frames have the same Salt-1, their age is determined by their order in the WAL, with newer frames occurring later

Pitfalls and Opportunities

The most obvious opportunity afforded by the Write Ahead Log is the potential for time-lining of activity in database. To prove the concept, a small Python script was written which would automate the analysis of the frames in a WAL file and provide a chronology of the data; a sample output is shown below:

Header Info:
    Page Size: 1024
    Checkpoint Sequence: 3
    Salt-1: 3046154444
    Salt-2: 2798791151

Reading frames...

Frame 1 (offset 32)
    Page Number: 2
    Commit Size: 2
    Salt-1: 3046154444
    Salt-2: 2798791151

Frame 2 (offset 1080)
    Page Number: 2
    Commit Size: 2
    Salt-1: 3046154443
    Salt-2: 3543470737

Frame 3 (offset 2128)
    Page Number: 2
    Commit Size: 2
    Salt-1: 3046154443
    Salt-2: 3543470737

Unique Salt-1 values:

Chronology of frames (oldest first):

Page Number: 2
    Frame 2
    Frame 3
    Frame 1

With further work it should be possible to display a sequence of insertions, updates and deletions of records within a database – a feature which is a top priority for the next update of Epilog. Even without the ability to timeline, it is clear that deleted records can be stored and recovered from the WAL (functionality already present in Epilog).

One behaviour which hasn’t been described in full so far is that a database file in WAL mode isolated from its associated “-wal” file is, in almost all circumstances, a valid database in its own right. For example, consider the test database above as it is at the end of Step 8. If the database file was moved to another directory, as far as the SQLite database engine is concerned this is a complete database file. If this isolated database file was queried, the data returned will be that which was present at the last checkpoint (in our test case, this would be the 3 live records present at the checkpoint performed in step 7).

This raises an important consideration when working with a SQLite database contained in a disk image or other container (eg. a TAR archive): if the database file is extracted from the image or container without its associated WAL files, the data can be out-of-date or incomplete. The other side of the coin is that the “full up-to-date” version of the data (viewed with the WAL present) may lack records present in the isolated database file because of deletions pending a checkpoint. There is, then, an argument for examining databases both ways: complete with WAL files and isolated as it may be possible to obtain deleted records “for free”.

Summing Up

The Write Ahead Log introduced in SQLite 3.7 may afford digital forensics practitioners new opportunities to extract extra data and behaviour information from SQLite databases; however the mechanism should be understood to get the most of the new opportunities and avoid confusion when working with the databases.

If you have any comments or questions, please leave a comment below or get in touch directly at


SQLite File Format

Write Ahead Log

Alex Caithness, CCL-Forensics

New version of PIP (XML and plist parser) coming soon

Our ever-popular XML and plist parsing tool, PIP, is coming of age with a new, improved version.

Currently in beta-test, and with the updated version available free to current PIP users (following its official release, obviously!), we’ll be announcing more details over the coming weeks.

As a teaser, this is what you can expect from the new version (v1.1):

  • Improved GUI – the layout of the application has been updated to improve work-flow
  • New Tree View – view your data graphically to see, at-a-glance, the structure of your data
  • Automatic XPath Building – Now you can use the Tree View to show PIP the data that you are interested in and PIP will generate the XPath automatically. This even works with Apple’s Property List ‘dictionary’ structures.
  • Import/Export Batch Jobs – Set-up a batch job of XPaths for a particular folder structure (iOS Library or Application folders for example) and then export the batch so that you, or anyone else in your lab can re-use it when you next come across the same data
  • Command line version – version 1.1 of PIP comes with the “pipcmd” command-line utility, allowing you to integrate PIP into tool chains and other automated tasks
For more information about XML and plist parsing, please visit or email us at

Double accreditation is a forensic first

We’re absolutely delighted to announce something of a ‘forensic first’.

CCL-Forensics has become the first UK digital forensic lab to be accredited to ISO17025 – not just for one part of the business – but for BOTH its computer and mobile phone labs.

It’s taken a long time to get here – and having this standard, cements our position as the UK’s leading supplier of digital investigation services.  It also means clients can have the maximum confidence in the quality of our services – especially if the case goes to court.

ISO17025 is a recommendation of the Home Office Forensics Science Regulator, Andrew Rennison – and all labs handling digital evidence should have it by 2015.  Put simply, it is one of the biggest steps forward the digital forensic industry has ever seen.  We’re already way ahead of the curve!

We’ve had ISO17025 for our phone lab for while now – and were one of only a small number of providers to do so.  The fact that we’ve now been accredited for our PC lab is huge news.

So what does it mean?  To give it its full title, it’s called “the general requirements for the competence of testing and calibration laboratories”.  That means that we are required to have in place an all-encompassing set of detailed standard operating procedures.  These procedures show that we operate a management system, are technically competent, and generate technically valid results.

It’s been the result of a lot of hard work – not only by our dedicated quality department – but by all members of staff who have worked tirelessly to ensure all the procedures are developed to the highest standard.

If you’d like more information about our quality standards, please email Dave Lattimore, Total Quality Manager at

Hidden digital evidence part 4

Welcome to the final instalment of our short blog series on places you may not have thought to look for digital evidence.

  1. Biometric data

The use of fingerprints, retinal scans and facial recognition is no longer the stuff of science fiction. Its use may not be widespread yet, but it is certainly becoming a reality on a number of advanced devices.

Computers, laptops and mobile devices may be protected from unauthorised use by such biometric technology. If the data can be retrieved, it can help significantly with the attribution of that device – especially useful on mobile phones if cell site analysis is being carried out as part of the case.

It’s an emerging technology, so keep an eye on developments over the coming months and years. More and more evidential opportunities will present themselves.

  1. Near-field communications

This refers to technology such as the Oyster Card on London’s transport system, and credit cards are also being equipped with chips, which when waved near to a reader device can perform the same function as when it is inserted into a chip and pin device.

Such technology is also finding its way into mobile phones – the Google Wallet has been in the news recently (for all the wrong reasons – namely security issues). This is an app that enables users to pay for goods using their mobile phone using a near-field communication device.

Data may be recorded on the phone – including when it was used, where, and for what purpose. This could be a rich vein of potential evidence, and although its use is not widespread at the moment, it’s certainly worth keeping up with developments.

  1. Games consoles

Games consoles such as Xboxes, Playstations and Wiis now have web browsers as standard. They are much more than just a platform for gaming, with many allowing users to stream music over the internet, rent movies and interact with others online.

External hard drives can also be attached to consoles, enabling people to play music and movies from their collections through the console.

Consoles also enable instant messaging to take place, within games and simply via the messaging function in the console itself.

Use of all these features leaves a digital trail on the hard drive, making such devices a rich source of potential evidence.

  1. Throwaway culture

The focus has been on modern emerging technology, particularly smartphones. But it’s sometimes sensible to consider the other end of the scale: antique technology can also provide interesting evidential opportunities.

Many people tend to upgrade their handsets – and laptops and PCs – and put the old ones in a drawer or box. There they languish, gathering dust, until they’re needed for a criminal case.

It’s entirely possible to extract all sorts of evidence from these older devices, some of which can prove as tricky as the higher-end handsets and computers. Just because a phone or other device looks simple and cheap, the data it may contain could be crucial.

Here’s an example: CCL-Forensics used its web cache toolkit on a six-year-old Nokia 6230i handset and recovered 600 pages of web history – not necessarily the result you’d expect from a modest old device.

  1. With the network provider

A vast quantity of valuable evidence can be gathered from mobile phone network providers using cell site analysis. Network providers keep a variety of information about a phone’s use for a period of time – including the cell masts it used to make and receive calls.

This type of analysis can help to build up a picture of where the phone has been used, and can help in attributing different phones to their users – especially where criminals make use of “clean” and “dirty” phones. (Clean phones are those used for “normal”, everyday activities; dirty phones are those used in the course of crime, and are often thrown away afterwards.)

If a mobile phone has been used in crime, don’t discount the possibility that a wealth of evidence could be gathered using cell site analysis.

Here endeth our list of possible locations for digital forensic evidence – although it is, of course, not exhaustive. As technology advances, more and more types of digital evidence will become available for investigators.

CCL-Forensics’ R&D team keeps pace with new technology, developing tools and techniques for extracting this new data. If you have any questions, they’re always happy to chat. Just drop us a line at

More hiding places for digital evidence

The saga continues… Keep reading for some more ideas as to where you can look for digital evidence.

  1. Deleted data

Just because data has been deleted from a computer or mobile phone, doesn’t mean it’s gone forever.

When a file is deleted, it may remain on the hard drive. What are actually deleted are the instructions for finding the file – the pathway – not the file itself. Only if the data is overwritten by new files will it become irretrievable.

By analysing a device’s hard drive, investigators can recover a wealth of information that is no longer available to a regular user.

  1. Bluetooth/WiFi pairings

Although Bluetooth is less popular than it once was, with WiFi access now widely available, it can be a valuable source of potential evidence. Each Bluetooth device has a unique identifier, which is likely to be recorded when it is paired or connected.

Examining a phone’s Bluetooth history could prove vital in proving association between other exhibits in the case – some of which may be attributed to other relevant individuals.

Analysing the unique identifiers of WiFi networks that a phone device was connected to, can be instrumental in proving a particular device was present or even in use at a certain location.

  1. Cloud computing and sync

Cloud computing – whereby shared resources, software and information are provided to computers and other devices as a utility over a network (typically the internet) – allows users to access software, data management and storage without needing to know the location and other details of the infrastructure.

As the amount of data people and businesses create and use increases, data storage becomes increasingly expensive, so many people and organisations are now choosing to use the cloud to store data, or to make various settings and favourites portable between devices.

This means they can effectively have access to the same data, which can include settings, cookies and preferences, regardless of which device they are using at the time. So, activity may take place on an individual’s computer which is automatically updated on their smartphone via the cloud.

A user’s data and preferences basically follow them around, providing evidential opportunities for digital investigators.

  1. Backups

It’s always worth considering that data from a mobile phone may be backed up onto the user’s computer.

Many people use their mobile phones almost as an extra limb (the author is guilty of this particular failing), so it’s a good idea to back up the phone on a regular basis just in case it’s lost, stolen or broken.

The evidence from backup files can be used to link a computer and phone as part of the same case, but there may also be more data on the computer from the phone’s backup than is still stored in the handset itself.

Additionally, many mobile applications (and even operating systems) are configured to sync with computers, leaving a digital trail and data on both devices. Systems such as iTunes and BlackBerry Backup are popular and store a huge amount of data. This can provide a valuable evidential opportunity.

  1. Voicemail

In days of yore, voicemail was stored at the telephone exchange operated by the mobile phone network. Users would call their automated voice mailbox, and listen to messages as part of that call.

Many modern phones are now capable of having those voicemails “pushed” to the handset using an internet connection. They’re then stored on the phone for access anywhere – even if there is no phone signal.

Additionally, many phones have the facility to record voice memos (much like a dictation machine), which again could provide valuable evidence as part of an investigation.

As with any data stored on a digital device, it’s always possible for it to leave a digital trail, which can be recovered using the correct forensic tools and techniques.

The final instalment in this short series of blogs about hidden digital evidence will be posted in a few days. See you then!