@night 1803 access accessdata active directory admissibility ads aduc aim aix ajax alissa torres amcache analysis anjp anssi answer key antiforensics apfs appcompat appcompatflags applocker april fools argparse arman gungor arsenal artifact extractor attachments attacker tools austin automating automation awards aws azure azuread back to basics backstage base16 best finds beta bias bitcoin bitlocker blackbag blackberry enterprise server blackhat blacklight blade blanche lagny book book review brute force bsides bulk extractor c2 carved carving case ccdc cd burning ceic cfp challenge champlain chat logs Christmas Christmas eve chrome cit client info cloud forensics command line computer forensics computername conference schedule consulting contest cool tools. tips copy and paste coreanalytics cortana court approved credentials cryptocurrency ctf cti summit cut and paste cyberbox Daily Blog dbir deep freeze defcon defender ata deviceclasses dfa dfir dfir automation dfir exposed dfir in 120 seconds dfir indepth dfir review dfir summit dfir wizard dfrws dfvfs dingo stole my baby directories directory dirty file system disablelastaccess discount download dropbox dvd burning e01 elastic search elcomsoft elevated email recovery email searching emdmgmt Encyclopedia Forensica enfuse eric huber es eshandler esxi evalexperience event log event logs evidence execution exfat ext3 ext4 extended mapi external drives f-response factory access mode false positive fat fde firefox for408 for498 for500 for526 for668 forenisc toolkit forensic 4cast forensic lunch forensic soundness forensic tips fraud free fsutil ftk ftk 2 full disk encryption future gcfe gcp github go bag golden ticket google gsuite guardduty gui hackthebox hal pomeranz hashlib hfs honeypot honeypots how does it work how i use it how to howto IE10 imaging incident response indepth information theft infosec pro guide intern internetusername Interview ios ip theft iphone ir itunes encrypted backups jailbreak jeddah jessica hyde joe sylve journals json jump lists kali kape kevin stokes kibana knowledgec korman labs lance mueller last access last logon leanpub libtsk libvshadow linux linux-3g live systems lnk files log analysis log2timeline login logs london love notes lznt1 mac mac_apt macmini magnet magnet user summit mathias fuchs md viewer memorial day memory forensics metaspike mft mftecmd mhn microsoft milestones mimikatz missing features mlocate mobile devices mojave mount mtp multiboot usb mus mus 2019 mus2019 nccdc netanalysis netbios netflow new book new years eve new years resolutions nominations nosql notifications ntfs ntfsdisablelastaccessupdate nuc nw3c objectid offensive forensics office office 2016 office 365 oleg skilkin osx outlook outlook web access owa packetsled paladin path specification pdf perl persistence pfic plists posix powerforensics powerpoint powershell prefetch psexec py2exe pyewf pyinstaller python pytsk rallysecurity raw images rdp re-c re-creation testing reader project recipes recon recursive hashing recycle bin redteam regipy registry registry explorer registry recon regripper remote research reverse engineering rhel rootless runas sample images san diego SANS sans dfir summit saturday Saturday reading sbe sccm scrap files search server 2008 server 2008 r2 server 2012 server 2019 setmace setupapi sha1 shadowkit shadows shell items shellbags shimcache silv3rhorn skull canyon skype slow down smb solution solution saturday sop speed sponsors sqlite srum ssd stage 1 stories storport sunday funday swgde syscache system t2 takeout telemetry temporary files test kitchen thanksgiving threat intel timeline times timestamps timestomp timezone tool tool testing training transaction logs triage triforce truecrypt tsk tun naung tutorial typed paths typedpaths uac unc understanding unicorn unified logs unread usb usb detective usbstor user assist userassist usnjrnl validation vhd video video blog videopost vlive vmug vmware volatility vote vss web2.0 webcast webinar webmail weekend reading what are you missing what did they take what don't we know What I wish I knew whitfield windows windows 10 windows 2008 windows 7 windows forensics windows server winfe winfe lite wmi write head xboot xfs xways yarp yogesh zimmerman zone.identifier

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].

[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.

[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.

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

Post a Comment


Author Name

Contact Form


Email *

Message *

Powered by Blogger.