Stored Program Security - Optimizing Stored Programs - MySQL Stored Procedure Programming (2009)

MySQL Stored Procedure Programming (2009)

Part IV. Optimizing Stored Programs

This final part of the book hopes to take you from "good" to "great." Getting programs to work correctly is hard enough: any program that works is probably a good program. A "great" program is one that performs efficiently, is robust and secure, and is easily maintained.

Stored procedures and functions raise a number of unique security concerns and opportunities: these are discussed in Chapter 18. Chapters 19 through 22 cover performance optimization of stored programs. Chapter 19 kicks off with a general discussion of performance tuning tools and techniques. The performance of your stored programs will be largely dependent on the performance of the SQL inside, so Chapters 20 and 21 provide guidelines for tuning SQL. Chapter 22 covers performance tuning of the stored program code itself.

Chapter 23 wraps up the book with a look at best practices in stored program development. These guidelines should help you write stored programs that are fast, secure, maintainable, and bug-free.

Chapter 18, Stored Program Security

Chapter 19, Tuning Stored Programs and Their SQL

Chapter 20, Basic SQL Tuning

Chapter 21, Advanced SQL Tuning

Chapter 22, Optimizing Stored Program Code

Chapter 23, Best Practices in MySQL Stored Program Development

Chapter 18. Stored Program Security

Security has always been critical in the world of databases and stored programs that work with those databases. Yet database security has taken on heightened importance in the last decade, with the global reach of the Internet and the increasing tendency for the database to be the target of those trying to compromise application security. In this chapter we explore two different aspects of security as it pertains to MySQL stored programming:

§ Controlling access to the execution and modification of stored programs themselves

§ Using stored programs to secure the underlying data in MySQL databases

Stored programs—in particular, stored procedures—are subject to most of the security restrictions that apply to other database objects, such as tables, indexes, and views. Specific permissions are required before a user can create a stored program, and, similarly, specific permissions are needed in order to execute a program.

What sets the stored program security model apart from that of other database objects—and from other programming languages—is that stored programs may execute with the permissions of the user who created the stored program, rather than those of the user who is executing the stored program. This model allows users to execute operations via a stored program that they would not be privileged to execute using straight SQL.

This facility—sometimes called definer rights security—allows us to tighten our database security: we can ensure that a user gains access to tables only via stored program code that restricts the types of operations that can be performed on those tables and that can implement various business and data integrity rules. For instance, by establishing a stored program as the only mechanism available for certain table inserts or updates, we can ensure that all of these operations are logged, and we can prevent any invalid data entry from making its way into the table.

We can also create stored programs that execute with the privileges of the calling user, rather than those of the user who created the program. This mode of security is sometimes called invoker rights security, and it offers other advantages beyond those of definer rights, which we will explore in this chapter.

Before delving into the two execution modes available in MySQL, we will first examine the basic permissions needed to create, manage, and execute stored programs . Then we'll go into a detailed discussion of definer rights and invoker rights, and consider how these capabilities might be used in our applications. Finally, we will consider the use of stored programs to increase the general security of our MySQL server and, conversely, identify ways in which the use of stored programs can reduce overall security if developers are not careful.

Permissions Required for Stored Programs

MySQL 5.0 introduced a few new privileges to manage stored programs. These privileges are:

CREATE ROUTINE

Allows a user to create new stored programs.

ALTER ROUTINE

Allows a user to alter the security mode, SQL mode, or comment for an existing stored program.

EXECUTE

Allows a user to execute a stored procedure or function.

With these distinct privileges available, we can very granularly decide what we want to allow individual developers to be able to do (as in "Sam can run program X, but not make any changes to it.").

Granting Privileges to Create a Stored Program

To give a user permission to create a stored procedure, function, or trigger, grant the CREATE ROUTINE privilege to that user using the GRANT statement. We can do this for a specific database or for all databases on the server. For example, the following GRANT statement gives the usersp_creator permission to create stored programs within the database mydatabase:

GRANT CREATE ROUTINE ON mydatabase.* TO sp_creator;

Granting Privileges to Modify a Stored Program

The ALTER ROUTINE privilege gives a user permission to change the security mode, SQL mode, or comment for a stored procedure or function. However, this privilege does not allow us to change the actual program code of a procedure. To change the program code, we must DROP and then CREATE a new program. In the following example, we change the security mode, sql_mode setting, and comment for a procedure:

ALTER PROCEDURE simple_stored_proc

SQL SECURITY INVOKER

READS SQL DATA

COMMENT 'A simple stored procedure';

Granting Privileges to Execute a Stored Program

The EXECUTE privilege gives a user permission to execute a stored procedure or function. (For triggers, see Chapter 11.) EXECUTE privileges should be granted selectively, especially if the program is created with the "definer rights" security setting (see the section "The SQL SECURITY Clause" later in this chapter). The syntax for this form of the GRANT statement is:

GRANT EXECUTE ON [{PROCEDURE|FUNCTION}] database.program_name TOuser

You can omit the ON PROCEDURE or ON FUNCTION clause if you are performing a wildcard grant, as in the following example:

GRANT EXECUTE ON mydatabase.* TO sp_creator;

If you are granting access to a specific program, you must specify ON PROCEDURE or ON FUNCTION explicitly; it is possible for a stored procedure and a stored function to have the same name, and it is unacceptable to issue an ambiguous security command. To grant the EXECUTE privilege on the procedure mydatabase.test1, issue the following statement:

GRANT EXECUTE ON PROCEDURE mydatabase.test1 TO sp_creator;

Execution Mode Options for Stored Programs

Stored program code differs from any other kind of code that might execute against the database in that it can have database privileges that are different from those of the account that executes the stored program. Normally, when we execute some SQL—whether it is inside the MySQL client, a PHP program, or whatever—the activities that the SQL will perform (read table X, update table Y, etc.) will be checked against the privileges that are associated with the database account to which we are connected. If our account lacks privilege to perform the activity, the SQL statement will fail with the appropriate error.

Stored programs can be defined to act in the same way, if the SQL SECURITY INVOKER clause is included in the CREATE PROCEDURE or CREATE FUNCTION statement used to create the program. However, if SQL SECURITY DEFINER (the default) is specified instead, then the stored program executes with the privilege of the account that created the stored program, rather than the account that is executing the stored program. Known as definer rights, this execution mode can be a very powerful way of restricting ad hoc table modifications and avoiding security breaches. Definer rights can also be a problem, however, if you are relying on traditional security privileges to secure your database.

Let's go through a quick example before we dig in more deeply. A user creates a procedure to execute a simple transaction, as shown in Example 18-1.

Example 18-1. Simple transaction using definer rights security

CREATE PROCEDURE tfer_funds

(from_account INT, to_account INT,tfer_amount NUMERIC(10,2))

SQL SECURITY DEFINER

BEGIN

START TRANSACTION;

UPDATE account_balance

SET balance=balance-tfer_amount

WHERE account_id=from_account;

UPDATE account_balance

SET balance=balance+tfer_amount

WHERE account_id=to_account;

INSERT into transaction_log

(user_id, description)

values(user( ), concat('Transfer of ',tfer_amount,' from ',

from_account,' to ',to_account));

COMMIT;

END;

We grant the EXECUTE privilege on this procedure to Fred, who has no other privileges to the account_balance table:

GRANT EXECUTE ON PROCEDURE prod.tfer_funds TO 'FRED'@'%';

Now, Fred would like to make some illicit changes to the account_balance table, but he is unable to do so directly:

C:\bin32>mysql -uFRED -pFRED -Dprod

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7 to server version: 5.0.18-nightly-20051211-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM account_balance;

ERROR 1142 (42000): SELECT command denied to user 'FRED'@'localhost' for table

'account_balance'

mysql> INSERT INTO account_balance (account_id,balance) values(324,4000);

ERROR 1142 (42000): INSERT command denied to user 'FRED'@'localhost' for table

'account_balance'

mysql> ARGH!

-> ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual

that corresponds to your MySQL server version for the right syntax to use

near 'ARGH'!' at line 1

Fred can use the stored procedure to adjust balances (as shown in Figure 18-1), but by doing so he is required to take the money "from" somewhere and to create an incriminating row in the transaction_log table:

mysql> CALL tfer_funds(324,916,200);

Query OK, 0 rows affected (0.44 sec)

mysql> SELECT * FROM transaction_log WHERE user_id LIKE 'FRED%';

+---------------------+----------------+---------------------------------+

| txn_timestamp | user_id | description |

+---------------------+----------------+---------------------------------+

| 2005-04-14 11:23:45 | FRED@localhost | Transfer of 200 from 324 to 916 |

+---------------------+----------------+---------------------------------+

2 rows in set (0.00 sec)

mysql> ARGH!

-> ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near ARGH!' at line 1

A definer rights stored program can execute SQL that the user does not have direct permission to execute

Figure 18-1. A definer rights stored program can execute SQL that the user does not have direct permission to execute

In short, using "definer rights" lets us grant permission to use the database only in ways that we clearly define through stored programs. If you like, you can think of such stored programs as an API to the database that we provide to users.

The down side of using stored programs in this way is that it makes it much harder to be certain how you have restricted access to certain objects. For instance, we can issue the following statement to try and make sure that Fred cannot look at account balances:

REVOKE SELECT ON prod.account_balance FROM 'FRED'@'%';

However, we would need to review all of the stored programs that Fred has access to before we could be 100% sure that he cannot perform any such activity.

If we want stored programs to succeed only if the user has sufficient privileges to execute the SQL statements that they contain, then we need to create an invoker rights program instead. Example 18-2 shows the tfer_funds stored procedure created with the SQL SECURITY INVOKERoption specified.

Example 18-2. Invoker rights stored procedure

CREATE PROCEDURE tfer_funds

(from_account INT, to_account INT,tfer_amount NUMERIC(10,2))

SQL SECURITY INVOKER

BEGIN

START TRANSACTION;

UPDATE account_balance

SET balance=balance-tfer_amount

WHERE account_id=from_account;

UPDATE account_balance

SET balance=balance+tfer_amount

WHERE account_id=to_account;

INSERT into transaction_log

(user_id, description)

values(user( ), concat('Transfer of ',tfer_amount,' from ',

from_account,' to ',to_account));

COMMIT;

END;

Now if we want Fred to be able to execute this stored program, we will have to explicitly grant him access to the tables involved. Otherwise, he gets a security error when he executes the procedure:

mysql> CALL tfer_funds(324,916,200);

ERROR 1142 (42000): UPDATE command denied to user 'FRED'@'localhost' for table

'account_balance'

Figure 18-2 illustrates these operations.

As well as arguably clarifying the relationship between users and table privileges, the use of the SQL SECURITY INVOKER option allows us to prevent certain security holes that can arise when stored programs execute dynamic SQL. A stored program that can execute dynamic SQL (seeChapter 5) and that runs with definer rights can represent a significant security risk; see the section "SQL Injection in Stored Programs" later in this chapter.

The SQL SECURITY Clause

The SQL SECURITY clause of the CREATE PROCEDURE and CREATE FUNCTION statements determines whether the program will operate with the privileges of the invoker or those of the definer. The syntax is straightforward:

An invoker rights procedure can only issue SQL that the user has permission to execute

Figure 18-2. An invoker rights procedure can only issue SQL that the user has permission to execute

CREATE {PROCEDURE|FUNCTION} program_name (parameter_definitions)

[ SQL SECURITY {INVOKER|DEFINER} ]stored_program_statements

If no SQL SECURITY clause appears, then the program is created with the SQL SECURITY DEFINER option.

The SQL SECURITY clause can be changed without having to re-create the stored procedure or function using the ALTER PROCEDURE or ALTER FUNCTION statement as follows:

