Daily Blog #295: Sunday Funday 4/13/14 Winner!

Hello Reader,
           Another Challenge has ended and we had a range of very good answers this week. I never know what to expect when I throw out a question. I thought this weeks question would get some responses but I didn't expect how much information was being submitted in the answers. You all certainly know your SQLite forensics! With that said, Andrew Case this week was the clear winner. Not only did he cite specific code that determine the behavior of deletion within the SQLite source he also gave full references to further reading. Well done Andrew, you are a Sunday Funday winner!

The Challenge:
  SQLite is becoming one of the most common application databases used across multiple operating systems and devices. As DFIR analysts we love SQLite for its ability to preserve deleted data. For this challenge let's see how well you understand why this rich deleted data set exists. Answer the following questions.
1. Why are deleted records accessibly in SQLite databases
2. What is the write ahead journal
3. What will cause deleted records to be overwritten

Winning Answer:
Andrew Case, @attrc

1. Why are deleted records accessibly in SQLite databases

Deleted records are often accessible in SQLite databases due to the default mode of Sqlite not performing secure deletion. This can be seen by following the code path for the handler of DELETE queries/operations inside the database. To start, the function sqlite3GenerateRowDelete inside of src/delete.c can be analyzed. In this function, on line 675 of delete.c [1], the following code is called:

sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, (count?OPFLAG_NCHANGE:0));

This function calls into Sqlite’s vdbe [2] (virtual database engine) in order to trigger an OP_Delete operation. The handler for OP_delete can be found on line 4144 of vdbe.c [3]. Inside this function, the data from the file on disk is deleted through a call to:

rc = sqlite3BtreeDelete(pC->pCursor);

sqlite3BtreeDelete is implemented inside of btree.c on line 7111 [4]. To remove an individual record (row/column) from the database file, clearCell and dropCell are called. dropCell is responsible for unlinking the record from the tree and does not touch the record’s actual data. clearCell’s behavior depends on if the secure_delete pragma [5] is set on the database being affected or if it is set globally. If it is set in either of these cases, then the data is overwritten with zeroes through a call to memset. By default this flag is NOT set though and the cell’s contents are not altered at all.

This default setting of secure_delete to off is the key aspect as to why records are recoverable. Since a cell’s contents are not securely deleted by default, and no modern mainstream applications set the flag, the remnant data is easily recoverable through forensics analysis.

An aside: It is possible to programmatically recover deleted records by walking the database’s freelist of pages. This list is populated by records that have been deleted and can be re-allocated during new writes to the database. A blog post on Linux Sleuthing gives an algorithm to accomplish this [7].

References
[1] http://repo.or.cz/w/sqlite.git/blob/f6ae24a0e5c5c5d22770ab70992dfab6b9d6fc5e:/src/delete.c#l675
[2] http://www.sqlite.org/vdbe.html
[3] http://repo.or.cz/w/sqlite.git/blob/f6ae24a0e5c5c5d22770ab70992dfab6b9d6fc5e:/src/vdbe.c#l4144
[4] http://repo.or.cz/w/sqlite.git/blob/f6ae24a0e5c5c5d22770ab70992dfab6b9d6fc5e:/src/btree.c#l7111
[5] http://www.tutorialspoint.com/sqlite/sqlite_pragma.htm
[6] http://repo.or.cz/w/sqlite.git/blob/f6ae24a0e5c5c5d22770ab70992dfab6b9d6fc5e:/src/btree.c#l5341
[7] http://linuxsleuthing.blogspot.com/2013/09/recovering-data-from-deleted-sqlite.html

2. What is the write ahead journal?

The write ahead journal [1] is used as a replacement to Sqlite’s old method of database journaling. In the WAL model, when contents are written to the database, instead of going directly to the database file, they instead go to the WAL file. This means that the current database contents are actually in the WAL file, and the database is partially holding outdated content. The WAL file may hold multiple records for the same records in the database. These are differentiated by a ‘salt’ value that increments with each operation. Information in the WAL file is flushed into the database file when a checkpointing [1] operation occurs.

The forensics implications of the write ahead journal, including timelining historical content based on the salt value, can be found at [2]. This blog post is a highly, highly informative post on the topic.

References
[1] https://www.sqlite.org/wal.html
[2] http://www.cclgroupltd.com/the-forensic-implications-of-sqlites-write-ahead-log/

3. What will cause deleted records to be overwritten

There are three main causes for deleted records to be overwritten. The first was explained in the answer to question 1 (secure delete flag being set and records are overwritten with zeroes as soon as the DELETE FROM … is executed).

The second cause is for entries on the freelist (see answer to question 1) to be re-used during new writes. This “old data blocks are available until re-allocated” cause is exactly the same issue faced when attempting to recover deleted files from file systems. File system drivers also keep free lists of blocks and until those blocks are re-allocated and overwritten then the file data is recoverable.

The third issue is database vacuuming [1]. The forensics implications of this are nicely explained in [2]. Vacuuming rewrites the database into a new file and removes all of the freed records from the database. This causes the database file to shrink and the old records to be placed into the unallocated storage of the file system.

References
[1] https://sqlite.org/lang_vacuum.html
[2] http://linuxsleuthing.blogspot.com/2011/02/recovering-data-from-deleted-sql.html

Also Read: Daily Blog #294