Prevent SQL Injection in ASP.NET

If you are generating dynamic SQL queries based on user input, an attacker could inject malicious SQL commands that can be executed by the database, it’s called SQL Injection. In this article, I have described how to see un- authorized data and login with invalid user id and password. After that I described how to prevent SQL injection.

For demonstration, I have used ASP.NET 2010 with C# and SQL Server 2008. Steps are given below.

Step 1: Create two tables in SQL server database, one for login status and second for person’s details. Query is given below for creating tables.

CREATE DATABASE [dbPatientStatusDemo]

USE [dbPatientStatusDemo]

CREATE TABLE [dbo].[tblPatientStatusLogin]

(

[SNo] INT IDENTITY(1,1) NOT NULL,

[PatientID] INT PRIMARY KEY NOT NULL,

[Password] VARCHAR(20) NOT NULL

)

INSERT [dbo].[tblPatientStatusLogin] ([PatientID], [Password]) VALUES ('101', '1011')

INSERT [dbo].[tblPatientStatusLogin] ([PatientID], [Password]) VALUES ('102', '1022')

INSERT [dbo].[tblPatientStatusLogin] ([PatientID], [Password]) VALUES ('103', '1033')

INSERT [dbo].[tblPatientStatusLogin] ([PatientID], [Password]) VALUES ('104', '1044')

CREATE TABLE [dbo].[tblPatientStatusDetails]

(

[SNo] INT IDENTITY(1,1) NOT NULL,

[PatientID] INT NOT NULL,

[AdmitDate] DATETIME NOT NULL,

[DischargeDate] DATETIME NOT NULL,

[PatientName] VARCHAR(100) NOT NULL,

[Treatment] VARCHAR(100) NOT NULL,

[Doctor] VARCHAR(50) NOT NULL,

[PatientStatus] VARCHAR(500) NULL

)

INSERT [dbo].[tblPatientStatusDetails] ([PatientID], [AdmitDate], [DischargeDate], [PatientName], [Treatment], [Doctor], [PatientStatus]) VALUES ('101','2013-01-05 00:00:00.000' , '2013-01-06 00:00:00.000', 'Jacob', 'Canavan disease', 'Dr. Jemsh', 'Good')

INSERT [dbo].[tblPatientStatusDetails] ([PatientID], [AdmitDate], [DischargeDate], [PatientName], [Treatment], [Doctor], [PatientStatus]) VALUES ('101','2013-01-06 00:00:00.000' , '2013-01-06 00:00:00.000', 'Jacob', 'Aphasia', 'Dr. Devid', 'Good')

INSERT [dbo].[tblPatientStatusDetails] ([PatientID], [AdmitDate], [DischargeDate], [PatientName], [Treatment], [Doctor], [PatientStatus]) VALUES ('101','2013-01-15 00:00:00.000' , '2013-01-15 00:00:00.000', 'Joshua ', 'Vocal fold cysts', 'Dr. Mark', 'Good')

INSERT [dbo].[tblPatientStatusDetails] ([PatientID], [AdmitDate], [DischargeDate], [PatientName], [Treatment], [Doctor], [PatientStatus]) VALUES ('101','2013-01-05 00:00:00.000' , '2013-01-08 00:00:00.000', 'Michael', 'Ectropion', 'Dr. Freank', 'Good')

INSERT [dbo].[tblPatientStatusDetails] ([PatientID], [AdmitDate], [DischargeDate], [PatientName], [Treatment], [Doctor], [PatientStatus]) VALUES ('101','2013-01-02 00:00:00.000' , '2013-01-09 00:00:00.000', 'Michael', 'Bladder cancer', 'Dr. Alderson', 'Good')

Screen Shot of table “tblPatientStatusLogin”

Figure 1:

Screen Shot of table “tblPatientStatusDetails”

Figure 2:

Step 2: Now we are going to create user interface. Create one .aspx (e.g. Default.aspx) page for User’s login area and display data regarding login user’s id as below image.

Figure 3:

.aspx code (e.g. Default.aspx)

<div>

<legend>Login</legend>

<table>

<tr>

</td>

<td>

<asp:TextBox ID="txtUserID" runat="server" Width="150px"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<span>Password :</span>

