Sql injection prevention - Part 6

Suggested Videos
Part 3 - ConnectionStrings in web.config configuration file
Part 4 - SqlCommand in ado.net
Part 5 - Sql injection tutorial

In Part 5, we have learn about, what can cause sql injection. In this session we will learn about 
1. Preventing sql injection using parametrized queries and stored procedures.
2. How to execute stored procedures and parameterized queries using ADO.NET command object

The table, this demo is based on, is shown below.




The following ADO.NET code is from Part 5. This is the code, that let's sql injection happen. 
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();
}

The above code can be easily re-written using parameterized queries to prevent sql injection attack. The re-written code is shown below. Notice, that the query now uses parameter - @ProductName. The value for this parameter is then provided using the AddWithValue() method. The parameter is associated with the command object using Prameters collection property of the command object.
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
    // Parameterized query. @ProductName is the parameter
    string Command = "Select * from tblProductInventory 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. So, first let's write a stored procedure, that returns the list of products. This stored procedure takes an input parameter @ProductName.
Create Procedure spGetProductsByName
@ProductName nvarchar(50)
as
Begin
Select * from tblProductInventory 
where ProductName like @ProductName + '%'
End

To test this procedure execute the follwing command in sql server management studio.
Execute spGetProductsByName 'ip'

Now, let's re-write the code, to use stored procedure spGetProductsByName.
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();
}

If you type the following input into the TextBox, the entire content of the TextBox is now treated as a value for the parameter - @ProductName not as a seperate sql statement.
i'; Delete from tblProductInventory --

So the conclusion is that, always used parameterized queries or stored procedures, to avoid sql injection attacks.

8 comments:

  1. When you fill the parameter at AddWithValue do you end with "%" but the stored procedure also add a % at the end of the where clause.
    Is not enought to have the one of the stored procedure?

    ReplyDelete
  2. When converting the first query into stored procedure and parameterised query, where does the apostrophe (') surrounded the textbox value go?
    like '" + TextBox1.Text + "%'";

    Secondly, is this necessary to duplicate the % twice? There is one in the stored procedure, but why do we still have add another one on AddWithValue() method?

    Could anyone explain please?

    ReplyDelete
    Replies
    1. 1) select * from tbl where product like 'i%';
      2)m its not necessary to add in both the places

      Delete
  3. How to create advance search using multiple textbox and dropdownlist?
    please help me out

    ReplyDelete
  4. Hello Venkat!

    I tried to use parameterized query but the parameters are not getting substituted with values. Should i include any special namespace for that?
    i am providing the code below:
    string pqCommandString = "Select * from UsersTable where Username=@paramUsername and Password=@paramPassword";
    SqlCommand command = new SqlCommand(pqCommandString, connection);


    command.Parameters.Add("@paramUsername", System.Data.SqlDbType.NChar, 10);
    command.Parameters["@paramUsername"].Value = UsernameTxtBox.Text;
    command.Parameters.Add("@paramPassword", System.Data.SqlDbType.NChar, 10);
    command.Parameters["@paramPassword"].Value = PasswordTxtBox.Text;

    connection.Open();
    SqlDataReader sqlDataReader = command.ExecuteReader();
    if (sqlDataReader.HasRows)
    {
    ResultLabel.Text = "success";
    }

    ReplyDelete
  5. why we are adding % in ( TextBox1.Text + "%" ) ? please explain .

    ReplyDelete
    Replies
    1. for any other caractere
      we look for the name started by textbox1.text and finish with any other caractere

      Delete

It would be great if you can help share these free resources