Re: SQL Injection Prevention

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 08:19:22 -0500

Here's my $.02:

> 1) Does this mean the other stored procedures using parameterized queries
> are all safe?

Stored procedures without dynamic SQL are safe as long as the application
passes arguments as parameters.

> 2) What is the most effective way to stop sql injection vulnerabilities
> from the remaining stored procedures that have dynamic sql in it?

Execute dynamic SQL inside stored procedures only with sp_executesql
containing parameters for all user input; do not use EXECUTE. The remainder
of the SQL statement string needs to be constructed from a trusted source. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Shabam" <blislecp@hotmail.com> wrote in message 
news:nPedna0VXKZ-E8fcRVn-jA@adelphia.com...
Ok I've read all of the posts here, and I'm more confused than ever.  Sorry 
but I'm not a database expert, so it's hard for me to follow all the 
arguments.
However, I do understand the overall concept, which is why I bothered to 
check the application in the first place.
So the question comes down to this.  The programmers have said to me they 
use dynamic sql inside some of the stored procedures that are used for 
searching the database.
2 questions:
1) Does this mean the other stored procedures using parametized queries are 
all safe?
2) What is the most effective way to stop sql injection vulnerabilities from 
the remaining stored procedures that have dynamic sql in it?

Relevant Pages

  • Protect Stored Procedures using dynamic sql
    ... Ok I've read all of the posts here, and I'm more confused than ever. ... but I'm not a database expert, so it's hard for me to follow all the ... Does this mean the other stored procedures using parametized queries are ... the remaining stored procedures that have dynamic sql in it? ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... Ok I've read all of the posts here, and I'm more confused than ever. ... Sorry but I'm not a database expert, so it's hard for me to follow all the arguments. ... The programmers have said to me they use dynamic sql inside some of the stored procedures that are used for searching the database. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... Ok I've read all of the posts here, and I'm more confused than ever. ... Sorry but I'm not a database expert, so it's hard for me to follow all the arguments. ... The programmers have said to me they use dynamic sql inside some of the stored procedures that are used for searching the database. ...
    (microsoft.public.dotnet.security)
  • 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: 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)