• Paul

    by Published on 23rd March 2017 15:48  Number of Views: 1674 
    Article Preview

    SQL is an extremely powerful programming language, and understanding SQL database schemas can often help immensely when creating queries on the database in question.

    The particular feature that I want to discuss in this blog is foreign keys, and I intend to show their value by example, as if I have just been asked to create a report on a database (BlackBerry Messenger master.db from an IOS device) of which I have little knowledge. To help with this let's assume I have been asked to investigate file transfers between the user of this DB and his/her contacts and create a simple report to detail the transfers.

    This example is, of course, a little contrived but it should, I hope, show the principles well.


    First, what are Foreign Keys?

    Simply put, foreign keys provide a way of ensuring that relationships between tables are valid. For example, in the case of a simple messaging database, they can ensure that for every message with a userID there is an entry for the userID in the users table.

    e.g.:

    Code:
    CREATE TABLE messages(
      time INTEGER PRIMARY KEY, 
      message TEXT,
      userID
    ...
    by Published on 14th November 2016 17:34  Number of Views: 6688 
    Article Preview

    At a recent conference while talking about SQLite forensics I found out that some people still use non forensic tools to investigate databases with WAL files and were quite happy that they would not miss anything of importance. This is something I disagree with very, very, strongly and I hope in this article to explain the very basics of how WAL files work and show why you should not use non-forensic tools to examine SQLite databases.

    Most of the databases we examine are appended to as user interactions take place. Be it a web browser which contains tables related to browsing events whereby new URLs are appended to associated tables as and when the user navigates to them. Or a messaging application where each message to and from the user is appended to the end of a list of such messsages and new contacts are likewise added to the end of a list of contacts. In the example below we will consider ...
    by Published on 2nd November 2016 10:34  Number of Views: 6181 
    Article Preview

    I have seen a number of posts on bulletin boards recently that refer to some of the main stream software failing to be able ...
    Published on 13th September 2016 12:27  Number of Views: 7910 
    Article Preview

    In a recent forensic case involving recovered deleted sms messages from an sms.db file on an IOS mobile device none of the mainstream mobile phone forensic software made the link between sender and recipient for the recovered records of interest.

    I have been asked a few times recently about obtaining the third party of a deleted IOS SMS message that has been recovered by the Forensic Browser. The procedure is simply to create a JOIN between two (or three there are two ways of establishing the third party) tables and if the data is in the relevant tables then a link is made. Unfortunately when messages are deleted, particularly when entire conversations are deleted, the primary keys on the all-important tables are often overwritten making these joins impossible.

    When the case mentioned was looked at with the Browser neither of the JOINS described above allowed the investigators to ascertain who the third party in a deleted conversation was. So I was asked to take a look at the DB further and I managed ...
    Published on 1st September 2016 09:08  Number of Views: 6653 
    Article Preview

    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 ...
    by Published on 29th June 2016 10:00  Number of Views: 4646 
    Article Preview

    I recently saw a Twitter conversation where a user wanted to see the EXIF data from some image files displayed as maps and showing a clickable URL for googlemaps. The latter part of this problem can easily be solved with the Browser - the steps are as follows:

    1. Run exiftool and export the relevant results as a csv
    2. Import the csv into an SQLite database
    3. Use the Browser to create a query displaying the lat and long as two fields
    4. Create a VIEW to represent this query
    5. Use the Browser to display a map for each row showing the location defined by the lat and longs
    6. Use the Browser to combine the lat and longs into a clickable URL




    This example assumes that you want to display the locations of all the files in the path "E:\My Pictures"


    1. Run exiftool and export the relevant results as a csv
    ...
    Published on 25th May 2016 21:24  Number of Views: 6257 
    Article Preview

    A. When a journal is in use (potentially).

    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. 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. 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. This securely deleted data can and sometimes does exist for quite some time.



    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. Think of a simple messaging application whereby a message is received asking to be "friends" ...
    Page 1 of 12 12311 ... LastLast