MySQL Prepared Statement

in this tutorial, you will learn how to use MySQL prepared statement to make your queries execute faster and more secure.

Introduction to MySQL Prepared Statement

Prior MySQL version 4.1, the query is sent to the MySQL server in the textual format. In turn, MySQL returns the data to the client using textual protocol. MySQL has to parse the query fully and coverts the result set into a string before returning it to the client.

The textual protocol has serious performance implication. To resolve this problem, MySQL added a new feature called prepared statement since version 4.1.

The prepared statement takes advantage of client/server binary protocol. It passes query that contains placeholders (?) to the MySQL server as the following example:

1 2 3

SELECT * FROM products WHERE productCode = ?

When MySQL executes this query with different productcode values, it does not have to parse the query fully. As a result, this helps MySQL execute the query faster, especially when MySQL executes the query multiple times. Because the prepared statement uses placeholders (?), this helps avoid many variants of SQL injection hence make your application more secure.

MySQL prepared statement usage

In order to use MySQL prepared statement, you need to use other three MySQL statements as follows:

• PREPARE – Prepares statement for execution.
• EXECUTE – Executes a prepared statement preparing by a PREPARE statement.
• DEALLOCATE PREPARE – Releases a prepared statement.

The following diagram illustrates how to use the prepared statement:

MySQL prepared statement example

Let’s take a look at an example of using the MySQL prepared statement.

1 2 3 4 5 6 7 8

PREPARE stmt1 FROM 'SELECT productCode, productName                     FROM products                     WHERE productCode = ?';   SET @pc = 'S10_1678'; EXECUTE stmt1 USING @pc;   DEALLOCATE PREPARE stmt1;

First we used the PREPARE statement to prepare a statement for execution. We used the SELECT statement to query product data from the  products table based on a specified product code. We used question mark (?) as a placeholder for the product code.

Next, we declared a product code variable  @pc and set it values to S10_1678.

Then, we used the EXECUTE statement to execute the prepared statement with product code variable @pc.

Finally, we used the  DEALLOCATE PREPARE to release the prepared statement.

In this tutorial, we have shown you how to use MySQL prepared statement to execute a query with placeholders to improve the speed of the query and make your query more secure.