MySQL Prepared Statement

MySQL Prepared Statement

A Prepared Statement in MySQL is a feature that lets you execute SQL queries multiple times efficiently and securely. It is particularly useful for queries that need to run repeatedly with different parameter values or when protecting against SQL injection attacks.

Why Use Prepared Statements?

1. Precompiled for Performance:

   • The SQL query is parsed and compiled once, and the execution plan is reused for multiple executions.
   • Improves performance for repetitive queries.

2. Prevents SQL Injection:

   • Parameters are bound separately from the query, ensuring user inputs cannot alter the SQL structure.

3. Flexibility:

   • Allows dynamic inputs for queries without rebuilding the SQL string each time.

Syntax

1. Prepare a Statement

PREPARE stmt_name FROM 'SQL_query';

• stmt_name: The name of the prepared statement.
• SQL_query: The SQL query to prepare.

2. Set Parameters

SET @param1 = value1, @param2 = value2, ...;

3. Execute the Statement

EXECUTE stmt_name [USING @param1, @param2, ...];

4. Deallocate the Statement

DEALLOCATE PREPARE stmt_name;

Example Usage

1. SELECT Statement

-- Prepare a statement
PREPARE stmt FROM 'SELECT * FROM employees WHERE department = ?';

-- Set the parameter
SET @dept = 'Sales';

-- Execute the prepared statement
EXECUTE stmt USING @dept;

-- Deallocate the prepared statement
DEALLOCATE PREPARE stmt;

2. INSERT Statement

-- Prepare a statement
PREPARE stmt FROM 'INSERT INTO products (product_name, price) VALUES (?, ?)';

-- Set the parameters
SET @name = 'Laptop', @price = 1200.99;

-- Execute the prepared statement
EXECUTE stmt USING @name, @price;

-- Deallocate the prepared statement
DEALLOCATE PREPARE stmt;

3. UPDATE Statement

-- Prepare a statement
PREPARE stmt FROM 'UPDATE orders SET status = ? WHERE order_id = ?';

-- Set the parameters
SET @status = 'Shipped', @order_id = 101;

-- Execute the prepared statement
EXECUTE stmt USING @status, @order_id;

-- Deallocate the prepared statement
DEALLOCATE PREPARE stmt;

Dynamic Queries

Prepared statements also support dynamic queries:

SET @table_name = 'employees';
SET @query = CONCAT('SELECT * FROM ', @table_name, ' WHERE id = ?');

PREPARE stmt FROM @query;
SET @id = 1;

EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

Advantages

1. Security: Protects against SQL injection attacks.
2. Efficiency: Avoids parsing and compiling the query for every execution.
3. Reusability: Can execute the same query with different parameters multiple times.

Limitations

1. No Direct Support for Table/Column Names as Parameters:

   • You cannot bind table or column names as parameters in a prepared statement.
   • Use dynamic SQL  CONCAT to include table or column names.

2. Complex Syntax for Some Operations:

   • Managing prepared statements can be less straightforward compared to inline SQL queries.

3. Extra Steps for Cleanup:

   • Requires explicit deallocation with DEALLOCATE PREPARE to free resources.

Use Cases

1. Batch Inserts or Updates:

   • Ideal for inserting/updating multiple rows with different values in a loop.

2. Dynamic Filtering:

   • When query conditions depend on user inputs or runtime variables.

3. Security in Web Applications:

   • Protects against SQL injection in applications that take user input.

Prepared Statements in MySQL Client Applications

1. PHP Example

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");

$stmt = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);

$username = "johndoe";
$email = "johndoe@example.com";
$stmt->execute();

$stmt->close();
$mysqli->close();
?>

Conclusion

MySQL Prepared Statements are an essential tool for building secure, efficient, and reusable SQL queries. By separating query structure from parameters, they improve performance and provide robust protection against SQL injection, making them indispensable for modern database-driven applications.