Re: SQL Injection Prevention

• Previous message: Raj: "RE: SignedXML.CheckSignature()/CreateSignature() Alternative?"
• In reply to: Shabam: "Re: SQL Injection Prevention"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Wed, 29 Sep 2004 22:37:33 -0500

>> 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.
>
> Can you explain this one in a more newbie fashion, or point me to an
> article
> that may help explain this in more detail? Thanks! :)

When you must build and execute SQL statements dynanically in a proc:

1) Always use sp_executesql.

2) Never build the SQL statement string by concatenating user-entered
parameter values.

3) Use parameters for user input values (instead of #2)

Below is an example taken from your initial post. Although there is no need
to use dynamic SQL in this situation, this illustrates the techniques
mentioned in this thread. In practice, dynamic SQL is employed to build a
statement that varies depending on user or application supplied values. To
prevent injection, ensure the strings you concatenate contain only SQL and
parameter tokens.

CREATE PROC GetUserData
    @Name varchar(50),
    @Password varchar(50)
AS

DECLARE
    @SqlStatement nvarchar(4000),
    @ParameterDef nvarchar(4000)

SET @SqlStatement =
    N'SELECT FirstName, LastName
    FROM Users
    WHERE
        Name = @Name AND
        Password = @Password'

SET @ParameterDef = N'@Name varchar(50), @Password varchar(50)'

EXEC sp_executesql
    @SqlStatement,
    @ParameterDef,
    @Name,
    @Password
GO

//app code snippet
SqlCommand objCommand = new SqlCommand(
   "EXEC GetUserData", objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add("@Name", NameTextBox.Text);
objCommand.Parameters.Add("@Password", PasswordTextBox.Text);
SqlDataReader objReader = objCommand.ExecuteReader();

Note that the user values are actually parameterized twice - once by the
SqlClient API and again by sp_executesql. Importantly, the intended The
parameritization ensures the intended SQL statement cannot be altered with
user-supplied values.

Check out http://www.sommarskog.se/dynamic_sql.html for a discussion on
dynamic SQL.

-- 
Hope this helps.
Dan Guzman
SQL Server MVP 

• Previous message: Raj: "RE: SignedXML.CheckSignature()/CreateSignature() Alternative?"
• In reply to: Shabam: "Re: SQL Injection Prevention"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]