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 user’s table.
Code:
CREATE TABLE messages(
time INTEGER PRIMARY KEY,
message TEXT,
userID integer,
FOREIGN KEY (userID) REFERENCES users(userID)
)
CREATE TABLE users(
userID INTEGER PRIMARY KEY,
username Text
)In the example above trying to add a new message with a userID that does not exist in the users table will fail, likewise attempting to delete a user when there is a message that refers to that userID will also fail.
When they exist, they, therefore, provide a useful clue to help us understand the relationships between tables.
Note there is much more to foreign keys than in my explanation above, but this is sufficient to understand the main points of this blog. There is a link to the SQLite website at the end of this blog where foreign keys are discussed at length.
The rest of this blog follows the basic thought processes I might go through when initially determining which tables relate to each other and creating the query for my final report.
The DB I am looking at has 58 tables and working out the relations between those tables is obviously going to be long-winded, so where do we start?
To decide what we need to do it is, of course, useful to understand something about the investigation. In this case, as discussed above, we are interested in file transfers between users.
There is a table named file transfers and so this would be a logical place to start looking. The content of the table looks as follows:
and the schema for the table:
Code:
CREATE TABLE FileTransfers
(
FileTransferId integer not null primary key autoincrement,
ConversationId integer not null,
RemotePin text not null,
UserId integer not null,
ParticipantId integer null,
ContactId integer null,
Incoming integer not null,
State integer not null,
AbortReason integer null,
Path text null,
ContentType text not null,
Description text not null,
SuggestedFilename text null,
TotalSize integer null,
BytesTransferred integer not null, GroupId integer null,
foreign key (UserId) references Users (UserId),
foreign key (ParticipantId) references Participants (ParticipantId) on delete set null,
foreign key (ContactId) references Contacts (ContactId) on delete set null
)You should see the foreign key references straight away and it shows us that the users, participants and contacts tables are all related to this table and we can see what columns are used to formulate the relationship. Useful information.
The users table looks as follows:
We can now create a simple join to start building some sort of report, what I would like to do is have a report that shows
The trasferID, file path and name, whether the file was sent or received, when and by whom.
Before I do this I like to take a look at the base table, in this case, the FileTransfers table to get an idea of what I expect to see in my query. The FileTransfers table contains 22 rows and so it follows that my report, after I have created my JOINs, should also have 22 rows. This might seem a little obvious but taking stock and making sanity checks as the report is built will pick up any errors early in the process.
The fields we want from the FileTransfers table are:
FileTransferId,
Path,
SuggestedFilename,
Incoming,
UserId
The field from Users is:
DisplayName
We can see from the foreign key description above that two tables are joined on the userId column, so we will use the same tables and columns in the foreign key for our JOIN.
As we want to have one row in the final report for each row in the FileTransfers table we want a LEFT JOIN, if you need a refresher on join types have a look at the article at this link.
The query to create this report is shown below and we can see that there are 22 rows in it as expected.:
The only thing missing is when the files were transferred. An examination of the FileTransfers table shows that this information is not stored in there and it is unlikely to be in Users, Participants or Contacts (I have checked – trust me). So how do we find out when the file was transferred? one method is to query the sqlite_master table to see if any tables reference the FileTransfers table (rather than the other way around, which is what we have been looking at so far).
The query to do this is shown below. Note that I tend to use “like” in my queries rather than “=” i.e.:
Code:
SELECT *
FROM sqlite_master
WHERE sqlite_master.sql LIKE '%references filetransfers%'rather than
Code:
SELECT *
FROM sqlite_master
WHERE sqlite_master.sql = '%references filetransfers%'This is simply because “=” is case sensitive in SQLite and “LIKE” is case insensitive, this just avoids missing something, particularly, as in this case, where the schema has capitalized portions of the table names.
There are three tables with foreign keys that reference the FileTransfers table, the most promising of these looks to be the TextMessages table, which has the following foreign key:
Code:
CREATE TABLE TextMessages
{
...
foreign key (FileTransferId) references FileTransfers (FileTransferId) on delete set null,
...
)So we take a quick look at the TextMessages table and see what is in the FileTransferId column, as this is the table and column referenced above:
Not what we were hoping for, there are no values at all in the FileTransferId column.
So we look at one of the other two tables that reference the FileTransfer table, the PictureTransfers table is referenced on both the SmallFileTransferId column and the LargeFileTransferId column. I have no idea what these mean – so let’s take a look at this table:
Now, this is more interesting, there are also 22 rows in this table and the SmallFileTransferId column has the same 22 unique IDs that we saw in the FileTransfers table. But there is still no date information.
So we do the same process as before and look to see what references the PictureTransfers table, and we see that the TextMessage table references PictureTransfers:
The line of interest here is reproduced below:
CREATE TABLE TextMessages
(
…
foreign key (PictureTransferId)
references PictureTransfers (PictureTransferId)
on delete set null,
…
)
This leads us back to the TextMessage table, but this time the referenced field is PictureTransferId, so we check out that column:
An examination of the TextMessage table shows that there is a timestamp column.
So before we create our final query if we summarise what we have found.
The FileTransfers table is referenced by the PictureTransfers table which is in turn referenced by the TextMessages table. We want a report showing each File Transfer along with various other data but particularly user name and date, so we want a report with 22 rows.
The final query is shown below along with the visual designer window. The incoming column has a yes/no boolean conversion applied and the timestamp column was a unix epoch date and is converted by the Browser as such. While the SQL looks complex with three JOINs, the visual query designer shows this in a much more intuitive way.
Of course, all of the above was created using the Forensic Browser for SQLite using drag and drop query building.
If you are a Forensic Browser user and have a similar problem I hope this helps and gives you an idea of how your could approach it, but if you do need further assistance then please do not hesitate to get in touch, it’s all part of the service.
The SQLite website has some very detailed information on Foreign Keys here: