I want to share with you here in this article an example of SQL Injection, how it can be used to access sensitive data and harm the database, and what are the recommendations and steps that can be done to protect your application or website from being vulnerable to SQL Injection.
I created a simple web site with a few lines of code:
- Added System.Data and System.Data.SqlClient Namespaces.
- Create connection, command and Data Adapter objects to execute an SQL command and fill the data table object.
- The command is a Select command query on one of database tables and the result set is filtered by email address, the value that is entered by the user before hitting search button.
- The result will be shown on a grid view object on the page.
The Web Page code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
namespace SqlInjection
{
public partial class _Default : Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void BtnSearch_Click(object sender, EventArgs e)
{
string connetionString;
System.Data.SqlClient.SqlConnection cnn;
connetionString = @"Data Source=xxx.database.windows.net;Initial Catalog=xxx;User ID=xxx ;Password=xxx ";
cnn = new SqlConnection(connetionString);
cnn.Open();
SqlCommand command = new SqlCommand("SELECT customerid as ID,Firstname + ' ' + lastname as Name,companyname as Company, emailaddress as Email,phone FROM saleslt.customer WHERE EmailAddress = '" + txtEmail.Text + "'", cnn);
SqlDataAdapter MyDataadapter;
MyDataadapter = new SqlDataAdapter(command);
command.Parameters[0].Value = txtEmail.Text;
command.ExecuteScalar();
DataTable Datatbl;
Datatbl = new DataTable();
MyDataadapter.Fill(Datatbl);
GridView.DataSource = Datatbl;
GridView.DataBind();
cnn.Close();
}
}
}
The application is working fine and retrieves data from the database as below screenshot:
But, if I change the email address value to be ‘ or 1=1 or 1=’ instead of any email address, I will get all the data of the "customers" table, as below screenshot:
If I try something else, like searching for example: ' or 1=2 union select object_id,name,schema_name(schema_id), name , name from sys.tables; select 0 where 1= '
Here I did not get the query’s table data, I added union statement to get database tables names using sys.tables system view, and I've got the following result:
Now I am able to simply get the list of all database tables and view any table I want, using same SQL injection scenario.
Also, I tried to insert the value : ' or 1=2; truncate table dbo.product; select 0 where 1= ' ,and I was able to truncate the product table.
The Queries that have been executed on the database are:
(@0 nvarchar(110))SELECT customerid as ID,Firstname + ' ' + lastname as Name,companyname as Company, emailaddress as Email,phone FROM saleslt.customer WHERE EmailAddress = '' or 1=2 union select object_id,name,schema_name(schema_id), name , name from sys.tables; select 0 where 1= ''
(@0 nvarchar(58))SELECT customerid as ID,Firstname + ' ' + lastname as Name,companyname as Company, emailaddress as Email,phone FROM saleslt.customer WHERE EmailAddress = '' or 1=2; truncate table dbo.product; select 0 where 1= ''
How to avoid SQL Injection:
Use Parameters:
I Modified my C# code and added the required parameter to the SQL Command as the following:
protected void BtnSearch_Click(object sender, EventArgs e)
{
string connetionString;
System.Data.SqlClient.SqlConnection cnn;
connetionString = @"Data Source=xxx.database.windows.net;Initial Catalog=xxx;User ID=xxx ;Password=xxxxx ";
cnn = new SqlConnection(connetionString);
cnn.Open();
SqlCommand command = new SqlCommand("SELECT customerid as ID,Firstname + ' ' + lastname as Name,companyname as Company, emailaddress as Email,phone FROM saleslt.customer WHERE EmailAddress = @0", cnn);
command.Parameters.Add(new SqlParameter("0", 1));
SqlDataAdapter MyDataadapter;
MyDataadapter = new SqlDataAdapter(command);
command.Parameters[0].Value = txtEmail.Text;
command.ExecuteScalar();
DataTable Datatbl;
Datatbl = new DataTable();
MyDataadapter.Fill(Datatbl);
GridView.DataSource = Datatbl;
GridView.DataBind();
cnn.Close();
}
Now, if I try the SQL injection it is not working any more, it is giving no result at all:
Whatever the value I write on the email text box, the query that is executed on the database is always the following:
(@0 nvarchar(26))SELECT customerid as ID,Firstname + ' ' + lastname as Name,companyname as Company, emailaddress as Email,phone FROM saleslt.customer WHERE EmailAddress = @0
Microsoft Defender:
Microsoft Defender for Cloud - an introduction | Microsoft Docs
Microsoft Defender for Cloud (Azure Security center) can detect such attacks and notify the customer, I received the following email alert:
|
Give the Application the minimum required permissions:
In the example I shared, the attacker was able to get any data he wants, table names and even was able to truncate or drop tables and more. Maybe it is easier to give permissions as sysadmin or db_owner in one step, but it recommended to give only required permissions (execute permission for example) and only on specific objects required by the application.
Use Application to validate data:
In my web page, the user should use the email address to search for data, it should have an expression special for the email address, and it could not contain spaces and part like 1=1 or .
I added a "Reqular expression Validator" object to the page and linked it to the text box I use for the email address.
Below is the validation expression for the email address:
Now I am not able to run the SQL injection again, I get a validation error instead:
