SQL Injection In Dynamic SQL


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.


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

  1. CREATE TABLE[dbo].[Employee](  
  2. [Id][int]IDENTITY(1,1)NOT NULL,  
  3. [Name][varchar](50)NOT NULL,  
  4. [Salary][money]NULL,  
  5. [EmailAddress][varchar](255)NULL,  
  6. [PhoneNumber][varchar](50)NULL,  
  7. [Address][varchar](max)NULL,  
  9. (  
  10. [Id]ASC  
  14. INSERT[dbo].[Employee]([Name],[Salary],[EmailAddress],[PhoneNumber],[Address])VALUES (N'Jignesh', 10000.0000,N'[email protected]',N'123',N'test')  
  15. GO  
  16. INSERT[dbo].[Employee]( Name],[Salary],[EmailAddress],[PhoneNumber],[Address])VALUES (N'Tejas', 10000.0000,N'[email protected]',N'123',N'test')  
  17. GO  
  18. 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
  1. CREATE PROCEDURE GetEmployeeDetails(@Name VARCHAR(50))  
  2. AS  
  3. BEGIN  
  4. DECLARE @sqlcmd NVARCHAR(MAX);  
  5. SET @sqlcmd = N'SELECT * FROM Employee WHERE Name = ''' + @Name + '''';  
  7. EXECUTE(@sqlcmd)  
  8. END  
  10. --Execute the above stored procedure  
  11. DECLARE @name VARCHAR(50) = ‘Jignesh’  
  12. 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.



The solution is to use parameterized query and use sp_executesql stored procedure ro execute dynamic SQL. So alter procedure is look like,
  1. ALTER PROCEDURE GetEmployeeDetails(@Name VARCHAR(50))  
  2. AS  
  3. BEGIN  
  4. DECLARE @sqlcmd NVARCHAR(MAX);  
  5. DECLARE @params NVARCHAR(MAX);  
  6. SET @sqlcmd = N'SELECT * FROM Employee WHERE Name = @Name';  
  7. SET @params = N'@Name NVARCHAR(50)';  
  8. EXECUTE sp_executesql @sqlcmd, @params, @Name;  
  9. END  

If we are using dynamic SQL with C# code, we can still use parameter.
  1. SqlConnection conn = new SqlConnection("connection string");  
  2. SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM Employee WHERE Name = @Name", conn);  
  3. SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50);  
  4. 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 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:

Up Next
    Ebook Download
    View all
    View all