SQLite
is a relational database management system that is been in the limelight as it
does not require any client Server engine. It is a cross platform system and is
supported by Windows, Linux and Mac Operating System. With its growing
popularity, many issues are associated with it hence, SQLite forensics comes
into the picture. Before dealing with the forensics of SQLite, let’s have a
glance on various files in SQLite.
WAL And Journal Files
– At A Glance
Entire
SQLite database gets stored in the database file of SQLite. Apart from it, some
temporary files are also created by SQLite. In total 9 temporary files are
created by SQLite. WAL and Journal Files are the temporary files created by
SQLite. A brief of these files are provided in this section.
>
Rollback journal
Rollback journal file
is used for implementing commitment and rollback ability in SQLite. This file
is located in the same directory where the database file is stored with the
same name of the database. However, “-journal” is appended with Rollback
journal in order to distinguish it from the database file. Rollback journal
file comes into existence at the beginning of a transaction and gets deleted on
its commitment. This file plays a crucial role in rebuilding the database at
the time of any disaster.
>
WAL File
Write-Ahead Log or a
WAL file was included in SQLite 3.7.0. The purpose of this file is to implement
rollback and atomic commitment in WAL mode. Unlike Rollback journal, WAL files
are stored in the same directory where the database file is located. However, for
its differentiation from the database file, it is appended with “-wal”. This
file gets created when the first connection of the database is opened and gets
removed on closing the last connection. If the last connection is not shut
cleanly, this file remains present in the file system and is deleted when the
database is opened again.
>
Master
Journal File
This file is used as an element of
atomic commitment process when a change in multiple databases is done using a
single transaction. This file is located in the same directory of main database
and is identified by some arbitrary suffix. Master journal file holds the name
of several databases which gets modified in the course of a transaction. In the
absence of Master Journal file, a transaction involving multiple databases
would be atomic for individual database, but across the entire database, the
transaction will not be atomic.
>
Statement
Journal File
This
file is employed to rollback specific outcomes of a statement in a large
transaction. The Statement journal file is provided with a random name and can
be located in any directory. Statement Journal file is created only for an
INSRRT or UPDATE statements which changes multiple rows and come to a constrain
or RISE exception in a trigger and hence there is a requirement to undo
shortened results. The size of Statement Journal file depends on the amount of
changes employed by INSERT or UPDATE statements. At the end of the transaction,
Statement Journal file gets deleted automatically. This file is absent on using
Conflict Resolution Algorithm.
Forensic Analysis Of WAL
And Journal File
It is a well known fact
that on opening SQLite database, the WAL and journal file associated with the
page gets written to the main database automatically. Hence, all previous
records are overwritten and the file gets reset. However, this file plays a
crucial role in Forensic analysis of SQLite database. In order to deal with WAL
and journal file, simply open main database of SQLite. This file can be viewed
in the same folder where main database resides.
A checkpoint arises when
the journal file reaches to a maximum saturation limit or if a programmer
forces to apply it. This value is 1000 pages in case of SQLite. Many a times, the
journal file is not deleted even on the application of check point and the next
pages are written from the beginning of the WAL file. The present transaction
is determined by SQLite via salt value. The salt value of the file changes for
each checkpoints. On writing the content
of WAL or journal file to the database, SQLite reads the Salt value from WAL or
journal header and compares it with the file. If the two value matches the data
is written into the database else it is not written and remains in the WAL or
journal file.
This implies that a frame
present in the journal file with a different Salt value will never be written
in the main database. On carefully examining the Salt value, one can determine whether
a specific transaction belongs to the present file or to some previous files
and thus, precious information can be extracted.
Issues
faced in forensic analysis of WAL And Journal file
The journal file provides beneficial
information about the present and previous transaction. However, extracting
information from it is not easy. The experts face a lot of difficulties in the
process. Some of the issues are:
§ The
older data gets overwritten and hence it cannot be viewed.
§ Data
can be deleted from file and thus there can be unused space.
§ More
than one version of a page can be present
Conclusion
It is clear from above
discussion that the WAL and journal file in SQLite is the backbone of SQLite
forensics. This file contains valuable information that helps the Experts to reach
to the root cause of an issue. However, WAL and journal files forensics is not
a cup of tea and the experts faces a lot of issues. Thus, the experts prefer to
pick some forensics tool that can help them to perform WAL and journal file
forensics. One such tool is “SQLite Forensics Explorer”
Due to its advance
features, the tool has been the first choice of the experts.
