MySQL — Prepared Statements

Basic Prepared Statement

PREPARE stmt FROM 'SELECT * FROM customers WHERE id = ?';
SET @id = 1;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

Multiple Parameters

PREPARE stmt FROM 'SELECT * FROM orders WHERE customer_id = ? AND total > ?';
SET @cust_id = 5;
SET @min_total = 100;
EXECUTE stmt USING @cust_id, @min_total;
DEALLOCATE PREPARE stmt;

Dynamic Table Names

SET @table_name = 'customers';
PREPARE stmt FROM CONCAT('SELECT COUNT(*) FROM ', @table_name);
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Prepared Statements in Stored Procedures

DELIMITER //
CREATE PROCEDURE SearchUsers(IN search_name VARCHAR(100))
BEGIN
    SET @sql = CONCAT('SELECT * FROM users WHERE name LIKE ''%', search_name, '%''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Benefits

Benefit	Description
Security	Prevents SQL injection
Performance	Reuses parsed query plan
Flexibility	Dynamic SQL construction

Parameter Limits

• Max 65,535 parameters per prepared statement
• Parameters are always text; cast as needed

Mini Practice

Write SQL code that:

1. Prepares and executes a SELECT with one parameter
2. Uses multiple parameters
3. Builds dynamic SQL with CONCAT
4. Creates a stored procedure using prepared statements

Up Next

Continue with Users — managing database users and permissions.