SQL Injection In Dynamic SQL

Introduction

SQL injection is an idea that malicious users can inject SQL command into SQL Query from the input control of the page. SQL injection allows attacker with unauthorized access to delete / change sensitive data, modify SQL server settings, etc. SQL injection is direct insertion of code into the input variables  that are used with SQL query

To run malicious SQL queries on database server, the attacker first finds the input control that value is part of SQL query and at this point he/she introduced malicious SQL queries to break existing functionality of application.

Example

Suppose I have an Employee Table and it has some dummy data. To retrieve a  particular employee record, I have written stored procedure, which except for name and based on username, will return employee data.

Table Definition and dummy data creation script

CREATE TABLE[dbo].[Employee](
[Id][int]IDENTITY(1,1)NOT NULL,
[Name][varchar](50)NOT NULL,
[Salary][money]NULL,
[EmailAddress][varchar](255)NULL,
[PhoneNumber][varchar](50)NULL,
[Address][varchar](max)NULL,
CONSTRAINT[PK_Employee_1]PRIMARY KEY CLUSTERED
(
[Id]ASC
)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]
)ON[PRIMARY]TEXTIMAGE_ON[PRIMARY]

INSERT[dbo].[Employee]([Name],[Salary],[EmailAddress],[PhoneNumber],[Address])VALUES (N'Jignesh', 10000.0000,N'[email protected]',N'123',N'test')
GO
INSERT[dbo].[Employee]( Name],[Salary],[EmailAddress],[PhoneNumber],[Address])VALUES (N'Tejas', 10000.0000,N'[email protected]',N'123',N'test')
GO
INSERT[dbo].[Employee]([Name],[Salary],[EmailAddress],[PhoneNumber],[Address])VALUES (N'Rakesh', 10000.0000,N'[email protected]',N'123',N'test')

Stored procedure with dynamic query

CREATE PROCEDURE GetEmployeeDetails(@Name VARCHAR(50))
AS
BEGIN
DECLARE @sqlcmd NVARCHAR(MAX);
SET @sqlcmd = N'SELECT * FROM Employee WHERE Name = ''' + @Name + '''';

EXECUTE(@sqlcmd)
END

--Execute the above stored procedure
DECLARE @name VARCHAR(50) = ‘Jignesh’
EXEC GetEmployeeDetails @name

Ohh, great! This work as expected.



Now, I have to change input value and it breaks our existing functionality. Now my query returns all the rows of employee table. This is called SQL injection attack.



Solution

The solution is to use parameterized query and use sp_executesql stored procedure ro execute dynamic SQL. So alter procedure is look like,

ALTER PROCEDURE GetEmployeeDetails(@Name VARCHAR(50))
AS
BEGIN
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @sqlcmd = N'SELECT * FROM Employee WHERE Name = @Name';
SET @params = N'@Name NVARCHAR(50)';
EXECUTE sp_executesql @sqlcmd, @params, @Name;
END

If we are using dynamic SQL with C# code, we can still use parameter.

SqlConnection conn = new SqlConnection("connection string");
SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM Employee WHERE Name = @Name", conn);
SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50);
parm.Value = "Jignesh";

Summary

Injected code is syntactically correct SQL query. As a prevention action, we can also validate our input. Some of the Prevention actions are mentioned below,

 

• Do not build T-SQL statements directly from user input.
• Doesn't concat user input string to input which are used in validation because concatenation is main entry point of script injection.
• Inspect input variables and accept only expected values. Do not accept value, which contain escape sequences, and comment characters.
• Do not accept character like semicolon (;), single quote ('), comment line delimiter of SQL (--)
• When worked with XML document, validate all data against schema.

Read more articles on SQL:

• SQL Basic Queries
• Full Text Search in SQL