MySQL Prepared Statements
In this page:
What Is a Prepared Statement?
A prepared statement splits a query into two steps: first the database compiles the SQL structure with placeholder markers (?) instead of literal values, then the actual input is sent separately to fill those placeholders. Because the structure is locked in before any data arrives, user input can never be reinterpreted as SQL syntax.
Example: What Is a Prepared Statement?
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
SET @id = 1;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;PREPARE, EXECUTE, and DEALLOCATE
In raw MySQL, PREPARE stmt_name FROM sql_with_placeholders compiles the statement once; EXECUTE stmt_name USING @var runs it with a specific value bound to each placeholder; DEALLOCATE PREPARE stmt_name frees the compiled statement when you are done with it. The same compiled statement can be executed repeatedly with different values without recompiling the SQL each time.
Example: PREPARE, EXECUTE, and DEALLOCATE
PREPARE get_user FROM 'SELECT * FROM users WHERE id = ?';
SET @id = 1;
EXECUTE get_user USING @id;
SET @id = 2;
EXECUTE get_user USING @id;
DEALLOCATE PREPARE get_user;Vulnerable Query vs. Prepared Statement
A vulnerable query builds SQL by concatenating raw input directly into the string, so a value like ' OR 1=1 -- can change the query's meaning entirely. The prepared-statement version keeps the exact same placeholder text no matter what value is bound to it, because the driver sends the value as pure data, never as part of the SQL it parses.
Example: Vulnerable Query vs. Prepared Statement
-- Vulnerable: concatenated input can change the query's meaning
SET @unsafe_input = "' OR 1=1 -- ";
-- Prepared statement: the value is always treated as pure data
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
EXECUTE stmt USING @unsafe_input;
DEALLOCATE PREPARE stmt;Binding Multiple Parameters
A single prepared statement can contain several placeholders, and each one is bound to its own variable in the USING clause, in the same left-to-right order they appear in the query. This makes prepared statements practical for realistic queries with multiple WHERE conditions or multi-column INSERTs, not just single-value lookups.
Example: Binding Multiple Parameters
PREPARE stmt FROM 'SELECT * FROM orders WHERE customer_id = ? AND status = ?';
SET @cid = 1, @status = 'paid';
EXECUTE stmt USING @cid, @status;
DEALLOCATE PREPARE stmt;Prepared Statements in Application Code
Most application database libraries (PDO in PHP, mysql-connector in Python, JDBC in Java) wrap this PREPARE/EXECUTE mechanism behind a simpler API, so you rarely write raw PREPARE statements by hand in production code. Understanding the underlying mechanism still matters, because it explains exactly why parameterized queries in any language are immune to the injection technique shown in the previous topic.
Example: Prepared Statements in Application Code
-- Raw MySQL PREPARE/EXECUTE, the mechanism PDO/JDBC/mysql-connector wrap internally:
PREPARE stmt FROM 'INSERT INTO logs (message) VALUES (?)';
SET @msg = 'user logged in';
EXECUTE stmt USING @msg;
DEALLOCATE PREPARE stmt;Chapter Quiz — Complete all 6 topics to unlock
0/6 topics done
Complete these topics first: