SQl Injection: example of SQL Injections and Recommendations to avoid it.

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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:

  1. Added System.Data and System.Data.SqlClient Namespaces.
  2. Create connection, command and Data Adapter objects to execute an SQL command and fill the data table object.
  3. 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.
  4. 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:

Picture1.jpg

 

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:

Picture2.jpg

 

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:

Picture3.jpg

 

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:

Picture4.jpg

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:

 

tarashee_0-1641745024671.png

 

 

 

 

MEDIUM SEVERITY

 

 

 

An application generated a faulty SQL statement on database ‘xxxx’. This may indicate that the application is vulnerable to SQL injection.

 

 

 

 

Activity details

Severity

Medium

Subscription ID

xxx

Subscription name

xxx

Server

xx

Database

xx

IP address

81.xx.xx.xx

Principal name

tr*****

Application

.Net SqlClient Data Provider

Date

November 28, 2021 14:50 UTC

Threat ID

2

Potential causes

Defect in application code constructing faulty SQL statements; application code doesn't sanitize user input and may be exploited to inject malicious SQL statements.

Investigation steps

For details, view the alert in the Azure Security Center.
To investigate further, analyze your audit log.

Remediation steps

Read more about SQL Injection threats, as well as best practices for writing safe application code. Please refer to Security Reference: SQL Injection.

 

 

 

 

 

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:

Picture5.jpg

 

Now I am not able to run the SQL injection again, I get a validation error instead:

Picture6.jpg

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.