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:
- Input boxes
- 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
- 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
- Create a new empty ASP.NET website project. Add the following two pages into it:
- Default.aspx
- viewuser.aspx
- 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>
- 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:
- 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>
- 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:
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.
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:
B-5. If I hit enter, then the label will display the password associated with userID = 1.
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?
- Validate the user input properly
- 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.
- 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;
}
}
- 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;
}
}
}
- 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>
- 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();
}
}
}
- 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>
- 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"]);
}
}
}
}
- Stored Procedure:
spDisplayUserAll
CREATE PROCEDURE spDisplayUserAll
AS
BEGIN
SET NOCOUNT ON;
SELECT userID, name, email
FROM user_info
END
- 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.
