Using MySql Prepared Statements

Prepared Statements were introduced in MySQl 4.1 and provide the option to set up a SQL statement query once and execute it many times with different parameters.  Prepared Statememts replace ad hoc query strings, are executed on the server, and in doing offer increased security and performance.

A typical prepared statement would look something like:
 SELECT * FROM employee WHERE code = ?

The ? is a placeholder. When you execute the above query, you would need to supply the value for it, which would replace the ? in the query above.  I have prepared(no pun intended..) a sample from a database I created to keep track of job applications.  The syntax is depicted below:

mysql>PREPARE fa FROM 'SELECT * FROM job WHERE title=?';
Query OK, 0 rows affected (0.00 sec)
Statement Prepared
mysql>SET @fa_param='Financial Analyst';
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE fa USING @fa_param;

+--------+-------------------+--------+-------------------------------------+
| job_id | title             | salary | requirements | requisition | company_id |
+--------+-------------------+--------+-------------------------------------+----+
|     11 | Financial Analyst |      0 | excel;pivot tables | NULL |       11 |
|     17 | Financial Analyst |      0 | Crystal Reports    | NULL |       24 |
..........
|    117 | Financial Analyst |     24 | nonprofit experience | NULL |     78 |
|    141 | Financial Analyst |      0 | monitoring and reporting |NULL | 108 |
+--------+-------------------+--------+-------------------------------------+----+

13 rows in set (0.00 sec)
mysql>DEALLOCATE PREPARE fa;
Query OK, 0 rows affected (0.00 sec)
mysql>

Prepared Statements work with DML(INSERT,DELETE,UPDATE, and REPLACE), CREATE TABLE and SELECT queries. The increased security comes from separating data  being supplied from SQL logic.  In addition,  the separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack.

Normally when you are dealing with an ad hoc query, you need to be careful when handling the data that you received from the user. This entails using functions that escape all of the necessary trouble characters, such as the single quote, double quote, and backslash characters. This is unnecessary when dealing with prepared statements. The separation of the data allows MySQL to automatically take into account these characters and they do not need to be escaped using any special function.


Second, the increase in performance comes from the MySQL engine parsing the query only once. When you initially prepare the statement, MySQL will parse the statement to check the syntax and set up the query to be run. Then if you execute the query many times, it will no longer have that overhead. This pre-parsing can lead to decreased processing time if you need to run the same query many times, such as when doing many INSERT statements.

As a side note, PHP has an API to Prepared Statements through its "mysqli" extension. You can read more about it in the mysqli section of the PHP Manual.