Avoiding SQL Injection with Dynamic SQL

I am exclusively using Stored Procedures to access the database, i.e. there are no Ad-Hoc SQL statements anywhere in the C# code. However, one thing I need to be able to do is to allow filtering for data grids on my ASP.NET page. I want to do the filtering in the Stored Procedure using Dynamic SQL to set the WHERE clause. However, one fear of mine is SQL injection from the client. How can I avoid arbitrary SQL injection, yet still allow for a dynamic WHERE clause to be passed into the stored procedure?

Jason PachecoFrom herehttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec03.mspx

Preventing SQL Injection

So long as injected SQL code is syntactically correct, it will be impossible to programmatically detect tampering on the server side. You must therefore validate all user input on the client side, and force server-side type checking by calling parameterized stored procedures. Always validate user input by testing type, length, format, and range. Untested input can cause program errors, and may be used by hackers as a point of entry into your system. When implementing precautions against malicious input, consider the architecture and deployment scenarios of your application. Remember that programs designed to run in a secure environment can be copied to an insecure environment.

Validate All Input

The following suggestions should be considered best practices:
- Make no assumptions about the size, type, or content of the data received by your application. For example, evaluate:
- How will your application behave if an errant, or malicious, user enters a 10-megabyte MPEG file where your application expects a postal code?
- How will your application behave if a DROP TABLE statement is embedded in a text field?
- Test the size and data type of input, and enforce appropriate limits. This can help prevent deliberate buffer overruns.
- Test the content of string variables and accept only expected values. Reject entries containing binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.
- When working with XML documents, validate all data against its schema as it is entered.
- Never build Transact-SQL statements directly from user input.
- Use stored procedures to validate user input.
- In multi-tiered environments, all data should be validated before admission to the trusted zone. Data that does not pass the validation process should be rejected, and an error returned to the previous tier.
- Implement multiple layers of validation. Precautions you take against casually malicious users may be ineffective against expert hackers. The best practice is to validate input in the user interface, and then at all subsequent points at which it crosses a trust boundary.

For example, data validation in a client-side application may prevent simple script injection; however, if the next tier assumes that its input has already been validated, any hacker capable of bypassing your client can have unrestricted access to your system.
- Never concatenate user input that is not validated. String concatenation is the primary point of entry for script injection.
- Do not accept the following strings in fields from which file names may be constructed: AUX, CLOCK$, COM1 through COM8, CON, CONFIG$, LPT1 through LPT8, NUL, and PRN.

When possible, reject input that contains the following potentially dangerous characters.
Input characterMeaning in Transact-SQL

; Query delimiter

' Character data string delimiter

-- Comment delimiter

/* ... */ Comment delimiters. Text between /* and */ is not evaluated by the server.

Xp_ Begins the name of catalog extended stored procedures such as xp_cmdshell.

Use Type-Safe SQL Parameters

The Parameters collection in SQL Server provides type checking and length validation. If you use the Parameters collection, input is treated as a literal value rather than executable code. An additional benefit of using the Parameters collection is that you can enforce type and length checks. Values outside of the range will trigger an exception. The following code fragment illustrates using the Parameters collection:

SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add(
"@.au_id", SqlDbType.VarChar, 11);
parm.Value = Login.Text;

In this example, the @.au_id parameter is treated as a literal value rather than executable code. This value is checked for type and length. If the value of @.au_id does not conform to the specified type and length constraints, an exception will be thrown.

Use Parameterized Input with Stored Procedures

Stored procedures may be susceptible to SQL injection if they use unfiltered input. For example, the following code is vulnerable:

SqlDataAdapter myCommand =
new SqlDataAdapter("LoginStoredProcedure '" +
Login.Text + "'", conn);

If you use stored procedures, you should use parameters as their input.

Use the Parameters Collection with Dynamic SQL

If you cannot use stored procedures, you can still use parameters, as shown below.

SqlDataAdapter myCommand = new SqlDataAdapter(
"SELECT au_lname, au_fname FROM Authors WHERE au_id = @.au_id", conn);
SQLParameter parm = myCommand.SelectCommand.Parameters.Add("@.au_id",
SqlDbType.VarChar, 11);
Parm.Value = Login.Text;

Filtering Input

Filtering input may also be helpful in protecting against SQL injection by removing escape characters, but due to the large number of characters that may pose problems it is not a reliable defense. The following snippet searches for the character string delimiter.

private string SafeSqlLiteral(string inputSQL)
{
return inputSQL.Replace("'", "''");
}

LIKE Clauses

Note that if you are using a LIKE clause, wildcard characters still need to be escaped:

s = s.Replace("[", "[[]");
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");|||

Many thanks to you DarrellNorton,

Very helpfull information.
this was my posthttp://forums.asp.net/926297/ShowPost.aspx

BR