Introduction
In the last articles on ADO.NET, we have discussed about SQL injection attack. You can read those articles here. In this article, we will go over prevention of SQL injection attack in ADO.NET.
We can avoid SQL injection attacks in two ways:
- Using parameterized queries
- Using stored procedures
Let’s examine one by one. The tblProductInventory
table which we have used in the previous article is as follows:
As we know, the following lines of code were causing 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();
}
Using Parameterized Queries
The above code can easily be rewritten using parameterized queries to prevent SQL injection attack.
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
string Command = "Select * from tblProductInventory _<br /> where ProductName like @ProductName";SqlCommand cmd = new SqlCommand(Command, con);
cmd.Parameters.AddWithValue("@ProductName", TextBox1.Text + "%");
con.Open();
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
}
Notice that the query now uses parameter @ProductName
. The value for this parameter is then provided using AddWithValue()
method. The parameter is associated with the command object using Parameters
collection property of the command object.
Now if we type the following input into the TextBox
, the entire content of the TextBox
is treated as a value for the parameter @ProductName
, not as a separate SQL statement. Thereby, it removes SQL injection attack.
i’; Delete from tblProductInventory –
Using Stored Procedures
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 following command in SQL Server Management Studio (SSMS).
EXECUTE spGetProductsByName ‘ip’
As a result of the above query, all the products whose name starts with ‘ip
’ will be returned.
Now, let’s rewrite the code to use stored procedure spGetProductsByName
.
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("spGetProductsByName", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProductName", TextBox1.Text + "%");
con.Open();
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
}
The above code as well will treat the entire content of the TextBox
as a value for @ProductName
and hence avoid SQL injection attack.
So the main point here to ponder is, always use parameterized queries or stored procedures to avoid SQL injection attacks.
Reference: Arun Ramachandran (http://BestTEchnologyBlog.Com)