MySQL Stored Procedure Programming

Name

DYN-01: Bind, do not concatenate, variable values into dynamic SQL strings

Synopsis

When you bind a variable value into a dynamic SQL string, you can insert a “placeholder” into the string. This allows MySQL to parse a “generic” version of that SQL statement, which can be used over and over again, regardless of the actual value of the variable, without repeated parsing.

This technique also makes your code more resistant to SQL injection attacks (see Chapter 18), since the value supplied to placeholders cannot include SQL fragments.

Example

Here’s an example of binding with the PREPARE and EXECUTE statements. This program updates any numeric column in the specified table, based on the supplied name:

    CREATE PROCEDURE update_anything
      (in_table     VARCHAR(60),
       in_where_col VARCHAR(60),
       in_set_col   VARCHAR(60),
       in_where_val VARCHAR(60),
       in_set_val   VARCHAR(60))
    BEGIN

      SET @dyn_sql=CONCAT(
          'UPDATE ' , in_table ,
            ' SET ' , in_set_col, ' = ?
            WHERE ' , in_where_col, ' = ?');

     PREPARE s1 FROM @dyn_sql;
     SET @where_val=in_where_val;
     SET @set_val=in_set_val;
     EXECUTE s1 USING   @where_val,@set_val;
     DEALLOCATE PREPARE s1;

    END$$

If you want to update the salary of employee #1 to $100,000, you might call this stored procedure as follows:

    CALL update_anything_g('employees','employee_id','salary', 1,100000)

The dynamic SQL generated will look like this:

    'UPDATE employees SET salary = ? WHERE employee_id = ?'

The ? characters indicate placeholders that will be replaced with the values for salary and employee_id ...