🛡️ Prepared Statements and Security

The Threat Landscape

Any application that mixes user input with a database faces a family of risks. The big ones:

• SQL injection — malicious SQL smuggled in through user input
• Data exposure — leaking sensitive data through errors or over-broad queries
• Weak access control — over-privileged database accounts
• Insecure credential storage — plaintext or weakly hashed passwords

💡 The bank-vault analogy. Your database is a vault of valuables. Connection credentials are the keys to the building, and user privileges are the permissions to open specific safety-deposit boxes. SQL injection is a thief tricking the guard into opening doors they never should. A prepared statement is a strict protocol the guard follows that simply can't be talked out of — no matter what the "customer" says, data is treated as data, never as instructions.

How SQL Injection Works

Injection happens when untrusted input is concatenated directly into a query string, so the database can no longer tell where your SQL ends and the attacker's begins. Consider this classic vulnerable login:

<?php
// DANGER — never do this
$username = $_POST['username'];
$sql = "SELECT * FROM users WHERE username = '$username'";
$result = $mysqli->query($sql);

If an attacker types ' OR '1'='1 into the username box, the string becomes:

SELECT * FROM users WHERE username = '' OR '1'='1'

Because '1'='1' is always true, the WHERE clause matches every row — the attacker is logged in, often as the first user, which is frequently an admin.

The many faces of injection

Prepared Statements: The Fix

A prepared statement separates the SQL structure from the data. You send the query with placeholders first; the database compiles that template; then you send the values separately. Because the template is already compiled, incoming values can only ever be data — never new SQL. Even ' OR '1'='1 is treated as a literal username string to search for, which simply doesn't exist.

Implementing in MySQLi & PDO

PDO — named parameters (recommended)

<?php
$stmt = $pdo->prepare(
    'SELECT * FROM users WHERE username = :username AND status = :status'
);
$stmt->execute([
    ':username' => $_POST['username'],   // safe: bound as data
    ':status'   => 1,
]);

foreach ($stmt as $row) {
    echo "{$row['username']} — {$row['email']}\n";
}

PDO — positional parameters

<?php
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = ? AND status = ?');
$stmt->execute([$_POST['username'], 1]);
$users = $stmt->fetchAll();

MySQLi — object-oriented with type binding

<?php
$stmt = $mysqli->prepare('SELECT * FROM users WHERE username = ? AND status = ?');

// Type string: s = string, i = integer, d = double, b = blob
$stmt->bind_param('si', $_POST['username'], $status);
$status = 1;

$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo "{$row['username']} — {$row['email']}\n";
}
$stmt->close();

Secure Password Storage

Prepared statements protect the query; password hashing protects your users if the database is ever stolen. Never store plaintext passwords, and never use MD5 or SHA1 — they are far too fast for attackers to brute-force. PHP's built-in functions do the right thing by default.

<?php
// REGISTER — hash before storing (bcrypt by default, salt handled for you)
$hash = password_hash($_POST['password'], PASSWORD_DEFAULT);

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

// LOGIN — verify against the stored hash
$stmt = $pdo->prepare('SELECT id, password_hash FROM users WHERE username = :u');
$stmt->execute([':u' => $username]);
$user = $stmt->fetch();

if ($user && password_verify($_POST['password'], $user['password_hash'])) {
    echo 'Login OK';

    // Keep hashes current if PHP's default algorithm improves
    if (password_needs_rehash($user['password_hash'], PASSWORD_DEFAULT)) {
        $newHash = password_hash($_POST['password'], PASSWORD_DEFAULT);
        $pdo->prepare('UPDATE users SET password_hash = :h WHERE id = :id')
            ->execute([':h' => $newHash, ':id' => $user['id']]);
    }
} else {
    echo 'Invalid username or password';   // don't reveal which was wrong
}

Beyond Prepared Statements

Prepared statements are the primary defense, but real security is layered. Add these on top.

Validate input anyway

