Click here to Skip to main content
11,437,397 members (34,833 online)
Click here to Skip to main content

Preventing SQL Injection Attack ASP.NET - Part I

, 1 Sep 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
SQL Injection is a very common attack in web applications. This article explains how SQL Injection occurs and how to prevent it.

Introduction

Security is the most important attribute for any system. Providing secure experience is one of the key principles in the process of gaining customer confidence for a system. Nowadays, almost all the websites are asking to store user’s personal information in servers to understand the customer and serve better. It’s the responsibility of an organization to confirm that customer’s data is safe and accessed in a secured manner.

Security in web application is always a big headache for the developer but providing secure environments is one of the key principles in the process of gaining customer confidence for a system. In this era of web applications, almost all websites are dynamic, i.e., database driven and large data will be accepted from user.

SQL Injection flaws are introduced when software developers create dynamic database queries that include user supplied input. This article explains how SQL Injection is prevented in ASP.NET.

Background

What actually is SQL Injection attack?
SQL Injection is an attack used to inject unintended SQL commands (statements) in a database by accepting malicious, unsecured, un-validated user input. Injected SQL commands can alter SQL statement and compromise the security of a web application. If you want to know SQL Injection attack in detail, please visit the following link:

Methods of Exploiting SQL Injection

Methods of exploits are as follows:

  1. Input boxes
  2. Query Strings [GET]

How to Exploit?

In today's dynamic web applications world, it's necessary to get user input and process it so we have to write the various types of SQL queries to process the data according to user input. Consider the following query. Table - user_info, Columns - userID, name, email, password.

SELECT name, email FROM user_info WHERE userID = 1

We can divide this query into 2 parts:

PART-1: Query Part - SELECT userID,email FROM user_info

PART-2: Input Part - userID=1

A hacker is usually not interested in PART-1, he is just interested in how he can insert malicious query in your PART-2. Let's take an example of how SQL injection will be exploited.

Using the Code

  1. Suppose we have table user_info with some data. Following is the script:
    CREATE TABLE [dbo].[user_info](
        [userID] [int] IDENTITY(1,1) NOT NULL,
        [name] [nvarchar](200) NULL,
        [email] [nvarchar](200) NULL,
        [password] [nvarchar](50) NULL,
     CONSTRAINT [PK_user_info] PRIMARY KEY CLUSTERED
    (
        [userID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, _
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[user_info] ON
    INSERT [dbo].[user_info] ([userID], [name], [email], [password]) _
    VALUES (1, N'Mayur Lohite', N'mayur@mayur.com', N'123456')
    INSERT [dbo].[user_info] ([userID], [name], [email], [password]) _
    VALUES (2, N'John Doe', N'john@john.com', N'654321')
    INSERT [dbo].[user_info] ([userID], [name], [email], [password]) _
    VALUES (3, N'Hacker', N'hack@hack.com', N'789123')
    SET IDENTITY_INSERT [dbo].[user_info] OFF
  2. Create a new empty ASP.NET website project. Add the following two pages into it:
    1. Default.aspx
    2. viewuser.aspx
  3. Code for Default.aspx is as follows:
    <%@ page language="C#" autoeventwireup="true" 
    codefile="Default.aspx.cs"
        inherits="_Default" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>SQL Injection Demo</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div style="width: 50%; margin: 0 auto; text-align: center;">
            <table>
                <tr>
                    <td colspan="2">
                        <h2>
                            SQL Injection Demo</h2>
                    </td>
                </tr>
                <tr>
                    <td>
                        Search by userid
                        <asp:textbox id="txtUserID" runat="server">
                        </asp:textbox>
                    </td>
                    <td>
                        <asp:button id="btnSubmit" onclick="BtnSubmit_Click"
                        runat="server" text="Search" />
                    </td>
                </tr>
                <tr>
                    <asp:gridview id="gvUserInfo" width="100%"
                    runat="server" datakeynames="userID" autogeneratecolumns="false">
                        <Columns>
                            <asp:BoundField DataField="userID" HeaderText="userID" />
                            <asp:BoundField DataField="name" HeaderText="name" />
                            <asp:BoundField DataField="email" HeaderText="email" />
                            <asp:HyperLinkField DataNavigateUrlFields="userID"
                            DataNavigateUrlFormatString="viewuser.aspx?userid={0}"
                                Text="View User" HeaderText="action" />
                        </Columns>
                    </asp:gridview>
                </tr>
            </table>
        </div>
        </form>
    </body>
    </html> 
  4. Code for Default.aspx.cs is as follows:
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataSet dset = new DataSet();
                SqlConnection conn = new SqlConnection
                (ConfigurationManager.ConnectionStrings["MyExpConnectionString"].ToString());
                using (conn)
                {
                    conn.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    SqlCommand cmd = new SqlCommand("SELECT userID, name, email FROM user_info", conn);
                    cmd.CommandType = CommandType.Text;
                    adapter.SelectCommand = cmd;
                    adapter.Fill(dset);
                    gvUserInfo.DataSource = dset;
                    gvUserInfo.DataBind();
                }
            }
        }
    
        protected void BtnSubmit_Click(object sender, EventArgs e)
        {
            DataSet dset = new DataSet();
            SqlConnection conn = new SqlConnection
            (ConfigurationManager.ConnectionStrings["MyExpConnectionString"].ToString());
            using (conn)
            {
                conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                string sqlQuery = string.Format("SELECT userID,
                name, email FROM user_info WHERE userID={0}", txtUserID.Text);
                SqlCommand cmd = new SqlCommand(sqlQuery, conn);
                cmd.CommandType = CommandType.Text;
                adapter.SelectCommand = cmd;
                adapter.Fill(dset);
                gvUserInfo.DataSource = dset;
                gvUserInfo.DataBind();
            }
        }
    }

    Default page screen shot is as follows:

    Default aspx page

  5. Code for viewuser.aspx is as follows:
    <%@ Page Language="C#" AutoEventWireup="true"
        CodeFile="viewuser.aspx.cs" Inherits="viewuser" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>SQL Injection Demo</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div style="width: 50%; margin: 0 auto; text-align: center;">
            <table>
                <tr>
                    <td colspan="2">
                        <h2>
                            SQL Injection Demo</h2>
                    </td>
                </tr>
                <tr>
                    <td>
                        <h3>
                            Welcome
                            <asp:Label ID="lblDetails" runat="server"></asp:Label>
                        </h3>
                    </td>
                </tr>
            </table>
        </div>
        </form>
    </body>
    </html>
  6. Code for viewuser.aspx.cs is as follows:
    public partial class viewuser : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Request.QueryString["userid"] != null)
            {
                DataSet dset = new DataSet();
                SqlConnection conn = new SqlConnection
                (ConfigurationManager.ConnectionStrings["MyExpConnectionString"].ToString());
                using (conn)
                {
                    conn.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    string sqlQuery = string.Format
                    ("SELECT name FROM user_info WHERE userID={0}", Request.QueryString["userid"]);
                    SqlCommand cmd = new SqlCommand(sqlQuery, conn);
                    cmd.CommandType = CommandType.Text;
                    adapter.SelectCommand = cmd;
                    adapter.Fill(dset);
                    if (dset.Tables[0].Rows.Count > 0)
                    {
                        lblDetails.Text = dset.Tables[0].Rows[0]["name"].ToString(); ;
                    }
                }
            }
        }
    }

    Viewuser page screen shot is as follows:

    Viewuser page source code

Exploitation

Approach 1: By Input Boxes.

A-1. First consider the Default Page, we have One TextBox, One Button and One GridView. On form load, all data will be displayed on grid view. We have functionality to search user by their ID. Suppose I enter 1 to textbox and press button, it will display the record associated with userID = 1.

A-2. Now, if we take a look at the above code in Default.aspx.cs there is button click event, i.e.,

protected void BtnSubmit_Click(object sender, EventArgs e)

The query is written as a string and user input is concatenated with it.

string sqlQuery = string.Format("SELECT userID, name, email FROM user_info WHERE userID={0}", txtUserID.Text);

A-3. Suppose the user input is not validated properly, then hacker or attacker can concatenate any malicious query with it. In this scenario, I am concatenating another SELECT statement with the help of UNION to txtUserID.Text.

A-4. I have entered the following text on textbox (txtUserID) without quotes "1 UNION SELECT userID,email,password FROM user_info"

A-5. Now complete query becomes:

string sqlQuery = SELECT userID, name, email FROM user_info _
    WHERE userID=1 UNION SELECT userID,email,password FROM user_info        

A-6. If I hit click on button, the gridview display combination of both SELECT QUERY and the user password is revealed. If the query used with user input concatenation without any input validations, then code is always vulnerable for SQL Injection Attack.

Note: I have increased the size of textbox to understand the query better.

Default page affected to sql injection

Approach 2: Query Strings [GET]

B-1. Now please go to default.aspx and click on viewuser link on GridView. The page will redirect to viewuser.aspx with userid query string parameter.

B-2. The page welcomes the user by their name. The name will founded by userid from query string value.

B-3. Now if we take a look at the above code in viewuser.aspx.cs Form_Load event...

        protected void Page_Load(object sender, EventArgs e)
        

The query is written as a string and the query string is concatenated with it.

string sqlQuery = string.Format("SELECT name _
    FROM user_info WHERE userID={0}", Request.QueryString["userid"]);         

B-4. Now suppose I append the malicious Select query to Request.QueryString["userid"] as same as the above approach, the URL becomes:

http://mayurlohite.com/viewsuer.aspx?userid=1 UNION SELECT password FROM user_info WHERE userID = 1        

B-5. If I hit enter, then the label will display the password associated with userID = 1.

view user page query string affected to sql injection

Why This Happens?

In both the above approaches, the query is concatenated with user input and the user input is not validating properly. So the attacker takes advantage of it and concatenates the malicious query with it and attacker can get the passwords , install the backdoor. Attacker can manipulate the whole database from sysobject.

How to Prevent?

  1. Validate the user input properly
  2. Use parameterized SQL queries (sqlParameter) with stored procedures

1. Validate user input

If your input takes only ids or integers, add some validations for accept only numbers. If inputs are complicated, then use the regex patterns to identify the correct inputs.

2. Parameterized SQL query & Stored Procedure

Parameterized queries do proper substitution of arguments prior to running the SQL query. It completely removes the possibility of "dirty" input changing the meaning of your query, with parameterized queries, in addition to general injection, you get all the data types handled, numbers (int and float), strings (with embedded quotes), dates and times (no formatting problems or localization issues when .ToString() is not called with the invariant culture and your client moves to a machine with and unexpected date format).

I have rewritten the above code safe from SQL Inection. Please take a look at it.

  1. Code for ConnectionManager.cs class is as follows:
    public class ConnectionManager
    {
        public static SqlConnection GetDatabaseConnection()
        {
            SqlConnection connection = new SqlConnection
            (Convert.ToString(ConfigurationManager.ConnectionStrings["MyExpConnectionString"]));
            connection.Open();
    
            return connection;
        }
    }
  2. Code for DataAccessLayer.cs class is as follows:
    public class DataAccessLayer
    {
        public static DataSet DisplayAllUsers()
        {
            DataSet dSet = new DataSet();
            using (SqlConnection connection = ConnectionManager.GetDatabaseConnection())
            {
                try
                {
                    SqlCommand command = new SqlCommand("spDisplayUserAll", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = command;
                    adapter.Fill(dSet);
                }
                catch (Exception ex)
                {
                    throw;
                }
                return dSet;
            }
        }
    
        public static DataSet DisplayUserByID(int userID)
        {
            DataSet dSet = new DataSet();
            using (SqlConnection connection = ConnectionManager.GetDatabaseConnection())
            {
                try
                {
                    SqlCommand command = new SqlCommand("spDisplayUserByID", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@userID", SqlDbType.Int).Value = userID;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = command;
                    adapter.Fill(dSet);
                }
                catch (Exception ex)
                {
                    throw;
                }
                return dSet;
            }
        }
    }
  3. Code for Default.aspx is as follows:
    <%@ Page Language="C#" AutoEventWireup="true" 
        CodeFile="Default.aspx.cs" Inherits="_Default" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>SQL Injection Demo</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div style="width: 50%; margin: 0 auto; text-align: center;">
            <table>
                <tr>
                    <td colspan="2">
                        <h2>
                            SQL Injection Demo</h2>
                    </td>
                </tr>
                <tr>
                    <td>
                        Search by userid
                        <asp:TextBox ID="txtUserID" runat="server">
                        </asp:TextBox>
                        <<asp:RequiredFieldValidator ID="rfvUserID"
                        ControlToValidate="txtUserID" Display="Dynamic"
                            runat="server" ErrorMessage="Required"></asp:RequiredFieldValidator>
                        <asp:RegularExpressionValidator ID="revUserID"
                        runat="server" ErrorMessage="Numbers Only"
                            ValidationExpression="[0-9]+" ControlToValidate="txtUserID"
                            Display="Dynamic"></asp:RegularExpressionValidator>
                    </td>
                    <td>
                        <asp:Button ID="btnSubmit" OnClick="BtnSubmit_Click" 
                        runat="server" Text="Search" />
                    </td>
                </tr>
                <tr>
                    <asp:GridView ID="gvUserInfo" Width="100%" runat="server"
                    DataKeyNames="userID" AutoGenerateColumns="false">
                        <Columns>
                            <asp:BoundField DataField="userID" HeaderText="userID" />
                            <asp:BoundField DataField="name" HeaderText="name" />
                            <asp:BoundField DataField="email" HeaderText="email" />
                            <asp:HyperLinkField DataNavigateUrlFields="userID"
                            DataNavigateUrlFormatString="viewuser.aspx?userid={0}"
                                Text="View User" HeaderText="action" />
                        </Columns>
                    </asp:GridView>
                </tr>
            </table>
        </div>
        </form>
    </body>
    </html>
  4. Code for Default.aspx.cs is as follows:
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataSet dset = DataAccessLayer.DisplayAllUsers();
                if (dset.Tables[0].Rows.Count > 0)
                {
                    gvUserInfo.DataSource = dset;
                    gvUserInfo.DataBind();
                }
            }
        }
    
        protected void BtnSubmit_Click(object sender, EventArgs e)
        {
            int userID = Convert.ToInt32(txtUserID.Text);
            DataSet dSet = DataAccessLayer.DisplayUserByID(userID);
            if (dSet.Tables[0].Rows.Count > 0)
            {
                gvUserInfo.DataSource = dSet;
                gvUserInfo.DataBind();
            }
        }
    }
  5. Code for viewuser.aspx is as follows:
    <%@ Page Language="C#" AutoEventWireup="true"
        CodeFile="viewuser.aspx.cs" Inherits="viewuser" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>SQL Injection Demo</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div style="width: 50%; margin: 0 auto; text-align: center;">
            <table>
                <tr>
                    <td colspan="2">
                        <h2>
                            SQL Injection Demo</h2>
                    </td>
                </tr>
                <tr>
                    <td>
                        <h3>
                            Welcome
                            <asp:Label ID="lblDetails" runat="server"></asp:Label>
                        </h3>
                    </td>
                </tr>
            </table>
        </div>
        </form>
    </body>
    </html>
  6. Code for viewuser.aspx.cs is as follows:
    public partial class viewuser : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Request.QueryString["userid"] != null)
            {
                int userID = Convert.ToInt32(Request.QueryString["userID"]);
                DataSet dSet = DataAccessLayer.DisplayUserByID(userID);
                if (dSet.Tables[0].Rows.Count > 0)
                {
                    lblDetails.Text = Convert.ToString(dSet.Tables[0].Rows[0]["name"]);
                }
            }
        }
    }
  7. Stored Procedure: spDisplayUserAll
    CREATE PROCEDURE spDisplayUserAll
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT userID, name, email
        FROM user_info
    END
  8. Stored Procedure: spDisplayUserByID
    CREATE PROCEDURE spDisplayUserByID
        @userID int = 0
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT userID, name, email
        FROM user_info
        WHERE userID = @userID
    END

Points of Interest

The SQL Injection is the most common security vulnerability known in web applications. The dynamic webpages without handling validations and improper handling of code may lead to SQLI but by knowing proper code standard and tricks, we will successfully prevent it.

License

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

Share

About the Author

Mayur V Lohite
CEO
India India
My name is Mayur Lohite. I am Programmer and Web Developer also I am Interested in Web Application Security and penetration. From a young age I’ve been fascinated with building web application and breaking it. So I have choose the Information technology as my career and completed the Degree in Information technology.

I’m a interested in operating and design of large-scale web applications/infrastructure and I’m interested in web application security also I am interested in some networking setting up servers, domains experimenting with clouds(well I am not professional at it)

I am a big fan of Anime series (Naruto) and also love to playing games. In the mean time I love to watching movies specially action movies.

Now I’m a white hat penetration tester. bug hunter and all out security freak by profession and passion. I’ve listed many websites Hall of fame e.g. Microsoft, Yahoo, Apple, Adobe
Follow on   Twitter   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 Pin
John B Oliver15-Sep-14 13:15
memberJohn B Oliver15-Sep-14 13:15 
GeneralMy vote of 1 Pin
Аslam Iqbal8-Sep-14 2:58
memberАslam Iqbal8-Sep-14 2:58 
GeneralRe: My vote of 1 Pin
Richard Deeming9-Sep-14 7:57
professionalRichard Deeming9-Sep-14 7:57 
GeneralRe: My vote of 1 Pin
John B Oliver15-Sep-14 13:12
memberJohn B Oliver15-Sep-14 13:12 
QuestionThe first code is OK but with 1 bug of novice Pin
RNOVAK3-Sep-14 3:31
memberRNOVAK3-Sep-14 3:31 
AnswerRe: The first code is OK but with 1 bug of novice Pin
Richard Deeming9-Sep-14 7:53
professionalRichard Deeming9-Sep-14 7:53 
GeneralMy vote of 2 Pin
Member 107698582-Sep-14 12:14
memberMember 107698582-Sep-14 12:14 
GeneralMy vote of 3 Pin
George L. Jackson2-Sep-14 8:57
memberGeorge L. Jackson2-Sep-14 8:57 
GeneralRe: My vote of 3 Pin
John B Oliver15-Sep-14 13:13
memberJohn B Oliver15-Sep-14 13:13 
GeneralMy vote of 1 Pin
Pstry2-Sep-14 4:56
memberPstry2-Sep-14 4:56 
GeneralRe: My vote of 1 Pin
Mayur V Lohite2-Sep-14 4:57
professionalMayur V Lohite2-Sep-14 4:57 
GeneralNice, but nothing new Pin
Malte Klena2-Sep-14 4:29
memberMalte Klena2-Sep-14 4:29 
GeneralRe: Nice, but nothing new Pin
Mayur V Lohite2-Sep-14 4:51
professionalMayur V Lohite2-Sep-14 4:51 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun1-Sep-14 20:31
memberHumayun Kabir Mamun1-Sep-14 20:31 
QuestionAbout the password Pin
Mika Wendelius1-Sep-14 18:10
mentorMika Wendelius1-Sep-14 18:10 
AnswerRe: About the password Pin
Mayur V Lohite1-Sep-14 20:42
professionalMayur V Lohite1-Sep-14 20:42 
GeneralRe: About the password Pin
Mika Wendelius2-Sep-14 2:33
mentorMika Wendelius2-Sep-14 2:33 
QuestionNice & Usefull Pin
Anthony Morera1-Sep-14 17:33
memberAnthony Morera1-Sep-14 17:33 

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
Web04 | 2.8.150428.2 | Last Updated 1 Sep 2014
Article Copyright 2014 by Mayur V Lohite
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid