Dynamic SQL

"Dynamic" means that the SQL statement that you execute is constructed, parsed, and compiled at runtime, not at the time the code is compiled. Dynamic SQL offers a tremendous amount of flexibilitybut also complexity and more than a little risk.

In the MySQL stored program language, you can process dynamic SQL by using the MySQL prepared statement feature. You can create a prepared statement with the PREPARE statement, supplying the SQL text in a session variable. The SQL can then be executed with the EXECUTE statement.

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

Heres 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,100000,1)

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. Those values are provided in the USING clause of the EXECUTE statement. Attempts to "inject" SQL into these values will fail (although injection into the table or column name parameters is still possiblewell address that in the next best practice).

Whenever you create a dynamic SQL statement based on parameters to a procedure or user inputs, you should always guard carefully against SQL injection (see Chapter 18). SQL injection allows the user to provide fragments of SQL as parameters to your stored programs, potentially subverting the resulting dynamic SQL.

Therefore, you should always carefully validate the inputs to your stored programs if they contribute to your dynamic SQL.

In the previous example, we prevented SQL injection through the careful use of placeholders. Variable binding could not, however, address the potential vulnerability of concatenating in the names of tables and columns.

In the modified version below, we perform a SQL query to confirm that the parameter inputs do, in fact, represent valid table and column names. Once we validate the inputs, we then construct and execute the dynamic SQL:

CREATE PROCEDURE update_anything_2 (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 DECLARE v_count INT; SELECT COUNT(*) INTO v_count FROM information_schema.columns WHERE table_name=in_table AND column_name IN (in_set_col,in_where_col); IF ( v_count <2 ) THEN SELECT Invalid table or column names provided; ELSE SET @dyn_sql=CONCAT( UPDATE , in_table , SET ,in_set_col, = ? WHERE , in_where_col, = ?); SELECT @dyn_sql; 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; END;

The definer rights modelin which stored programs execute with the permissions of the creator rather than the invokergenerally confers significant security advantages, since you can allow access to database objects only under the controlled conditions implemented in your stored programs.

However, in the case of stored programs that contain dynamic SQL, the definer rights model can create security concerns, since these programs can conceivably be vulnerable to SQL injection, as described in Chapter 18. Since the creator of the stored program is almost always a highly privileged user, the implications of SQL injection into a definer rights procedure is potentially very serious indeed.

Whenever you create a stored program that processes a dynamic SQL statement, you should consider defining the program with the invoker rights model. Do this by adding the following clause to the program header:

SQL SECURITY INVOKER

This clause ensures that the dynamic SQL string is parsed under the authority of the account currently running the program.

Without the SQL SECURITY INVOKER clause, the stored program will execute with the privileges of the user that created the stored program. Sinceby definitionyou don know exactly the full text of the dynamic SQL to be executed, you almost always want the SQL to be rejected if the user does not have sufficient privileges.

Using the alternative definer rights model also magnifies the potential vulnerabilities created should your stored program be susceptible to SQL injection.

Example

In the previous examples, we created a stored program that would update the value of any column in any table. Since we omitted the SQL SECURITY clause, a user can use the stored program to update tables to which she wouldn normally have access. We didn intend that!

So we should have defined the stored program with invoker rights, as follows:

CREATE PROCEDURE update_anything_2 (in_table VARCHAR(60), in_where_col VARCHAR(60), in_set_col VARCHAR(60), in_where_val VARCHAR(60), in_set_val VARCHAR(60)) SQL SECURITY INVOKER BEGIN . . . .