Injection enabled by data Truncation

PS: From the BLOG of heige, we can see that the problem lies in QUOTENAME () and REPLACE (). I jumped to Microsoft and found it.

Injection enabled by data Truncation
If any dynamic Transact-SQL statement assigned to a variable is larger than the buffer allocated to the variable, it is truncated. If an attacker can forcibly execute a statement truncation by passing an unexpected length string to the stored procedure, the attacker can perform this operation. For example, the stored procedure created by the following script is vulnerable to injection attacks 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

-- 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 128 characters to the buffer zone with 154 characters, attackers can set a new sa password without knowing the old password.

EXEC sp_MySetPassword sa, dummy, 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012

Therefore, a large buffer zone should be used for command variables, or dynamic Transact-SQL statements should be executed directly in the EXECUTE statement.

Truncation when QUOTENAME (@ variable,) and REPLACE () are used
If the string returned by QUOTENAME () and REPLACE () exceeds the allocated space, the string is automatically truncated. The stored procedure created in the following example shows possible situations.

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 th 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 sets the passwords of all users to the values passed in the previous code.

EXEC sp_MyProc --, dummy, 12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678

When using REPLACE (), you can force string truncation by exceeding the allocated buffer space. The stored procedure created in the following example shows possible situations.

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 th 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... [2, 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

Like QUOTENAME (), you can avoid string truncation caused by REPLACE () by declaring a temporary variable that is large enough for all cases. QUOTENAME () or REPLACE () should be called directly in dynamic Transact-SQL as far as possible (). Alternatively, you can calculate the required buffer size as follows. For @ outbuffer = QUOTENAME (@ input), the size of @ outbuffer should be 2 * (len (@ input) + 1 ).. When using REPLACE () and double quotation marks (as in the previous example), the buffer size of 2 * len (@ input) is sufficient.

The following calculations cover all situations:

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
When the SQL Server security object name is passed to a statement in the form of QUOTENAME (@ variable,]), truncation may occur. The following code shows this possibility.

Create procedure sp_MyProc

@ Schemaname sysname,

@ Tablename sysname,

AS

-- Declare a variable as sysname. The variable will be 128 characters.

-- But @ objectname actually must accommodate 2*258 + 1 characters.

DECLARE @ objectname sysname

SET @ objectname = QUOTENAME (@ schemaname) +. + QUOTENAME (@ tablename)

-- Do some operations.

GO

When you concatenate values of the sysname type, use a temporary variable that is large enough to save up to 128 characters for each value. QUOTENAME () should be called directly in dynamic Transact-SQL as far as possible (). Alternatively, you can calculate the required buffer size as described in the previous section.