Prepared Statements in MySQL

What Is a Prepared Statement?

A prepared statement separates an SQL template from the data values supplied at execution. Parameter markers are parsed as data positions, so quotes and SQL punctuation inside a bound value do not become executable SQL.

SELECT order_id, customer_name
FROM orders_prepared_lab
WHERE status = ? AND total_amount >= ?;

Prepared statements offer two main benefits:

• Security: correctly bound values resist SQL injection.
• Reuse: executing the same template repeatedly can reduce repeated parsing and data transfer overhead.

Create a Reproducible Orders Lab

DROP TABLE IF EXISTS orders_prepared_lab;
CREATE TABLE orders_prepared_lab (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_name VARCHAR(100) NOT NULL,
  status VARCHAR(20) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  order_date DATE NOT NULL,
  INDEX ix_orders_status_amount (status, total_amount)
) ENGINE = InnoDB;

INSERT INTO orders_prepared_lab
  (customer_name,status,total_amount,order_date)
VALUES
('Aman','PAID',1200.00,'2026-08-01'),
('Riya','PENDING',500.00,'2026-08-05'),
('Kabir','PAID',750.00,'2026-08-03'),
('Meera','CANCELLED',300.00,'2026-08-04'),
('Sara','PAID',1500.00,'2026-08-10');

The composite index supports equality on status followed by a range on amount. Prepared statements do not replace indexing or query optimization.

Server-Side SQL: PREPARE, EXECUTE, DEALLOCATE

PREPARE order_search FROM
  'SELECT order_id, customer_name, total_amount
   FROM orders_prepared_lab
   WHERE status = ? AND total_amount >= ?
   ORDER BY order_id';

SET @status = 'PAID';
SET @minimum = 1000.00;
EXECUTE order_search USING @status, @minimum;

SET @minimum = 700.00;
EXECUTE order_search USING @status, @minimum;

DEALLOCATE PREPARE order_search;

The SQL interface requires user variables in EXECUTE ... USING, with exactly one value per marker. A prepared statement belongs to the current session and should be deallocated when no longer needed; closing the session also releases it.

Secure PHP PDO Prepared Statement

$pdo = new PDO(
    'mysql:host=localhost;dbname=school;charset=utf8mb4',
    $dbUser,
    $dbPassword,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

$sql = 'SELECT order_id, customer_name, total_amount
        FROM orders_prepared_lab
        WHERE status = :status
          AND total_amount >= :minimum
        ORDER BY order_id';

$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':status' => 'PAID',
    ':minimum' => 1000.00,
]);

$rows = $stmt->fetchAll();

Credentials belong in protected environment configuration, not source control. Native prepares are requested with emulation disabled; deployment must still be tested against the actual PDO MySQL driver and server.

For repeated inserts, prepare once and execute many times:

$insert = $pdo->prepare(
    'INSERT INTO orders_prepared_lab
     (customer_name,status,total_amount,order_date)
     VALUES (:name,:status,:amount,:date)'
);

foreach ($orders as $order) {
    $insert->execute([
        ':name' => $order['name'],
        ':status' => $order['status'],
        ':amount' => $order['amount'],
        ':date' => $order['date'],
    ]);
}

Binding Types, LIKE Patterns, LIMIT and IN Lists

A marker represents one complete data literal. Supply wildcards as part of the value, not around a quoted placeholder:

$stmt = $pdo->prepare(
    'SELECT customer_name FROM orders_prepared_lab
     WHERE customer_name LIKE :pattern'
);
$stmt->execute([':pattern' => '%am%']);

Bind integer LIMIT values explicitly when required by the driver:

$stmt = $pdo->prepare(
    'SELECT order_id FROM orders_prepared_lab
     ORDER BY order_id LIMIT :row_count'
);
$stmt->bindValue(':row_count', 10, PDO::PARAM_INT);
$stmt->execute();

One marker cannot represent several IN values. Generate one marker per value:

$statuses = ['PAID', 'PENDING'];
$marks = implode(',', array_fill(0, count($statuses), '?'));
$sql = "SELECT order_id FROM orders_prepared_lab
        WHERE status IN ($marks) ORDER BY order_id";
$stmt = $pdo->prepare($sql);
$stmt->execute($statuses);

The generated SQL punctuation comes from trusted program logic; only status values come from data.

Dynamic Table, Column and Sort Identifiers

Parameters cannot stand for identifiers or keywords:

-- Invalid idea: ? cannot represent a column name
SELECT * FROM orders_prepared_lab ORDER BY ?;

Use strict mappings for a dynamic sort:

$allowedSort = [
    'date' => 'order_date',
    'amount' => 'total_amount',
    'customer' => 'customer_name',
];
$allowedDirection = ['asc' => 'ASC', 'desc' => 'DESC'];

$sort = $allowedSort[$_GET['sort'] ?? 'date'] ?? 'order_date';
$direction = $allowedDirection[strtolower($_GET['dir'] ?? 'desc')]
             ?? 'DESC';

$sql = "SELECT order_id, customer_name, total_amount
        FROM orders_prepared_lab
        WHERE status = :status
        ORDER BY `$sort` $direction";
$stmt = $pdo->prepare($sql);
$stmt->execute([':status' => 'PAID']);

Only hard-coded mapping values enter the SQL structure. Never “sanitize” an arbitrary identifier with simple character removal and assume it is authorized.

Transactions, Errors and Statement Lifecycle

try {
    $pdo->beginTransaction();

    $update = $pdo->prepare(
        'UPDATE orders_prepared_lab
         SET status = :status
         WHERE order_id = :id'
    );
    $update->execute([':status' => 'PAID', ':id' => 2]);

    if ($update->rowCount() !== 1) {
        throw new RuntimeException('Order was not updated');
    }

    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;
}

• Prepare does not begin a transaction; transaction boundaries are separate.
• Use exceptions and rollback deliberately; do not expose SQL or credentials in user-facing errors.
• Reuse only while connection and statement lifetime make sense.
• Server-side SQL PREPARE cannot contain multiple semicolon-separated statements.
• Monitor max_prepared_stmt_count if long-lived sessions create many statements.

Prepared-Statement Security and Review Checklist

1. Bind every external data value.
2. Never concatenate raw request data into SQL.
3. Allowlist identifiers, operators and sort directions.
4. Use one marker for each IN-list item.
5. Handle LIKE wildcards as data and define escaping rules.
6. Validate business type, range, length and authorization separately.
7. Use least-privileged database accounts.
8. Enable exceptions and avoid leaking database errors.
9. Test quote, backslash, Unicode, NULL and boundary inputs.
10. Measure query plans and add indexes; preparation is not optimization.

Review SQL injection prevention, query optimization, transactions and user privileges.

Official References

• MySQL 8.4: Prepared Statements
• MySQL 8.4: PREPARE Statement
• PHP Manual: PDO::prepare

Marker rules, lifecycle, server SQL syntax and PDO binding limits were checked against official MySQL and PHP documentation.