Talking to MySQL safely – prepared statements everywhere

MySQL prepared statements – the safe way to run queries with user input. The intro series showed basic queries. If you still concatenate strings like "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'", stop. That is how SQL injection happens.

PDO setup

<?php
declare(strict_types=1);

$dsn = 'mysql:host=127.0.0.1;dbname=myapp;charset=utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

$pdo = new PDO($dsn, 'db_user', 'db_pass', $options);

ATTR_EMULATE_PREPARES => false sends real prepared statements to MySQL. Leave it that way.

SELECT with placeholders

$stmt = $pdo->prepare('SELECT id, name, email FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);
$user = $stmt->fetch();

if (!$user) {
    // handle missing user
}

The database treats :email as data, not SQL. Even if someone submits ' OR '1'='1, the query stays safe.

INSERT and UPDATE

$stmt = $pdo->prepare(
    'INSERT INTO notes (user_id, title, body, created_at) VALUES (:user_id, :title, :body, :created_at)'
);

$stmt->execute([
    'user_id'    => $userId,
    'title'      => $title,
    'body'       => $body,
    'created_at' => date('Y-m-d H:i:s'),
]);

$newId = (int) $pdo->lastInsertId();

IN lists and integers

Never interpolate a raw list into SQL. For a fixed number of IDs, use multiple placeholders. For dynamic lists, build placeholders carefully or use a helper. Cast IDs to integers when you know they are numeric:

$ids = array_map('intval', $ids);
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT * FROM posts WHERE id IN ($placeholders)");
$stmt->execute($ids);

Repository pattern (light version)

Wrap queries in a class so controllers stay clean. One place for SQL, one place to fix bugs:

<?php
declare(strict_types=1);

namespace AppModels;

use PDO;

class NoteRepository
{
    public function __construct(private PDO $db) {}

    public function forUser(int $userId): array
    {
        $stmt = $this->db->prepare(
            'SELECT id, title, body FROM notes WHERE user_id = :uid ORDER BY created_at DESC'
        );
        $stmt->execute(['uid' => $userId]);
        return $stmt->fetchAll();
    }
}

Prepared statements everywhere is non-negotiable for user-facing apps. No exceptions for ‘just this one admin query’.