MySQL Prepared Statement

Master MySQL prepared statements — PREPARE / EXECUTE / DEALLOCATE PREPARE for SQL injection protection and plan reuse. Covers the difference between server-side and application-side prepared statements, when each is appropriate, dynamic SQL with variable identifiers, and security best practices.

A prepared statement is a SQL query parsed and compiled once by the server, then executed many times with different parameter values. Instead of submitting the full SQL text every time, you submit a template with placeholders (?) and supply the actual values separately on each execution.

Prepared statements solve two problems:

Two distinct things go by the name "prepared statement":

1. PREPARE — Compile a statement template

• stmt_name — your name for this prepared statement.
• preparable_stmt — a single SQL statement, typically with ? placeholders for parameters. Can be a string literal or a user variable holding a string.

2. EXECUTE — Run the prepared statement with values

• Bind one user variable per ?, in order.
• Only user variables (the @-prefixed kind) work as parameters — not local procedure variables.
• You can EXECUTE the same statement many times with different values without re-preparing.

3. DEALLOCATE PREPARE — Release the resource

DEALLOCATE PREPARE and DROP PREPARE are equivalent. Each connection has a limit on how many prepared statements it can hold simultaneously (the max_prepared_stmt_count system variable, server-wide). Always deallocate when finished.

The reason every modern database driver uses prepared statements: building queries by string concatenation is the classic SQL injection vulnerability.

-- If $username comes from user input...
SET @sql = CONCAT("SELECT * FROM users WHERE username = '", @username, "'");
PREPARE stmt FROM @sql;
EXECUTE stmt;

If @username is set to ' OR 1=1 --, the query becomes SELECT * FROM users WHERE username = '' OR 1=1 --' — and returns every row. The right approach uses placeholders:

PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
EXECUTE stmt USING @username;

Now @username is treated as a value, regardless of what's inside it — quotes, semicolons, or SQL keywords. Injection attempts become harmless.

Open the mysql CLI and create a sample table:

Now create the table we'll use for the rest of the examples:

DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    username  VARCHAR(50)  NOT NULL UNIQUE,
    email     VARCHAR(100) NOT NULL UNIQUE
);

Prepare once, execute many times with different parameter values:

-- Prepare the template once
PREPARE insert_user FROM
    'INSERT INTO users (username, email) VALUES (?, ?)';

-- First execution
SET @username = 'john_doe';
SET @email    = 'john@example.com';
EXECUTE insert_user USING @username, @email;

-- Second execution — same prepared plan, new values
SET @username = 'jane_doe';
SET @email    = 'jane@example.com';
EXECUTE insert_user USING @username, @email;

-- Verify
SELECT * FROM users;

-- Clean up
DEALLOCATE PREPARE insert_user;

The killer use case for server-side PREPARE: building queries where the table or column name itself is a variable. You can't bind these as parameters — they have to be part of the SQL text. Construct the text in a variable, then prepare and execute:

SET @tbl  = 'users';
SET @sql  = CONCAT('SELECT COUNT(*) AS row_count FROM ', @tbl);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

1. Use parameter placeholders, never string concatenation — for any value that could come from user input.
2. Always DEALLOCATE PREPARE when done — prepared statements take server memory and count against max_prepared_stmt_count.
3. For application code, use your driver's prepared statements — mysqli_prepare() in PHP, cursor.execute(query, params) in Python, etc. Server-side PREPARE is mostly for stored procedures and ad-hoc CLI work.
4. Don't pass passwords on the command line — use -p alone (interactive prompt) or ~/.my.cnf with permissions 0600.
5. For dynamic table/column names, validate against an explicit allow-list before concatenating.
6. Reuse prepared statements when possible — preparing once and executing many times is the whole point. If you're preparing inside a loop, you're doing it wrong.

• Prepared statements separate the SQL template (parsed once) from the parameter values (supplied per execution).
• The three statements: PREPARE name FROM 'SQL with ?', EXECUTE name USING @vars, DEALLOCATE PREPARE name.
• Parameters must be MySQL user variables (the @var kind) — not local procedure variables.
• The main benefits are SQL injection protection and plan reuse across executions.
• Most application code uses driver-level prepared statements (mysqli, PDO, JDBC, etc.); server-side PREPARE is mainly for dynamic SQL inside stored procedures and ad-hoc CLI work.
• Always DEALLOCATE when finished, validate dynamic identifiers against an allow-list, and never pass passwords on the command line.