CFS280 SQLite Lab

Case Overview: ABQ-737-BLUE

Late last night, Albuquerque Police recovered a burner phone during a search of a suspicious vehicle abandoned near a commercial laundry facility in the North Valley.

Preliminary forensic triage indicates the device contains a collection of SQLite databases used by local narcotics distributors to coordinate logistics. Your objective is to authorize the extraction, navigate the complex database schema, and isolate the "smoking gun" communication that links the suspect to the alias 'Heisenberg'.

Investigator Identity Required

Unauthorized access is a federal offense • APD Digital Forensics Unit

APD Forensic Laboratory: ABQ-737-BLUE

Evidence Extraction

Lead Investigator

Unauthorized

Live Extraction

Lab Instructions

Forensic Protocol

Precision is required. Ensure every query ends with a semicolon (;). Successfully executed commands will clear the terminal automatically.

Task 1: Basic Triage

View all records in the contacts table.

SELECT * FROM contacts;

Task 2: Message Overview

View all records in the messages table to see the total "noise".

SELECT * FROM messages;

Task 3: Identifying Heisenberg

Find the id for the contact named 'Heisenberg'.

SELECT id FROM contacts WHERE name = 'Heisenberg';

Task 4: Keyword Search

Locate any messages containing the drug-related keyword 'batch'.

SELECT * FROM messages WHERE body LIKE '%batch%';

Task 5: THE CHALLENGE

Convert the timestamp 1712361600 into human-readable time:

SELECT * FROM messages WHERE timestamp = 1712361600;

Task 6: Wireless Mapping

Identify the internal network SSID in the wifi_log table containing the characters 'A1A'.

SELECT * FROM wifi_log WHERE ssid LIKE '%A1A%';

Task 7: Evidence Correlation

Perform a JOIN to see the Name of the contact alongside the Body of the message.

SELECT contacts.name, messages.body FROM messages JOIN contacts ON messages.contact_id = contacts.id;

Task 8: Timeline Construction

Finalize the investigation by ordering the joined evidence by timestamp DESC;.

SELECT contacts.name, messages.body FROM messages JOIN contacts ON messages.contact_id = contacts.id ORDER BY timestamp DESC;

DB Schema

contacts: id, name, phone

messages: id, contact_id, body, timestamp

wifi_log: id, ssid, last_conn

SQL Fundamentals Reference

Use this guide to master the commands required for mobile database extraction. Remember: Every command must end with a semicolon (;).

1. Basic Retrieval

SELECT * FROM contacts;

2. Filtering Data

SELECT * FROM contacts WHERE name = 'Jesse Pinkman';

3. Keyword Search

SELECT * FROM messages WHERE body LIKE '%territory%';

4. Joining Tables

SELECT contacts.name, messages.body FROM messages JOIN contacts ON messages.contact_id = contacts.id;

APD-SQL-v4.0

Output

No Data

Awaiting Query...