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.

One thought on “Stored Procedures Do Not Prevent SQL Injection”

  1. dynamic sql is just as bad as pass thru sql. It shouldn’t be used in structured shops.

Comments are closed.