Lets look at an example with a rollback journal. in order to maintain database integrity SQLite MUST maintain a copy of the data that has been deleted somewhere until it ‘knows’ the last transaction has completed correctly, that somewhere is the journal. Well that gives me a warm fuzzy feeling, when I delete it, it’s gone! But hang on, if SQLite immediately overwrites something I delete and something goes wrong before the transaction completes how can SQLite rewind the DB to its last good state? It would make no sense to update the messages table with a message that referred to user x when user x’s details had not yet been added to the users table, so both these actions could be wrapped in a transaction so either both tables are updated or neither is updated.įirst we need to understand what secure delete does, according to the SQLite website the command (pragma) that initiates secure delete says “When secure-delete is (sic) on, SQLite overwrites deleted content with zeros.” Think of a simple messaging application whereby a message is received asking to be “friends” – our hypothetical app needs to write the friend request to the messages table and add a user to the users table. We also need to understand that SQLite groups actions together in transactions, transactions can be one database update (write, modify, delete) or it can be many thousands of such actions as determined by the user. This securely deleted data can and sometimes does exist for quite some time. It might seem obvious then to state that a copy of securely deleted data would need to be kept in order to facilitate this functionality. Simply put if an operation fails for whatever reason then the changes to the database are unwound to put the DB back to its last known good state. The raison d’etre for a journal, be it a traditional rollback journal or the newer SQLite Write Ahead Log (WAL) file is to maintain database integrity. The screenshot below shows a JOIN created on the two tables and just those I require (containing the msg_id, date, userID, message text and senderID) are selected for my custom report:Ī. You can select a subset of the above but as all of the data is added to individual columns in a new table it is easier to use the SQL features of the Browser to select your chosen columns. The following screenshot shows the decode orca blob structure: The Browser will then parse a structured storage blob and decode each of the data types and create tree structure that represents the underlying datat and create an associated table with a new column for each element. The simplest solution here is to select “Add all elements” from the pop-up menu: Once all the above has been selected we are ready to decide which items from the decoded blob we want to select to copy to the extracted data table. Strcutured storage type (Facebook orca blob) is the encoding type used to store the structured data selected from the list of currently supported types Structured Storage field (msg_blob) is the field/column that contains the blob dataĭestination table name (StructuredStorage_messages) i steh name of a new table that will be created in the case file that will hold the extracted data ID field (msg_id) is the primary key of this table – we need something unique so that a query can be made tying the extracted data back to its source Source table (ssages) is the database.tablename that contains the blob column In the following dialog we need to provide some data: Then invoke the structured storage manager from the Tools menu: The blobs are shown in their raw (hex) form and are clearly a binary (non-text format) and thus it is not possible to query these objects using normal SQL commands:Ĭreate a case file and then open the Facebook orca2.db (the decoded data from the orca blobs will be written to a new table in the case file). The following screenshot shows the msg_blob records from the messages table in a Facebook orca2.db file. The Structured Storage Manager does this by using a template to break down the items in each BLOB object and converts the data to a table held within the case file. ![]() ![]() Often the data in each blob in a table is in the same format and it would be useful to query these objects and include selected data in a report. XML and Binary Plists are examples of these structured storage objects. Often data held within tables in databases is stored within a BLOB (Binary Large OBject) this data is often structured data that is encoded in a particular format. Forensic Browser for SQLite – Structured Storage Manager
0 Comments
Leave a Reply. |