SQL Injection In Dynamic SQL

Introduction

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

To run malicious SQL queries on a database server, the attacker first finds the input control that value is part of the SQL query. At this point, they introduce malicious SQL queries to break the application's existing functionality.

Example

Suppose I have an Employee Table, and it has some dummy data. To retrieve a  particular employee record, I have written a 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 'trest@gamil.com', 
  N '123', N 'test'
)    GO   INSERT[dbo].[Employee](
   Name], [Salary], [EmailAddress], 
  [PhoneNumber], [Address]
) VALUES (
  N 'Tejas',  10000.0000, N 'trest@gamil.com', 
  N '123', N 'test'
)    GO   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)
)    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 worked as expected.

work

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

injection

Solution

The solution is to use parameterized query and sp_executesql stored procedure to execute dynamic SQL. So altered procedure looks like this,

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  

table

We can still use parameters if we use dynamic SQL with C# code.

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 the 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.
  • It doesn't concat the user input string to the input used in validation because concatenation is the main entry point of script injection.
  • Inspect input variables and accept only expected values. Do not accept value, which contains escape sequences, and comment characters.
  • Do not accept characters like semicolons (;), single quote ('), comment line delimiter of SQL (--)
  • When working with XML documents, validate all data against the schema.

Read more articles on SQL

Recommended Ebook

SQL Queries For Beginners

Download Now!
Similar Articles