ALTER {PROCEDURE|FUNCTION} program_name

SQL SECURITY {INVOKER|DEFINER};

The SQL SECURITY clause applies only to procedures or functions; a related clause— DEFINER—can be applied to triggers if you want to change the execution privileges under which a trigger runs. See Chapter 11 for more details about this clause.

Using Definer Rights to Implement Security Policies

As we have already discussed, stored programs defined with the SQL SECURITY DEFINER clause can execute SQL statements that would normally not be available to the account executing the stored program. We can use this facility to provide extensive control over the way in which the user interacts with the database.

If we write our application without stored programs, then our front-end code (written in, say, PHP) interacts directly with the underlying MySQL tables. As a consequence, each MySQL account that will be used to run the application must be granted all of the permissions required by the application code.

Directly granting privileges to accounts, however, can lead to significant security problems. Users can take advantage of any client tool, including the MySQL command line, to connect to this account, thereby circumventing any security controls that might have been placed within the application logic.

Let's take a look at a scenario that demonstrates the security issues with a MySQL application that does not use stored programs. If an application performs operations on tables within the prod schema, we might create an account for that application and grant it rights to perform queries and DML on all of the tables in that schema:

GRANT SELECT, UPDATE, DELETE, INSERT ON prod.* TO myapp@'%'

The myapp account is now a highly privileged account—a hacker who got hold of the account password could delete any or all rows in any of the application tables, select any data (salaries, credit cards, etc.), and perform any number of malicious or dishonest activities.

On the other hand, in a scenario in which we use stored programs to control access to the database, we only need to grant EXECUTE permission on the programs that make up the application:

GRANT EXECUTE ON prod.* TO myapp@'%'

A user connecting to the myapp account can still get her work done, by calling the appropriate elements in the application—but that is precisely all that the user can do. If the capability is not implemented within the application, then it is not available to the user. This significantly reduces the exposure of the database to malicious users if the connection information for the myapp account is compromised.

For instance, our application might contain internal logic that prevents a user from accessing the salary information of employees unless the user is a senior-level manager or a member of the Payroll department. However, this application-level restriction can easily be circumvented if the user logs into the database using the MySQL Query Browser and issues SQL against the database.

By using a "definer rights" stored program, we can ensure that the user gains access to database tables only via code that we provide within the stored program. In that way, we can ensure that the security and integrity of our database is maintained, even if a user logs onto the database directly.

Example 18-3 shows a stored procedure that returns employee details. The stored procedure was created with the SQL SECURITY DEFINER clause, so anyone with the EXECUTE privilege on this procedure will be able to view the employee details, even if he or she doesn't have the SELECTprivilege on this table.

The stored procedure checks the ID of the user who executes the procedure and compares this ID with information in the employees table. If the user executing the stored procedure is a senior-level manager or a member of the Payroll department, then the employee details are returned without modification. Otherwise, the employee details are returned with the salary details obscured.

Example 18-3. Procedure that restricts access to employee salary data

1 CREATE PROCEDURE sp_employee_list(in_department_id DECIMAL(8,0))

2 SQL SECURITY DEFINER READS SQL DATA

3 BEGIN

4 DECLARE l_user_name VARCHAR(30);

5 DECLARE l_not_found INT DEFAULT 0;

6 DECLARE l_department_name VARCHAR(30);

7 DECLARE l_manager_id INT;

8

9 DECLARE user_csr CURSOR FOR

10 SELECT d.department_name,e.manager_id

11 FROM departments d JOIN employees e USING(department_id)

12 WHERE db_user=l_user_name;

13

14 DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_not_found=1;

15

16 /* Strip out the host from the user name */

17 SELECT SUBSTR(USER(),1,INSTR(USER( ),'@')-1)

18 INTO l_user_name;

19

20 OPEN user_csr;

21 FETCH user_csr INTO l_department_name,l_manager_id;

22 CLOSE user_csr;

23

24 IF l_department_name='PAYROLL' OR l_manager_id IN (0,1) THEN

25 SELECT surname,firstname,salary

26 FROM employees

27 WHERE department_id=in_department_id

28 ORDER BY employee_id;

29 ELSE

30 /* Not authorized to see salary */

31 SELECT surname,firstname,'XXXXXXX' AS salary

32 FROM employees

33 WHERE department_id=in_department_id

34 ORDER BY employee_id;

35 END IF;

36

37 END;

Let's look at the key parts of this code:

Line(s)

Explanation

17

Retrieve the name of the account currently executing the stored procedure.

20–22

Retrieve the employee record with the matching ID.

24–28

If the corresponding user is in the Payroll department or is a first- or second-level manager, then we return the employee salary unmasked.

31–34

Otherwise, return the data with the salary details masked.

Fred is a software developer with our company who should not be able to see employee salary details. When he executes the stored procedure, the salary details are masked out, as shown in Example 18-4.

Example 18-4. Using a stored procedure to restrict access to sensitive information

C:\>mysql -ufred -pfred -Dprod

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 21 to server version: 5.0.18-nightly-20051211-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CALL sp_employee_list(3);

+-------------+-----------+---------+

| surname | firstname | salary |

+-------------+-----------+---------+

| RAYMOND | GOLDIE | XXXXXXX |

| RACE | ARLENA | XXXXXXX |

| HAGAN | LYNNA | XXXXXXX |

| MARSTEN | ALOYS | XXXXXXX |

| FILBERT | LEON | XXXXXXX |

| RAM | SANCHO | XXXXXXX |

| SAVAGE | SORAH | XXXXXXX |

| FLOOD | ULRIC | XXXXXXX |

| INGOLD | GUTHREY | XXXXXXX |

| WARNER | WORTH | XXXXXXX |

| LEOPARD | AUSTIN | XXXXXXX |

| ROBBINETTE | BRIAN | XXXXXXX |

| REUTER | LORIS | XXXXXXX |

| MITCHELL | HUGO | XXXXXXX |

Fred is unable to select from the employees table directly, so there is no way for him to retrieve the employee salary data, as shown in Example 18-5.

Example 18-5. Direct access to the underlying tables is denied

mysql> SELECT * FROM employees;

ERROR 1142 (42000): SELECT command denied to user 'fred'@'localhost' for table

'employees'

Jane is a member of the Payroll department, so when she executes the procedure, she can see the salary details, as shown in Example 18-6.

Example 18-6. The stored procedure allows authorized users to view salary details

C:\>mysql -uJane -pJane -Dprod

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 21 to server version: 5.0.18-nightly-20051211-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CALL sp_employee_list(3);

+-------------+-----------+--------+

| surname | firstname | salary |

+-------------+-----------+--------+

| RAYMOND | GOLDIE | 53465 |

| RACE | ARLENA | 45733 |

| HAGAN | LYNNA | 85259 |

| MARSTEN | ALOYS | 49200 |

| FILBERT | LEON | 97467 |

| RAM | SANCHO | 58866 |

| SAVAGE | SORAH | 83897 |

| FLOOD | ULRIC | 84275 |

| INGOLD | GUTHREY | 60306 |

| WARNER | WORTH | 47473 |

Note that, like Fred, Jane may not directly access the employees table. Instead, she must call the sp_employee_list procedure when she wants to see the salaries for a department. If we move her to another department, she will automatically lose the ability to view these salary details.

We can also use definer rights programs to ensure that transactions applied to the database always conform to various business rules and regulatory compliance measures that we might have in place. Using a stored program to control all inserts into the sales table, for example, could be used to automate the maintenance of audit and summary tables. We saw an example of logging DML within a stored procedure in Example 18-2.

Stored Program or View?

It is sometimes possible to use a view rather than a stored program to implement some aspects of database security. For example, a user can select from a view even if he does not have access to the underlying tables, so with a view you can control which columns and rows a user can see.

