MySQL Prepared Statement

Summary: in this tutorial, you will learn how to use the MySQL Prepared Statement to make your queries execute more securely and faster.

Introduction to MySQL Prepared Statement

MySQL prepared statements are a feature that helps you enhance the security and performance of database queries.

MySQL prepared statements allow you to write SQL queries with placeholders for parameters, and then bind values to those parameters at runtime. They can help prevent SQL injection attacks and optimize query execution.

The syntax of prepared statements is based on three statements:

• PREPARE
• EXECUTE
• DEALLOCATE PREPARE

PREPARE statement

The PREPARE statement prepares a statement for execution:

PREPARE stmt_name FROM preparable_stmt;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the prepared statement (stmt_name) after the PREPARE keyword.
• Second, provide the SQL statement with placeholders (?) (preparable_stmt) after the FROM keyword. The preparable_stmt represents a single SQL statement, not multiple statements.

The preparable_stmt is sent to the MySQL server with placeholders (?) for parameters. Upon receiving the statement, the MySQL server parses, optimizes, and precompiles the query, and then creates the prepared statement.

After creating a prepared statement, you need to initialize a set of user variables to supply values for the parameter placeholders (?) specified in the prepared statement:

SET @var_name1 = value1;
SET @var_name2 = value2;Code language: CSS (css)

EXECUTE statement

The EXECUTE statement runs the prepared statement with the actual values:

EXECUTE stmt_name [USING @var_name [, @var_name] ...];Code language: SQL (Structured Query Language) (sql)

If the prepared statement contains any parameter markers (?), you need to supply the user variables containing values for these parameters.

Note that you can use only user variables as the values for the parameters.

You can execute the same prepared statement as many times as you want, each time, you can set the variables to different values before each execution.

DEALLOCATE PREPARE statement

The DEALLOCATE PREPARE statement releases the resource associated with the prepared statement:

{DEALLOCATE | DROP} PREPARE stmt_name;Code language: SQL (Structured Query Language) (sql)

In this statement, you specify the name of the prepared statement after the PREPARE keyword.

If you create too many prepared statements and do not deallocate them, you might encounter the upper limit controlled by the max_prepared_stmt_count system variable.

The following picture illustrates how to use a prepared statement:

Basic MySQL Prepared Statement Example

1) Setting up a sample table

First, open the command prompt on Windows or Terminal on Unix-like systems and connect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, create a new database called mydb:

create database mydb;Code language: SQL (Structured Query Language) (sql)

Third, change the current database to mydb:

use mydb;Code language: SQL (Structured Query Language) (sql)

Finally, create a new table called users:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE
);Code language: SQL (Structured Query Language) (sql)

2) Using MySQL prepared statement to insert data into the users table

First, create a prepared statement that inserts a new row into the users table:

PREPARE insert_user FROM 'INSERT INTO users (username, email) VALUES (?, ?)';Code language: SQL (Structured Query Language) (sql)

Second, set the values for the username and email parameters:

SET @username = 'john_doe';
SET @email = '[email protected]';Code language: SQL (Structured Query Language) (sql)

Third, execute the prepared statement insert_user with the @username and @email parameters:

EXECUTE insert_user USING @username, @email;Code language: SQL (Structured Query Language) (sql)

Fourth, set the values for the username and email parameters and execute the prepared statement again. This time, MySQL will use the precompiled statement:

SET @username = 'jane_doe';
SET @email = '[email protected]';
EXECUTE insert_user USING @username, @email;Code language: SQL (Structured Query Language) (sql)

Fifth, verify the results:

SELECT * FROM users;Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------+------------------+
| id | username | email            |
+----+----------+------------------+
|  1 | john_doe | [email protected] |
|  2 | jane_doe | [email protected] |
+----+----------+------------------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Fifth, release the resource of the prepared statement using the DEALLOCATE PREPARE statement:

DEALLOCATE PREPARE insert_user;Code language: SQL (Structured Query Language) (sql)

Summary

• Use MySQL prepared statements to enhance the security and performance of database queries.