Stored Procedures and SQL Injection

Every once in a while in my career, I hear some speak some variation of the following statement:

We should use stored procedures because that will prevent SQL injection attacks.

And it causes me pain every time I hear it.  Why?  Because it's incorrect, and worse because it's a dangerous mentality to espouse.  There is no magic bullet.  There is no one thing to do or one policy to implement which would keep one's code and servers safe from nefarious forces.  And statements like the one above make it very hard to explain to people that:

Security is a process, not a product.

Recently I was asked to come up with an example of how a stored procedure could be open to a SQL injection attack.  ("Asked" is a generous word.  The body language implied more of a dare, with a hint of a scoff.)  Sure, no problem.  Here it is:

CREATE PROCEDURE [dbo].[ThisIsATest]
@clause nvarchar(20)
AS
BEGIN

DECLARE @SQLQuery AS NVARCHAR(500)
SET @SQLQuery = 'SELECT * FROM [User] WHERE [user_name] = ''' + @clause + ''''
EXECUTE sp_executesql @SQLQuery

END

And you call it with this:

EXEC  [dbo].[ThisIsATest]
      @clause = N'test'' OR 1=1--'

It's a contrived example, but understand that I didn't write this example from scratch. I pulled it from production code (someone else's) and simply changed the names, removed pieces unnecessary to the example, and added the injection in the calling code.

Now, this isn't rocket science.  The input just needs to be escaped.  The point that I was making in the conversation which prompted this was to simply call attention to the fallacy of the aforementioned statement.  Assuring upper management that their software is safe because of the use of stored procedures is misleading and wrong.  It will lead to directives from on high which demand the continued use of stored procedures, naturally for the purpose of protecting code.  These directives will continue after the developer who made the aforementioned statement has left.  And there remains a likelihood that the code is open to SQL injection, despite assurance from senior leadership that it is not.

So what does prevent the SQL injection?  In the above code, properly escaping the input.  (Though I've seen a lot of production code in a lot of companies where that wasn't the case.)  Ah, so then escaping the input is the magic bullet?  No.  I'm sure there are other examples of nefarious attacks that can get around other magic bullets.  I can't think of one off the top of my head right now, but that doesn't mean it doesn't exist.

Never assume absolute security.  Test it.  All the time.  Consider the notion that the most secure installations (both virtual and physical) in the world are secure not only because they hire experts to set up their security, but also because they hire experts to break their security.  If your system isn't actively fending off attacks, how can you be sure that it's capable of doing so?