Are stored Procedures any more secure than parameterized queries?

I am not going to include this post in my security series, because I am not really sure of the answer. This is more of a thought exercise and a request for input from the community.

So there has been a lot of discussion about best practices for application/database security. One of the "Best Practices" that is mention fairly often is:

Use Stored Procedures for Update/Insert queries.

My question is, is this really necessary to have a secure application.

My first reason for asking this questions is because, and correct me if I am wrong, but most SQL injection attacks are against SELECT statements. In my experience, SELECT statements are the easiest to exploit since they usually have very simple WHERE clauses and because its easy to inject sub-queries.

Now UPDATE queries also use WHERE clauses so they would probably be similarly easy to exploit, but why are they any more dangerous than a SELECT statement? Why is it OK for a select statement to be inline but not an UPDATE? And is a properly parameterize UPDATE or SELECT query any less vulnerable than a Stored Procedure?

I did a little research into this, and have found mixed messages. I found one interesting link on the OWASP site with the following statement about using Stored Procedures to protect against SQL injection:

"I have seen this answer too many times without qualifications. Merely the use of stored procedures does not assist in the mitigation of SQL injection. If not handled properly, dynamic SQL within stored procedures can be just as vulnerable to SQL injection as dynamic SQL within a web page.

When using dynamic SQL within a stored procedure, the application must properly sanitize the user input to eliminate the risk of code injection. If not sanitized, the user could enter malicious SQL that will be executed within the stored procedure."

It sounds to me like you need to sanitize input into a stored procedure just as you would for a standard SQL statement. So why bother with the Stored Procure?

Sure, in some cases a Stored Procedure may be less vulnerable to Injection, but is it really necessary to go to all of the trouble of creating dozens or more Stored Procedures to get, what may be, the same level of security as using parameterized queries?

Additionally, do you really want to so tightly couple your application to a DBMS? Stored Procedures are not generally portable. An Oracle SP will not work in MS SQL Server nor MySQL (to my knowledge).

Even worse still is that a Store Procedure policy like those we are hearing about would prevent the use of an ORM framework like Transfer or Reactor, and will also prevent the use of the Hibernate integration in CF9. OO programmers around the world frown at this idea.

To top it off, creating stored procedures with dynamic WHERE clauses, dynamic table names, or dynamic column lists is a very verbose and daunting task compared to doing the same thing in an inline query in ColdFusion, not to mention it only further couples your application to the DBMS.

So I am asking the community, and any DBAs that may be reading this. Is it really worth the security benefit (perceived security benefit?) to force developers to use Stored Procedures.

Again, I am not sure of the answer to this. And no doubt, I will get passionate responses on both sides. But my initial reaction is to balk at the idea of only using stored procedures and I feel that parameterized queries, in most cases, are adequately secure.

Scott Bennett's Gravatar @Jason,

I have always taken the approach of simply sanitizing all user input for Cross-Site-Scripting and SQL-injection attacks. Catching these types of things in the application before it ever makes it's way to a query or stored procedure is the best way to go, IMHO.
# Posted By Scott Bennett | 6/26/08 11:15 AM
Brad Wood's Gravatar Good questions.

My short answer is: "Absolutely Not".
Silver bullets are in the same category as the tooth fairy for me.

That being said, there are definitely reasons to use stored procedures. Organizing all your SQL code in one easily accessible and re-usable place can have its advantages.
If you work on a team where ALL SQL code is written by a member of the SQL team and you are to keep your grimy hands off the database, then stored procs would almost be a necessity.

That being said, all soap boxes have a slippery slope on both sides. I have also seen stored procedures become an excuse to place way too much business logic down into your database instead of up in the application layer where it belongs.

One more thing, sanitization is great and all, but what is better is ALWAYS using a parameterized input to your SQL. Not only will you reap the benefits of a cached execution plan, but the contents of the inputs will always stay safely inside the parameter and never leak into the SQL itself. Dynamic table names, etc are sometimes a necessary evil, but they are where the real danger comes in IMO.
# Posted By Brad Wood | 6/26/08 1:01 PM
Scott Bennett's Gravatar @Brad,

"sanitization is great and all, but what is better is ALWAYS using a parameterized input to your SQL"

My first comment was pretty brief, but that is exactly how I feel as well. In addition to attempting to sanitize user input, I always use CFQUERYPARAM when using data that originates from user input in my SQL. While I occasionally use dynamic table names, I would never have user input to direcly determine the name of the table.

Perhaps it would be clearer if I said that I believe the application should be responsible for ensuring the integrity of the data that is received from it's users input, and protect the database from any sort of malicious attack. That should not be the databases responsibility.

I wouldn't use security as a deciding factor to whether I used a stored procedure. I would only use a stored procedure if there is a definite performance gain. I would never use it for a simple insert,update, delete query, it would have to be something where a series of queries were needed and I didn't want a bunch of data the application didn't need going back and forth between the app server and the db server to do something the DB server could do more efficiently all by itself.
# Posted By Scott Bennett | 6/26/08 2:58 PM
zac spitzer's Gravatar They are effectively the same, the real DANGER is that often the developers writing the SP's are
less proficient at T-SQL or PL/SQL than CFML and they end up writing crappy SP's, trust me, I've seen a lot :)

This is an age old debate :) which dates back to much earlier times when there was much less horsepower available

SP's are brilliant for doing high performance data heavy processing. I agree with Scott, simple CRUD or selects is overkill
when people think it improves performance, when simply wrapping up the 50 inserts in a transaction will achieve pretty
much the same gain

Bound SQL is much better for on the fly security auditing (and performance tuning) as with CF debug enabled you can
see if the queries are bound and their structure. It's also more flexible and allows for quicker refactoring.
# Posted By zac spitzer | 6/26/08 11:04 PM
Matt's Gravatar a couple years old but still a useful discussion. One thing I like about stored procedures is you can deny direct access to your tables and all db access is via stored procedures and that's all users have permissions to run.

aside from utility procedures, the layer exposed by the stored procedures can be simple CRUD procs and business logic stays where it belongs, in the middle tier (whether that be physical or logical).

The downside as mentioned, is ORMs lend themselves more to direct table access and many ORMs do very well in terms of performance etc and stored procedures are less flexible than ORMs.
# Posted By Matt | 4/14/10 7:36 AM
Matt's Gravatar Oh and the original question seems to come from a confused perspective as you can wrap up parameters in calling stored procedures in the same way as textual commands
# Posted By Matt | 4/14/10 7:39 AM
sf's Gravatar Aww, this was a really quality post. In theory I’d like to write like this too – taking time and real effort to make a good article… but what can I say… I procrastinate alot and never seem to get something done.
# Posted By sf | 5/17/10 1:18 AM
mbt's Gravatar This article has great reference value, thank you very much for sharing, I would like to reproduced your article, so that more people would see it.
# Posted By mbt | 5/18/10 1:25 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1. Contact Blog Owner