Forensics Question: In the absence of recoverable records, what can be learned about missing records from a SQLite database?
OS Version:
N/A
Tools:
ArtEx i.5.i.0 (Windows only),
DB Browser for SQLite 5 three.12.1 (on Mac),
mirf.py (First release 22nd December 2020)
Missing SQLite Records Analysis
By: Shafik G. Punja & Ian Whiffin
Groundwork
The SQLite database engine is one of the most widely used database formats, where its employ can be plant in countless areas such every bit spider web browsers, instant messengers, all smartphones, Mac computers, Windows ten computers, also automotive infotainment systems, and surprisingly also found in smart tv sets and cable boxes [ane]. The utilization of SQLite databases across a wide spectrum of so many mediums, is due to its performance, reliability, portability, simplicity and accessibility of data. SQLite can be used as on disk application file format [2], or as an SQLite Archive (where the SQLite Archive is similar to a ZIP file or archive or Tarball) [3].
This commodity will specifically discuss the identification of missing records, inside the SQLite database in its use every bit an application file format. The various analysis tools that will be used to analyze missing records within SQLite databases volition be noted throughout the article. The authors are working from the premise that recovery of deleted, partially recoverable, or wholly intact recoverable records, is no longer feasible. What will not exist covered is the explanation on the various methods to recover deleted records. For that we direct you to the only textbook on this subject area authored in 2018 by Paul Sanderson, titled, SQLite Forensics.
SQLite Database Structure Nuts
The SQLite database as a file on disk can consist of iii split files. There are, however, actually nine distinct types of temporary files that can exist used by SQLite during database processing operations [5]. Merely for the purposes of this article, nosotros will briefly mention the database file itself and the 3 types of temporary files ('shm', 'wal', and 'journal') that are most usually encountered by digital forensics practitioners.
The database file itself. It can utilize diverse types of suffixes (listed below), or in some cases the SQLite database file will non accept any suffix appended afterwards the arbitrary prefix file proper name.
The database write-alee log (WAL) or periodical file. The employ of either a WAL or curl back journal file is determined past the value within the SQLite database file at decimal offsets 18 and 19. The legacy refers to the use of a journal file. Both types of files (WAL and journal) serve the aforementioned purposes of 'diminutive commit' and rollback, but both implemented in dissimilar means. In addition to several other system level benefits over journal files, WAL files tend to perform faster [half dozen].
If a WAL file is used by SQLite, then a digital forensics practitioner can potentially notice three files as shown in the screenshot beneath.
Effigy : history.db with WAL and SHM files
If the legacy rollback journal file is used past SQLite, then a digital forensics practitioner can potentially observe two files as shown in the screenshot beneath. Note the absenteeism of the shared retentivity (*.db-shm) file, as this blazon of temporary file is only used with WAL.
Figure : msn.db with journal file
WAL file: The WAL file tin can be found in filesystems where the database connection has non shutdown cleanly. This allows for potential recovery of live records that have not been committed to the database, or recovery of previously existing records that have been deleted and are no longer recoverable from the database itself, but all or part of the record resides in the WAL file. The WAL file tin can be recognized by the presence of 4 characters, "-wal" appended to the end of the name of the primary database filename [5]. Example WAL filename:'sms.db-wal'.
Rollback Journal file: This rollback journal file provides another method of 'atomic commit' and rollback method for SQLite database operations. An SQLite database periodical file can be identified by the viii characters "-journal" appended to the stop of the filename [five]. Case 'periodical' filename: 'sms.db-periodical'
The shared retention ('shm') file. This file does not comprise any database content. If a database crash occurs, the 'shm' file is not vital to the recovery of the database [4]. In the authors' experience, the shared memory file has non been observed to be of investigative value. Example 'shm' filename: 'sms.db-shm'.
Database Header Structure, which is comprised of the first 100 bytes, can be thoroughly enjoyed by referring to this source: https://world wide web.sqlite.org/fileformat2.html#vnums
The SQLite database file header (or magic number) is 16 bytes in length, starting at decimal starting time 0 (zero), can hands exist recognized in ASCII every bit "SQLite format three" (or in hexadecimal every bit 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00).
The Record Number Autoincrements
The SQLite database itself consists of tables, where each table has rows of records with columns that depict the data inside the rows. The SANS SQLite Pocket Reference Guide (created by Lee Crognale, Heather Mahalik and Sarah Edwards) has a dainty graphic that visually explains this [ten].
Figure : SANS SQLite Pocket Reference Guide (created past Lee Crognale, Heather Mahalik, and Sarah Edwards)
As stated on the 'sqlite.org' website: 'All rows inside SQLite tables have a 64-bit signed integer [primary] key that uniquely identifies the row within its table'. In other words, by default, every SQLite database has a 'rowid' column (or alternately may choose to use the PRIMARYKEY as a PK column) that uniquely identifies a record inside a specific row in the database itself [7]. Record numbering starts with a value of i, and autoincrements with every new record that is created. The tape numbers are therefore face-to-face, with no gaps [8].
**Note: The reader should also be aware, there besides exists the ability within an SQLite database to create a row, using 'WITHOUT ROWID' table method, that omits the creation of a 'rowid' cavalcade [7]. The 'WITHOUT ROWID' tabular array method is non default behaviour for an SQLite database.**
The following screenshot is an case of the 'sms.db' SQLite database file (from Cellebrite CTF of Ruth Langmore's Apple iPhone Ten) opened with the 'sms.db-wal' nowadays, using DB Browser for SQLite. This is a great free open source cross platform application that can open up an SQLite database file. The 'sms.db' SQLite database file from Apple devices running iOS fourteen.x (or iPadOS 14.ten) and lower, records SMS, MMS and iMessage activity for the native Messages application.
The message tabular array shows the ROWID column which contains a total of 73 records. In the screenshot record numbers 1 through to 20 are displayed in a contiguous manner. At that place are no missing records from rows i to 73. We checked.
Figure : Screenshot from DB Browser for SQLite v 3.12.ane on macOS xi.0.1, showing contiguous record numbers in the ROWID column.
If the 'sms.db' SQLite database file (from Cellebrite CTF of Ruth Langmore'southward Apple tree iPhone Ten) is opened with and without its associated WAL file, 'sms.db-wal', there is a deviation in the number of contiguous records present in the message tabular array. Two copies of the 'sms.db' SQLite database were created in separate storage locations. The first copy did not have the associated WAL file, 'sms.db-wal' just the second copy did. Both 'sms.db' SQLite database files were opened in their ain instances of DB Browser for SQLite as shown in the screenshots below.
The 'sms.db' SQLite database file without its associated WAL file, 'sms.db-wal', presents with 65 contiguous records in the message tabular array with ROWID column values from i through 65.
Figure : Screenshot from DB Browser for SQLite v 3.12.ane on macOS 11.0.1, no WAL.
The 'sms.db' SQLite database file with its associated WAL file, 'sms.db-wal', presents with 73 face-to-face records in the message tabular array with ROWID values from 1 through to 73. Compared to the previous, that is a divergence of 8 live records that were not committed (or merged) to the 'sms.db' SQLite data, until it was opened with the associated WAL file, 'sms.db-wal' present.
Figure : Screenshot from DB Browser for SQLite five 3.12.1 on macOS 11.0.ane, with WAL.
So, in this example, how important is the 'sms.db-shm' file? When the 'sms.db' SQLite database file is opened with its associated WAL and shm files nowadays, the aforementioned number of records is observed every bit with WAL file just.
It is important to note besides that the inverse can be true. The main SQLite database may contain data that gets removed once the WAL has been incorporated. The reason behind this is because at that place could be records that are assigned to exist deleted from the main SQLite database, for which the write (delete record) operation has not been committed. The WAL contains all the awaiting write operations and every bit the delete performance is also a write, you will find them together with other writes in the WAL. So, in this case opening the primary SQLite database with the WAL file will cause the records to exist removed.
To demonstrate this, we opened up a cache database from an iPhone both with and without processing the WAL file using ArtEx (developed by Ian Whiffin). When the WAL was processed, you can encounter that the ZRTCLLOCATION table contains 1303 records and that the commencement record is PK 15906 and the last PK is 17209, in the screenshot beneath.
Graphical user interface, application, tabular array Clarification automatically generated
Figure : Screenshot of ArtEx with WAL parsing.
The same cache database without WAL parsing is shown below. When the WAL is not candy, yous tin can see in that location are 1345 records, with a first and terminal PK at 15777 and 17121 respectively.
Graphical user interface, table, Excel Description automatically generated
Figure : Screenshot of ArtEx without WAL parsing.
This makes perfect sense. When the WAL commit occurs, records 15777 through to 15905 are deleted (a loss of 129 records) but records 17122 to 17209 are created (a proceeds of 88 records). This leaves us with a internet loss of 41 records. Just the internet difference actually isn't the indicate (After all, a single tape could make all the divergence when information technology comes to prove). The bespeak is that many tools automatically procedure the WAL (if nowadays) and will therefore risk the loss of data by record deletion. Merely you cannot afford to ignore the WAL every bit information technology may contain new records that y'all may demand.
Once the connectedness to the 'sms.db' SQLite database is airtight, both the 'sms.db-wal' file and 'sms.db-shm' files, were observed to be removed from the locations they were copied to, equally the changes have been committed or merged within the 'sms.db' file.
The practiced news for digital forensics practitioners, is that most (if surely not all) digital forensics products, incorporate the associated WAL (or the rollback periodical) file, every bit role of the analysis process, in lodge to place as many unmerged live, unique records as possible. The authors' in this context, are non taking into account the recovery of deleted records from the freelist space specific to the SQLite database or from within the WAL (or the rollback journal) file itself. What nosotros are unable to say is exactly which digital forensics products clearly identify, unmerged live records from the WAL
If the digital forensics practitioner elects to export a SQLite database file, out of their chosen digital forensics analysis platform, for review in whatever complimentary open source tool, as demonstrated higher up, it is of import to understand the associated WAL (or the rollback journal) and to know if you lot desire to include it or non. Every bit a caution to the reader, any gratuitous open source tools too take the ability to cause write changes to the SQLite database, as opposed to those SQLite database analysis tools that are part of a digital forensics tool suite.
Deleted SQLite Records
As smartphone operating systems evolve, the ability to recover deleted records from SQLite databases becomes increasingly more difficult. Generally speaking, until the SQLite database is fully vacuumed and defragmented, deleted (non live) records tin be recovered. One time the vacuum and defragmenting operation occurs (transparent to the device user), deleted (not live) records are permanently removed, at which point at that place is no hope in any record recovery [xi].
Accept for case pre Apple iOS 12, where recovery of deleted records related to text messages and iMessages is more likely, as the deleted records were not immediately wiped. Equally of iOS 12 and newer the deleted text letters and iMessages are wiped nigh immediately after deletion, and cannot be recovered from the SQLite database [11].
When a record is deleted, regardless of whether it tin can be recovered or not, the face-to-face numbering sequence is cleaved, and gaps showtime to announced in the ROWID numbers. This leads to the actual point of the article, which is the analysis of the missing records from a specific tabular array within an SQLite database file.
Missing SQLite Records
The identification of not-contiguous numbering sequences in the record numbers, within an SQLite database tin can be useful for identifying a missing tape. For example, a missing record in the SMS database may signify a deleted message or a missing record from the call history table may signify a missing call tape.
Effigy : Screenshot from DB Browser for SQLite v iii.12.1 on macOS 11.0.i showing a missing telephone call record in the CallHistory.storedata database (from Cellebrite CTF of Ruth Langmore's Apple tree iPhone X).
For tables with timestamps, information technology may be useful to work out the time flow that the deleted tape was made. As the records are created consecutively, it stands the reason that the records are created in time order. Therefore, the missing record was made after the previous tape but before the next. This may not always be the case and should exist practical on a table-by-table ground.
As well annotation that this refers to the fourth dimension that the record was made in the database, not necessarily the time the record was deleted or even the time that something occurred. SMS for instance may be delayed in transit, and so the time received may exist much later than the fourth dimension information technology was sent. This has been addressed in a blog mail service authored by Ian Whiffin titled, "Principal Key / Date Stamp fallacy" (http://doubleblak.com/weblog/primarykeyfallacy).
Yous may also want to take this further and explore other associated tables to run into if there is any correlation you tin make with the time period of the missing tape(s). For example, are there whatever notifications, application utilize, sounds etc. fabricated that could give yous additional clues almost the missing record.
You are unlikely to get as much information from a missing tape equally you would similar; only that doesn't mean you can't go anything. This technique is easy when y'all take an obviously missing record, merely sometimes the missing record isn't quite so obvious.
In the screenshot shown beneath, in the CallHistory.storedata database, ZCALLRECORD tabular array , (from Cellebrite CTF of Ruth Langmore's Apple iPhone X) the missing record is actually AFTER the terminal record being displayed. It isn't immediately credible as there is no record later on it.
Graphical user interface, text Description automatically generated
Figure : Screenshot from DB Browser for SQLite v 3.12.one on macOS 11.0.ane showing a missing record after the terminal used tape in the CallHistory.storedata database (from Cellebrite CTF of Ruth Langmore's Apple iPhone X).
What we have to practise in this instance is effort to find the final generated record value in use within the table of involvement. In this case, the Z_PRIMARYKEY tabular array helps identify the final generated record value in utilize equally record 15, and the last observed record in the Z_PK column, in the ZCALLRECORD table is 14. The reader should note that the authors intentionally deleted the last tape, number 15 (row 14) to demonstrate the terminal tape number used value can exist one college than the observed record number in its respective table.
In near cases, you may discover a table called "sqlite_sequence" equally an internal table within the database. The sqlite_sequence table is an internal tabular array maintained by the SQLite database. Information technology is automatically created by the SQLite database when there are fields in tables that have the AUTOINCREMENT keyword set [12].
Now allow'due south endeavour and locate the "sqlite_sequence" table. I approach is to query a list of all the tables present within an SQLite database. The post-obit custom query will listing all tables nowadays in the SQLite database:
SELECT name FROM sqlite_master WHERE Blazon = 'table'
Using the 'sms.db' SQLite database file (from Cellebrite CTF of Ruth Langmore's Apple iPhone X) with its associated WAL file, a list of tables is returned, where the name of each table is beingness returned, and presented in non-alphabetical club.
Graphical user interface, text Description automatically generated
Figure : Screenshot from DB Browser for SQLite five 3.12.i on macOS 11.0.1 showing search for all tables query.
With a slight variation to the previously shown query, the listing of tables can be alphabetically ordered by name where the 'Society BY proper name' has been added.
SELECT * FROM sqlite_master WHERE TYPE = 'table' Guild Past name
If y'all skip the beginning aforementioned approach, you can assume the internal sqlite_sequence table exists, and effort to directly call the sqlite_sequence table using the side by side query. This will render a list of all the tables with their respective sequence (seq) values that are currently in use.
SELECT * from sqlite_sequence
Using the 'sms.db' SQLite database file (from Cellebrite CTF of Ruth Langmore's Apple iPhone X) with its associated WAL file, the screenshot beneath shows SQL query beingness used in DB Browser for SQLite v three.12.1 on macOS xi.0.one. With successful execution of the query, you should at present come across all the tables in your database. Looking through the tables, information technology should be obvious which table you are interested in.
A picture containing diagram Description automatically generated
Figure : Screenshot from DB Browser for SQLite v 3.12.1 on macOS 11.0.i showing "sqlite_sequence" query.
The "name" column shows yous the names of tables that have automatically incrementing fields. The "seq" cavalcade shows you the latest id of that field. In the "message" tabular array, this ways that the latest in-apply sequence record number is 73. Thus, if you lot were to add together a new row into the message tabular array, it would be assigned record number 74 and the "seq" field would be updated in the sqlite_sequence table to reflect the new record number.
You may ask what if in that location is no "sqlite_sequence" table. The "sqlite_sequence" table exists to handle the task of counting records. By using the AUTOINCREMENT field, the developer tin offload the counting process to the SQLite database. The database will increment the tape id sequentially. If a field with AUTOINCREMENT exists, then SQLite will not permit you to drop the "sqlite_sequence" table. If y'all do not detect an "sqlite_sequence" table, so chances are the app isn't using an AUTOINCREMENT field. In this example, it is incumbent upon the programmer to handle the tape counting.
The 'CallHistory.storedata' SQLite database, (from Cellebrite CTF of Ruth Langmore'southward Apple iPhone Ten) is a neat example of where at that place is no "sqlite_sequence" tabular array, that is used to place the electric current record in use within the tables present in the database. Instead, what is beingness used is the Z_PRIMARYKEY table that performs this function. The next screenshot shows the custom query search for the tables in the database. There are a total of 7 tables present, none of which are the "sqlite_sequence" tabular array.
Graphical user interface, text, application Description automatically generated
Figure : Screenshot from DB Browser for SQLite v 3.12.1 on macOS 11.0.1 showing the Z_PRIMARYKEY table listed, from Cellebrite CTF of Ruth Langmore'southward Apple iPhone Ten.
There is however the Z_PRIMARYKEY table, as shown in the screenshot beneath, which provides essentially the same data every bit the "sqlite_sequence" table. The Z_MAX column in this instance is used to place the current tape number in employ inside each of the tables in the 'CallHistory.storedata' SQLite database.
Graphical user interface, application Clarification automatically generated
Figure : Screenshot from DB Browser for SQLite v three.12.1 on macOS xi.0.one showing the structure of the Z_PRIMARYKEY table, from Cellebrite CTF of Ruth Langmore's Apple iPhone 10
The 'CallRecord' table, under the Z_NAME column, from the previous screenshot, shows a Z_MAX value of 15. In comparing to the adjacent screenshot of the ZCALLRECORD tabular array, nosotros can come across that in that location are indeed 15 records that have been created thus far in this table, just only 14 rows of records are displayed.
Figure : Screenshot from DB Browser for SQLite v 3.12.1 on macOS 11.0.1 showing the structure of the ZCALLRECORD table, from Cellebrite CTF of Ruth Langmore's Apple iPhone Ten.
Don't focus on the row count merely rather on the Z_PK cavalcade value, which is the record number (and is an allonym for the ROWID column), which in this instance identifies each unique record. In that location are xiv rows of records because the Z_PK column of the ZCALLRECORD table, has a gap between record 2 and tape 4. Record 3 has been deleted! The utilise of the 'Z_' prefix cavalcade and table names appears to be unique to the Apple ecosystem itself. So, you may not encounter Z_' prefix column and tabular array names in non-Apple SQLite databases.
Quick tip about the PRIMARY Central table, cited direct from https://www.sqlitetutorial.net/sqlite-primary-key/: "If a table has the main central that consists of one column, and that column is defined as INTEGER and so this primary central cavalcade becomes an alias for the rowid column."
We have now learned how to place a listing of tables, and the current 'seq' (sequence) record value or Master KEY (PK) tape number, that is in employ, for tables within an SQLite database. Recall that a key principle in the functioning of SQLite databases is the record value increments by one for every record added to the table, starting at tape 1. Once a record number is deleted that record number value is never reused within an SQLite database. So, a tabular array with a tape value of 100 would be expected to have 100 records. This ways that if there are simply 99 records, and 1 through 99 are contiguous, then information technology must be record 100 that is missing.
This came in useful for Ian on a case where the user had deleted the incriminating photograph, which but so happened to be the last photograph taken. Using the 'seq' (sequence) record value, Ian was able to identify that in that location was a missing photograph, and due to the incremental mode that iOS names photographs, he could work the name of the missing photograph itself. This led to finding the image as an email attachment that he was able to recover.
Missing Record Assay
In this section we are going to exist actually analyzing the missing records that exist within the 'CallHistory.storedata' SQLite database file from Josh Hickman's iOS 13.four.1 Public Image. This is a full file system extraction of an iPhone device, and therefore does incorporate the associated WAL file.
A quick check of the Z_PRIMARYKEY table, shows the 'CallRecord' tabular array is currently using record 65, as identified in the Z_MAX column. We should, therefore, expect to come across a record with a value of 65 in the Z_PK column of the ZCALLRECORD table, which we don't. Instead, we see a value of 64.
A screenshot of a computer Description automatically generated
Figure : Screenshot from DB Browser for SQLite 5 iii.12.one on macOS 11.0.1 showing the last record number 64 with tape value 65 missing from the ZCALLRECORD tabular array, from Josh Hickman's iOS 13.4.1 Public Epitome.
You should also have observed, from the above screenshot the evident disparity between the row number 37 and record number 64. This is a clear indication of the missing records within the ZCALLRECORD table of the 'CallHistory.storedata' SQLite database file.
Missing Record Finder
Information technology is possible to automate the assay of missing records from an SQLite database. The next several screenshots prove an example of a Windows based tool called Missing Tape Finder developed by Ian Whiffin. He developed information technology when we worked together at our former law enforcement agency's digital forensics lab. At this time, Missing Record Finder is not publicly available.
Missing Record Finder 5 1.7 just checks for consecutive numbers in the relevant supported SQLite database tables. Information technology does not endeavor data recovery or comparisons. It as well does not take into account any unmerged alive records from the WAL (or journal) file.
In this screenshot below, the left aspect of shows the Missing Tape Finder 1.7, that has identified (without the WAL file) 35 existing records in the ZCALLRECORD table of the 'CallHistory.storedata' SQLite database file. The right attribute of the screenshot shows the same database opened with WAL file present, displaying the ZCALLRECORD table, in DB Browser for SQLite, presenting 37 existing records.
Graphical user interface, application Clarification automatically generated
Figure : Missing Tape Finder 1.7 showing presence of missing records within the ZCALLRECORD table of the 'CallHistory.storedata' SQLite database file.
The visual representation of missing records in Missing Record Finder 1.seven is an invaluable aid in understanding how many records are missing, in between specific time periods relative to existing records. What Missing Record Finder 1.7 does not do is place missing records prior to the oldest existing tape, or the very last missing record later on the near recent existing record.
Some other prissy feature in the Missing Tape Finder ane.seven output is displaying the SQLite custom queries that were used and a summary of missing records between date ranges. The date ranges are determined from existing records.
A picture containing text Clarification automatically generated
Figure : Missing Tape Finder i.7 showing a summary of missing records and the custom SQL query used to identify missing records in the ZCALLRECORD table of the 'CallHistory.storedata' SQLite database file.
ArtEx
Side by side, using ArtEx 1.five.1.0, a complimentary open source iOS analysis tool (developed by Ian Whiffin), the 'CallHistory.storedata' SQLite database file was analyzed for missing records, from Josh Hickman's iOS xiii.4.1 full file organization (FFS) extraction. ArtEx is a Windows just tool, and was used, in this example, on Windows 10 20H2 (Bone Build 19042.thirty) through VMFusion 12.1.0, running on macOS 11.0.1. Information technology only accepts data from iOS full file system (FFS) extractions. It is non possible, to analyze a single SQLite database with ArtEx.
The post-obit screenshot, in ArtEx, shows the existing call records parsed with identification of missing records, interjected in betwixt existing records, based on the corresponding missing record number. In this view ArtEx currently does non bear witness the missing records previous to the oldest existing record or after the almost recent existing tape. The record found count includes the existing and missing (non including the missing records previous to the oldest existing record or after the about recent existing record).
Effigy : ArtEx i.5.one.0 showing parsed existing and missing call records from 'CallHistory.storedata' SQLite database.
Within ArtEx, the SQLite viewer can be invoked either in ii ways. Start, through the Source column interface as shown in the screenshot in a higher place. If y'all prefer to analyze the SQLite database and its associated WAL file, then use the 'Directory View' method, as shown in the screenshot below.
Graphical user interface Clarification automatically generated
Figure : ArtEx 1.five.i.0 showing how to admission Directory View.
Once 'Directory View' is opened, enter the database name and so click on 'Search Results' push. From the search results windows, click on the name of the SQLite database under the 'FileName' cavalcade to open the SQLite Viewer.
Graphical user interface, application Description automatically generated
Effigy : ArtEx ane.5.i.0 in Directory Tree view searching for CallHistory.storedata database.
The next screenshot shows the CallHistory.storedata SQLite database opened in SQLite Viewer (within ArtEx). At that place are 3 tabs from left to correct. With WAL, Without WAL and WAL comparison. The missing records are not yet identified as that function must exist initiated by the examiner. Make sure to commencement, select the database tabular array you want to examine for missing records. Second, click on 'Find Missing Records'.
Table Description automatically generated
Figure : ArtEx i.five.1.0 showing 'CallHistory.storedata' SQLite database open in SQLite Viewer, with three tabs: With WAL, Without WAL and WAL comparison.
After running the 'Discover Missing Records' button, if missing records are identified, that are prior to the oldest existing tape, in this case record 12, a bulletin window will appear that identifies how many records were found to be missing merely are not shown. The reason behind this is that if there are thousands of missing records prior to the oldest existing tape, the digital forensics practitioner is fabricated enlightened of it, every bit ArtEx only visually identifies missing records from the oldest existing tape going forward to the near recent existing record.
A picture containing table Description automatically generated
Figure : ArtEx ane.5.1.0, Discover Missing Records part has been run on 'ZCALLRECORD' table (''CallHistory.storedata' SQLite database), WAL tab.
Later on the message windows (from the previous screenshot) has been acknowledged, the visual representation of missing records, highlighted as reddish coloured rows, can be observed.
The 'With WAL' tab shows the merged (added and removed) records betwixt the WAL file and the database together, against all the tables within the database. This side by side screenshot shows 37 existing records, and 28 missing records found in the ZCALLRECORD table ('CallHistory.storedata' SQLite database). Missing tape 65 is observed subsequently the most recent existing record 64. (You can refer back to the screenshot in Figure 15 which shows this too, in DB Viewer for SQLite).
Table Description automatically generated
Figure : ArtEx 1.v.1.0, most recent record is identified as a missing record in the ZCALLRECORD table ('CallHistory.storedata' SQLite database), WAL tab.
The 'Without WAL' tab is the SQLite database simply. The screenshot below, shows that the ZCALLRECORD table is an empty table, with nix (0) records. This means that the 'CallHistory.storedata' SQLite database file (without the WAL) contains no records! All the records have come from the WAL file. This allows you to infer the bodily write (commit) operations to the SQLite database file have not still taken identify.
Graphical user interface, awarding Description automatically generated
Figure : ArtEx 1.5.one.0, Find Missing Records function has been run on 'ZCALLRECORD' table (''CallHistory.storedata' SQLite database), Without WAL tab.
Trust but verify! The next screenshot shows the ZCALLRECORD table from 'CallHistory.storedata' SQLite database file (without the WAL), opened in DB Browser for SQLite. We just verified that at that place are no records present in the actual database file.
Figure : DB Browser for SQLite 3.12.1 showing no records in the ZCALLRECORD table from 'CallHistory.storedata' SQLite database file (without the WAL).
The 'WAL Comparison' tab provides a overnice summary snapshot of all the tables in the SQLite database, beingness examined, and tape number count with WAL and without WAL.
Table Description automatically generated
Figure : ArtEx 1.five.1.0 shows 'CallHistory.storedata' SQLite database, with identification of missing records
mirf (missing record finder)
This is an agnostic, gratuitous open source script, developed using Python 3, by Sheran Gunasekera which is available at https://github.com/sheran/mirf. The development of 'mirf' was inspired past the missing tape analysis features in ArtEx. In its electric current iteration, 'mirf' is but designed to parse the 'CallHistory.storedata' and 'sms.db' SQLite database files that are exported from iOS total file system extractions. It uses a predefined template to understand the construction of these 2 aforementioned SQLite database files.
The following screenshot of the Concluding app (on macOS 11.one) shows Python 3.ix.0 (invoked through a pyenv environs) executing the 'mirf.py' script, to analyse a sms.db SQLite database file (exported from Josh Hickman's iOS 13.four.ane full file arrangement (FFS) extraction) for missing records. The analysis results are direct frontwards and like shooting fish in a barrel to interpret. In this case 2 missing records are identified, including date ranges in UTC, and the missing record number.
Text Description automatically generated
Figure : mirf.py analyzing sms.db (exported from Josh Hickman'southward iOS 13.4.1 full file system (FFS) extraction) which contains 2 missing records.
The next screenshot shows the 'mirf.py' script being run (in the same surround as previously noted), analysing the 'CallHistory.storedata' SQLite database file (exported from Josh Hickman's iOS 13.4.ane full file system (FFS) extraction) for missing records. The analysis of this database shows that there is a total of 28 missing records. The Missing Records List shows the details of all missing records. For example, that records 1 through xi are missing from before 20:02:52 on 23rd March 2020 (UTC). Besides notation that in relation to the Concluding Record ID value that was used for ZCALLRECORD table is 65, relative to the Final Record ID value of 64 that is nowadays. This means the concluding record in the ZCALLRECORD table is missing.
Text Clarification automatically generated
Figure : mirf.py analyzing 'CallHistory.storedata' (exported from Josh Hickman'due south iOS 13.4.1 full file system (FFS) extraction) which contains 28 missing records.
It is anticipated that further development of 'mirf.py' will take identify to allow for a guided (versus template) mode of assay of missing records from whatever SQLite database. This method of analysis would rely on the digital forensics practitioner, based on prompts from assay tool, to identify the relevant tables(s) and their corresponding fields to be analysed.
Concluding Thoughts
The purpose behind the reader having to empathize the pages of a sometimes dry and somewhat challenging concept of SQLite databases, is to make the digital forensics practitioner aware of the value of missing records analysis.
After processing and understanding the concepts explained in the previous pages, there are generalized inferences most missing records, that can be explained equally follows:
An identified missing tape, that is afterward the most contempo existing record, indicates with a high caste of professional certainty that the device user has intentionally deleted a record.
Every bit, a group of missing records that exist between a set up of two existing records, indicates with a loftier degree of professional certainty that the device user has intentionally deleted these records.
If timestamps are present for existing records, this provides a temporal context relative to the existing records, of when the missing/deleted records were originally created.
The verbal engagement and time, a missing record was deleted cannot be ascertained.
Who performed the action of deleting records cannot be ascertained from the identification of missing records alone.
If a database table is empty and missing records are identified, in conjunction with the presence of the most recent tape value in employ, this would indicate with a loftier caste of professional person certainty that the device user has intentionally deleted these records.
While the absenteeism of a record number signifies deletion of a row, information technology is incumbent on the digital forensics practitioner to distinguish between system deleted or user deleted records. This can just actually exist washed by determining if the user has access to the database in question. For example, a record from an SMS database would be deleted when the user deletes the message. Simply system databases aren't typically accessible to the user so deleted records here would exist assumed to exist system deleted. Furthermore, if possible, application settings should also be checked on the device and/or in the application structure (from the extracted data), in club to decide how long information is specifically retained by that application. These settings can greatly influence in determining whether the presence of missing records in an SQLite database are caused by the actions of the device user or past the device itself.
Finally, cheers for taking the fourth dimension to read this article and we hope you lot enjoyed information technology and learned concepts that you can use in your digital forensics tradecraft!
Clustered Indexes and the WITHOUT ROWID Optimization: https://world wide web.sqlite.org/withoutrowid.html
Sanderson, P. (2018). SQLite Forensics (1st ed.). Paul Sanderson.
Page 268 Paraphrased: The ROWID value, is an integer primary key that is divers every bit autoincrement. This ensures that each new record added to the database will accept a unique key that is i more the previously used maximum value. The ROWID integer primary key values are therefore contiguous, with no gaps in the integer values.
Sanderson, P. (2018). SQLite Forensics (1st ed.). Paul Sanderson.
Page 69 Paraphrased: Implemented in SQLite version three.three.five, 'secure_delete' status is not stored in the SQLite database; when investigating an SQLite database, there is nothing that a digital forensics practitioner can check to ascertain if secure delete is enabled. If an SQLite database is compiled with the 'secure_delete' option, then deleted records are overwritten with zeroes.
SQLite POCKET REFERENCE GUIDE (by Lee Crognale, Heather Mahalik and Sarah Edwards): https ://digital-forensics.sans.org/media/SQlite-PocketReference-terminal.pdf
The iPhone Information Recovery Myth: What Yous Can and Cannot Recover (July tenth, 2020 by Oleg Afonin): https://blog.elcomsoft.com/2020/07/the-iphone-information-recovery-myth-what-you-can-and-cannot-recover/
"Your text messages and iMessages are stored in a database in the SQLite format. Past default, SQLite does not overwrite records immediately afterward they've been deleted. Instead, SQLite marks them every bit "deleted". Deleted pages become unused and are stored on what is called a "freelist". If you lot obtain the database files (past making a backup), these records can be recovered until the moment the database is fully vacuumed and defragmented (if information technology is, the deletion becomes permanent). This used to exist the case in iOS 8 through iOS 11. Starting with iOS 12, Apple tree seemingly moved to a not-standard implementation, physically wiping records well-nigh immediately subsequently they are deleted. Every bit a event, deleted text messages and iMessages cannot be recovered in iOS 12, 13 and newer."
"The one trouble of recovering deleted records (be it messages, phone call logs or contacts) is the volatile nature of SQLite databases in modern versions of iOS. The only easy way to obtain SQLite databases from the device is making an iTunes backup. Until yous brand the fill-in, the databases are used with unmerged WAL (write-alee logs), and However, the very moment yous initiate the backup, the SQLite databases are merged, and the deleted records are lost forever."
ArtEx by Ian Whiffin (registration required): https://www.doubleblak.com/
Josh Hickman's iOS 13.4.1 Public Image: https://thebinaryhick.blog/?south=thirteen.4.i
Missing Record Finder Python iii script, mirf.py: https://github.com/sheran/mirf
Acknowledgements
The authors would like to thank Sheran Gunasekera for taking the fourth dimension to review this article and providing guidance. Over ten years agone Sheran and Shafik collaborated extensively on BlackBerry Forensics research and recovery of SQLite deleted records.
Sheran is also the author of two Android App Security books:
Blue Team perspective published in September 2012.
Red Team perspective published November 2020: Android Apps Security: Mitigate Hacking Attacks and Security Breaches https://www.amazon.ca/Larn-Android-Security-Stack-Zhauniarovich/dp/1484216814
DFIR Review
The paper presents an interesting arroyo to identify missing records in a SQLite database by studying the records nowadays and data from specific tables. One of the reviewers was curious why the Missing Record Finder tool was presented if information technology is not publicly available.
One of the reviewers found that all SQLite queries in the paper were valid and produced the expected results. The reviewer establish that mirf was able to correctly identify the number of missing records, but did not correctly place which records were missing.
One of the concerns noted was that the conclusions section refers to intentional deletion, but so there is discussion about distinguishing between records deleted by the user and records deleted past the arrangement. The reviewer suggests that the linguistic communication should qualified with "may bespeak" or "likely indicates". Another reviewer had a similar business organisation, that caution should be exercised when discussing a user's intentionality.
Future Work
Future work might include looking at applications that routinely delete rows without user interaction. Information technology would also be beneficial to keep developing mirf and expand its capabilities, particularly in regards to inclusion/exclusion of commits from WAL files. Future piece of work could also include developing a script to analyze missing records from whatsoever SQLite databases. Contributions to complimentary and open source tools to address the shortcomings documented by the authors would be helpful.
Reviewers
Timothy Bollé (Methodology Review, Validated Review using Reviewer Generated Datasets)
Nickolas Ligman (Methodology Review, Verified Review using Writer Provided Datasets)
Johann Polewczyk (Methodology Review, Validated Review using Reviewer Generated Datasets)
Linda Shou (Methodology Review, Validated Review using Reviewer Generated Datasets)
0 Response to "Open and Read Sqlite Databasse Files Reddit"
Postar um comentário