Stored Procedures Do Not Prevent SQL Injection

There’s another sproc-or-not battle royale going on over at
CodeBetter.com.  Not going there, but one statement Eric
Wise made isn’t completely correct:

First, you can be sure that no sql injection attacks will ever
occur.

That’s part of a longer paragraph, but if I read the paragraph correctly, not
taken out of context.

For the most part, Eric is correct.  But, if SQL statements are
generated dynamically in a stored procedure, and in-line parameter concatenation
is part of the statement, then you’re just as susceptible to SQL
Injection.  For instance, code such as

IF @orderId IS NOT NULL
 select @sql
= @sql + ‘ AND order_id=’ + @orderId

would be susceptible to SQL Injection.  I have
seen code such as this used (including some by as reportedly high-level DBA),
and sadly written some myself.

There’s an excellent paper on the right and wrong
of dynamic SQL at http://www.sommarskog.se/dyn-search.html;
I have a summary blog post at http://aspadvice.com/blogs/rjdudley/archive/2005/06/30/2626.aspx.