In my last post, Top 10 T-SQL Code Smells, I
caught some flack got some feedback for including one (#3) about the use of Stored Procedures for Select statements. Several people expressed objections over the risk of SQL Injection, and how Stored Procs would prevent it, but some of the correspondence I've gotten made me worry that, perhaps, some of those folks might have a false sense of security around this issue.
Disclaimer: the whole Stored Procs or not Stored Procs debate has already happened; I am not trying to poke that hornets' nest here. If you want all the pros and cons, and sometimes heated debate, please Bing-gle (or whatever). I have nothing new to add to that conversation, and I don't take sides in it.
I want to point out some specific details around this issue, in case you, gentle reader, are not as safe as you imagine. If you know all this detail already, you can stop here. If you don't, then I'll warn you that you might not sleep as well after you see this. Here's the basic issue: only permissions and parameters protect a database from SQL injection. Stored Procedures alone don't protect you, really, unless the developers of your apps also use parameters correctly. And if they do use parameters correctly, then demanding that they use only Stored Procs, just for protection from SQL Injection isn't really necessary (gasp!). It's just parameterization that does the work.
If your hackles are up at this point, please refer back to the disclaimer above - I am trying to clarify, not create an argument here.
Secondly, I want to note that from a database perspective, you're largely at the mercy of application code. The app developers had better handle this right, or injection will happen regardless.
So, the fact is that apps interact with SQL Databases using text strings of SQL. Period. There is no other way, unless you count something like SMO maybe, to make the SQL Server do things and fetch back data. Even a call to a Stored Procedure is a string passed to the server that says, "Execute someProcedure." The problem with injection, then, is that someone might send "bad things" in that string, instead of the "good things" an application is trying to do. DROP or DELETE, you name it. It doesn't matter, too much, what else is in the string along with the injected code - it could be "SELECT" or "EXECUTE." The fundamental issue is really simple.
There are only two ways to let "good things" work and prevent "bad things" in this interaction:
Stop the application from allowing "bad things" to be passed to the server at all, or
Once the "bad things" get to the server, prevent them from executing.
The only feasible and reliable way to do the first is by parameterizing all input to the server, which traps all the user input and enforces that it be treated as a value and not as executable code. Stored Procs are sympathetic to that pattern, but they don't enforce it. Even on a system using only Stored Procedures, there can be vulnerability to SQL injection if the parameters are not handled correctly on the application side. Which, incidentally, is the same situation as on a system that does not enforce use of Stored Procs.
The only way to do the second thing is via permissions. Stored Procs can play a role in this, in that they allow a user (principal) to perform some encapsulated action with a higher level of permissions than he/she would otherwise have; for example, a user can use a Stored Proc to perform inserts on tables that they can't otherwise manipulate. BUT, and this is important, it's the permissions that provide the safety mechanism, not just the fact that you have Stored Procedures. So, if your application uses the trusted application model, such that it connects to the database with a high level of permissions (dbo is common), then even if you use only Stored Procs in your design, you might not be safe from SQL injection.
It's also worth noting here that the vulnerability exists even if the DBA is totally in control of the Stored Procedure code, and there is no dynamic sql or other obvious issue database-side -- because the vulnerability is client-side, not in the database.
So, imagine we have a database for an application, and have implored our Devs only to use Stored Procs, but the app connects to the database as the database owner. What if a developer does this?:
static void Main(string args)
SqlConnection con = new SqlConnection("Data Source=.\\Test2005 <...> ");
String userInput = "FOO'; DROP TABLE ImportantStuff; Select '";
String sqlstring = "Exec myProcForSafety @aParameter = '" + userInput + "';";
SqlCommand command = new SqlCommand(sqlstring);
command.Connection = con;
Console.WriteLine("Are you pwnd? Press Any Key...");
We have designed the system only to use Stored Procedures, and the developer did that, yet the injected code will slip through anyway, and will execute at whatever level of permissions the application has.
So, I'm not pro or con Stored Procs here, I just think it's important to see what protects or doesn't protect. Restricting the permissions on an application account helps, and parameterizing all input application side helps, but please don't assume that just having Stored Procs makes your data safe. If we as DBA's are going to chant about preventing injection, I'd rather see us chanting "parameter parameter parameter" than "proc proc proc."