[dba-SQLServer] SQL Injection and Sprocs

On 29 Aug 2006 at 13:38, artful at rogers.com wrote:

> Rightly or wrongly, I have been under the impression that the saftest method
> of avoiding SQL injection attacks is by using Sprocs exclusively. My theory
> is that Sproc parameters are typed, and also handled differently, than
> variables that might be plugged into a dynamic SQL statement. 
> 
> Have you ever seen an example that proves my theory incorrect?

No :-)

> I.e. imagine
> some form that obtains three variables from a user, then fires a sproc and
> passes it these three variables. To spice up the argument, imagine that one
> textbox is text, one numeric and one date.
> 
> I have done various experiments on this scenario, and I cannot come up with
> a single case that fools the underlying sproc. Can you?

The risk in SQL Injection is feeding a syntactically correct SQL statement 
at the end of you SQL string by appending malformed user-input strings when 
you build and execute a dynamic string in code.

By forcing these user-input parameters into sproc parameters, they become 
"just data" inside the sproc and there is no way for them to  actually be 
executed.



-- 
Stuart