|
Moving forward, we will try to understand the SQL
injection attack with an example code.
Consider the below code,
private void GetEmp()
{
SqlConnection con = new
SqlConnection(ConfigurationManager.
ConnectionStrings["ConnectionString"].ConnectionString);
SqlDataReader dr = null;
try
{
con.Open();
SqlCommand com = new SqlCommand("Select * from
Employee where EmpID =" + txtID.Text, con);
dr = com.ExecuteReader();
while (dr.Read())
{
txtAge.Text = dr["Age"].ToString();
txtName.Text =
dr["EmpName"].ToString();
}
}
catch
{
}
finally
{
dr.Close();
con.Close();
}
}
In the above code, the SQL query is written inline and
the parameter value is concatenated directly reading from TextBox.
See below,
"Select * from Employee where EmpID =" + txtID.Text
Now, consider you are giving the below string as input
in the TextBox(txtID),
0;delete from employee; --
When executed, the above code will successfully delete
the Employee table data as it is prone to SQL injection vulnerability.
Now, let’s see the above scenario in detail.
If you take a close look at the above input, the
semicolon after the number 0 will complete the previous SQL statement. Then
comes the disastrous delete statement and -- will make any statement after its
occurrence void since it is a commenting operator.
On execution, final query that will be formed and
executed will be like below.
Select * from Employee where EmpID =0;delete from employee;
--
Now, let see an another example where a user can gain
access to restricted sections by bye-passing the authentication logic that has
SQL injection vulnerability. Consider the below sql statement which is used for
login screen.
"Select count(login) from tblLogin where login =
'+"txtLogin.Text"+' and password ='"+txtPass.Text+"' "
Now, a malicious user can give the below input to
txtLogin TextBox to bye-pass the where condition.
' OR 1=1 ;--
The above input will produce a SQL query something like
below and can produce count greater than 1 and thus achieving his aim.
Select count(login) from tblLogin where login = '' OR 1=1
;-- and password ='test'
We will now move forward and will understand how to
prevent SQL injection in ASP.Net.
1. Clean input Validation
2. Use Parameterized SQL query
3. Using Stored Procedure
4. Using ORM tools
Clean Input Validation
Always have a validation on user input in place. Choose
white listing as opposed to blacklisting because we certainly know what is
expected as input. But, doing this alone may not be sufficient at times. You
need employ other techniques as well.
Use Parameterized Query
This is one way of preventing SQL injection attack.
Never believe the user’s input and directly concatenate it with query like
above. Instead, it is always advisable to have an input validation and use
parameterized query. So the above code can be re-written as,
private void GetEmp(int empid)
{
SqlConnection con = new
SqlConnection(ConfigurationManager.
ConnectionStrings["ConnectionString"].ConnectionString);
SqlDataReader dr = null;
try
{
con.Open();
SqlCommand com = new SqlCommand("Select * from
Employee where EmpID =@EmpID", con);
com.Parameters.Add(new SqlParameter("@EmpID",
empid));
dr = com.ExecuteReader();
while (dr.Read())
{
txtAge.Text = dr["Age"].ToString();
txtName.Text =
dr["EmpName"].ToString();
}
}
catch
{
}
finally
{
dr.Close();
con.Close();
}
}
|