In the last articles on ADO.NET, we have discussed about
SQLCommand object. You can read those articles here. In this article, we will go over SQL injection attack in ADO.NET.
What is Meant by SQL Injection Attack?
Let’s understand this with an example. We will be using
tblProductInventory table for this.
First of all, write the SQL script to create the table.
Create table tblProductInventory
Id int primary key,
Then, write insert scripts to populate the table with sample data.
Insert into tblProductInventory values(101,‘iPhone’,101)
Insert into tblProductInventory values(102,‘Apple Laptops’,100)
Insert into tblProductInventory values(103,‘Books’,120)
Insert into tblProductInventory values(104,‘Acer Laptops’,119)
Insert into tblProductInventory values(105,‘iPads’,134)
Now drag and drop a
Button and a
GridView control on to the webform. Change the ID of the
ProductsGridView. Change the ID of the
GetProductsButton and the Text to Get Products. At this point, the HTML of the webform should be as shown below:
<asp:TextBox ID="ProductNameTextBox" runat="server"></asp:TextBox>
<asp:Button ID="GetProductsButton" runat="server" Text="Get Products" />
<br /><br />
<asp:GridView ID="ProductsGridView" runat="server">
Now double click on the
Button control to generate the Click event handler in the code behind file and then copy and paste the following code. In this example, we are building the query dynamically by concatenating the
strings that the user has typed into the textbox. This is extremely dangerous as it is vulnerable to SQL injection attacks.
protected void GetProductsButton_Click(object sender, EventArgs e)
using (SqlConnection connection = new SqlConnection("DatabaseConnectionString"))
SqlCommand cmd = new SqlCommand("Select * from tblProductInventory
where ProductName like ‘" + ProductNameTextBox.Text + "%’", connection);
ProductsGridView.DataSource = cmd.ExecuteReader();
Now run the project. Enter letter i into the textbox and click Get Products button. The iPhone and iPad products will be listed in the
GridView as expected. But user can type some dangerous SQL queries into the textbox as well which in turn will be executed by the application on the database. For example, just imagine what could happen if the user types the following into the
TextBox, and clicks Get Products button.
i’; DELETE FROM tblProductInventory –
Now execute the following select query on the database.
SELECT * FROM tblProductInventory
The entire data from
tblProductInventory table is deleted. This is called SQL injection attack.
Can SQL Injection Attack be Avoided?
Absolutely. We can easily avoid SQL injection attack by using parameterised queries or stored procedures, which we will discuss in the next article.