Prepared Statements

Prepared Statements

Server-Side Prepared Statements

PREPARE name FROM 'sql' parses a statement once, with ? where values go; EXECUTE name USING @var supplies values that are never parsed; DEALLOCATE PREPARE frees it. The listing replays the tautology through concatenated dynamic SQL, then through a placeholder:

Concatenated dynamic SQL versus a placeholderSQL

SET @email = "x' OR '1'='1";
SET @sql = CONCAT("SELECT COUNT(*) AS matched FROM customers WHERE email = '", @email, "'");
PREPARE unsafe FROM @sql;
EXECUTE unsafe;
PREPARE lookup FROM 'SELECT COUNT(*) AS matched FROM customers WHERE email = ?';
EXECUTE lookup USING @email;
PREPARE bad FROM 'SELECT COUNT(*) FROM ?';
DEALLOCATE PREPARE lookup;

Output

+---------+
| matched |
+---------+
|       8 |
+---------+
+---------+
| matched |
+---------+
|       0 |
+---------+
ERROR 1064 (42000) at line 7: You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use near '?' at line 1

PREPARE alone protects nothing: unsafe was built from text that already held the attack. The placeholder does, because the statement's shape is fixed before any input exists. It also stands only for a value, never a table, column or ASC/DESC, so take those from a whitelist in code and quote them with backticks (sys.quote_identifier() in SQL). Statements belong to their session and are capped by max_prepared_stmt_count (16,382). PHP uses the binary protocol's COM_STMT_PREPARE instead; Subsections 4.16.5 and 4.16.6 show it through PDO.

MENU