June 11, 2008

Stored Procedures

A friend of mine is studying up for an SQL Server certification, and posted some commentary on a section about stored procedures.

I have to say I don't like the tone of the passage either. I think I see where the author's coming from, though, and, like the first commenter, I think it's largely for protection against injection attacks. In particular, I'd say it's because they provide language-agnostic protection against injection attacks. I almost wanna say that it's an attempt at protection against developer error creating attack vectors in your software.

What I mean by this is simple: if your policy is to only ever use stored procedures, you don't have to worry about a choice of languages, libraries, or SQL server calls allowing end-users to get into your database. You don't have to depend on someone coding input sanitization (which you should do anyway) or the use of parameterized queries when all the data access is being done with delegated authority through a stored procedure.


Brian Sullivan said...

Your point about client environment is well taken. I hadn't really considered the scenario of different front end languages/environments and how it would impact data access. However, I would say that it probably ought to be the responsibility of the developer to know the proper SQL injection prevention techniques for his/her programming environment. My main gripe was the idea that the database is something to be jealously guarded from all but the anointed DBA priesthood. It can become quite a hindrance when even a fairly trivial operation (adding a column to a test table, ahem) requires a DBA. You incur quite a bit of efficiency cost when the operation goes from 1 required person to 2.