神刀安全网

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 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.

Example

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,   [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 work as expected.

SQL Injection In Dynamic SQL

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.

SQL Injection In Dynamic SQL

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   

SQL Injection In Dynamic SQL

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 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 :

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » SQL Injection In Dynamic SQL

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