The single most common database security inquiry I get is, "What's this whole stored procedure parameter thing, and how does it help with SQL injection?"With the Albert Gonzales hearings under way, SQL injection is in the headlines again. Security professionals I speak with are tired of having to react to SQL injection: Their current process is to nervously await a database vendor patch to fix the vulnerability before it can be exploited, and once they get it, hope that the patch doesn't break the application or database processes. CISOs want to know how they address SQL injection in advance, with minimal disruption to business processing functions.

It seems like every week I get questions from CISOs or application developers who are not well-versed about database internals, asking me how to secure databases. Conversely, I get questions from long-time database administrators who do not have a complete grasp of the security threats they need to be aware of. Most people do not know that databases can store and run programs internally. There are several variants, such as triggers and functions, but to combat SQL injection attacks, we use stored procedures.

Stored procedures are small programs inside the database, written in the databases native language: SQL. Oracle, Postrges, SQL Server, MySQL, and the rest have this capability. Conceptually, a stored procedure is nothing more than a bunch of queries lumped together and stored in the database. A stored procedure helps stop SQL injection because it processes input parameters differently than raw SQL statements.

A SQL statement may look something like the following: SELECT last_name, salary FROM emp WHERE emp_id =123;

This asks the database for the last name and salary of person who's employee number is 123. The same query could be embedded in a stored procedure saved within the database. Calling the stored procedure request would look something like this: get_salary(123);

What is important is not the query, but the stored procedure declaration, which might look something like this: PROCEDURE get_salary (IN emp_id NUMBER,OUT last_name CHAR,OUT salary REAL) BEGIN ... Think of any SQL statement as being similar to a Perl script, where it is interpreted and executed after it is received. A stored procedure is closer to a C/C++ program in that it must be compiled and linked in advance before it can be used. To "compile" the stored procedure, the database needs to resolve a lot of questions, such as the number, data type, and size of each parameter to be exchanged between the calling application and the database. The stored procedure must define the parameters exactly or it will fail to compile. This process even generates an execution plan, which is conceptually similar to a road map instructing the database on how to execute the stored SQL statements. When a SQL statement is passed to the database from an application, it is sent to the parser. It's the parser's job to figure out what task the user is trying to accomplish, and translate the human readable SQL into instructions for the database engine.

In its simplest terms, SQL injection is an attack where you confuse the statement parser into doing something it is not supposed to do via specially crafted statements or parameters. Contrast this with a stored procedure, where the database automatically verifies the parameters that are being passed in. Each input value is compared against the procedure's predefined requirements of data type and size.

If any of the parameters are missing or do not meet the definition specified within the procedure, then an error is generated and the statement(s) within the procedure are not executed. There is very little room for error or confusion because input parameters are validated before use. And unlike raw SQL statements, the database already knows how to process the request, and cannot be fooled into performing other actions. And aside from defending against a SQL injection attack, there are many positive benefits to stored procedures over ad-hoc SQL statements sent by an application. Stored procedures provide huge performance benefits, provide granular error-handling, and support optional subprograms that allow you to further inspect content.

Stored procedures can effectively harden an application, so consider transferring some of your riskier queries inside the database.

Adrian Lane is an analyst/CTO with Securosis LLC, an independent security consulting practice. Special to Dark Reading.