Preventing SQL injection in ASP.NET application...

You can protect your application from SQL Injection by following way

       1. Constrain input

           2. Use parameter with stored procedure 
  3. Use parameter with dynamic  SQL

     

Constrain Input: You have to validate all input to your ASP.NET application for type, range, length etc.

 i.e. RegularExpressionValidator, RangeValidator or Regex class.

Use parameter with stored procedure: 

The following code shows how to use SqlParameterCollection when calling a stored procedure.

using System.Data;

using System.Data.SqlClient;

using (SqlConnection connection = new SqlConnection(connectionString))

{

  DataSet userDataset = new DataSet();

  SqlDataAdapter myCommand = new SqlDataAdapter("LoginStoredProcedure", connection);

  myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

  myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);

  myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;

  myCommand.Fill(userDataset);

}

 

Use parameter with dynamic sql:

The following code shows how to use SqlParametersCollection with dynamic SQL.

using System.Data;

using System.Data.SqlClient;

using (SqlConnection connection = new SqlConnection(connectionString))

{

  DataSet userDataset = new DataSet();

  SqlDataAdapter myDataAdapter = new SqlDataAdapter(

         "SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id", 

         connection);                

  myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);

  myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;

  myDataAdapter.Fill(userDataset);

}