</td>

<td>

<asp:TextBox ID="txtPassword" runat="server" TextMode="Password" Width="150px"></asp:TextBox>

</td>

</tr>

<tr>

<td>

</td>

<asp:Button ID="btnLogin" runat="server" Text="Button" OnClick="btnLogin_Click" />

</td>

</tr>

</table>

<asp:Label ID="lblErrorMsg" runat="server" Text="User ID or Password Incorrect" ForeColor="#FF3300"

Visible="False"></asp:Label>

</fieldset>

</div>

<div>

<asp:GridView ID="GridView1" runat="server" ShowHeaderWhenEmpty="false" AutoGenerateColumns="False">

<asp:HyperLinkField DataTextField="SNo" HeaderText="S. No." SortExpression="SNo"

DataNavigateUrlFields="SNo" DataNavigateUrlFormatString="PatientDetails.aspx?SNo={0}" />

<asp:TemplateField>

Admit Date</HeaderTemplate>

<asp:Label ID="lblAdmitDate" runat="server" Text='<%#Bind("AdmitDate")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

Treatment</HeaderTemplate>

<asp:Label ID="lblTreatment" runat="server" Text='<%#Bind("Treatment")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

Step 3: Now, write connection string in web.config file as below

</connectionStrings>

Note: Change connection string according your server name, user id and password.

Step 4: Now come on .cs page (e.g. Default.aspx.cs) and write below line of code.

Include below namespaces.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Text;

Write below code within button click event.

protected void btnLogin_Click(object sender, EventArgs e)

{

using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString))

