Stored Procedures and SQL Injection

There is a nice post by Michael Howard on a couple simple steps to help mitigate SQL Injection attacks over on the Security Development Lifecycle blog this morning.  Simple steps that are effective by reducing the avenues of attack or reducing the assumptions of trust between the application and the database. However wanted to add a couple of comments onto this subject that I believe add some value to the suggestions he made. Specifically:

-Don't allow create/modify procedure permissions

-Use a dedicated, non-admin database user account

-Don't use external stored procedures

The first comment I wanted to make is on “Use SQL Execute Only Permissions” and “Use Stored Procedures”. This is absolutely correct. Not only is there typically a performance benefit, there is some built in parameter screening in the RDBMS that checks consistency and type.  The use of stored procedures is to combat injection of ad-hoc SQL statements. And the additional suggestion of using ‘execute only’ permissions on procedures is because I want to make sure that the stored procedures that I have cannot be replaced or altered by the SQL Injection attack. I do not want an attacker using SQL Injection to read my existing code, but this is a secondary consideration. The attacks I see could usually care less what logic you have, but really want to introduce their own functionality, either by introducing an ad-hoc query or by introducing a stored procedure. Use of stored procedures mitigates the first issue, and ‘execute only’ stops the alteration of the code. I am adding that you want to periodically check that the database user does not have create procedures permissions in their role in addition to the existing stored procedures being execute only. This combats the ‘Injection’ of new stored procedures to launch additional attacks.

There is another angle on this story that I would like to point out. I love the introduction to this post when Michael states “If you have a Web server (doesn't matter what type), and it's hooked up to a database (doesn't matter what type) you need to go in and review your code that performs the database work”. Absolutely! They go hand in hand. But the remainder of the post goes on to discuss deterrence and security from the web application developer’s perspective. That’s not a criticism, but I want to help illustrate that SQL Injection is a database attack made through a web application. 

But database administration and database platform security is oft left out of the conversation, and it should not be, as there are other simple tips that have similar benefits to helping reduce the possibility of SQL Injection. I have made some comments to several of the OWASP & WASC members here in San Jose, and that given the symbiotic relationship between database platforms and web application, may want to include some additional database platform security discussions with the application security discussions that they have today. For example, I had commented in a previous post that the entire attack could be negated by running the database administered under a different user account than the web application administrator. That is a very simple step to address a common abuse of trust relationships to carry out an attack.  So my second suggestion is use a dedicated database user for the web application account that does not have administrative rights.

My final suggestion on the topic of stored procedures has to do with external stored procedures. Most relational databases have the option of using external links and stored procedures to reference OS level code. This is in essence a program outside the database that can either run OS level functions, and often can use database functionality as well. I do not want to go into a long discussion here about the types of attacks that can be conducted through external code, or the dozens of issues pertaining to permissions, but simply comment that in using stored procedures for web applications, resist the urge to use external stored procedures or links. They can be very dangerous, and I typically advocate checking that the user rights do not include use of these external resources.

Most web developers look at a document like this one and tend to tune out.  So thanks to Michael for posting simple and effective SQL Injection prevention/mitigation steps for web applications.