How to Prevent SQLInjection

SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker.

SQL Server injection or SQLInjection is very common in low cost software applications, which can cause an organization to lose all their data in worse case. Hackers are able to take control of entire SQL Server and Windows Server if there are additional security breach issues.

There are few database/application development mistakes that can lead us to have SQLInjection in our environment by the followings:-

Hard Coded Ad-hoc Queries

This type of queries are very common and built-in to the application software due to lack of database development skills or short period of development schedule. The following C-Sharp code snip is illustrates that.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace SQLInjectionTest
{
    class Program
    {
        static void Main(string[] args)
        {
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection("Server=.;database=SQLInjection;trusted_connection=true;");
            System.Data.SqlClient.SqlCommand com = con.CreateCommand();
            com.CommandText = @"DROP Table SQLINJECTION; 
                                Create Table SQLInjection(ID bigint Identity(1,1), ConfidentialData varchar(30) Default 'Fard solutions');";
            com.Connection.Open();
            com.ExecuteNonQuery();
 
            Console.WriteLine("Type Confidential Data: ");
            string confdata = Console.ReadLine();
 
            com.CommandText = "Insert Into SQLInjection (ConfidentialData) Values ('" + confdata + "');";
 
            try
            {
                com.Prepare();
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); }
 
            com.Dispose();
            Console.ReadKey();
        }
    }
}

The above Console screenshot shows that we use SQLInjection technique to create new table named ‘DBisInjected’ with ‘Haha’ column. That action shows how vulnerable is our application and database. To prevent such vulnerability, we are required to change the above CSharp application code with the following codes:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace SQLInjectionTest
{
    class Program
    {
        static void Main(string[] args)
        {
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection("Server=.;database=SQLInjection;trusted_connection=true;");
            System.Data.SqlClient.SqlCommand com = con.CreateCommand();
            com.CommandText = @"DROP Table SQLINJECTION; 
                                Create Table SQLInjection(ID bigint Identity(1,1), ConfidentialData varchar(30) Default 'Fard solutions');";
            com.Connection.Open();
            com.ExecuteNonQuery();
 
            Console.WriteLine("Type Confidential Data: ");
            string confdata = Console.ReadLine();
 
            //Changes are on the following two code lines;
            com.CommandText = "Insert Into SQLInjection (ConfidentialData) Values (@ConfData)";
            com.Parameters.AddWithValue("@ConfData", confdata);
 
            try
            {
                com.Prepare();
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); }
 
            com.Dispose();
            Console.ReadKey();
        }
    }
}

Dynamic Queries Within Database

In this scenario, application developer wants to provide some filtering flexibility by concatenating T-SQL commands received from application which comes with SQLInjection vulnerability issue. The following is the CSharp application codes and T-SQL scripts:-

CREATE PROCEDURE USP_DynamicQuery(@Query NVARCHAR(MAX))
AS BEGIN
	DECLARE @script NVARCHAR(MAX);
	SET @script = 'Select * From dbo.Employees '+@Query;
 
	EXEC (@script);
 
END

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
 
namespace DynamicQuery
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection("Server=.;database=SQLInjection;trusted_connection=true;");
            System.Data.SqlClient.SqlCommand com = con.CreateCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = @"USP_DynamicQuery";
            string Query = "";
 
            Query = "Where ";
 
            if(Firstname.Text.Trim() != string.Empty)
            {
                if(string.Equals(Query.Trim(),"Where"))
                    Query += string.Format("Firstname = '{0}'",Firstname.Text);
                else
                    Query += string.Format("AND Firstname = '{0}'", Firstname.Text);
            }
 
            if (Lastname.Text.Trim() != string.Empty)
            {
                if (string.Equals(Query.Trim(), "Where"))
                    Query += string.Format("Lastname = '{0}'", Lastname.Text);
                else
                    Query += string.Format(" AND Lastname = '{0}'", Lastname.Text);
            }
 
            if (Country.Text.Trim() != string.Empty)
            {
                if (string.Equals(Query.Trim(), "Where"))
                    Query += string.Format("Country = '{0}'", Country.Text);
                else
                    Query += string.Format(" AND Country = '{0}'", Country.Text);
            }
 
            com.Parameters.AddWithValue("@Query", Query);
 
            try
            {
                com.Prepare();
                com.Connection.Open();
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
 
            com.Dispose();
 
            MessageBox.Show("The Dynamic Query Executed.");
        }
    }
}

As you see, the Dynamic Query solution is also vulnerable due to TSQL command concatenation operation. To prevent SQLInjection in Dynamic SQL script, you may need to change the stored procedure and application as below:-

ALTER PROCEDURE USP_DynamicQuery(@Firstname NVARCHAR(20), @Lastname NVARCHAR(20), @Country NVARCHAR(20))
AS BEGIN
	DECLARE @concate BIT;
	DECLARE @script NVARCHAR(MAX);
	SET @script = 'Where ';
	SET @concate = 0;
	IF(@Firstname IS NOT null)
	BEGIN
		IF(SELECT DATALENGTH(@script))

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
 
namespace DynamicQuery
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection("Server=.;database=SQLInjection;trusted_connection=true;");
            System.Data.SqlClient.SqlCommand com = con.CreateCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = @"USP_DynamicQuery";
 
            if (string.IsNullOrEmpty(Firstname.Text))
                com.Parameters.AddWithValue("@Firstname", DBNull.Value);
            else
                com.Parameters.AddWithValue("@Firstname", Firstname.Text);
 
            if (string.IsNullOrEmpty(Lastname.Text))
                com.Parameters.AddWithValue("@Lastname", DBNull.Value);
            else
                com.Parameters.AddWithValue("@Lastname", Lastname.Text);
 
            if (string.IsNullOrEmpty(Country.Text))
                com.Parameters.AddWithValue("@Country", DBNull.Value);
            else
                com.Parameters.AddWithValue("@Country", Country.Text);
 
            try
            {
                com.Prepare();
                com.Connection.Open();
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
 
            com.Dispose();
 
            MessageBox.Show("The Dynamic Query Executed.");
        }
    }
}

 

The above two methods of application coding and database stored procedure coding are the most common in commercial applications. To increase the SQLInjection prevention, it is required to do some SQL Server instance and database security hardening to make sure each user has limited permission rights. I hope this blog post was informative for you. 

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments