MySQL Prepared Statement – A Beginner’s Guide

By /
MySQL Prepared Statement

In this tutorial, we will study the Prepared statement in MySQL. We will learn what is a prepared statement, how it is used, and the advantages of using a prepared statement.

Introduction

A prepared statement is a SQL query that is stored in the server so that it can be reused multiple times. Prepared statements improve performance because they allow the server to cache and reuse frequently-used SQL queries.

In the earlier versions of MySQL, the query was sent to the server in textual format. Then the MySQL server used to parse the query and return the data to the client in textual format but in the process, it was transforming the result set into the string before returning it to the client. This whole process seems easy but actually has a lot of complications. So MySQL added a new feature called Prepared Statement which basically works on the principle of client/server binary protocol. The query contains the placeholder(?) as it is passed to the MySQL server.

The syntax is as follows-

SELECT * 
FROM table_name
WHERE column_name = ?;Code language: SQL (Structured Query Language) (sql)

Due to the placeholder(?) many variants of SQL injection are avoided and it makes the application more secure.

Usage of Prepared Statement

MySQL prepared statement consists of the following statements-

  • PREPARE – This prepares a statement for execution.
  • EXECUTE – This executes a prepared statement.
  • DEALLOCATE PREPARE – This releases a prepared statement.

Example

Here we will use the classicmodels database to illustrate an example of a prepared statement. Let us take a look at the tables present in classicmodels database.

Classicmodels
Classicmodels

We will use the orders table. First, prepare a statement that returns the orderDate and orderNumber specified by orderNumber:

PREPARE st1 FROM 
	'SELECT 
   	    orderDate, 
        orderNumber 
	FROM orders
        WHERE orderNumber = ?';Code language: SQL (Structured Query Language) (sql)

Output-

Statement
Statement

Now declare a variable oN that stands for orderNumber and set it to ‘10421’ :

SET @oN = '10421'; Code language: SQL (Structured Query Language) (sql)

Lastly, execute the prepared statement-

EXECUTE st1 USING @oN;Code language: SQL (Structured Query Language) (sql)

Output-

Execution
Execution

Now deallocate the prepared statement-

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

Hence, our prepared statement works well.

Conclusion

In this tutorial, we studied Prepared statement in MySQL. We saw how to create and execute a prepared statement using the PREPARE and EXECUTE keywords. Using prepared statements is always recommended as they improve performance and reduce the risk of SQL injection attacks.

The key advantage of using prepared statements in MySQL is that they can help improve performance and reduce the risk of SQL injection attacks. They allow you to create a single statement that can be reused multiple times, saving you time and effort compared to creating separate queries for each use case.

If you are working with large databases on MySQL, it is definitely worth taking the time to learn how to use prepared statements effectively. Thanks for reading!