Re: SQL Injection Prevention

From: Valery Pryamikov (Valery_at_nospam.harper.no)
Date: 09/28/04 

Date: Tue, 28 Sep 2004 17:12:27 +0200

Aaron,
here is the summary from my posts to that thread that are related to your
question:
1. when you call parameterized stored procedure, it (the procedure) may
internally use dynamic sql (ie concatenate parameters to SQL string), thus
introducing another SQL injection vulnerability.
2. but when you call parameterized DML statement, you are guaranteed against
SQL injection vulnerabilities (if we don't consider the most weird situation
of triggers that are using dynamic sql). Guarantee is because parameters are
not passed to SQL parser, but is used on later stage when command is already
prepared and execution plan is ready.

Anyway, dynamic SQL in stored procedure is much more likely to occur than
dynamic SQL in triggers (I'm not sure that last is possible at all).
Therefore my conclusion. You can check my other posts to that thread for
more details.

p.s. And, it doesn't matter how likely it actually is that dynamic SQL be
used in stored procedures - as long as this possibility exists, we have to
account for it.

-Valery.
http://www.harper.no/valery

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OW1owqWpEHA.2900@TK2MSFTNGP12.phx.gbl...
>> Therefore my statement stays that parameterized SQL select
>> (insert/update/delete) actually provides better protection against SQL
>> injection than parameterized call to stored procedure.
>
> I'm not sure I understand where you've proven that parameterized DML
> statements are better than parameterized stored procedure calls.
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>

Relevant Pages

  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... stored procedure, the user needs EXECUTE rights to the stored procedure. ... Query 1 requires the user to have SELECT rights on the Customers ... Also, FWIW, the dynamic SQL is only syntax checked at run time, not when the ...
    (microsoft.public.sqlserver.tools)
  • Re: Pass-thru SQL performance vs Stored Proc
    ... >> are going to have to create a lot of dynamic SQL based on user selects ... > stored procedure per permutation of parameters, ... > ms per execution, and you are executing it 100 times an hour, no problem, a ... Right now all our code is in stored procs. ...
    (microsoft.public.sqlserver.programming)
  • Re: stored procedure security
    ... > execute privelages on the stored procedure, ... As soon as you employ dynamic sql, the logon executing the stored procedure ... In order to execute this stored ...
    (microsoft.public.sqlserver.security)
  • Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string
    ... Once you go to dynamic SQL, you are outside the ownership / security context ... I wrote a script that GRANTS EXECUTE to ... every stored procedure in the database to and DENY ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Injection Prevention
    ... here is the summary from my posts to that thread that are related to your ... introducing another SQL injection vulnerability. ... of triggers that are using dynamic sql). ... dynamic SQL in stored procedure is much more likely to occur than ...
    (microsoft.public.dotnet.security)