SQL Injection In Dynamic SQL

Datetime:2016-08-23 00:16:46          Topic: SQL  SQL Injection           Share


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 variable that are used with SQL query

To run malicious SQL queries on database server, attacker first find 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.


Suppose, I have Employee Table and it have some dummy data. To retrieve particular employee record, I have written stored procedure, which except name and based on username, it 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,  
INSERT[dbo].[Employee]([Name],[Salary],[EmailAddress],[PhoneNumber],[Address])VALUES (N'Jignesh', 10000.0000,N'trest@gamil.com',N'123',N'test')  
INSERT[dbo].[Employee]( Name],[Salary],[EmailAddress],[PhoneNumber],[Address])VALUES (N'Tejas', 10000.0000,N'trest@gamil.com',N'123',N'test')  
INSERT[dbo].[Employee]([Name],[Salary],[EmailAddress],[PhoneNumber],[Address])VALUES (N'Rakesh', 10000.0000,N'trest@gamil.com',N'123',N'test')  

Stored procedure with dynamic query

CREATE PROCEDURE GetEmployeeDetails(@Name VARCHAR(50))  
SET @sqlcmd = N'SELECT * FROM Employee WHERE Name = ''' + @Name + '''';  
--Execute the above stored procedure  
DECLARE @name VARCHAR(50) = ‘Jignesh’  
EXEC GetEmployeeDetails @name  

Ohh, great! This work as expected.

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


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))  
SET @sqlcmd = N'SELECT * FROM Employee WHERE Name = @Name';  
SET @params = N'@Name NVARCHAR(50)';  
EXECUTE sp_executesql @sqlcmd, @params, @Name;  

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";  


Injected code is syntactically correct SQL query. As a prevention action, we can also validate our input. Some of the Prevention actions are mention 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 :

About List