Prepared Statements in MySQL: A Complete Guide with Examples

Posted on by Ian

In MySQL, prepared statements are a feature that allows us to execute the same SQL query multiple times with different parameters. They can improve performance, make our code cleaner, and help defend against SQL injection attacks.

In this article, we’ll explore prepared statements in MySQL, with examples and practical tips on how to use them effectively. We’ll also cover the importance of deallocating statements to free up resources.

What Are Prepared Statements?

Prepared statements are a kind of template SQL query that can be executed multiple times with different parameters. They are often used to prevent SQL injection attacks, and because the query is pre-compiled, they can also offer performance benefits when executing the same query many times.

Instead of sending the entire SQL query each time, you send a template query once, and then supply different values for the placeholders (also known as parameters) during subsequent executions.

For example, consider the following template:

SELECT * FROM users WHERE id = ?;

The ? in the query acts as a placeholder that can be substituted with a specific value each time the statement is executed.

Prepared statements are specific to the session in which they were created. We can’t run or reference a prepared statement from another session. That said, in MySQL prepared statements are server-side, and so it’s possible to view information about prepared statements created by other sessions.

Benefits of Using Prepared Statements

Here are a few reasons to consider using prepared statements:

  • Security: Prepared statements prevent SQL injection attacks by separating the SQL logic from the data inputs. The database ensures that user inputs are properly escaped before being executed.
  • Efficiency: The query is compiled and optimized once by the database server, allowing subsequent executions with different parameters to be faster.
  • Code Reusability: With prepared statements, the same SQL query can be used multiple times with different inputs, reducing redundancy in the code.

How to Create and Use a Prepared Statement in MySQL

Here is a step-by-step guide on how to create and use a prepared statement.

Step 1: Prepare a Statement

In MySQL, we use the PREPARE statement to create a prepared statement. Here’s how we can prepare a simple SELECT query:

PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';

This query prepares the statement stmt for execution. The ? represents a placeholder for the id value that we will pass later.

Step 2: Set Variables

Next, we need to set a variable that will be used in place of the ?. We can do this as follows:

SET @userId = 2;

Step 3: Execute the Statement

To execute the prepared statement, use the EXECUTE command:

EXECUTE stmt USING @userId;

This will run the prepared statement using the value 2 for the id field.

Step 4: Changing the Parameters

To execute the same query with a different value, simply change the value of the variable:

SET @userId = 3;
EXECUTE stmt USING @userId;

This reuses the same prepared statement but with a different parameter value.

View Current Prepared Statements

We can use the performance_schema.prepared_statements_instances table to return a list of prepared statements used in the server:

SELECT * FROM performance_schema.prepared_statements_instances;

Example output:

+-----------------------+--------------+----------------+----------------------------------+-----------------+----------------+-------------------+---------------------+-------------------+------------------+---------------+-----------------+---------------+-------------------+-------------------+-------------------+-------------------+---------------+------------+--------------+-------------------+---------------+-------------------+-----------------------------+------------------------+----------------------+----------------------------+------------------+------------------------+-----------------+-----------------------+----------------+---------------+---------------+-------------------+------------------------+--------------+-----------------------+------------------+-----------------+
| OBJECT_INSTANCE_BEGIN | STATEMENT_ID | STATEMENT_NAME | SQL_TEXT                         | OWNER_THREAD_ID | OWNER_EVENT_ID | OWNER_OBJECT_TYPE | OWNER_OBJECT_SCHEMA | OWNER_OBJECT_NAME | EXECUTION_ENGINE | TIMER_PREPARE | COUNT_REPREPARE | COUNT_EXECUTE | SUM_TIMER_EXECUTE | MIN_TIMER_EXECUTE | AVG_TIMER_EXECUTE | MAX_TIMER_EXECUTE | SUM_LOCK_TIME | SUM_ERRORS | SUM_WARNINGS | SUM_ROWS_AFFECTED | SUM_ROWS_SENT | SUM_ROWS_EXAMINED | SUM_CREATED_TMP_DISK_TABLES | SUM_CREATED_TMP_TABLES | SUM_SELECT_FULL_JOIN | SUM_SELECT_FULL_RANGE_JOIN | SUM_SELECT_RANGE | SUM_SELECT_RANGE_CHECK | SUM_SELECT_SCAN | SUM_SORT_MERGE_PASSES | SUM_SORT_RANGE | SUM_SORT_ROWS | SUM_SORT_SCAN | SUM_NO_INDEX_USED | SUM_NO_GOOD_INDEX_USED | SUM_CPU_TIME | MAX_CONTROLLED_MEMORY | MAX_TOTAL_MEMORY | COUNT_SECONDARY |
+-----------------------+--------------+----------------+----------------------------------+-----------------+----------------+-------------------+---------------------+-------------------+------------------+---------------+-----------------+---------------+-------------------+-------------------+-------------------+-------------------+---------------+------------+--------------+-------------------+---------------+-------------------+-----------------------------+------------------------+----------------------+----------------------------+------------------+------------------------+-----------------+-----------------------+----------------+---------------+---------------+-------------------+------------------------+--------------+-----------------------+------------------+-----------------+
|            5777788832 |            1 | stmt           | SELECT * FROM users WHERE id = ? |              48 |             73 | NULL              | NULL                | NULL              | PRIMARY          |     663000000 |               0 |             3 |        1202000000 |         394000000 |         400666000 |         406000000 |      17000000 |          0 |            0 |                 0 |             2 |                 2 |                           0 |                      0 |                    0 |                          0 |                0 |                      0 |               0 |                     0 |              0 |             0 |             0 |                 0 |                      0 |            0 |                 39440 |           951326 |               0 |
+-----------------------+--------------+----------------+----------------------------------+-----------------+----------------+-------------------+---------------------+-------------------+------------------+---------------+-----------------+---------------+-------------------+-------------------+-------------------+-------------------+---------------+------------+--------------+-------------------+---------------+-------------------+-----------------------------+------------------------+----------------------+----------------------------+------------------+------------------------+-----------------+-----------------------+----------------+---------------+---------------+-------------------+------------------------+--------------+-----------------------+------------------+-----------------+
1 row in set (0.00 sec)

As mentioned, this returns the prepared statements in the server, so we can see prepared statements that were created in another session. That doesn’t mean that we can run or access them – prepared statements are specific to the session in which they were created.

Cleaning Up with DEALLOCATE

Once we’re done with a prepared statement, we should deallocate it in order to free up the resources it uses. MySQL allocates memory for prepared statements, and not deallocating them can lead to memory bloat, especially in applications that prepare and execute many statements.

To remove a prepared statement, use the DEALLOCATE command:

DEALLOCATE PREPARE stmt;

This will free up the resources associated with the stmt prepared statement. It’s good practice to always deallocate prepared statements when they are no longer needed.

The syntax also allows for the DROP keyword instead of DEALLOCATE. So the following does the same thing as the previous statement:

DROP PREPARE stmt;

Full Example

Here’s a full example of preparing, executing, and deallocating a statement:

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

-- Step 2: Set the parameter value
SET @userId = 3;

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

-- Step 4: Clean up the statement
DEALLOCATE PREPARE stmt;

In this example, the prepared statement is created, used to fetch data with a dynamic parameter, and then properly deallocated.

Key Points

Here are the main points to take away from this article:

  • Prepared statements offer security, performance, and efficiency.
  • Use the PREPARE statement to create a query template with placeholders.
  • Execute the query with the EXECUTE command using different parameters.
  • Always deallocate prepared statements using DEALLOCATE PREPARE to avoid memory leaks.