Using CASE statements and WHERE clause conditions, it is often possible to create views that restrict access to only appropriate rows or—using updatable views—those that restrict modifications. For instance, the two views in Example 18-7 were designed to perform some of the security limitations provided by the stored procedure from Example 18-3.

Definer Rights in N-tier Applications

In the days when client/server applications ruled the earth, end users were often given individual database accounts, and they authenticated themselves to the application by connecting to the database. In modern web-based or N-tier applications, users typically authenticate with a middle-tier application or web server, and all users share a pool of common "proxy" database connections.

The definer rights stored program security model was first defined during the client/server era, and it largely reflects this idea that the end user might actually know her database username and password. Nevertheless, the definer rights model still has a valid role in a web-based environment, since it helps limit the exposure if the proxy account is compromised.

In a modern application that uses proxy accounts, access to the password for the proxy account will be carefully restricted. The proxy account should generally be used only by the application server. If a malicious user obtains the password to the proxy account, however, he could then have unrestricted access to the underlying database tables.

By using stored programs to mediate between the application server and the database, we can carefully limit the activities that the proxy account can undertake. We can also implement auditing, alarming, and logging to help us identify any malicious use of this account.

Of course, you should very carefully secure an application's proxy database account under any scenario. But if you are careful to limit that proxy account to execution of application stored programs, you will also limit the damage a malicious user can inflict in a compromised scenario.

Example 18-7. Using a view to implement security policies

CREATE VIEW current_user_details_view AS

SELECT departments.department_name,employees.manager_id

FROM employees join departments using (department_id)

WHERE db_user=convert(SUBSTR(USER(),1,INSTR(USER( ),'@')-1) using latin1) ;

CREATE VIEW employees_view AS

SELECT firstname,surname,salary,db_user,

CASE WHEN u.department_name='PAYROLL' OR u.manager_id IN (0,1) THEN

salary

ELSE '0000000000'

END CASE AS salary

FROM employees e, current_user_details_view u ;

Using a view to implement these kinds of access restrictions is attractive, since the view implementation would allow the user more flexible query capabilities (aggregate functions, WHERE clause restrictions, etc.). On the other hand, as the security restrictions become more complex, it becomes increasingly difficult—and ultimately impossible—to create views to implement those restrictions. Finally, most organizations must ensure the integrity of transactions, and this cannot be encoded in view definitions.

Handling Invoker Rights Errors

When you create a stored program with invoker rights, you can be sure that the stored program will succeed only if the user executing the stored program has the necessary privileges. This means that you don't have to be particularly careful about who gets EXECUTE privileges to the program—the program will never let them do something that they didn't already have the privilege to do in native SQL. What this means, however, is that the program is now more likely to raise an exception at run-time, since we can't know in advance that the user has the required privileges.

The possibility of runtime security exceptions in invoker rights programs means that you will generally want to add handler logic to these programs. Consider the stored procedure shown in Example 18-8.

Example 18-8. Stored procedure using invoker rights

CREATE PROCEDURE sp_cust_list (in_sales_rep_id INT)

SQL SECURITY INVOKER

BEGIN

SELECT customer_id, customer_name

FROM customers

WHERE sales_rep_id=in_sales_rep_id;

END;

This stored procedure includes the SQL SECURITY INVOKER clause, so any user who invokes the stored procedure must have the SELECT privilege on the customers table. When Fred, who does not have this privilege, runs sp_cust_list, he will see the error message shown in Example 18-9.

Example 18-9. Invoker privileges can lead to unhandled security-violation errors

mysql> CALL sp_cust_list(14);

ERROR 1142 (42000): SELECT command denied to

user 'fred'@'localhost' for table 'customers'

Under some circumstances, throwing an unhandled exception in this way might be sufficient. For many applications, however, it will be necessary to trap the error and provide better information and guidance to the user. Consider the revised implementation of the sp_cust_list procedure, shown in Example 18-10.

