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]
GO
USE [dbPatientStatusDemo]
GO
CREATE TABLE [dbo].[tblPatientStatusLogin]
(
[SNo] INT IDENTITY(1,1) NOT NULL,
[PatientID] INT PRIMARY KEY NOT NULL,
[Password] VARCHAR(20) NOT NULL
)
GO
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')
GO
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
)
GO
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')
GO
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>
<fieldset style="width: 230px;">
<legend>Login</legend>
<table>
<tr>
<td style="text-align: right">
<span>User ID :</span>
</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>
<td style="text-align: center;">
<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">
<Columns>
<asp:HyperLinkField DataTextField="SNo" HeaderText="S. No." SortExpression="SNo"
DataNavigateUrlFields="SNo" DataNavigateUrlFormatString="PatientDetails.aspx?SNo={0}" />
<asp:TemplateField>
<HeaderTemplate>
Admit Date</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblAdmitDate" runat="server" Text='<%#Bind("AdmitDate")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
Treatment</HeaderTemplate>
<ItemTemplate>
<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>
<add name="dbconnection"connectionString="Data Source = Server Name; Initial Catalog=dbPatientStatusDemo; User ID=id; Password=password;"providerName="System.Data.SqlClient"/>
</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
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">
<Columns>
<asp:TemplateField>
<HeaderTemplate>
Patient ID</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblPatientID" runat="server" Text='<%#Bind("PatientID")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
PatientName</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblPatientName" runat="server" Text='<%#Bind("PatientName")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
Treatment</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblTreatment" runat="server" Text='<%#Bind("Treatment")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
Admit Date</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblAdmitDate" runat="server" Text='<%#Bind("AdmitDate")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
Discharge Date</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDischargeDate" runat="server" Text='<%#Bind("DischargeDate")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
Doctor</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDoctor" runat="server" Text='<%#Bind("Doctor")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
Patient Status</HeaderTemplate>
<ItemTemplate>
<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]
GO
-- Stored Procedure for Login
CREATE PROCEDURE [dbo].[spPatientStatusLogin]
(
@PatientID INT,
@Password VARCHAR(20)
)
AS
SELECT * FROM tblPatientStatusLogin where [PatientID] =@PatientID AND [Password] = @Password
GO
-- Stored Procedure for List of Treatment
CREATE PROCEDURE [dbo].[spPatientStatusTreatment]
(
@PatientID INT
)
AS
SELECT [SNo],[AdmitDate],[Treatment] FROM tblPatientStatusDetails where [PatientID] =@PatientID
GO
-- Stored Procedure for Getting Details
CREATE PROCEDURE [dbo].[spPatientStatusDetails]
(
@SNo INT,
@PatientID INT
)
AS
SELECT [PatientID],[PatientName],[Treatment],[AdmitDate],[DischargeDate],[Doctor],[PatientStatus] FROM tblPatientStatusDetails WHERE [SNo]=@SNo AND [PatientID] = @PatientID
GO
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
}
}
Note: Build and Save application and Press F5 key for run application and insert SQL Injection Query in URL and see output. Now your data are secured with un-authorized and SQL Injection. See figure 13.
Figure 13:
You can see below link for more about SQL Injection
http://www.mindstick.com/Blog/228/Preventing%20SQL%20Injection
http://www.mindstick.com/Blog/227/Sql%20Injection
http://www.mindstick.com/Articles/a7279c4f-4768-4ef6-8fdc-25e51e1c10dc/?SQL%20Injection%20Attacks
Leave Comment