Re: SQL Injection Prevention

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 09/28/04 

Date: Tue, 28 Sep 2004 12:14:34 -0400

> 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.

And if the stored procedure doesn't contain dynamic SQL? You seem to be
under the impression that all stored procedures contain dynamic SQL. I can
assure you that this is not true.

Stored procedures can be made just as safe as your method. (plus let's not
forget all the other benefits of stored procedures over parameterized DML
statements).

So I don't think it's fair to make a blanket statement. Everything requires
caution to some degree... I really don't think it's worthwhile to throw away
all the benefits of stored procedures to gain this slight edge in security,
which is eliminated if your stored procedure doesn't contain dynamic SQL
and/or you properly validate input...

A

Relevant Pages

  • Re: SQL Injection Prevention
    ... > internally use dynamic sql (ie concatenate parameters to SQL string), ... And if the stored procedure doesn't contain dynamic SQL? ... Stored procedures can be made just as safe as your method. ... So I don't think it's fair to make a blanket statement. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... > under the impression that all stored procedures contain dynamic SQL. ... more than 1.5 MLoC of Fortran code + more than 1.2 MLoC of C++ ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... > under the impression that all stored procedures contain dynamic SQL. ... more than 1.5 MLoC of Fortran code + more than 1.2 MLoC of C++ ...
    (microsoft.public.dotnet.security)
  • Re: Dynamic SQL
    ... Dynamic SQL versus stored procedures is really a minimal discussion. ... Obviously if you CAN use stored procedures, you are going to have a MUCH ... With only minimal modification you could make your middle tier use stored ... > While I am not trying to start another discussion about> business rules and where they ...
    (microsoft.public.sqlserver.programming)
  • Re: Exec statements in stored procedures
    ... > dynamic sql executes in the security context of the current user. ... Dynamic SQL accesses objects directly so permissions on those objects are ... > No. Stored procedures execute in the context of the owner, ...
    (microsoft.public.sqlserver.programming)