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. XML and Binary Plists are examples of these structured storage objects. 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.
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.
The following screenshot shows the msg_blob records from the messages table in a Facebook orca2.db file. 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:
We can decode the data by :
Create 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).
Then invoke the structured storage manager from the Tools menu:
In the following dialog we need to provide some data:
Source table (main.messages) is the database.tablename that contains the blob column
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
Structured Storage field (msg_blob) is the field/column that contains the blob data
Destination 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
Strcutured storage type (Facebook orca blob) is the encoding type used to store the structured data selected from the list of currently supported types
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. The simplest solution here is to select “Add all elements” from the pop-up menu:
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 following screenshot shows the decode orca blob structure:
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 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: