ADO.NET – What is Meant by SQL Injection Attack?

Introduction

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,
ProductName nvarchar(50),
QuantityAvailable int
)

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 TextBox, Button and a GridView control on to the webform. Change the ID of the TextBox to ProductNameTextBox and GridView to ProductsGridView. Change the ID of the Button to 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">
</asp:GridView>

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)
{
string ConnectionString= 
    ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;
    using (SqlConnection connection = new SqlConnection("DatabaseConnectionString"))
    {
        //Build the query dynamically, by concatenating the text, that the user has 
        //typed into the ProductNameTextBox. This is a bad way of constructing
        //queries. This line of code will open doors for SQL injection attack.
        SqlCommand cmd = new SqlCommand("Select * from tblProductInventory 
        where ProductName like ‘" + ProductNameTextBox.Text + "%’", connection);
        connection.Open();
        ProductsGridView.DataSource = cmd.ExecuteReader();
        ProductsGridView.DataBind();
    }
}

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.