Stored Programs and Code Injection

SQL injection is the name given to a particular form of security attack in applications that rely on dynamic SQL. With dynamic SQL, the SQL statement is constructed, parsed, and executed at runtime. If that statement is pieced together from one or more fragments of SQL syntax, a malicious user could inject unintended and unwanted code for execution within the dynamic SQL framework.

For an example of code injection , consider the PHP code shown in Example 18-12. This code requests a department ID from the user (line 7) and then builds up a SQL statement to retrieve the names of all employees in that department (lines 24-35).

See Chapter 13 for a detailed discussion of interfacing between PHP and MySQL.

Example 18-12. PHP code susceptible to SQL injection

2Employee Query

1 

3

Employee Query

4 56

Enter Department Id: 7 8

9

10 11 query($sql)) { 28 $table =new HTML_Table('border=1'); 29 $table->addRow(array('ID','Surname','Firstname')); 30 $table->setRowAttributes(0,array("bgcolor" => "silver")); 31 32 while ($row = $result_set->fetch_row( )) { 33 $table->addRow(array($row[0],$row[1],$row[2])); 34 } 35 print $table->toHtml( ); 36 } 37 else { 38 printf("

Error retrieving stored procedure result set:%d (%s) %s ", 39 mysqli_errno($dbh), mysqli_sqlstate($dbh), mysqli_error($dbh)); 40 } 41 42 43 result_set->close( ); 44 $dbh->close( ); 45? > 46 47

Notice, however, that this program does not perform any validation of the user input; it is simply appended directly to the end of the SELECT statement. This careless method of construction allows a user to type in text that subverts the intention of the programmer, andin this caseit causes the application to return data that was never intended. Figure 18-3 demonstrates this problem. The user enters UNION and SELECT clauses, and causes the application to return not just the names of employees for a specific department, but also the salaries of all employees in all departments.

Figure 18-3. Using SQL injection to obtain employee salaries

The application intended to issue a SQL statement that looked something like this:

 SELECT employee_id,surname,firstname
 FROM employees
 WHERE department_id =1;

However, by "injecting" SQL into the department_id, the application was tricked into running this SQL instead:

 SELECT employee_id,surname,firstname
 FROM employees
 WHERE department_id =-1
 UNION
 SELECT salary,surname,firstname
 FROM employees

Using this technique, it would be possible for a malicious user to "coerce" the application to display data from any tables to which it has access, even potentially including internal MySQL tables such as mysql.user.

Although it is distressingly easy to create an application that is vulnerable to SQL injection, it is, thankfully, not all that difficult to immunize an application from such an attack. Essentially, SQL injection becomes possible when the application fails to validate user input before inserting that text into a SQL statement. So the simplest solution is often to validate that input. For instance, in Example 18-13, we check that the user input represents a numeric value before inserting it into the SQL.

Example 18-13. Using simple validation to protect against SQL injection

 $department=$_POST['department'];
 if (is_numeric($department)) {

 $sql="SELECT employee_id,surname,firstname FROM employees".
 " WHERE department_id = $department";
 if ($result_set = $dbh->query($sql)) {

Most of the APIs that support MySQL allow you to predefine parameters or "bind variables" to a SQL statement and to supply these just prior to execution of the SQL. These APIs will typically not allow the injection of SQL syntax into the resulting SQL and will often validate the data type of the user input. So, for instance, in Example 18-14, we use the bind_param() method of the mysqli PHP interface to accept only a numeric parameter. Even if the parameter were a string, it would be impossible to "inject" SQL syntax when using mysqli prepared SQL statements.

Example 18-14. Binding parameters to resist SQL injection

$sql="SELECT employee_id,surname,firstname FROM employees ".
 " WHERE department_id = ? ";
 $sth=$dbh->prepare($sql) or die($dbh->error);
 $sth->bind_param("i",$department);
 $sth->bind_result($employee_id,$surname,$firstname);
 $sth->execute( ) or die ($dbh->error);
 $table =new HTML_Table('border=1');
 $table->addRow(array('ID','Surname','Firstname'));
 $table->setRowAttributes(0,array("bgcolor" => "silver"));

 while ($sth->fetch( )) {
 $table->addRow(array($employee_id,$surname,$firstname));
 }

18.3.1. Protecting Against SQL Injection with Stored Programs

MySQL stored programs provide yet another way to protect against SQL injection attacks. The CALL statement that is used to invoke stored programs cannot be modified by a UNION statement or other SQL syntaxit can only accept parameters to the stored program call. This makes a stored program call effectively immune to SQL injectionregardless of whether the application validates user input or uses parameter binding.

To illustrate, consider the short stored procedure in Example 18-15, which returns employee details for a specific department.

Example 18-15. Stored procedure to replace embedded SQL in PHP

CREATE PROCEDURE emps_in_dept(in_dept_id int)
 READS SQL DATA
BEGIN
 SELECT employee_id,firstname,surname
 FROM employees
 WHERE department_id=in_dept_id;
END;

We can use this stored procedure in our PHP program as the mechanism by which we retrieve our employee list, as shown in Example 18-16. This PHP code contains the same lack of input validation as our original example, and does not use parameter binding. Nevertheless, it is immune to SQL injection because the stored procedure can only accept a numeric input, and, additionally, the SQL statement within the stored procedure cannot be modified.

Example 18-16. Stored procedure calls are (usually) immune to SQL injection

 $department = $_POST['department'];
 $sql="CALL emps_in_dept( $department )";
 if ($result_set = $dbh->query($sql)) {
 $table =new HTML_Table('border=1');
 $table->addRow(array('ID','Surname','Firstname'));
 $table->setRowAttributes(0,array("bgcolor" => "silver"));
 while ($row = $result_set->fetch_row( )) {
 $table->addRow(array($row[0],$row[1],$row[2]));
 }
 print $table->toHtml( );

Although there are many ways of structuring application code to withstand a SQL injection attack, stored programs that do not contain prepared statements are immune to SQL statement injection, and an application that interacts with the database only through these stored programs will also be immune to SQL injection.

 

18.3.2. SQL Injection in Stored Programs

There is, unfortunately, one circumstance in which a stored program itself might be vulnerable to a SQL injection attack: when the stored program builds dynamic SQL using a PREPARE statement that includes values passed into the stored program as parameters.

We looked initially at prepared statements in Chapter 5: using prepared statements, we can build dynamic SQL that potentially includes strings provided as parameters to the stored program. These parameter strings might include SQL fragments and, hence, make the program susceptible to SQL injection.

Consider the stored procedure shown in Example 18-17; for reasons known only to the author, the stored procedure builds the SQL dynamically and executes it as a stored procedure. Strangely, the author also used a very long VARCHAR parameter even though department_id is a numeric column.

Example 18-17. Stored procedure susceptible to SQL injection

CREATE PROCEDURE 'emps_in_dept2'(in_dept_id VARCHAR(1000))
BEGIN
 SET @sql=CONCAT(
 "SELECT employee_id,firstname,surname
 FROM employees
 WHERE department_docText">This stored procedure is susceptible to exactly the same form of SQL injection attack as the PHP code shown in Example 18-12. For instance, we can extract employee details from the stored procedure by executing it as shown in Example 18-18.

Example 18-18. Injecting SQL into a stored procedure call

mysql> CALL emps_in_dept2("-1 UNION SELECT salary,surname,firstname
 FROM employees ");
+-------------+-----------+----------+
| employee_id | firstname | surname |
+-------------+-----------+----------+
| 105402 | FERRIS | LUCAS |
| 89949 | KIPP | STAFFORD |
| 77142 | HOLMES | GUTHREY |
| 86839 | KNOX | TALIA |
| 55638 | MORALES | JOHN |

If the PHP application relied on this stored procedure to retrieve department_ids, it would continue to be vulnerable to SQL injection attack.

SQL injection through stored programs can be serious, since stored programs that execute with definer rights can execute SQL not normally available to the user invoking the stored programs. Not only would the database be vulnerable to SQL injection attacks through a privileged account associated with a web application, but SQL could be injected by a nonprivileged user at the MySQL command line.

In this example, the use of dynamic SQL was unnecessary and arguably dangerous, since no validation of the input parameter was undertaken. In general, dynamic SQL inside of stored programs represents a significant security risk. We recommend the following policies to minimize your vulnerability:

• Use prepared statements inside of stored programs only when absolutely necessary.
• If you must use a prepared statement, and if that prepared statement includes strings provided as input parameters, make sure to validate that the strings are of the expected data type and length. For instance, in our previous example, had the input parameter been defined as an INTEGER, then the SQL injection would not be possible.
• Consider using invoker rights (SQL SECURITY INVOKER) when a stored program includes prepared statements. This limits your exposure, since the invoker will only be able to inject SQL that is within her security rights.