This article is contributed. See the original author and article here.

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

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.