You should review all code that calls EXECUTE, EXEC, or sp_executesql. You can use queries similar to the following to help you identify procedures that contain these statements. This query checks for 1, 2, 3, or 4 spaces after the words EXECUTE or EXEC.
SELECT object_Name(id) FROM syscomments
WHERE UPPER(text) LIKE '%EXECUTE (%'
OR UPPER(text) LIKE '%EXECUTE (%'
OR UPPER(text) LIKE '%EXECUTE (%'
OR UPPER(text) LIKE '%EXECUTE (%'
OR UPPER(text) LIKE '%EXEC (%'
OR UPPER(text) LIKE '%EXEC (%'
OR UPPER(text) LIKE '%EXEC (%'
OR UPPER(text) LIKE '%EXEC (%'
OR UPPER(text) LIKE '%SP_EXECUTESQL%'
Wrapping Parameters with QUOTENAME() and REPLACE()
In each selected stored procedure, verify that all variables that are used in dynamic Transact-SQL are handled correctly. Data that comes from the input parameters of the stored procedure or that is read from a table should be wrapped in QUOTENAME() or REPLACE(). Remember that the value of @variable that is passed to QUOTENAME() is of sysname, and has a maximum length of 128 characters.
Name of a securable
|
QUOTENAME(@variable)
|
String of ≤ 128 characters
|
QUOTENAME(@variable, '''')
|
String of > 128 characters
|
REPLACE(@variable,'''', '''''')
|
When you use this technique, a SET statement can be revised as follows:
--Before:
SET @temp = N'select * from authors where au_lname='''
+ @au_lname + N''''
--After:
SET @temp = N'select * from authors where au_lname='''
+ REPLACE(@au_lname,'''','''''') + N''''
Injection Enabled by Data Truncation
Any dynamic Transact-SQL that is assigned to a variable will be truncated if it is larger than the buffer allocated for that variable. An attacker who is able to force statement truncation by passing unexpectedly long strings to a stored procedure can manipulate the result. For example, the stored procedure that is created by the following script is vulnerable to injection enabled by truncation.
CREATE PROCEDURE sp_MySetPassword
@loginname sysname,
@old sysname,
@new sysname
AS
-- Declare variable.
-- Note that the buffer here is only 200 characters long.
DECLARE @command varchar(200)
-- Construct the dynamic Transact-SQL.
-- In the following statement, we need a total of 154 characters
-- to set the password of 'sa'.
-- 26 for UPDATE statement, 16 for WHERE clause, 4 for 'sa', and 2 for
-- quotation marks surrounded by QUOTENAME(@loginname):
-- 200 – 26 – 16 – 4 – 2 = 154.
-- But because @new is declared as a sysname, this variable can only hold
-- 128 characters.
-- We can overcome this by passing some single quotation marks in @new.
SET @command= 'update Users set password=' + QUOTENAME(@new, '''') + ' where username=' + QUOTENAME(@loginname, '''') + ' AND password = ' + QUOTENAME(@old, '''')
-- Execute the command.
EXEC (@command)
GO
By passing 154 characters into a 128 character buffer, an attacker can set a new password for sa without knowing the old password.
EXEC sp_MySetPassword 'sa', 'dummy', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012'''''''''''''''''''''''''''''''''''''''''''''''''''
For this reason, you should use a large buffer for a command variable or directly execute the dynamic Transact-SQL inside the EXECUTE statement.
Truncation When QUOTENAME(@variable, '''') and REPLACE() Are Used
Strings that are returned by QUOTENAME() and REPLACE() will be silently truncated if they exceed the space that is allocated. The stored procedure that is created in the following example shows what can happen.
CREATE PROCEDURE sp_MySetPassword
@loginname sysname,
@old sysname,
@new sysname
AS
-- Declare variables.
DECLARE @login sysname
DECLARE @newpassword sysname
DECLARE @oldpassword sysname
DECLARE @command varchar(2000)
-- In the following statements, the data stored in temp variables
-- will be truncated because the buffer size of @login, @oldpassword,
-- and @newpassword is only 128 characters, but QUOTENAME() can return
-- up to 258 characters.
SET @login = QUOTENAME(@loginname, '''')
SET @oldpassword = QUOTENAME(@old, '''')
SET @newpassword = QUOTENAME(@new, '''')
-- Construct the dynamic Transact-SQL.
-- If @new contains 128 characters, then @newpassword will be '123... n
-- where n is the 127th character.
-- Because the string returned by QUOTENAME() will be truncated,
-- it can be made to look like the following statement:
-- UPDATE Users SET password ='1234. . .[127] WHERE username=' -- other stuff here
SET @command = 'UPDATE Users set password = ' + @newpassword
+ ' where username =' + @login + ' AND password = ' + @oldpassword;
-- Execute the command.
EXEC (@command)
GO
Therefore, the following statement will set the passwords of all users to the value that was passed in the previous code.
EXEC sp_MyProc '--', 'dummy', '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678'
You can force string truncation by exceeding the allocated buffer space when you use REPLACE(). The stored procedure that is created in the following example shows what can happen.
CREATE PROCEDURE sp_MySetPassword
@loginname sysname,
@old sysname,
@new sysname
AS
-- Declare variables.
DECLARE @login sysname
DECLARE @newpassword sysname
DECLARE @oldpassword sysname
DECLARE @command varchar(2000)
-- In the following statements, data will be truncated because
-- the buffers allocated for @login, @oldpassword and @newpassword
-- can hold only 128 characters, but QUOTENAME() can return
-- up to 258 characters.
SET @login = REPLACE(@loginname, '''', '''''')
SET @oldpassword = REPLACE(@old, '''', '''''')
SET @newpassword = REPLACE(@new, '''', '''''')
-- Construct the dynamic Transact-SQL.
-- If @new contains 128 characters, @newpassword will be '123...n
-- where n is the 127th character.
-- Because the string returned by QUOTENAME() will be truncated, it
-- can be made to look like the following statement:
-- UPDATE Users SET password='1234…[127] WHERE username=' -- other stuff here
SET @command= 'update Users set password = ''' + @newpassword + ''' where username='''
+ @login + ''' AND password = ''' + @oldpassword + '''';
-- Execute the command.
EXEC (@command)
GO
As with QUOTENAME(), string truncation by REPLACE() can be avoided by declaring temporary variables that are large enough for all cases. When possible, you should call QUOTENAME() or REPLACE() directly inside the dynamic Transact-SQL. Otherwise, you can calculate the required buffer size as follows. For @outbuffer = QUOTENAME(@input), the size of @outbuffer should be 2*(len(@input)+1). When you use REPLACE() and doubling quotation marks, as in the previous example, a buffer of 2*len(@input) is enough.
The following calculation covers all cases:
While len(@find_string) > 0, required buffer size =
round(len(@input)/len(@find_string),0) * len(@new_string)
+ (len(@input) % len(@find_string))
Truncation When QUOTENAME(@variable, ']') Is Used
Truncation can occur when the name of a SQL Server securable is passed to statements that use the form QUOTENAME(@variable, ']'). The following example shows this.
CREATE PROCEDURE sp_MyProc
@schemaname sysname,
@tablename sysname,
AS
-- Declare a variable as sysname. The variable will be 128 characters.
-- But @objectname actually must allow for 2*258+1 characters.
DECLARE @objectname sysname
SET @objectname = QUOTENAME(@schemaname)+'.'+ QUOTENAME(@tablename)
-- Do some operations.
GO
When you are concatenating values of type sysname, you should use temporary variables large enough to hold the maximum 128 characters per value. If possible, call QUOTENAME() directly inside the dynamic Transact-SQL. Otherwise, you can calculate the required buffer size as explained in the previous section.