Example 18-10. Handling security violations with invoker rights procedures

CREATE PROCEDURE sp_cust_list2 (in_sales_rep_id INT)

SQL SECURITY INVOKER

BEGIN

DECLARE denied INT DEFAULT 0;

DECLARE command_denied CONDITION FOR 1142;

DECLARE CONTINUE HANDLER FOR command_denied SET denied=1;

SELECT customer_id, customer_name

FROM customers

WHERE sales_rep_id=14;

IF denied =1 THEN

SELECT 'You may not view customer data.'

AS 'Permission Denied';

END IF;

END;

Now when Fred runs this program, he is denied the ability to see the customer information, but at least gets a clearer explanation of the problem, as shown in Example 18-11.

Example 18-11. Handling security violations in a stored procedure

mysql> CALL sp_cust_list2(14);

+--------------------------------------------------------------------+

| Permission Denied |

+--------------------------------------------------------------------+

| You may not view customer data. |

+--------------------------------------------------------------------+

1 row in set (0.00 sec)

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

1 <html>

2 <title>Employee Query</title>

3 <h1>Employee Query</h1>

4

5 <FORM ACTION="<?php echo $_SERVER['PHP_SELF']; ?>" METHOD=POST>

6 <p>Enter Department Id:

7 <input type="text" name="department" size="60">

8 <input type="submit" name="submit" value="submit"><p>

9 </form>

10

11 <?php

12 require_once "HTML/Table.php";

13

14

15 /*Check to see if user has hit submit*/

16 if (IsSet ($_POST['submit'])) {

17 $dbh = new mysqli($hostname, $username, $password, $database);

18

19 /* check connection */

20 if (mysqli_connect_errno( )) {

21 printf("Connect failed: %s\n", mysqli_connect_error( ));

22 exit ( );

23 }

24 $sql="SELECT employee_id,surname,firstname FROM employees".

25 " WHERE department_id =".$_POST['department'];

26 print $sql;

27 if ($result_set = $dbh->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("<p>Error retrieving stored procedure result set:%d (%s) %s\n",

39 mysqli_errno($dbh), mysqli_sqlstate($dbh), mysqli_error($dbh));

40 }

41

42

43 result_set->close( );

44 $dbh->close( );

45? >

46

47 </body></html>

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, and—in this case—it 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.

Using SQL injection to obtain employee salaries

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));

}

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 syntax—it can only accept parameters to the stored program call. This makes a stored program call effectively immune to SQL injection—regardless 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( );

Tip

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.

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_id=",in_dept_id);

PREPARE s1 FROM @sql;

EXECUTE s1;

DEALLOCATE PREPARE s1;

END;

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.

Conclusion

In this chapter we looked at the basic security permissions required for creating and executing stored programs and at how the SQL SECURITY clause affects the security context of an executing stored program.

By default—or if the SQL SECURITY DEFINER clause is specified—stored programs execute with the permissions of the account that created the stored program. This means that a database user can execute a stored program that can perform database operations not available to that user through normal SQL. You can use this feature to implement a scheme in which a user can manipulate the database through stored programs but has no privilege to manipulate the database through normal SQL. Restricting database access in this way through stored programs can improve database security, since you can ensure that table accesses are restricted to known routines that perform appropriate validation or logging. You can reduce your exposure should the database account involved be compromised.

If the SQL SECURITY INVOKER clause is specified, then the stored program will execute with the permissions of the account that is executing the stored program. In this case, an exception will be raised if the stored program attempts to execute a SQL statement that the invoker does not have permission to execute as native SQL.

Stored programs in MySQL 5.0 are implicitly resistant to SQL injection—unless they include dynamic SQL via prepared statements. We recommend that you exercise caution when using dynamic SQL in stored programs—take every precaution to ensure that the stored procedure or function is not vulnerable to malicious SQL injection. If prepared statements and dynamic SQL are necessary, then make sure to validate input parameters, and consider using the SQL SECURITY INVOKER mode to limit your exposure.