Stored Procedure Security

All modern database engines provide support for stored procedures or prepared statements, database commands (or more frequently, routines containing multiple commands) that are stored in the database itself alongside the data. (MySQL was the last holdout, and didn’t implement stored procedures until version 5.0, which was released in October 2005.) Using stored procedures in your application can improve it in many ways. Stored procedure code can be easier to maintain than dynamic SQL built into the application source code, since developers and database administrators (DBAs) can make changes to stored procedures without having to recompile the application. Stored procedures also often have much better performance than dynamic SQL, since the database engine can compile and optimize the stored procedure code. And finally, using stored procedures can improve the security of your application too. In this section, we’ll take a look at some best security practices around the use of stored procedures, and point out some misconceptions and pitfalls to avoid, too.

The Stored-Procedures-Only Approach: Reducing Permissions Even Further

In the previous section, we showed how to reduce the attack surface of your application by reducing the permissions granted to the application database user. With stored procedures, we can take this defense to an even higher level. If you really want to have the most injection-proof application possible, you can grant your application database user only the rights to execute stored procedures. This means that the database user (and the application) will have no direct CRUD access to any of the database tables. You won’t be able to make direct queries like SELECT * FROM Users, but this means that attackers won’t be able to make these queries either.

Without the rights to access the database tables directly, you’ll have to write your application to always use stored procedures to access the database. For example, to add order searching to our example application, we would first add a “getOrdersByCustomerId” stored procedure to the database:

Next, we add execute permissions for the new stored procedure for our database user. Once that’s complete, we can call the stored procedure from our application code:

Note

Notice that we’re still using the parameterized query syntax to call the stored procedure. If we were to fall back to building an ad-hoc query to call it, our efforts to make the application more secure would backfire and the code would once again be vulnerable to SQL injection.

While it’s a little more upfront work to create a separate stored procedure and set permissions instead of just writing an inline query every time you want to access the database, this design strategy dramatically limits the potential damage from any SQL injection attack against the database. Even if an attacker were to successfully inject an attack, the payloads he could execute would be limited to only what the application is supposed to be able to do anyway. For example, an attacker couldn’t drop the Users table unless you’ve already created a stored procedure to do it.

SQL Injection in Stored Procedures

Even after all of the SQL injection defense precautions we’ve already talked about—hiding detailed errors, avoiding ad-hoc SQL statement creation, using stored procedures, and reducing permission sets—it’s still possible to end up with vulnerable code. Just as you had to parameterize your queries and stored procedure calls in your application code, you also have to take similar steps to parameterize your SQL queries within the stored procedures themselves. Otherwise, if you write stored procedure code that dynamically builds SQL query strings, you can actually end up with SQL-injectable stored procedures.

There is a SQL command named EXECUTE (or EXEC) that will execute any string passed to it, and you can use this function inside a stored procedure. For example, this stored procedure code is perfectly legal:

This particular example is a little weird—there’s no good reason to use EXECUTE here, it only degrades performance and offers no benefits in return—but it’s still technically secure against SQL injection. However, if you change the procedure code to search on a customer name passed in as a parameter, you’ll get into trouble:

An attacker could enter ′; DROP TABLE Users; -- for the customer name, and the engine would execute the command:

And you’re right back to the vulnerable state you were in at the very start of the chapter.

Most of the time, people write ad-hoc SQL stored procedures for cases where they can’t use procedure parameters in the normal, secure way. Just as you can’t use parameterized queries to vary table names or column names (for example, you can’t write a query like SELECT? FROM Users), you can’t do this in a stored procedure either. The same security guidelines apply in this case that did before: First, consider breaking up the stored procedure into multiple stored procedures, one for each table or column possibility. (If the security benefits of this approach aren’t enough to convince you, there are performance benefits too: database engines can’t precompile dynamically built EXECUTE stored procedures so that they run faster.) If you can’t break up the stored procedures and you really do need to execute ad-hoc SQL, then you’ll have to manually escape the parameters within the stored procedure SQL code.