Summary: 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:
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.
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
Then, we used the
EXECUTE statement to execute the prepared statement with product code variable
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.