Challenge: Broken Database

Difficulty: Medium
Bugs: 2
Time: 60 minutes

The Problem

The database class has two critical security vulnerabilities.

Symptoms:

SQL injection vulnerability in query method
Parameters not passed to execute(), breaking queries

Setup

Backup Current Files

cp framework/Core/Database.php framework/Core/Database.php.backup

Copy Broken Files

cp challenges/broken-database/framework/Core/Database.php framework/Core/
cp -r challenges/broken-database/Http/controllers/posts Http/controllers/

Add Routes

cat challenges/broken-database/routes/routes.php >> routes/routes.php

Test the Bugs

Visit /posts - SQL injection possible!
Visit /posts/1 - Query fails!

Bug #1: SQL Injection Vulnerability

The Symptom

User input is concatenated directly into SQL queries.

What's Happening

// BROKEN - SQL injection!
public function query($query, $params = []) {
    // Concatenates user input directly
    $query = $query . " WHERE id = " . $_GET['id'];
    $this->statement = $this->connection->prepare($query);
    $this->statement->execute();
}

Why It's Dangerous

Normal: ?id=1
Query: SELECT * FROM posts WHERE id = 1

Attack: ?id=1 OR 1=1
Query: SELECT * FROM posts WHERE id = 1 OR 1=1
Result: Returns ALL posts!

Attack: ?id=1; DROP TABLE posts--
Query: SELECT * FROM posts WHERE id = 1; DROP TABLE posts--
Result: Deletes your entire table!

Critical: SQL injection is one of the most dangerous web vulnerabilities. Always use prepared statements!

The Fix

Use parameter binding:

// ✅ CORRECT
public function query($query, $params = []) {
    $this->statement = $this->connection->prepare($query);
    $this->statement->execute($params);
    return $this;
}

Bug #2: Parameters Not Passed to execute()

The Symptom

Queries with parameters fail or return no results.

What's Happening

// BROKEN - params ignored
public function query($query, $params = []) {
    $this->statement = $this->connection->prepare($query);
    $this->statement->execute(); // Missing $params!
    return $this;
}

When you call:

$db->query('SELECT * FROM posts WHERE id = :id', ['id' => 123]);

The :id placeholder is never replaced because $params isn't passed to execute().

The Fix

Pass parameters to execute:

// ✅ CORRECT
public function query($query, $params = []) {
    $this->statement = $this->connection->prepare($query);
    $this->statement->execute($params); // Pass params!
    return $this;
}

How Prepared Statements Work

Step 1: Prepare

$statement = $pdo->prepare('SELECT * FROM posts WHERE id = :id');

PDO creates a prepared statement with a placeholder.

Step 2: Execute with Parameters

$statement->execute(['id' => 123]);

PDO safely binds the value and executes the query.

Step 3: Fetch Results

$post = $statement->fetch();

Why It's Safe: PDO escapes the parameters, preventing SQL injection. The value is treated as data, not code.

Verification

After fixing both bugs, run verification:

php artisan verify broken-database

Expected output:

╔══════════════════════════════════════════════════════════════╗
║           DALT Challenge Verification System                ║
╚══════════════════════════════════════════════════════════════╝

Verifying: broken-database
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

✓ Parameters passed to execute()
✓ No SQL concatenation found
✓ Prepared statements used correctly
✓ No SQL injection vulnerabilities

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Results: 4/4 tests passed
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

✅ All tests passed! Challenge complete!

Testing Your Fix

Test SQL Injection Protection

# Should only return post 1
curl "http://localhost:8000/posts/1"

# Should NOT return all posts (injection blocked)
curl "http://localhost:8000/posts?search=1' OR '1'='1"

Test Parameter Binding

# Should work correctly
curl "http://localhost:8000/posts?search=test"

Success Criteria

When fixed correctly:

✅ All queries use parameter binding
✅ No SQL injection vulnerabilities
✅ Queries with parameters work correctly
✅ Search functionality works safely

Learning Objectives

After completing this challenge, you understand:

✅ Why SQL injection is dangerous
✅ How parameter binding prevents attacks
✅ How PDO prepared statements work
✅ Why parameters must be passed to execute()
✅ How to write secure database queries

Debugging Tips

Check the Query

// In Database::query()
dd(['query' => $query, 'params' => $params]);

Test SQL Injection

Try this in your browser:

http://localhost:8000/posts?search=1' OR '1'='1

If it returns all posts, you have SQL injection!

Verify Parameters

// After execute()
dd($this->statement);

Files to Investigate

framework/Core/Database.php - Query execution (bugs here!)
Http/controllers/posts/show.php - Uses parameter binding
Http/controllers/posts/index.php - Vulnerable to SQL injection

Cleanup

After completing the challenge:

# Restore original Database class
cp framework/Core/Database.php.backup framework/Core/Database.php

# Remove challenge controllers (optional)
rm -rf Http/controllers/posts

Next Challenge

Continue to Lesson 6: Sessions or try Challenge: Broken Session.

Broken Database

On this page