Prepared statements stop injection, but validation catches bad or malicious data earlier and gives users better feedback.

<?php
$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
if ($id === false || $id === null || $id <= 0) {
    http_response_code(400);
    exit('Invalid user ID.');
}
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute([':id' => $id]);

Whitelist dynamic identifiers

Placeholders can bind values, but not table or column names. If those must be dynamic (e.g. a sort column from the UI), check them against an allow-list.

<?php
// UNSAFE — a column name straight from the user
// $sql = "SELECT * FROM products ORDER BY " . $_GET['sort'];

// SAFE — only permit known-good columns
$allowed = ['name', 'price', 'created_at'];
$sort = in_array($_GET['sort'] ?? '', $allowed, true) ? $_GET['sort'] : 'name';
$sql  = "SELECT * FROM products ORDER BY $sort";   // $sort is now trusted
$rows = $pdo->query($sql)->fetchAll();

A Secure Data-Access Layer

Centralizing database work in one class means the safe way is the only way — every query in your app automatically goes through prepared statements.

<?php
class Database
{
    private PDO $conn;

    public function __construct()
    {
        $dsn = sprintf(
            'mysql:host=%s;dbname=%s;charset=utf8mb4',
            getenv('DB_HOST') ?: 'localhost',
            getenv('DB_NAME')
        );
        $this->conn = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASS'), [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,   // real prepared statements
        ]);
    }

    // Always prepared — callers can't bypass it
    public function select(string $sql, array $params = []): array
    {
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function insert(string $table, array $data): string
    {
        $cols = implode(', ', array_keys($data));
        $ph   = ':' . implode(', :', array_keys($data));
        $stmt = $this->conn->prepare("INSERT INTO $table ($cols) VALUES ($ph)");
        $stmt->execute($data);
        return $this->conn->lastInsertId();
    }

    public function beginTransaction(): bool { return $this->conn->beginTransaction(); }
    public function commit(): bool           { return $this->conn->commit(); }
    public function rollBack(): bool         { return $this->conn->rollBack(); }
}

<?php
$db = new Database();

// Safe read — params are bound automatically
$users = $db->select(
    'SELECT id, username FROM users WHERE status = :s',
    [':s' => 1]
);

// Safe write returning the new id
$newId = $db->insert('users', [
    'username'      => 'newuser',
    'email'         => 'new@example.com',
    'password_hash' => password_hash('secret123', PASSWORD_DEFAULT),
]);

// A transaction: both inserts succeed or neither does
$db->beginTransaction();
try {
    $orderId = $db->insert('orders', ['user_id' => 1, 'total' => 99.99]);
    $db->insert('order_items', ['order_id' => $orderId, 'product_id' => 42, 'qty' => 2]);
    $db->commit();
} catch (Throwable $e) {
    $db->rollBack();
    error_log($e->getMessage());
}

Hands-on Exercise

<?php
// secure_login.php
$username = $_POST['username'] ?? '';
$password = $_POST['password'] ?? '';

$stmt = $pdo->prepare(
    'SELECT id, username, password_hash FROM users WHERE username = :u'
);
$stmt->execute([':u' => $username]);   // ' OR '1'='1 is just a literal string here
$user = $stmt->fetch();

if ($user && password_verify($password, $user['password_hash'])) {
    session_start();
    session_regenerate_id(true);       // prevent session fixation
    $_SESSION['user_id']  = $user['id'];
    $_SESSION['username'] = $user['username'];
    header('Location: /dashboard.php');
    exit;
}

// Same message for both failure modes — don't help attackers enumerate users
echo 'Invalid username or password';

Summary & Quiz

📚 Further Reading

• OWASP — SQL Injection Prevention Cheat Sheet
• OWASP Top 10 Web Application Security Risks
• PHP Manual — password_hash()

🚀 What's Next?

You've secured the MySQL side of the world. Next we broaden our horizons: PostgreSQL Features and Setup introduces the other giant of open-source relational databases and the powerful features that set it apart.