Performing Dynamic SQL with Prepared Statements

MySQL supports a facility known as server-side prepared statements , which provides an API-independent way of preparing a SQL statement for repeated execution efficiently and securely. Prepared statements are interesting from a stored programming perspective because they allow us to create dynamic SQL calls.

We create a prepared statement with the PREPARE statement:

 PREPARE statement_name FROM sql_text

The SQL text may contain placeholders for data values that must be supplied when the SQL is executed. These placeholders are represented by ? characters.

The prepared statement is executed with the, EXECUTE statement:

 EXECUTE statement_name [USING variable [,variable...]]

The USING clause can be used to specify values for the placeholders specified in the PREPARE statement. These must be supplied as user variables (prefixed with the @ character), which we described in Chapter 3.

Finally, we can drop the prepared statement with the DEALLOCATE statement:

 DEALLOCATE PREPARE statement_name

An example of using prepared statements within the MySQL command-line client is shown in Example 5-25.

Example 5-25. Using prepared statements

mysql> PREPARE prod_insert_stmt FROM "INSERT INTO product_codes VALUES(?,?)";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql>
mysql> SET @code='QB';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @name='MySQL Query Browser';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE prod_insert_stmt USING @code,@name;
Query OK, 1 row affected (0.00 sec)

mysql> SET @code='AD';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @name='MySQL Administrator';
Query OK, 0 rows affected (0.02 sec)

mysql> EXECUTE prod_insert_stmt USING @code,@name;
Query OK, 1 row affected (0.00 sec)

mysql> DEALLOCATE PREPARE prod_insert_stmt;
Query OK, 0 rows affected (0.00 sec)

Now, the idea of prepared statements is to reduce the overhead of re-parsing (preparing) a SQL statement for execution if all that has changed is a few data values, and to enhance security by allowing SQL statement parameters to be supplied in a way that prevents SQL injection (for more about SQL injection, see Chapter 18). Stored procedures don't need prepared statements for these reasons, since the SQL statements in stored procedures are already "prepared" for execution. Moreover, SQL injection is not really a threat in stored programs (ironically enough, unless you use prepared statements!).

However, prepared statements come in handy in stored programs, because they allow you to execute dynamic SQL from within a procedure (but not from within a trigger or function). A SQL statement is dynamic if it is constructed at runtime (whereas a static SQL statement is one that is constructed at the time of compilation of the program unit). You will generally rely on dynamic SQL only when you don't have all the information you need at compile time to complete your statement. This usually occurs because you need input from a user or from some other data source.

The stored procedure in Example 5-26 offers a demonstration of running dynamic SQL as a prepared statement; it will, in fact, execute any SQL that is passed in as an argument.

Example 5-26. Stored procedure with dynamic SQL

CREATE PROCEDURE execute_immediate(in_sql VARCHAR(4000))
BEGIN

 SET @tmp_sql=in_sql;
 PREPARE s1 FROM @tmp_sql;
 EXECUTE s1;
 DEALLOCATE PREPARE s1;

END;

SQL executed as a prepared statement within a stored procedure acts pretty much the same way as a static SQL statement that is embedded inside the stored procedure. However, the EXECUTE statement does not support an INTO clause, nor is it possible to define a cursor from a prepared statement. Therefore, any results from a prepared statement will be returned to the calling program and cannot be trapped in the stored procedure. To catch the rows returned by a dynamic SQL call, store them in a temporary table, as outlined in the section "Returning Result Sets to Another Stored Procedure," earlier in this chapter.

You should rely on dynamic SQL only when needed. It is more complex and less efficient than static SQL, but it does allow you to implement otherwise impossible tasks and create useful, generic utility routines. For instance, the stored procedure in Example 5-27 accepts a table name, column name, WHERE clause, and value; the procedure uses these parameters to build up an UPDATE statement that can update any table column value.

Example 5-27. Stored procedure that can update any column in any table

CREATE PROCEDURE set_col_value
 (in_table VARCHAR(128),
 in_column VARCHAR(128),
 in_new_value VARCHAR(1000),
 in_where VARCHAR(4000))

BEGIN
 DECLARE l_sql VARCHAR(4000);
 SET l_sql=CONCAT_ws(' ',
 'UPDATE',in_table,
 'SET',in_column,'=',in_new_value,
 ' WHERE',in_where);
 SET @sql=l_sql;
 PREPARE s1 FROM @sql;
 EXECUTE s1;
 DEALLOCATE PREPARE s1;
