{"id":474,"date":"2016-09-01T19:35:00","date_gmt":"2016-09-01T19:35:00","guid":{"rendered":"http:\/\/sqliteforensictools.com\/?p=474"},"modified":"2019-07-08T18:43:03","modified_gmt":"2019-07-08T18:43:03","slug":"forensic-browser-for-sqlite-structured-storage-manager","status":"publish","type":"post","link":"https:\/\/sqliteforensictoolkit.com\/forensic-browser-for-sqlite-structured-storage-manager\/","title":{"rendered":"Forensic Browser for SQLite – Structured Storage Manager"},"content":{"rendered":"\n

\n\nOften 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.<\/p>\n\n\n\n

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. <\/p>\n\n\n\n

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: <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

\n\nWe can decode the data by :<\/p>\n\n\n\n

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).<\/p>\n\n\n\n

Then invoke the structured storage manager from the Tools menu:\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

\n\nIn the following dialog we need to provide some data:\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

\n\nSource table (main.messages) is the database.tablename that contains the blob column<\/p>\n\n\n\n

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<\/p>\n\n\n\n

Structured Storage field (msg_blob) is the field\/column that contains the blob data<\/p>\n\n\n\n

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<\/p>\n\n\n\n

Strcutured storage type (Facebook orca blob) is the encoding type used to store the structured data selected from the list of currently supported types<\/p>\n\n\n\n

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: <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

\n\nThe 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.<\/p>\n\n\n\n

The following screenshot shows the decode orca blob structure:\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

\n\nYou 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. <\/p>\n\n\n\n

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:\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure><\/div>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"off","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[3],"tags":[],"class_list":["post-474","post","type-post","status-publish","format-standard","hentry","category-forensic-browser-for-sqlite"],"_links":{"self":[{"href":"https:\/\/sqliteforensictoolkit.com\/wp-json\/wp\/v2\/posts\/474","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqliteforensictoolkit.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqliteforensictoolkit.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqliteforensictoolkit.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sqliteforensictoolkit.com\/wp-json\/wp\/v2\/comments?post=474"}],"version-history":[{"count":2,"href":"https:\/\/sqliteforensictoolkit.com\/wp-json\/wp\/v2\/posts\/474\/revisions"}],"predecessor-version":[{"id":560,"href":"https:\/\/sqliteforensictoolkit.com\/wp-json\/wp\/v2\/posts\/474\/revisions\/560"}],"wp:attachment":[{"href":"https:\/\/sqliteforensictoolkit.com\/wp-json\/wp\/v2\/media?parent=474"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqliteforensictoolkit.com\/wp-json\/wp\/v2\/categories?post=474"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqliteforensictoolkit.com\/wp-json\/wp\/v2\/tags?post=474"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}