Prompt

Analyze a Firefox sqlite history database and answer questions about what happened. It you are not familiar with SQL you may want to learn more about SQL here: https://www.tutorialrepublic.com/sql-tutorial/

browser.sqlite10240.0KB

Tutorial Video

Walk-Through

This challenge will give you experience analyzing a SQLite database. The answers can be obtained by using the sqlite3 Linux program or a GUI-based viewer. There are also browser-based SQLite viewers such as https://inloop.github.io/sqlite-viewer/. The examples below use the SQLPro viewer for OSX.

After opening the SQLite database using a viewer, selecting the moz_places table will reveal the information needed to answer the first few questions.

From this table, it is possible to identify what pages the user has visited

The remaining questions can be answers by visiting the URL that are listed inside the database. The URL with id 290 is for a bitcoin transaction listed on blockchain.info. The main page displays the ID as well as the total value of the inputs.

The Bitcoin transaction ID and the amounts that were transferred are all listed on the blockchain.info page.

Questions

Q1. What did the user search for on craigslist?

The user’s search can be seen in row 23 inside the “query’ parameter in the URL.

Q2. What was the current price (USD) of bitcoin when the user was browsing?

The current price is listed in row 23 in of the “title” of Bitstamp’s homepage.

Q3. What Bitcoin exchange did the user log in to?

The Bitcoin exchange is identified in row 253, which shows the user successfully loading their account page after logging in.

Q4. What is the email that was used to log into the exchange?

The email can be found on row 47 in the “title” of the Gmail webpage.

Q5. What was the ID of the Bitcoin transaction that the user looked at?

The ID of the Bitcoin transaction is the “Hash” value listed on the blockchain.info page.

Q6. What was the total BTC value of all the inputs of the Bitcoin transaction?

The total BTC value of the inputs can be obtained by adding up the values of all the BTC inputs on the blockchain.info page.

Q7. Which Bitcoin address received the majority of the Bitcoin in the transaction?

The right side of the blockchain.info page contains the addresses and amounts that were sent to each recipient. The address that received the majority of the Bitcoin was that one that was sent the most BTC.

History