There are over 6.3 million apps available to the modern data smart device user between the major operating systems like iOS, Android, Windows Phone, and BlackBerry. With only approximately 400 unique apps supported by mobile forensic tools there is a high probability that you, the expert, will run into an app that is not directly supported for decoding. What should be done? Notify the company producing the software—sure! What if you cannot wait for the app to be supported? Now there is a way to support these apps, and parse/decode the data all within the Oxygen Forensic tools!
Oxygen Forensics has released Oxygen Forensic Detective v. 9.3 which includes an updated version of the built-in SQLite Viewer. This updated version will now allow an expert to support an application SQLite database that is not yet supported, or that could be examined further. This easy to use newly released feature is called the Visual Query Builder.The Visual Query Builder is a simple tool that allows the expert to build SQL queries within the Oxygen Forensic® SQLite Viewer using simple drag and drop procedures. The best part—the expert does not have to know SQL! When using our Visual Query Builder, the expert can select each column they require data to be parsed and also relate that table to another existing relational table in the database. During this process a SQL query is being built before the expert’s eyes! Once completed, simply pressing the execute button will run the query and display the output for easy reporting. Also included is the ability to save the built query into the SQLite Viewer query library to use on another examination if the same app or database is encountered.
Once the database is open and available in the SQLite Viewer the expert can view the data in each table as before. Simply click the table and open the Table Data tab as indicated in Figure 1. The expert can review the data in each table using this method. Once the columns of interest are located the expert should select the Visual Query Builder button.
Figure 1 Table Data tab showing the message table
Inside the Visual Query Builder view the expert can now drag any column to the workspace as seen below in Figure 2. The first step in creating a query that might involve multiple tables is identifying the column that “links” the two to each other. These are referred to as keys. If a key icon is located next to a checkbox, that is known as the primary key for the table. When joining tables the expert does not have to link the primary key at all times. Apps at times link the primary key of one table and a secondary key in another table, called a foreign key.
In Figure 2 we have linked the primary key of one table with the foreign key of another. Linking keys can be accomplished by simply selecting the table record name and dragging to the second table releasing once the table record name in the corresponding table is selected. A line will then be drawn from one table to the other table, a relationship (INNER JOIN) will now be represented.
Once the relationship is created, the expert can now select the table records they require to be extracted from the database table. This is completed by simply applying a tick to each box by the corresponding table record name. While this is accomplished a SQL command is being built in the window below.
Figure 2 Visual Query window with 2 selected tables
Once the query has been built, the expert can either save or execute the query. If the expert saves the query the query will be saved into the library of queries and can be reused if the same database is encounter. When executed, the data is displayed in the window below the query in the SQL Editor Window (Figure 3). If the data, or columns, need to be changed the expert can go back to the Visual Query Builder to change/modify selections and then execute or save the query again.
Figure 3 Executed SQL command and the extracted data
The expert can now select the rows that they would like to print into a report by using the first column. If the expert needs to convert any of the columns into a readable date-time or data type, the covert columns method is still operational. Simply right click on the column and select convert column. (Figure 4)
Figure 4 Converting columns to readable data-times
Figure 5 Converted dates and times
As previously mentioned, the expert can save any created query. This information can be accessed using the Library tab located in the SQL Editor view (Figure 6). This is a useful function that will allow the expert to simply double click on the saved query and immediately can execute on the imported database. A description as well as the entire SQL command are easily viewed so the expert can identify the information quickly to determine what app, and what information will be obtained.
Figure 6 SQL Query Library
About Oxygen Forensics, Inc.
Founded in 2000, Oxygen Forensics is the worldwide leading maker of the advanced forensic data examination tools for smartphones and other mobile devices. The company is dedicated to delivering the universal forensic solution covering the widest range of mobile devices running Android, iOS, Blackberry, Windows Phone, Symbian and other operating systems. Law enforcement and government agencies, institutions, corporations and private investigators, help desk personnel and thousands of private consumers rely on Oxygen Forensics products to ensure evidence availability in the event of mobile device data analysis and recovery.
For more information, please visit our web site.
