View RSS Feed

mayurlohite

Preventing SQL Injection attack ASP.NET PART I

Rating: 464 votes, 4.98 average.

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.
Code:
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
Code:
<%@ 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
Code:
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
Click image for larger version. 

Name:	default.jpg 
Views:	995 
Size:	32.3 KB 
ID:	783
5. Code for viewuser.aspx
Code:
<%@ 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
Code:
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
Click image for larger version. 

Name:	viewuser.jpg 
Views:	875 
Size:	11.2 KB 
ID:	782
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.
Code:
protected void BtnSubmit_Click(object sender, EventArgs e)
The query is written as a string and user input is concatenated with it.
Code:
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:
Code:
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.
Click image for larger version. 

Name:	defaultsqli.jpg 
Views:	1071 
Size:	61.3 KB 
ID:	781
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
Code:
protected void Page_Load(object sender, EventArgs e)
The query is written as a string and the query string is concatenated with it.
Code:
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
Code:
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

Click image for larger version. 

Name:	viewusersqli.jpg 
Views:	892 
Size:	25.4 KB 
ID:	780

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. Parametrized SQL query & Stored Procedure:
Parametrized 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 parametrized 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
Code:
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
Code:
 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
Code:
<%@ 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
Code:
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
Code:
<%@ 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
Code:
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
Code:
CREATE PROCEDURE spDisplayUserAll
AS
BEGIN
    SET NOCOUNT ON;
    SELECT userID, name, email 
    FROM user_info
END
8. Stored Procedure: spDisplayUserByID
Code:
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.

Article
http://mayurlohite.com/preventing-sq...sp-net-part-i/
http://codeproject.com/Articles/8139...ASP-NET-Part-I

Download Source Code

Updated 09-02-2014 at 03:14 PM by mayurlohite

Categories
Uncategorized

Comments

Trackbacks

Total Trackbacks 0
Trackback URL: