MySQL Prepared Statement

The MySQL prepared statement is a way to execute SQL queries with placeholders for parameters. Prepared statements are useful for improving the security and performance of your database interactions. They help prevent SQL injection attacks and can be more efficient when executing the same query multiple times with different parameter values. Here’s how to use prepared statements in MySQL:

1. Preparation:
   To create a prepared statement, you first prepare the SQL query with placeholders using the PREPARE statement. You give a name to the prepared statement and specify the query with placeholders. For example:

   PREPARE statement_name FROM 'SELECT * FROM employees WHERE department = ?';

In this query, ? is a placeholder for a parameter.

2. Binding Parameters:
   After preparing the statement, you can bind values to the placeholders using the SET statement:

   SET @department = 'Sales';

You can set the values for the placeholders as user-defined variables, which start with @.

3. Execution:
   You execute the prepared statement using the EXECUTE statement:

   EXECUTE statement_name USING @department;

Here, @department is the value that will replace the ? placeholder in the prepared statement.

4. Fetching Results:
   If your prepared statement is a SELECT query, you can fetch the results using the FETCH statement:

   FETCH FROM statement_name INTO @result;

This retrieves the result into the @result variable.

5. Deallocate:
   Once you’re done with the prepared statement, you should deallocate it to release resources:

   DEALLOCATE PREPARE statement_name;

Here’s a complete example that demonstrates the use of a prepared statement to retrieve employees from the “Sales” department:

-- Step 1: Prepare the statement
PREPARE stmt FROM 'SELECT * FROM employees WHERE department = ?';

-- Step 2: Set the parameter value
SET @department = 'Sales';

-- Step 3: Execute the statement
EXECUTE stmt USING @department;

-- Step 4: Fetch the results
DECLARE @employee_name VARCHAR(255);
DECLARE @employee_salary DECIMAL(10, 2);

-- Fetch the results into variables
FETCH FROM stmt INTO @employee_name, @employee_salary;

-- You can loop through the results and do something with them

-- Step 5: Deallocate the statement
DEALLOCATE PREPARE stmt;

Prepared statements are especially useful in scenarios where you need to execute the same query multiple times with different parameter values, as they can be more efficient than repeatedly parsing the same SQL query. Additionally, they help protect against SQL injection because the parameters are automatically escaped.