Busting the SQL stored procedure myth

One of the commonly proposed remedies for SQL Injection is to use SQL stored procedures. Use of stored procedures can greatly reduce the likelihood that you’ll code an SQL injection, but it’s not the stored procedure-ness that’s saving you. Stored procedures let you use Static-SQL instead of forcing you to always use Dynamic-SQL. In Static-SQL the metadata for the query is known at compile-time whereas in Dynamic-SQL the program constructs the query at runtime. Dynamic-SQL is injectable; Static-SQL is not.

For most programs, Static-SQL is sufficient. Only a small subset of features such as user driven filtering interfaces need Dynamic-SQL’s flexibility. Unfortunately, the common database access protocols, ODBC and JDBC, are based on Dynamic-SQL and there’s no provision in the interface for static queries. It’s into this void that SQL stored procedures stepped in.

But SQL stored procedures aren’t limited to Static-SQL. It’s possible to use Dynamic-SQL through the PREPARE, EXECUTE and EXECUTE IMMEDIATE statements (these are the ANSI SQL-92 statement names, your RDBMS have different names). In fact, ODBC and JDBC are just wrappers that ship the SQL text from your program to the database server and make calls to the RDBMS through the runtime support for these Dynamic-SQL statements (actually). So, stored procedures that execute Dynamic-SQL statements are equally vulnerable to SQL injection. It doesn’t matter if it’s Java code concatenating strings and passing them to JDBC or if the strings are concatenated in PL/SQL. Both are equally vulnerable.

By now you’re probably wondering why I’m splitting this hair. I’m splitting it because I want to ensure that we protect ourselves from the programmer we’ve not yet met. It’s the programmer that goes and dutifully reads our coding guideline that says “Use SQL stored procedures to prevent SQL Injection” and now has to extend our application with the feature that introduces Dynamic-SQL. But, because we haven’t properly articulated the guidance, s/he creates an SQL injection vulnerability.

Implementing your queries using Static-SQL will go a long way to protect your application from SQL Injection. It’s also possible to simulate Static-SQL using ODBC/JDBC type interfaces with the religious use of SQL parameter markers. This leaves the use of Dynamic-SQL only for truly dynamic queries like the dynamic user-defined filter I mentioned above. There’s a pattern for securely implementing such a filter that’s floating around in my head. I need to write that down, but you’ll have to wait until next month. Sorry.