I have seen a lot of new developers building queries dynamically by concatenating the strings, that end users enter into user interface controls like textboxes , this opens the door for SQL Injection attack.
Please Notice in the above figure
1. The malicious user enters user name as sachin'-- and password as xyz.
2. The application forms the query by concatenating the user Inputs.
3. The key thing here is that the double-dash sequence, -- is a comment indicator in SQL, and means that the rest of the query is interpreted as a comment.
4. SQL Server considers the query as select * from users where Name='Admin'.
5. In this way, the malicious user successfully logged in as Admin.
What is SQL injection ?
SQL injection is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database. It generally allows an attacker to view data that they are not normally able to retrieve. This might include data belonging to other users, or any other data that the application itself is able to access. In many cases, an attacker can modify or delete this data, causing persistent changes to the application's content or behavior.
What is the impact of a successful SQL injection attack?
A successful SQL injection attack can result in unauthorized access to sensitive data, such as passwords, credit card details, or personal user information. In some cases, an attacker can obtain a persistent backdoor into an organization's systems, leading to a long-term compromise that can go unnoticed for an extended period.
Examples
Retrieving hidden data
SELECT * FROM products WHERE category = 'Cars' AND launched = true
This SQL query asks the database to return:
• all details (*)
• from the products table
• where the category is Cars
• and launched is true.
The restriction launched = true is being used to hide products that are not released. For unreleased products, presumably launched = false.
The application doesn't implement any defenses against SQL injection attacks, so an attacker can construct an attack like shown below
https://insecure-website.com/products?category=Cars--
This results in the SQL query:
SELECT * FROM products WHERE category = ' Cars'--' AND launched = true
The key thing here is that the double-dash sequence -- is a comment indicator in SQL, and means that the rest of the query is interpreted as a comment. This effectively removes the remainder of the query, so it no longer includes AND launched = true. This means that all products are displayed, including unreleased products.
Subverting application logic
Consider an application that lets users log in with a username and password. If a user submits the username Sachin and the password Sachin123, the application checks the credentials by performing the following SQL query
SELECT * FROM users WHERE username = 'Sachin' AND password = 'Sachin123'
If the query returns the details of a user, then the login is successful. Otherwise, it is rejected.
Here, an attacker can log in as any user without a password simply by using the SQL comment sequence -- to remove the password check from the WHERE clause of the query. For example, submitting the username admin'-- and a blank password results in the following query:
SELECT * FROM users WHERE username = 'admin'--' AND password = ''
This query returns the user whose username is admin and successfully logs the attacker in as that user.
Retrieving data from other database tables
In cases where the results of an SQL query are returned within the application's responses, an attacker can leverage an SQL injection vulnerability to retrieve data from other tables within the database. This is done using the UNION keyword, which lets you execute an additional SELECT query and append the results to the original query.
For example, if an application executes the following query containing the user input "Cars":
SELECT name, description FROM products WHERE category = 'Cars'
then an attacker can submit the input:
'UNION SELECT username, password FROM users--
This will cause the application to return all usernames and passwords along with the names and descriptions of products.
Let's do a practical demonstration
we have to develop an Application where if the user provides correct credentials like username and password then application allows them to edit their profile.
Step 1 : Create the User table using the following SQL script
Create database SqlInjectionTestDB
Go
use SqlInjectionTestDB
Go
Create Table Users
(
Id int identity(1,1) not null,
UserName nvarchar(50),
Password nvarchar(50),
PhoneNumber varchar(10),
Address varchar(50)
)
GO
SET IDENTITY_INSERT [dbo].[Users] ON
INSERT INTO [dbo].[Users] ([Id], [UserName], [Password], [PhoneNumber], [Address]) VALUES (1, N'Sachin', N'Sachin@123', N'788987020', N'Odisha,India')
INSERT INTO [dbo].[Users] ([Id], [UserName], [Password], [PhoneNumber], [Address]) VALUES (2, N'Arjun', N'Arjun@11', N'7889870200', N'Ramgarh,India')
INSERT INTO [dbo].[Users] ([Id], [UserName], [Password], [PhoneNumber], [Address]) VALUES (3, N'Vikash', N'Vikash@10', N'7889870222', N'Delhi,India')
SET IDENTITY_INSERT [dbo].[Users] OFF
Step 2 : Create a new empty asp.net web application. Name it SqlInjectionDemo.
Step 3 : Include connection string in web.config file
<connectionStrings>
<add name="CS"connectionString="Data Source=SACHIN-PC\SQLEXPRESS;Initial Catalog=SqlInjectionTestDB;Integrated Security=True"/>
</connectionStrings>
Step 4 : Add a WebForm. Copy and paste the following HTML.
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<div class="container" style="margin-top:100px;">
<table class="table table-bordered table-responsive">
<tr>
<td>User Name</td>
<td>
<asp:TextBox ID="txtUserName" runat="server" CssClass="form-control"></asp:TextBox>
</td>
</tr>
<tr>
<td>Password</td>
<td>
<asp:TextBox ID="txtPassword" runat="server" CssClass="form-control"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="btnEditProfile" runat="server" Text="Edit Profile" OnClick="btnEditProfile_Click" CssClass="btn btn-block btn-primary" />
</td>
</tr>
</table>
<div class="modal fade" id="myModal" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<div class="modal-dialog">
<asp:UpdatePanel ID="upModal" runat="server" ChildrenAsTriggers="false" UpdateMode="Conditional">
<ContentTemplate>
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<h4 class="modal-title">
<asp:Label ID="lblModalTitle" runat="server" Text=""></asp:Label></h4>
</div>
<div class="modal-body">
<asp:Label ID="lblModalBody" runat="server" Text=""></asp:Label>
<table class="table table-bordered table-responsive">
<tr>
<td>User Name</td>
<td>
<asp:TextBox ID="txtUserNameNew" runat="server" CssClass="form-control"></asp:TextBox>
</td>
</tr>
<tr>
<td>Password</td>
<td>
<asp:TextBox ID="txtUserPasswordNew" runat="server" CssClass="form-control"></asp:TextBox>
</td>
</tr>
<tr>
<td>Phone Number</td>
<td>
<asp:TextBox ID="txtPhoneNumber" runat="server" CssClass="form-control"></asp:TextBox>
</td>
</tr>
<tr>
<td>Address</td>
<td>
<asp:TextBox ID="txtAddress" runat="server" CssClass="form-control"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="btnUpdate" runat="server" Text="Update Profile" CssClass="btn btn-primary" />
</td>
</tr>
</table>
</div>
<div class="modal-footer">
<button class="btn btn-info" data-dismiss="modal" aria-hidden="true">Close</button>
</div>
</div>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</div>
</div>
</form>
Step 5 : Double click on btnEditProfile button to generate button event handler and Copy and paste the following code.
protected void btnEditProfile_Click(object sender, EventArgs e)
{
string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand(@"select * from Users where UserName='" + txtUserName.Text + "' and Password=+'" + txtPassword.Text
+ "'", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while(rdr.Read())
{
txtUserNameNew.Text = rdr["UserName"].ToString();
txtUserPasswordNew.Text = rdr["Password"].ToString();
txtPhoneNumber.Text = rdr["PhoneNumber"].ToString();
txtAddress.Text = rdr["Address"].ToString();
}
}
lblModalTitle.Text = "Edit User Details";
lblModalBody.Text = "fill all fields";
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "myModal", "$('#myModal').modal('show');", true);
upModal.Update();
}
}
Testing : Run the application and Enter a valid user Name and Password and Click on "Edit Profile" button ,The profile details will be display in a popup for update .
Bypass Password with SQL Injection
Now , Be a hacker for a moment and Enter user name as Sachin'-- without providing any password. You will notice that even if you don't provide the Password , the Edit profile dialog is still opening , when you click on Edit profile button
SQL Injection prevention
There are two techniques to prevent SQL Injection attack.
1. Parameterized Queries.
2. Store Procedure.
Method I) Parameterized Query
The above code can be easily re-written using parameterized queries to prevent sql injection attack. The re-written code is shown below. Notice, that the query now uses parameters - @UserName and @Password. The value for these parameters is then provided using the AddWithValue() method. The parameter is associated with the command object using Prameters collection property of the command object.
protected void btnEditProfile_Click(object sender, EventArgs e)
{
string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("select * from Users where UserName=@UserName and Password=@Password", con);
cmd.Parameters.AddWithValue("@UserName",txtUserName.Text);
cmd.Parameters.AddWithValue("@Password",txtPassword.Text);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while(rdr.Read())
{
txtUserNameNew.Text = rdr["UserName"].ToString();
txtUserPasswordNew.Text = rdr["Password"].ToString();
txtPhoneNumber.Text = rdr["PhoneNumber"].ToString();
txtAddress.Text = rdr["Address"].ToString();
}
}
lblModalTitle.Text = "Edit User Details";
lblModalBody.Text = "fill all fields";
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "myModal", "$('#myModal').modal('show');", true);
upModal.Update();
}
Method II) Store Procedure
SQL injection can also be prevented using stored procedures. So, first let's write a stored procedure, that returns the user details. This stored procedure takes two input parameter @Username and @Password.
Create Procedure spGetUserDetails
@ Username nvarchar(50),
@Password nvarchar(50)
as
Begin
select * from Users where UserName=@UserName and Password=@Password
End
Now, let's re-write the code, to use stored procedure spGetUserDetails.
protected void btnEditProfile_Click(object sender, EventArgs e)
{
string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spGetUserDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName",txtUserName.Text);
cmd.Parameters.AddWithValue("@Password",txtPassword.Text);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while(rdr.Read())
{
txtUserNameNew.Text = rdr["UserName"].ToString();
txtUserPasswordNew.Text = rdr["Password"].ToString();
txtPhoneNumber.Text = rdr["PhoneNumber"].ToString();
txtAddress.Text = rdr["Address"].ToString();
}
}
lblModalTitle.Text = "Edit User Details";
lblModalBody.Text = "fill all fields";
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "myModal", "$('#myModal').modal('show');", true);
upModal.Update();
}
How parameterized Query and Store procedure successfully prevent SQL Injection Attack?
Both does the same thing to prevent SQL injection , the user input are now becomes the value for input parameters as a whole and not as a separate SQL query.
If you type the following input into the UserName textbox, the entire content of the TextBox is now treated as a value for the parameter - @UserName not as a seperate sql statement.
Sachin'-- becomes 'Sachin''--' meaning the single quote (apostrophe) becomes double single quote(double apostrophe) as in SQL Server in order to print apostrophe we need to use double apostrophe
.The entire query becomes
Select * from Users where UserName='Sachin''--'and Password='XYZ'
In this way it prevents SQL injection attack.
So the conclusion is that, always use parameterized queries or stored procedures, to avoid sql injection attacks.