Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

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

, 10 Feb 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

tblProductInventory

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Arun Ramachandran India
Software Developer
India India
Arun Ramachandran is a Software Engineer having hands on experience in different Microsoft Technologies who is presently working in Experion Technologies, India. He has written over 95 articles on the subject on his blog at http://BestTEchnologyBlog.com. Along with 3 years of hands on experience he holds a Master of Computer Applications degree from Cochin University of Science & Technology (CUSAT).

Comments and Discussions

 
Questiongo Pinmembermhdradhyps12-Feb-14 8:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411028.1 | Last Updated 10 Feb 2014
Article Copyright 2014 by Arun Ramachandran India
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid