73

MySQL Prepared Statement

Before the MySQL version 4.1, each query was sent to the MySQL server in the textual format and using the textual protocol, it returned the data to the client. Before returning the result to the client, MySQL has fully parsed the query and transforms the result set into a string. Here, parsing means the submitted query is syntactically and semantically verified, and privileges are also verified.

The textual protocol that returns the data to the client has serious performance issues. To overcome this issue, MySQL provides a new feature called prepared statement since version 4.1.

The prepared statement or parameterized statement is used to execute the same statements repeatedly with high efficiency. It takes advantage of the client/server binary protocol. Prepared statement passes the query that contains placeholders (?) to the MySQL Server. See the below example:

When MySQL executes the above statement using different values of studentId, it cannot parse the statement fully. As a result, MySQL will execute the statement faster, especially when it executes the same query multiple times.

The prepared statement contains placeholders (?), which helps to avoid many SQL injection variants and makes our application more secure.

Advantages of Prepared Statement

The following are the advantages of the prepared statement in MySQL:

• We can execute a prepared statement multiple times repeatedly.
• Upon every execution, the current value of the bound variable is evaluated and sent to the server. The statement is not parsed again. The statement template is not transferred to the server again.

Basic Workflow of Prepared Statement

The basic workflow of the prepared statement mainly consists of two stages. However, it has one optional stage that is summarized below:

1. PREPARE
2. EXECUTE
3. DEALLOCATE (OPTIONAL)

PREPARE Stage

At the prepare stage, a statement template is sent to the database server. The server performs a syntax check and initializes internal server resources for later use. In short, it prepares a statement for execution.

Syntax

The following are the syntax of the prepare stage:

EXECUTE Stage

At the execution stage, the client binds parameter values and sends them to the server. The server creates a statement from the statement template and the bound values to execute it using the previously created internal resources. In short, once the prepared statement prepares the query, we are ready to execute that query.

Syntax

The following are the syntax to execute the prepared statement:

DEALLOCATE/DROP Stage

It is the last and optional stage, which is used to release the prepared statement.

Syntax

The following are the syntax to deallocate the prepared statement:

Key Points Related To Prepared Statement

• A prepared statement created in one session is not available to other sessions. It means prepared statements are session-specific.
• When a session ends, whether normally or abnormally, its prepared statements no longer exists in the memory.
• A prepared statement created within a stored program continues to exist after the program finishes executing and can be executed outside the program later.

MySQL Prepared Statement Example

Let us understand how to use the MySQL Prepared Statement by taking some examples.

Here, we are going to create a statement directly with the help of the PREPARE statement as follows:

Next, we will assign the two values into two variables that can be used for placeholder (?):

Now, we can execute the query with the help of an EXECUTE statement:

After execution, we will get the result as a sum. See the below image to understand the result:

The below examples will use the employee table from the sample database that contains the following data.

First, we will prepare a statement that returns the employee name and designation specified by employee id:

Next, we need to declare a variable named id and set its value to ‘1’:

Now, we can execute the prepared statement with the help of an EXECUTE statement:

After execution, we will get the result that contains the name and designation of an employee. See the below image to understand the query execution:

Again, we will assign another value for variable id:

Now, execute the prepared statement with the new employee id. We will see the output as follows:

Finally, we can release the prepared statement manually. However, they will be removed automatically when the session is closed.

If we try to execute the prepared statement after executing the above query, we will get an error as follows:

How can we use prepared statements in a stored procedure?

We can use the prepared statements in a stored procedure by writing it inside the BEGIN and END block. We can understand it by creating an example that returns all records from a table by passing the table’s name as a parameter of the stored procedure.

Create the stored procedure as follows:

See the below image to execute the stored procedure:

After successful creation, we can invoke this procedure by specifying the table name as its parameter.

It will show all records of the table. See the below image:

Statement vs. Prepared Statement

The following are the main differences between the statement and prepared statement in MySQL:

Statement	Prepared Statement
It is used when we want to execute the SQL query only once.	It is used when we want to execute the SQL query multiple times.
It is used for DDL statements.	It can be used for any SQL query.
It cannot be used to read and write binary data.	It can be used to read and write binary data.
It is static, which means we cannot pass parameters at runtime.	It is dynamic, which means we can pass parameters at runtime.
The performance of execution is slow.	The performance of execution is fast.
It does not prevent SQL injection.	It helps to prevent SQL injection attacks.
It uses a textual protocol for communication.	It uses a binary protocol for communication.

Stored Procedure vs. Prepared Statement

The following are the main differences between the stored procedure and prepared statement in MySQL:

Stored Procedure	Prepared Statement
Stored procedures are a sequence of SQL statements that access the relational database management system.	Prepared statements are queries that contain the placeholders instead of actual values.
It can be stored in the database server.	It cannot be stored in the database.

---