END;

We could call this program to zero-out the salary of employee ID 1 (eat this, CEO!) by invoking the procedure as follows:

 mysql> CALL set_col_value('employees','salary','0','employee_id=1')

Another common application of dynamic SQL is to build up conditional WHERE clauses. Often, we construct user interfaces in which the user may specify multiple search criteria. Handling the "missing" conditions without dynamic SQL can lead to complex and awkward SQL, which can be difficult for MySQL to optimize. Example 5-28 shows a simple example of a search procedure that allows the user to specify any combination of customer name, contact name, or phone number.

Example 5-28. Search procedure without dynamic SQL

CREATE PROCEDURE sp_customer_search
 (in_customer_name VARCHAR(30),
 in_contact_surname VARCHAR(30),
 in_contact_firstname VARCHAR(30),
 in_phoneno VARCHAR(10))

BEGIN
 SELECT *
 FROM customers
 WHERE (customer_name LIKE in_customer_name
 OR in_customer_name IS NULL)
 AND (contact_surname LIKE in_contact_surname
 OR in_contact_surname IS NULL)
 AND (contact_firstname LIKE in_contact_firstname
 OR in_contact_firstname IS NULL)
 AND (phoneno LIKE in_phoneno
 OR in_phoneno IS NULL) ;

END;

The SQL in Example 5-28 is not yet unbearably complex, but as the number of candidate search columns increases, the maintainability of this statement will rapidly diminish. Even with this statement, however, we may be legitimately concerned that the SQL is not correctly optimized for the specific search criteria supplied by the end user. We may therefore wish to build up a more customized search query. Example 5-29 shows a procedure in which we construct the WHERE clause dynamically to match the search criteria supplied by the user and call that SQL dynamically using prepared statements.

Example 5-29. Search procedure with dynamic SQL

CREATE PROCEDURE sp_customer_search_dyn
 (in_customer_name VARCHAR(30),
 in_contact_surname VARCHAR(30),
 in_contact_firstname VARCHAR(30),
 in_phoneno VARCHAR(10))

BEGIN
 DECLARE l_where_clause VARCHAR(1000) DEFAULT 'WHERE';

 IF in_customer_name IS NOT NULL THEN
 SET l_where_clause=CONCAT(l_where_clause,
 ' customer_name="',in_customer_name,'"');
 END IF;

 IF in_contact_surname IS NOT NULL THEN
 IF l_where_clause<>'WHERE' THEN
 SET l_where_clause=CONCAT(l_where_clause,' AND ');
 END IF;
 SET l_where_clause=CONCAT(l_where_clause,
 ' contact_surname="',in_contact_surname,'"');
 END IF;

 IF in_contact_firstname IS NOT NULL THEN
 IF l_where_clause<>'WHERE' THEN
 SET l_where_clause=CONCAT(l_where_clause,' AND ');
 END IF;
 SET l_where_clause=CONCAT(l_where_clause,
 ' contact_firstname="',in_contact_firstname,'"');
 END IF;

 IF in_phoneno IS NOT NULL THEN
 IF l_where_clause<>'WHERE' THEN
 SET l_where_clause=CONCAT(l_where_clause,' AND ');
 END IF;
 SET l_where_clause=CONCAT(l_where_clause,
 ' phoneno="',in_phoneno,'"');
 END IF;

 SET @sql=CONCAT('SELECT * FROM customers ',
 l_where_clause);


 PREPARE s1 FROM @sql;
 EXECUTE s1;
 DEALLOCATE PREPARE s1;

END;

Although the procedure in Example 5-29 is longer and more complicated than the static example shown in Example 5-28, it may execute faster because we have eliminated redundant WHERE clauses from the SQL that is finally executed. In that way, we give MySQL better data on which to base its decisions regarding indexes and other optimizations.

You will probably not need to use dynamic SQL and prepared statements very often, but they can certainly save the day when you are faced with the need to construct a SQL statement based on user input or stored program parameters. However, a final word of caution: when you construct SQL based on user input, you allow for the security attack known as SQL injection to occur, and SQL injection in stored procedures can pose a particularly high risk because of the unique execution context of stored procedures. We discuss SQL injection in stored programs in detail within Chapter 18.