Using parameterized SQL queries to help prevent SQL injection attack.

Using parameterized SQL queries to help prevent SQL injection attack.

If you are not new to web development in particular, it is quite likely that you already know what a SQL injection is and how it poses threat to your system security. This is more probable when you are adding strings to SQL commands.

 

 

 

string sql = "SELECT * FROM tblUser WHERE Name = '" + txtName.Text +
   "' AND Password = '" + txtpassword.Text + "'";

 

Well, the above statement looks fine but what a malicious user would now do is add condition such as

 

' OR 3=3 --

 

So that the actual SQL statement becomes

 

 

SELECT * FROM tblUser  WHERE Name = '' OR 3=3 --' AND Password = ''

 

 

The double dashes comment out rest of the statement and the condition 3=3 is added. Since 3 is always equal to 3 the query selects every row in the table thus giving access to information which wouldn't be available otherwise.

 

However, parameterizing the SQL statement would not only remove this vulnerability but also enhance performance multifold. Parameterized SQL statements are in some ways similar to stored procedures, so if you have worked with the latter, the concept of parameterized query would be easier to understand. Further, since the parts of the SQL statement are added as parameters, the same code can be reused.

 

Now let us create a parameterized SQL statement in ASP.NET. As usual, we would be required to first create our connection and command objects. We would then add parameters to it before it is executed.

 

 

SqlConnection objCon = new SqlConnection(ConnectionString);
objCon.Open();
SqlCommand objCommand = new SqlCommand(
   "SELECT * FROM User WHERE Name = @Name AND Password = @Password",
   objConnection);
objCommand.Parameters.Add("@Name",  txtName.Text);
objCommand.Parameters.Add("@Password", txtpassword.Text);
SqlDataReader objReader = objCommand.ExecuteReader();
if (objReader.Read())
{
  ----------
 --------
---------
}

 

Refrence