Prepared Statements
What the domain tests
Prepared Statements tests server-side prepare, execute, and deallocate operations, placeholder rules, and the difference between SQL text and data. It also checks how prepared statements help prevent SQL injection and when dynamic SQL must be built differently.
Core rules
- PREPARE stmt_name FROM 'SQL text' parses and prepares a statement. EXECUTE stmt_name [USING @var,...] runs it. DEALLOCATE PREPARE stmt_name frees it.
- Placeholders are written as ? and can stand in for values, not for identifiers such as table names, column names, or SQL keywords. To vary an identifier you must build the SQL string and prepare that string.
- The USING clause accepts user variables such as @id, not arbitrary expressions. Assign values to user variables first.
- Prepared statements reduce parsing overhead and separate data from code, which mitigates injection when used for values.
- MySQL supports both text-protocol and binary-protocol prepared statements. The binary protocol is used by APIs such as Connector/J.
- Prepared statements are session-scoped and are automatically deallocated when the session ends.
Worked example
SET @id = 42; PREPARE s FROM 'SELECT name FROM users WHERE id = ?'; EXECUTE s USING @id; DEALLOCATE PREPARE s; The placeholder is bound to the value 42 at execution. If the query needed a dynamic column, 'SELECT name FROM users WHERE ? = ?' would not work; the column name must be safely interpolated into the SQL text before PREPARE.
Traps
- Trying to use ? for a table or column name.
- Forgetting DEALLOCATE PREPARE, leaving the statement until session end.
- Assuming PREPARE accepts a local stored-program variable directly; use a user variable for the SQL text when needed.
- Believing prepared statements eliminate all injection risk when identifiers are concatenated.
- Mixing up the statement name and the user variables used in USING.