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.