{

try

{

var dt = new DataTable();

var cmd = new SqlCommand("Select * From tblPatientStatusLogin where [PatientID] ='" + txtUserID.Text + "' AND [Password] = '" + txtPassword.Text + "'", con);

if (con.State == ConnectionState.Closed)

con.Open();

SqlDataReader dr = cmd.ExecuteReader();

if (dr.HasRows)

{

cmd.Dispose();

dr.Dispose();

var cmd1 = new SqlCommand("Select [SNo],[PatientID],[AdmitDate],[DischargeDate],[PatientName],[Treatment],[Doctor],[PatientStatus] From tblPatientStatusDetails where [PatientID] ='" + txtUserID.Text + "'", con);

var adap = new SqlDataAdapter(cmd1);

adap.Fill(dt);

GridView1.DataSource = dt;

GridView1.DataBind();

lblErrorMsg.Visible = false;

GridView1.Visible = true;

}

else

{

lblErrorMsg.Visible = true;

GridView1.Visible = false;

}

catch

{

lblErrorMsg.Visible = true;

GridView1.Visible = false;

}

finally

{

if (con.State == ConnectionState.Open)

con.Close();

}

Note: Build and Save the application and execute. Login with correct userid and password (see userid (PatientID is userid) and password in table “tblPatientStatusLogin” in step 1) and check data. Here you noticed, if you try to login with invalid user id or password, it display error message as below image

Figure 4:

Step 5: Here I tell you how to login with invalid user id and password. Use one by one (which given below) string as user id and password.

' or 0=0 –

' or 'x'='x

' or 1=1—

hi' or 1=1 –

hi' or 'a'='a

Note: Here I have entered ' or 0=0 – as user id and password, as below image

Figure 5:

Another example as below image

Figure 6:

Note: You have seen in Figure 5 and Figure 6, using SQL injection we can get all data of tables with invalid user id and password.

Step 6: Same problem come with QueryString. For QueryString demo, we are going to create another .aspx page (e.g. PatientDetails.aspx) for display details, based on Login person’s S.No. (This column is a hyper link that open PatientDetails.aspx page). See hyper link (red rectangular) in figure 7.

Figure 7:

.aspx code (e.g. PatientDetails.aspx)

<div>

<asp:GridView ID="GridView1" runat="server" ShowHeaderWhenEmpty="false" AutoGenerateColumns="False">

<asp:TemplateField>

Patient ID</HeaderTemplate>

<asp:Label ID="lblPatientID" runat="server" Text='<%#Bind("PatientID")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

PatientName</HeaderTemplate>

<asp:Label ID="lblPatientName" runat="server" Text='<%#Bind("PatientName")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

Treatment</HeaderTemplate>

<asp:Label ID="lblTreatment" runat="server" Text='<%#Bind("Treatment")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

Admit Date</HeaderTemplate>

<asp:Label ID="lblAdmitDate" runat="server" Text='<%#Bind("AdmitDate")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

Discharge Date</HeaderTemplate>

<asp:Label ID="lblDischargeDate" runat="server" Text='<%#Bind("DischargeDate")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

Doctor</HeaderTemplate>

<asp:Label ID="lblDoctor" runat="server" Text='<%#Bind("Doctor")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

Patient Status</HeaderTemplate>

<asp:Label ID="lblPatientStatus" runat="server" Text='<%#Bind("PatientStatus")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

Step 7: Line of code for .cs page (e.g. PatientDetails.aspx.cs).

PatientDetails.aspx.cs

Include namespaces

using System;

using System.Data;

using System.Configuration;

using System.Data.SqlClient;

using System.Text;

Write below line of code within Page_Load Event (e.g. PatientDetails.aspx.cs Page_Load event)

protected void Page_Load(object sender, EventArgs e)

{

using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString))

{

try

{

var cmd = new SqlCommand("select * from tblPatientStatusDetails where SNo='" + Request.QueryString["SNo"].ToString() + "'", con);

var adap = new SqlDataAdapter(cmd);

var dt = new DataTable();

adap.Fill(dt);

GridView1.DataSource = dt;

GridView1.DataBind();

}

catch

{

//Custom Error Message

}

Step 8: Build and save the application and press F5 key for execution. Then Login with correct user id and password. Click on hyper link (S.No.) column’s value for display details of patient as below image. For example here I click on “1” (Figure 8) and see details in figure 9.

Figure 8:

Figure 9:

1. It’s indicating QuerySting. From the QuerySting, if we changed value of QuerySting (e.g. SNo=2 or 5 or any other existing value in table “tblPatientStatusLogin”) then it populate data into gridview, see figure 10. When we write QuerySting as PatientDetails.aspx?SNo=5 it populate data into gridview, but it is wrong, because , SNo 5’s data belong to User Id “104” (See figure 2) and currently we are login with user id 101 (See figure 8) . In another way if we write QueryString as PatientDetails.aspx?SNo=' or 0=0 – it populate all table (tblPatientStatusDetails) value, see figure 11. You can try this also ' or 'x'='x, ' or 1=1—, hi' or 1=1 – hi' or 'a'='a, hi' or 'a'='a, and other SQL statement that perform DDL or DML query in data base. This is an example of SQL Injection.

2. This is a gridview that display data according QuerySting value.

Figure 10:

Figure 11:

Note: PatientDetails.aspx?SNo=' or 0=0 – converted into PatientDetails.aspx?SNo='%20 or%200=0%20 – . That means white space converted into %20.

SQL Injection Preventing

In the above steps, I figure out, how our data Stolen using SQL injection. In the below steps, I have described how to prevent our application from SQL Injection. There are few basic concepts, using that we easily prevent SQL Injection. These are

1. Always use Stored Procedure for passing data between front-end and back-end.

2. Check input data’s length and data type.

3. Remove special character from input data, if not needed that.

4. If you fetch data from QueryString based on current user’s login, than check authentication during fetching data.

Now I’m telling you how to prevent User login which I have described in step 5.

Step 9: Create stored procedure within same database where we are created table in step 1. Query is given below

-- Use Database

USE [dbPatientStatusDemo]

-- Stored Procedure for Login

CREATE PROCEDURE [dbo].[spPatientStatusLogin]

(

@PatientID INT,

@Password VARCHAR(20)

)

SELECT * FROM tblPatientStatusLogin where [PatientID] =@PatientID AND [Password] = @Password

-- Stored Procedure for List of Treatment

CREATE PROCEDURE [dbo].[spPatientStatusTreatment]

(

@PatientID INT

)

SELECT [SNo],[AdmitDate],[Treatment] FROM tblPatientStatusDetails where [PatientID] =@PatientID

-- Stored Procedure for Getting Details

CREATE PROCEDURE [dbo].[spPatientStatusDetails]

(

@SNo INT,

@PatientID INT

)

SELECT [PatientID],[PatientName],[Treatment],[AdmitDate],[DischargeDate],[Doctor],[PatientStatus] FROM tblPatientStatusDetails WHERE [SNo]=@SNo AND [PatientID] = @PatientID

Step 10: Open .cs page (e.g. Default.aspx.cs) which I have described in step 4 and replace that code from below code

// Prevent From SQL Injection

protected void btnLogin_Click(object sender, EventArgs e)

{

using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString))

{

var dt = new DataTable();

// Remove Spacial Character from User ID

StringBuilder sbUserID = new StringBuilder();

foreach (char c in txtUserID.Text)

{

if (Char.IsLetterOrDigit(c) || c == '.' || c == '_')

{

sbUserID.Append(c);

}

// Remove Spacial Character from Password

StringBuilder sPassword = new StringBuilder();

foreach (char c in txtPassword.Text)

{

if (Char.IsLetterOrDigit(c) || c == '.' || c == '_')

{

sPassword.Append(c);

}

try

{

// Call spPatientStatusLogin Stored Procedure and pass parameters values

var cmd = new SqlCommand("spPatientStatusLogin", con);

cmd.Parameters.Add("@PatientID", SqlDbType.Int, 10).Value = Convert.ToInt32(sbUserID.ToString());

cmd.Parameters.Add("@Password", SqlDbType.VarChar, 20).Value = sPassword.ToString();

cmd.CommandType = CommandType.StoredProcedure;

if (con.State == ConnectionState.Closed)

con.Open();

SqlDataReader dr = cmd.ExecuteReader();

if (dr.HasRows)

{

// Dispose Unnecessary object

cmd.Dispose();

dr.Dispose();

// Call spPatientStatusLogin Stored Procedure and pass parameters values

var cmd1 = new SqlCommand("spPatientStatusTreatment", con);

cmd1.Parameters.Add("PatientID", SqlDbType.Int, 10).Value = Convert.ToInt32(sbUserID.ToString());

cmd1.CommandType = CommandType.StoredProcedure;

var adap = new SqlDataAdapter(cmd1);

adap.Fill(dt);

GridView1.DataSource = dt;

GridView1.DataBind();

lblErrorMsg.Visible = false;

GridView1.Visible = true;

// Set loign user id value in session

Session["UserID"] = Convert.ToInt32(sbUserID.ToString());

}

else

{

lblErrorMsg.Visible = true;

GridView1.Visible = false;

}

catch

{

lblErrorMsg.Visible = true;

GridView1.Visible = false;

}

finally

{

if (con.State == ConnectionState.Open)

con.Close();

}

Note: Build and Save application and Press F5 key for run application and insert SQL Injection Query in textbox and see output. Now your data are secured. See figure 12.

Figure 12:

Step 11: Let’s come to prevent QueryString from SQL Injection which I have described in step 8. Replace line of code which described in step 7 from below code.

using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString))

{

try

{

// Prevent From SQL Injection

// Remove Spacial Character from QueryString

StringBuilder sb = new StringBuilder();

foreach (char c in Request.QueryString["SNo"].ToString())

{

if (Char.IsLetterOrDigit(c) || c == '.' || c == '_' || c == ' ' || c == '%')

{

sb.Append(c);

}

// Call Stored Procedure and pass parameters

var cmd = new SqlCommand("spPatientStatusDetails", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@SNo", SqlDbType.Int, 3).Value = Request.QueryString["SNo"];

cmd.Parameters.Add("@PatientID", SqlDbType.Int, 10).Value = Convert.ToInt32(Session["UserID"]);

var adap = new SqlDataAdapter(cmd);

var dt = new DataTable();

GridView1.DataSource = dt;

GridView1.DataBind();

}

catch

{

//Custom Error Message

}

Catch-up the I.T. Bus

Friday, 21 March 2014

Prevent SQL Injection in ASP.NET

Figure 1:

Figure 2:

Figure 3:

Figure 4:

Figure 5:

Figure 6:

Figure 7:

Figure 8:

Figure 9:

Figure 10:

Figure 11:

SQL Injection Preventing

Figure 12:

No comments:

Post a Comment

About Me

Blog Archive