THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

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

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 FirstNameLastNameZIPCreditCardNumberCreditCardTypeCreditCardExpoiration
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.

Published Thursday, April 03, 2008 12:44 AM by Peter W. DeBetta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul Nielsen said:

It's not a question of performance, security, or SQL injection.

It boils down to the architecture. Any  tightly coupled component will be difficult to extend or modifiy. Extensibility requires encapsulation and abstraction. SOA easily understands this for software components, but application developers somehow don't see how this should apply to the database.

I used to belive that cursors were the worse thing one could do to a database, I was wrong. Cursors can be refactored. But ad-hoc SQL creates a brittle database that's nearly impossible to modify. The worst thing one can do to a database is to bypass the abstraction layer with ad-hoc SQL.

I have seen many otherwise competent IT organizations become comepltely **crippled** by the lack of database abstraction. It is simply too expensive to even identiy the SQL that directly connects to a table from hundreds of reports, SQL in millions of lines of application code, and dozens of ETL procedures.

Will Sullivan argues that a logical database abstration layer in application layer is sufficient to issolate the database. I don't believe it. An abstraction layer must be complete or the database is still coupled. Unless reports and ETL processes also go through the abstraction layer, it's still too expensive to modify the database.

Extensibility: Sprocs: 10   Ad-Hoc SQL: -10  

April 3, 2008 1:42 AM
 

Randy Walker said:

I disagree with Peter on one point, and I agree with Paul, however I want to put it in more layman's terms.

When dealing with the maintenance issue, having a central location for dealing with data and only data, increases the ease of maintenance.  Let's take for instance, calculated data (cola + colb / colc).  In an ad hoc situation, your calculation is created and ran by the user / application.  Each user / application could calculate the same metric differently (both (cola + colb) / colc  or the previous example).  Whereas, sprocs (or even views) help eliminate that problem.  Additionally, should the calculation be wrong, you no longer have to update the application, you only have to update the sproc.  Because remember, when you update the application, you then have to deal with redeployment of that app.

Two great examples of what this affects is SOA and reporting.

April 3, 2008 2:48 AM
 

Peter W. DeBetta said:

First off, I want to be sure that we are all on the same page for terminology: Ad-hoc means parameterized queries and not dynamic SQL. Nobody from either camp thinks dynamic SQL is good.

Second, let me be clear that I am not defending ad-hoc SQL.

Now, even though I agree with Paul on the point of architecture, I believe security trumps this. I have seen very well architected solutions that used ad-hoc SQL (not dynamic SQL) with an ORM such as nHibernate (even though I voted against it).

Randy, ad-hoc is not dynamic SQL; and the ad-hoc SQL and would never come from the user in a client application. Also, in a well architected solution, all of the SQL code is maintained in a single place, be it in the middle tier or in stored procs. Views would always be in the database, and used by either SPs or ad-hoc SQL.

My point is that people can and will debate these points you have made, because they can be construed as subjective points. But nobody can refute the security argument. It is based on cold, hard facts and not development techniques, design choices, and so on.

April 3, 2008 3:27 AM
 

ALZDBA said:

- Indeed no argue regarding the security advantage, even more with sql2005 since you can create a proc using WITH EXECUTE AS ...

- Bugfixing is a second one

- Tunability without having to reinstall the full application(layer) is also non-neglectable. Especialy because your dba can do that based on the actual performance monitoring figures he collected.

- did someone consider all the (plan)hint-stuff that a dba can apply in some worst case scenarios to get the data-system to work whilst the app-dev-team rewrites/redesigns the application(s).

April 3, 2008 8:40 AM
 

Peter Schott said:

I'd agree with most of the points above.  However, where Stored Procs have a huge advantage for me is in the ability to make changes relatively quickly.  If you have your SQL code embedded in the app and find something that needs to be changed/updated, you have to track it down, make the changes, compile, deploy to some environment, etc, etc.  If the SQL Code is in a stored procedure, you make the change to the stored proc, test, and propagate up the chain.  I've rarely seen a shop that can easily deploy SQL Changes within the code quickly because there are usually so many other dependencies that you have to worry about.

One other thing worth noting is that stored procs can easily mask the underlying DB Schema from the app.  If the DBA's realize that splitting one table into two or three tables would help performance, they could make this change "behind the scenes" and only change the stored procs.  The code and programs would never know there was a difference.  Yes, this isn't something to do without testing, but it doesn't require re-doing object models and all sorts of other things that seem to almost always accompany SQL in the Code base.

April 3, 2008 2:53 PM
 

sqlcook said:

Agree with ALZDBA. I'm not pro either, as both have their purpose. However, that being said, most ad-hoc approaches deal with not needing heavy database use, such as small in house financial app. Along with all the disadvantages that this brings, most people that are defending this approach are either lazy programmers who think they're dbas or other way around. There is a very simple fundamental fact that most seem to disregard, T-SQL is not a programming language, its a relational language driven by an engine. This engine is very dynamic, and complex, that requires maintenance, optimization, and monitoring, in order to ensure scalability and performance. By taking the code out of the database, you are leaving DBA handicapped from controlling the data flow and ensuring uptime. And while this will work in a lot of environments this is not an enterprise approach, and designs such these require complete redesign.

April 4, 2008 6:04 PM
 

Andy Steinke said:

I prefer stored procedures as well for the ease of "where used" searches.  In an environment with perfect or ideal change management and control it isn't as much of an issue, but in an environment with 10+ disparate source safe databases, little control on coding, applications ranging from Foxpro to VB 6 to VB.net and about 70 developers any database change always creates some type of broken issue.

While I would love to move to the ivory towers of completely controlled code, I don't have the power to spend the resources or money to change that in my organization.  The best I can do is enforce the use of stored procedures and other things that abstract the database and reduce the risk of causing issues when database changes occur.

I also agree that by abstracting the database consistently it would give DBAs a huge advantage in being able to optimize the design without forcing developer allocation every time.

April 7, 2008 9:02 AM
 

Merrill Aldrich said:

My take on this is slightly different. As has been discussed at length, what one would like in a SQL Server database / front end application combination is to have the best possible set up for query optimization (scalability), security, full use of SQL's linguistic powers, and flexibility in making changes to either the database schema or application without one breaking the other. The design I find that supports those goals best is: stored procedures for all data *manipulation* but *views* for returning results. This combination gives all the advantages outlined above (abstraction / flexibility, security, performance / scalability) AND avoids the problem of closure with stored procs.

If you haven't seen the "closure problem," here it is:

select * from dbo.sproc1 inner join dbo.sproc2 on sproc1.key = sproc2.key

In a nutshell, stored procedures that simply return a result set and encapsulate a select statement cripple the otherwise highly composable language we have access to, and needlessly so.

So by all means make stored procs for all your insert, update, delete logic (which is where much of the security issue is, and where the logic tends to be complex enough to want an "abstraction layer") but please, please consider views for selects instead of stored procs.

April 8, 2008 5:12 PM
 

Armando Prato said:

I'm late to this blog but I had to comment because it's such an interesting debate

My dad was a building contractor who was an electrician by trade.  

I asked him once why he didn't do some of the non-electrical work, like plumbing, himself since he's handy.  His reply was something along the lines of "You don't hire electricians to do plumbing". His point being you let the expert handle his/her area of expertise. I think this translates to this very well to this debate.  

I use SP's because, frankly, I want to isolate the database's internals from anything calling it and I don't want the logic tier developers to know anything about the tables or their data. My data model changes a lot and I don't want to dig through Java code finding any references and possibly missing some.  Furthermore, I've had many situation where I had to provide a SP to a customer immediately to fix a bug. Compare that to having to generate a JAR file, stop services, drop in the JAR file, re-start services, and cross fingers.

While the referenced blogger mentions isolating logic to a module, you may be able to get away doing that with simple INSERTs, SELECTs, etc but more complex code that has to churn through multiple tables using multiple statements really should be in an SP where the entire SP can be cached as a single plan and re-used as opposed to creating multiple individual plans.

Finally, I equate this debate to Java methods; methods generally keep its variables private while the method itself is public.  You just pass in a String or int or whatever and you know you'll get back some data type.  You don't care what the method is doing, you just know you get something back in some form.  SP's, to me, are no different.

November 18, 2008 12:10 AM

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement