Preventing SQL Injection attack ASP.NET PART I

Mayur V Lohite

Rate me:

5.00/5 (2 votes)

22 Apr 2016CPOL5 min read

7.8K

IntroductionSecurity 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.

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. Now days, 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 big headache to 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 application almost all websites are dynamic i.e. database driven and large data will accepts 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 is Actually SQL Injection attack?
SQL Injection is a 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 following link:
https://www.owasp.org/index.php/SQL_Injection

Methods of exploit SQL Injection

Methods of exploits:
1. Input boxes
2. Query Strings [GET]

How to exploit?

In today’s dynamic web applications world its 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 devide this query into 2 parts.
PART-1: Query Part – SELECT userID,email FROM user_info
PART-2: Input Part – userID=1

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

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 following two pages into it. I. Default.aspx II. viewuser.aspx

3. Code for Default.aspx

<%@ 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

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

Default Page

5. Code for viewuser.aspx

<%@ 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

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

View User Page

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 look at 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 validate properly then hacker or attacker can concatenate any malicious query with it. In this scenario I am concatenating another SELECT statement with 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.

SQL Injection Sample

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 look at 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

Why this happens?

In above both approaches the query is concatenated with user input and the user input is not validating properly. So the attacker take advantage of it and concatenate 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 take 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

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

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

<%@ 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

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

<%@ 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

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

SQL

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 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 standred and tricks we will successfully prevent it.

CodeProject

This article was originally posted at http://www.somethingtocode.com/post/preventing-sql-injection-attack-asp-net-part-i

License

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

Written By

Mayur V Lohite

Web Developer

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

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