How to Prevent SQL Injection Attacks in ADO.NET

The following lines of code are causing a SQL Injection Attack:

string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
string Command=“Select * from tblProductInventory where ProductName like ‘”+TextBox1.Text     + “%’”;
    SqlCommand cmd = new SqlCommand(Command, con);
    con.Open();
    GridView1.DataSource = cmd.ExecuteReader();
    GridView1.DataBind();
}

We can avoid SQL injection attacks in two ways:

  1. Using parameterized queries
  2. Using stored procedures

Through parameterized queries, the code above can be written in this format to prevent SQL injection attack:

string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
// Parameterized query. @ProductName is the parameter
  string Command = “Select * from tblProductInventory _<br />  where ProductName like @ProductName”;SqlCommand cmd = new SqlCommand(Command, con);
// Provide the value for the parameter
    cmd.Parameters.AddWithValue(“@ProductName”, TextBox1.Text + “%”);
    con.Open();
    GridView1.DataSource = cmd.ExecuteReader();
    GridView1.DataBind();
}

SQL injection can also be prevented using stored procedures like so:

string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
// The command that we want to execute is a stored procedure,
// so specify the name of the procedure as cmdText
    SqlCommand cmd = new SqlCommand(“spGetProductsByName”, con);
// Specify that the T-SQL command is a stored procedure
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
// Associate the parameter and it’s value with the command object
    cmd.Parameters.AddWithValue(“@ProductName”, TextBox1.Text + “%”);
    con.Open();
    GridView1.DataSource = cmd.ExecuteReader();
    GridView1.DataBind();
}

Read the rest of the explanation in this blog post: http://www.codeproject.com/Articles/732429/ADO-NET-How-to-Prevent-SQL-Injection-Attack-2

Leave a Reply

Your email address will not be published. Required fields are marked *