How to Prevent SQLInjection

The estimated reading time for this post is 5 minutes

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)) <= 12
			SET @script += 'Firstname = ' + QUOTENAME(@firstname,'''');
		ELSE
			SET @script += ' AND Firstname = ' + QUOTENAME(@firstname,'''');
	END
 
	IF(@Lastname IS NOT null)
	BEGIN
		IF(SELECT DATALENGTH(@script)) <= 12
			SET @script += 'Lastname = ' + QUOTENAME(@Lastname,'''');
		ELSE
			SET @script += ' AND Lastname = ' + QUOTENAME(@Lastname,'''');
	END
 
	IF(@Country IS NOT null)
	BEGIN
		IF(SELECT DATALENGTH(@script)) <= 12
			SET @script += 'Country = ' + QUOTENAME(@Country,'''');
		ELSE
			SET @script += ' AND Country = ' + QUOTENAME(@Country,'''');
	END
 
	PRINT @script;
 
	EXEC ('Select * from dbo.employees '+@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";
 
            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. 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn