Yet Another Stored Procedure vs. Ad-hoc Query Discussion?

Peter W. DeBetta — Thu, 03 Apr 2008 04:44:00 GMT

Earlier today, Will Sullivan posted a blog entry, My Statement on Stored Procedures, in which he emphatically states his official opinion of stored procedures as:

"I prefer not to use them."

He then goes about dismissing most of the misinformation about why stored procedures are better than ad-hoc (parameterized) queries.

The first bit of misinformation he dispels is the now defunct argument that "Stored Procedures are faster than ad-hoc queries". He states that "Unless your ad-hoc queries are always significantly different from each other, their execution plans are cached right along side those of the SP's." I completely agree. We'll call that one a tie, so the score so far: SP 0, Ad-hoc 0.

Another myth he tries to debunk is that "Editing SP's is a breeze with Query Analyzer". Query Analyzer - that's so SQL Server 2000. Seriously, though, there are a number of fine code editors that allow you to edit SPs with ease. Query Analyzer is not at the top of that list, however. I will say that when you write T-SQL you should use a code editor that is meant for T-SQL, for the same reasons that when you write C#, you want to use a code editor meant for C#. Again, no winner here, so the score remains: SP 0, Ad-hoc 0.

He addresses another statement that is supposedly made in defense of SPs: "Ad-hoc queries are a nightmare to maintain, as they are spread all over your code". Again, either one is easy to maintain, with the right tools. We are still scoreless: SP 0, Ad-hoc 0.

It just so happens that I agree with many of his points. And there are other objective and subjective points on topics such as organization, maintenance, design, and so on, which one could argue for either SPs or ad-hoc queries equally so. Don't get me wrong, however, as I believe that using ad-hoc queries when you could have used stored procedures is simply wrong.

And so I will address Will's last point (actually, it was his second point) that is repeatedly misrepresented: "Stored Procedures are safe against SQL injection attacks; ad-hoc queries are not".

Ad-hoc queries prevent SQL Injection attacks as well as SPs do. Any claim otherwise would be wrong. But that's not the issue. The problem is that ad-hoc queries require that you expose the underlying objects of the database. In order to use ad-hoc queries, you must allow direct access for select, insert, update, and delete operations to the tables in the database. Although I know most experienced developers would only write ad-hoc/parameterized queries against the underlying data, at a later date, some disgruntled or inexperienced developer may write dynamic SQL instead (I have seen it happen), and expose the database to SQL injection attacks (which I have also seen in production systems), including exposure to such awful actions as...

-- Can you say Identity Theft?
SELECT FirstName, LastName, ZIP, CreditCardNumber, CreditCardType, CreditCardExpoiration, CVV
FROM Customers

...or worse...

-- Do we really need all those customers?
DELETE Customers

...or even worse...

-- NEVER EVER DO THIS, PLEASE
-- This will execute a DELETE against all tables
EXEC sp_MSforeachtable 'DELETE ?'

...or even, even worse (assuming the SQL login has elevated permissions - which many apps do)...

-- NEVER EVER DO THIS, PLEASE
-- This will drop all tables from the database
EXEC sp_MSforeachtable 'DROP TABLE ?'

...and so although your ad-hoc query code won't allow SQL injection, some other programmer's dynamic SQL will. Assuming you've correctly secured your database, this doesn't happen with stored procedures since you do not have to expose any of the underlying tables (because of a little something known as chain of ownership).

Of course, you could completely self-destruct any security benefits by creating a SP such as this one:

-- NEVER EVER DO THIS, PLEASE, I beg of you...
CREATE PROC prExecuteSql (@sql VARCHAR(MAX))
WITH EXECUTE AS dbo
AS
EXEC (@sql)
GO

As you can see, SPs aren't fool proof, but you can mitigate your risk by having an employee or a consultant who knows what they are doing in the database.

Yes, there are some applications will not require the extra security, or other factors may simply prevent you from using stored procedures, and so using ad-hoc SQL is a viable option in those cases. But I believe that security should be at the top of your important-things-for-your-application list, and alas, ad-hoc queries require you to unnecessarily expose your database objects, which will more than likely lead to problems down the road. You can argue any other point and there are no clear winners, but when it comes to security, ad-hoc loses. If you want to a more secure database, you need to be using SPs.

And so, the final score (well, for now anyways): SP 1, Ad-hoc 0.

Peter DeBetta's SQL Programming Blog : Ad-hoc

Yet Another Stored Procedure vs. Ad-hoc Query Discussion?