New SQL Truncation Attack and Defense methods

The SQL injection vulnerability attacks have aroused widespread concern because they can penetrate the firewall and Intrusion Detection System to damage your data layer. Whether it is the first or second-level injection attack, if you look at the basic code mode, it is similar to any other injection attack problems, that is, you use untrusted data when constructing statements. Most developers have begun to reduce these vulnerabilities by using parameterized SQL queries and stored procedures on the backend. However, in some cases, developers still use dynamically constructed SQL statements, for example, when a Data Definition Language (DDL) Statement is constructed based on user input or applications written in C/C ++.

In this article, I will discuss some new ideas. The results may modify SQL statements or inject SQL code, even if the code escapes the delimiter. First, I will introduce some best practices for building separated identifiers and SQL strings. Then I will introduce several new methods for attackers to inject SQL code to help you protect your applications.

Delimiter between identifiers and strings

In SQL Server, there are two types of string variables: the unique SQL identifier that can recognize SQL objects (such as tables, views, and stored procedures), and the string used to represent data. The method for separating SQL identifiers is different from that for separating data strings. We will discuss the best practices for using dynamic SQL build methods for these data variables.

If the SQL object name uses keywords, or the object name contains special characters, you need to use a separator identifier. To delete the my_dbreader login name, run the following statement:

DROP LOGIN my_dbreader

What if you try to delete a login name that uses drop as its name (also a keyword? If you use the following SQL statement, SQL Server Returns a syntax error.

DROP LOGIN DROP

What if you want to delete a login name like my] [dbreader? This will also cause syntax errors.

In the above two examples, because the login name is a keyword or contains special characters, you need to provide some start and end tags so that SQL server can recognize the object name in SQL statements.

You can use double quotation marks or square brackets as the separator for SQL identifiers. When quoted_identifier is enabled (a connection-based setting), you can only use double quotation marks. We recommend that you always use square brackets for simplicity.

To successfully Delete the drop login name, you can use square brackets to construct your SQL statement:

DROP LOGIN [DROP]

But what are the following statements?

DROP LOGIN [my][dbreader]

In this special case, because the login name my] [dbreader contains delimiter characters, SQL considers [my] As the login name because it is contained in square brackets. Because [dbreader] is behind the login name, this statement does not constitute a correct SQL statement and may cause syntax errors. You can solve this problem by escaping the upper right square brackets with another right square brackets. Therefore, if you execute the following statement, SQL Server will successfully delete my] [dbreader Logon Name:

DROP LOGIN [my]][dbreader]

The escape mechanism only doubles the appearance times of the right square brackets. You do not need to change other characters, including left square brackets.

The separator string is similar to the SQL identifier to be separated. The main difference is the separator character to be used. Before introducing rules similar to constructed separator strings, let's take a look at the following examples.

Assume that you want to create a dbreader login name with the password P @ $ w0rd. You will use the following SQL statement:

CREATE LOGIN [dbreader] WITH PASSWORD = 'P@$$w0rd'

In this statement, P @ $ w0rd is a string data separated by single quotes. Therefore, SQL knows where the string starts and ends. But what if the string data contains single quotes? SQL Server will cause an error because the statement is invalid:

CREATE LOGIN [dbreader] WITH PASSWORD = 'P@$$'w0rd'

You need to escape all single quotes in the string to construct a valid SQL statement:

CREATE LOGIN [dbreader] WITH PASSWORD = 'P@$$''w0rd'

When you execute this statement, SQL Server creates the dbreader logon name with the password P @ $ 'w0rd.

You can also use double quotation marks as separators, but as I mentioned earlier, whether this method is successful depends on whether the quoted_identifier setting is enabled. Therefore, it is best to always use single quotes as the separator of strings.

T-SQL Functions

It can be seen that the rules for handling identifiers and strings are relatively simple. If you know this string in advance, you can manually separate it. But what if you want to build dynamic T-SQL statements based on user input? You must use an automatic method. Two T-SQL functions can help you prepare separator strings, which are quotename and replace.

Quotename returns a unicode string with a separator added to make the input string a valid identifier. The quotename function uses the following syntax:

QUOTENAME ( 'string' [ , 'delimiter' ] )

You can transmit the string to be separated and a single character string used as the separator to quotename. Separators can be square brackets, single quotes, or double quotation marks.

This function is mainly used to prepare separate SQL identifiers. Therefore, it only accepts the sysname type and is nvarchar (128) in SQL Server ). You can also use this function to separate SQL strings. However, due to parameter length restrictions, it supports a maximum of 128 characters (at this point, replace functions can be used for their purposes ).

Create procedure sys. sp_addlogin @ loginame sysname, @ passwd sysname = NULL, @ defdb sysname = 'master', @ deflanguage sysname = NULL, @ Sid varbinary (16) = NULL, @ encryptopt varchar (20) = nullas -- Setup runtime options/declare variables ---- some code ---- set @ exec_stmt = 'create login' + quotename (@ loginame ,'[') if @ passwd is nullselect @ passwd = ''if (@ encryptopt is null) set @ exec_stmt = @ exec_stmt +' With Password = '+ quotename (@ passwd, ''') else -- Some codego and later programs show how sp_addlogin uses quotename to prepare to separate login names and password strings.

As you can see, because both @ loginname and @ passwd are of the sysname type, you can use the quotename function to prepare SQL identifiers and strings to be separated.

Therefore, even if someone passes @ loginname = 'my [] dbreader' and @ passwd = 'P @ $ ''w0rd', there will be no chance of SQL injection, because quotename properly escapes the delimiter:

create login [my[]]dbreader] with password = 'P@$$''w0rd'
The replace function replaces all occurrences of a given string with the specified replacement string. Unlike quotename, the replace function has no length limit on the parameters it accepts:
REPLACE ( 'string1' , 'string2' , 'string3' )
Replace contains three strings:
String1 is the expression to be edited, string2 is the item to be replaced in string1, and string3 is the item used to replace string2.
Any string expression can contain characters or binary data.
To separate SQL strings, you can use replace to double the number of single quotes, but you need to manually add separators (single quotes at the beginning and end ).
 
create procedure sys.sp_attach_single_file_db@dbname sysname,@physname nvarchar(260)asdeclare @execstring nvarchar (4000)-- some code --select @execstring = ‘CREATE DATABASE ‘+ quotename( @dbname , ‘[‘)+ ‘ ON (FILENAME =‘+ ‘‘‘‘+ REPLACE(@physname,N’’’’,N’’’’’’)+ ‘‘‘‘+ ‘ ) FOR ATTACH’EXEC (@execstring)-- some code --GO
The preceding figure shows how sp_attach_single_file_db can use this function to prepare a file's name. Because @ physname is nvarchar (260), you cannot use quotename to prepare separator strings, which is why replace is used. Therefore, even if someone passes a string with single quotes, they cannot break SQL statements and inject any SQL code.
SQL Injection Vulnerability
Next we will introduce the stored procedure, which can change the password of the user account after verifying the current password:
Create procedure sp_setpassword @ username varchar (25), @ old varchar (25), @ new varchar (25) asdeclare @ command varchar (100) set @ command = 'Update users SET Password = ''' + @ New + ''' where username = ''' + @ username + ''' and Password = ''' + @ old + ''' exec (@ command) go
You can quickly browse the stored procedure and find that no parameters are escaped from single quotes, which is also vulnerable to SQL injection attacks. Attackers can pass several specific parameters and modify the SQL statement:
update Users set password='NewP@ssw0rd'where username='admin' --' and password='dummy'
The result is that the password for the Administrator account (or any known account) can be set without the actual password. In the T-SQL function, you can fix this code by using the replace or quotename function.
Create procedure sp_setpassword @ username varchar (25), @ old varchar (25), @ new varchar (25) as -- declare variables. declare @ command varchar (100) -- construct the dynamic sqlset @ command = 'Update users SET Password = ''' + Replace (@ new ,'''', ''''') + ''' + 'where username = ''' + Replace (@ username ,'''','''''') + ''' + 'and Password = ''' + Replace (@ old ,'''','''''') + ''' -- execute the command. exec (@ Co MmAnd) Go shows the correct code after using the replace function.
As you can see, replace doubles the appearance times of all single quotes in the parameter. Therefore, if the attacker passes the same parameter, the statement changes:
update Users set password='NewP@ssw0rd'where username='admin''--' and password='dummy'
In this way, it is not easy to be affected by common SQL Injection problems.
 

Modify by Truncation
If you pay close attention to the stored procedure shown above, you will find that the @ command variable can only store 100 characters, but when the 25 characters are single quotes, each variable of these characters can return 50 characters after being processed by the replace function. If the variable does not have a buffer that is large enough, SQL Server 2000 SP4 and SQL Server 2005 SP1 will cut data by themselves. This provides attackers with the opportunity to truncate command strings.
In this example, if someone can truncate the command after the username = 'username' expression, the account password can be changed without knowing the current password of a known user.
Suppose the attacker knows that the administrator user name exists in the Web application (this can be any user account ). Attackers need to provide a new password with a length of 41 characters so that the command length is sufficient to be truncated. The reason is that among the 100 characters used for the command, 27 characters are used for update statements, 17 characters are used for where clauses, 13 characters are used for "Administrator", and 2 characters are used for single quotation marks before and after the new password.
The attacker can pass only 25 characters as the new password. However, you can avoid this restriction by passing single quotes, because the replace function doubles the number of single quotes. Therefore, by passing 18 single quotes, 1 uppercase letter, 1 Symbol, 2 lower-case letters, and 1 number, attackers can intercept the commands following the where username = 'admin' expression. If the attacker sends ''''''''''''''''''! If abb1 is passed to the @ New Parameter and administrator is used as the username parameter, @ command is changed:
update Users set password='''''''''''''''''''''''''''''''''''''!Abb1' where username='administrator'
Create procedure sp_setpassword @ username varchar (25), @ old varchar (25), @ new varchar (25) as -- declare variables. declare @ command varchar (100) -- in the following statement, we will need 43 characters -- to set an administrator password without knowing its current password. -- 100-26-16-15 = 43 (26 for update stmt, 16 for where clause, -- 15 for 'admin '). but @ new only takes 25 characters, wh Ich we -- can get around by using single quotes. so one can pass the following -- parametes and set admin password. @ new = 18 single quotes, 1 Capital -- Letter, 1 Symbol, 2 small case letters, 1 digit -- @ username = Administrator -- @ command becomes -- Update users SET Password = ''''''''''''''''''''''' ''''''''''''''! Abb1 '-- where username = 'admin' set @ command = 'Update users SET Password =' + quotename (@ new ,'''') + 'where username = '+ quotename (@ username, ''') +' and Password = '+ quotename (@ old, ''') -- execute the command. exec (@ command) Go uses quotename instead of replace. The only difference between the above example and this example is that in the above example, the developer adds a single quotation mark separator for the user name, new password, and old password. In this example, the quotename function is added. As the data provided by the user remains unchanged, the same attack string used in the preceding example can still be exploited by attackers.
 
DWORD changepassword (char * psusername, char * psold, char * psnew) {char * character scapedusername = NULL; char * character scapedoldpw = NULL; char * character scapednewpw = NULL; char szsqlcommand [100]; hresult hR = 0; // Input Validation... // calculate and allocate the new buffer with length // userdatalen * 2 + 1 // escape all single quotes with double quotes... // construct the queryhr = stringcchprintf (szsqlcommand, sizeof (szsqlco MmAnd)/sizeof (char), "Update users SET Password = '% s' where username =' % S'" "And Password = '% s', using scapednewpw, using scapedusername, psescapedoldpw); If (s_ OK! = HR) {// handle error cases} // execute and return} is the abbreviated version of the C/C ++ function written in the middle layer application, which can implement the same functions. It is vulnerable to the same attacks.

SQL Injection by Truncation

Create procedure sp_setpassword @ username varchar (25), @ old varchar (25), @ new varchar (25) as -- declare variables. declare @ escaped_username varchar (25) Declare @ brief varchar (25) Declare @ brief varchar (25) Declare @ command varchar (250) set @ escaped_username = Replace (@ username, ''', ''') set @ escaped_oldpw = Replace (@ old ,'''','''''') set @ escaped_newpw = Replace (@ new, ''', ''') set @ C Ommand = 'Update users SET Password = ''' + @ escaped_newpw + ''' + 'where username = ''' + @ escaped_username + ''' + 'and Password = ''' + @ escaped_oldpw + ''' exec (@ command) go shows another variant of the same Code, but it can be fixed using a separate variable. It can be seen that this code stores the escaped string in a separate variable, and @ command has enough buffer to store the entire string. @ Escaped_username, @ escaped_oldpw, and @ escaped_newpw are declared as varchar (25), but if all characters in @ username, @ old, and @ new are 25 single quotes, they must be 50 characters long. This creates an opportunity to truncate the converted string.
Attackers can pass 123... 'N' as the new password, where n is 24th characters, making @ escaped_newpw 123... n' (the second single quotation mark character returned by the replace function will be truncated), so that the final query is as follows. Attackers can inject code through the User Name field to use this query:
update users set password='123...n''where username='<SQL Injection here using Username>
This code mode is more dangerous because it provides an opportunity to inject SQL code, not just trunking existing SQL statements.
ALTER PROCEDURE sp_setPassword@username varchar(25),@old varchar(25),@new varchar(25)AS-- Declare variables.DECLARE @quoted_username varchar(25)DECLARE @quoted_oldpw varchar(25)DECLARE @quoted_newpw varchar(25)DECLARE @command varchar(250)-- In the following statements, all the variables can only hold-- 25 characters, but quotename() will return 52 characters when all-- the characters are single quotes.SET @quoted_username = QUOTENAME(@username, ‘‘‘‘)SET @quoted_oldpw = QUOTENAME(@old, ‘‘‘‘)SET @quoted_newpw = QUOTENAME(@new, ‘‘‘‘)-- By passing the new password as 123...n where n is 24th character,-- @quoted_newpw becomes ‘123..n-- Observe carefully that there is no trailing single quote as it gets-- truncated.-- So the final query becomes something like this-- update users set password=‘123...n where username=‘ <SQL Injection-- here using Username>SET @command= ‘update Users set password=‘ + @quoted_newpw +‘ where username=‘ + @quoted_username +‘ AND password = ‘ + @quoted_oldpwEXEC (@command)GO
Another example of using the same variant of the quotename function instead of replace is provided. Because the quotename function requires a separator, the load is different, but it is still vulnerable to SQL injection attacks.
In this example, the code stores the separated strings in separate variables, and @ command has enough buffer to store the entire command string. As shown in the preceding example, the problem lies in the referenced variables @ quoted_username, @ quoted_oldpw, and @ quoted_newpw. They are declared as varchar (25), but if all the characters in @ username, @ old, and @ new are 25 single quotes, they need to store 52 characters. (Quotename also adds the start and end delimiters .) This creates an opportunity for attackers to intercept separated strings.
Attackers can pass 123... N (where N is 24th characters) as the new password, so that @ escaped_newpw also becomes '100... N (the start single quotes are added by quotename), so that the final query is as follows. Attackers can inject code through the username field to use this query:
update users setpassword='123...n whereusername=' <SQL Injection here using Username>
DWORD changepassword (char * psusername, char * psold, char * psnew) {char szescapedusername [26]; char szescapedoldpw [26]; char szescapednewpw [26]; char szsqlcommand [250] // Input Validation // escape user supplied datareplace (psusername, "'", "'' ", szescapedusername, sizeof (szescapedusername); Replace (pspassword ,"'", "'' ", szescapedoldpw, sizeof (szescapedoldpw); Replace (pspassword," '"," ''", szescapednewpw , Sizeof (szescapednewpw); // construct the querystringcchprintf (szsqlcommand, sizeof (szsqlcommand)/sizeof (char ), "Update users SET Password = '% s' where username =' % S'" "And Password = '% s', szescapednewpw, szescapedusername, szescapedoldpw ); // execute and return} is the abbreviated version of this Code in C/C ++, which can implement the same function. It is also vulnerable to the same attacks.
Although I'm using T-SQL code in the demo, you don't actually need to use dynamic SQL to construct a data operation language (DML) statement, therefore, most applications that contain DML Code are not prone to these problems.
Next, let's look at another example of constructing a dynamic DDL statement based on user input, as shown in
Create procedure sys. sp_password @ old sysname = NULL, -- the old (current) password @ new sysname, -- the new password @ loginame sysname = NULL -- user to change password onas -- Setup runtime options/declare variables -- set nocount ondeclare @ exec_stmt nvarchar (4000) declare @ escaped_oldpw sysnamedeclare @ escaped_newpw sysnameset @ escaped_oldpw = quotename (@ old, '''') set @ escaped_newpw = quotename (@ ne W, ''') set @ exec_stmt = 'alter login' + quotename (@ loginame) + 'with Password =' + @ escaped_newpw + 'old_password = '+ @ escaped_oldexec (@ exec_stmt) If @ error <> 0 return (1) -- Return success -- Return (0) -- sp_password. As in the previous example, the following statement also has the truncation problem:
set @escaped_oldpw = quotename(@old, '''')set @escaped_newpw = quotename(@new, '''')
By passing @ new = '1970... '(where, starting from 123 characters (without single quotes), the attacker is @ old ='; SQL Injection '), the SQL statement is as follows:
alter login [loginname]with password = '123... old_password = '; SQLInjection
Although stored procedures are more likely to have these problems, not all stored procedures may cause security vulnerabilities. Next we will introduce which stored procedures need to be carefully reviewed.
In SQL Server, by default, all stored procedures are executed in the caller's environment. Therefore, even if a process has an SQL injection problem, malicious local users who have execution permissions on the process cannot improve their permissions, and the injected code will be executed in the environment. However, if you have an internal maintenance script that can be executed by the computer owner or a specific user, the caller can execute the code in different user environments, and escalate the permission to this user.
All truncation problems must be bugs, but they are not necessarily security vulnerabilities. But it is best to fix these problems because you don't know who will find them and use them in the future.
You can take other measures to reduce the injection vulnerability in your SQL code. First, avoid using dynamic SQL to construct DML statements in the stored procedure. If you cannot avoid using dynamic SQL, you can use sp_executesql. Second, as described in the example in this article, you need to calculate the buffer length correctly. Finally, in the C/C ++ code, check the return value of the string operation and check whether the string has been truncated. If yes, the corresponding result is incorrect. See "Vulnerability Detection Methods" in the abstract to learn the summary of the measures you can take.
Injection Through truncation Detection
To use automated tools to detect SQL Injection by truncation issues, you need to have a clear understanding of all code modes that may produce truncation possibilities. You can use different string data for different specific code modes. Assume that N is the length of the input buffer in the following cases.
To detect the quotename separation problem, first assume that you use quotename (or a similar function used by the C/C ++ application) to prepare the separator identifier or string, and the buffer size of the separator string is less than 2 * n + 2. When the buffer length of the separator string is equal to N, to capture these problems, you can pass an unseparated long string. The tail separator will be truncated, and you will get the injection opportunity by using another input variable.
When the length of the separated buffer is an odd number, to capture these problems, you can pass a long string of single quotes character (or right square brackets or double quotes. Since quotename doubles the number of occurrences of all delimiters and adds the start separator, the trailing separator is truncated when the string buffer to be converted can only store an odd number of characters.
When the length of the separated buffer is an even number, to capture these problems, you can pass strings such as 1, 1, 1, and 1, the number of single quotes (or right square brackets) increases each iteration. Since quotename doubles the number of occurrences of all single quotes, the returned string contains an even number of single quotes, with the start separator and 1, and an even number of characters. Therefore, the separator at the end is truncated.
If replace (or a similar function used by the C/C ++ application) is used to prepare the converted string, and when the size of the converted string buffer is less than 2 * n, you can also detect the above problems. When the length of the transcoded string buffer is equal to N, to capture these problems, you can pass such problems as 1 ', 12', 123 ', and 123... the length of the input string increases progressively during each iteration. In this case, if you have reached the proper length, the replace function will add one more single quotation mark character. Since the converted string variables do not have enough buffer space, the last single quotation mark will be truncated and saved during the transfer, which provides an opportunity to break the SQL statement.
When the length of a buffer is an odd number, you must use replace to capture the problem. You can pass a single quotation mark string with an increasing length, such as '', ''', and ''''... '(Or a long string that only contains single quotes ). In this case, replace doubles the number of occurrences of all single quotes. However, because some buffer lengths are odd, the last single quotation mark will be truncated, which provides an opportunity to break the statement.
When the length of a buffer is an even number, to capture these problems, you can pass strings such as 1, 1, 1, and 1, the number of single quotes (or right square brackets) increases each iteration. The return value starts with 1 and contains an even number of characters. Therefore, the return value contains an odd number of characters. Because the buffer length is even, the single quotation marks at the end are truncated, which provides an opportunity to break SQL statements.
Vulnerability Detection Method
Use code review if you want to perform code review, you can use the following detection methods to detect problems in SQL statements.
Check level 1 or Level 2 SQL Injection

            
            

             
             
Identifies the API used to execute dynamic SQL statements.
             
             
Check whether any data verification is performed on the data used in the dynamic SQL statement.
             
             
If data verification has not been performed, check whether the data has escaped the delimiter (the string uses single quotes, and the SQL identifier uses the right square brackets.
            
            
SQL modification detected by Truncation

            
            

             
             
Check the buffer length used to store the final dynamic SQL statement.
             
             
Calculate the maximum buffer required to store SQL statements in the case of limit input, and check whether the buffer used to store SQL statements is large enough.
             
             
Pay special attention to the return values of the quotename or replace function. If the length of the input data is n characters, when all input characters are separated, the return values of these functions are 2 * n + 2 or 2 * n.
             
             
For C/C ++ applications, check whether the return values of Apis such as stringcchprintf used to prepare SQL statements have been errors with insufficient buffer areas.
            
            
SQL Injection Detected by Truncation

            
            

             
             
Check the buffer length used to store the separator string or the converted string.
             
             
If the length of the input string is N, you need a 2 * n + 2 buffer to store the return value of the quotename function. You need a 2 * n buffer to store the return value of the replace function.
             
             
For C/C ++ applications, check whether the return values of the same functions as replace have checked errors with insufficient buffer areas.
            
            
If you have an automated tool or intelligent fuzzy processing program using the black box method, you can use the following detection methods to detect problems in SQL statements.
Check SQL Injection Problems

            
            

             
             
Send single quotes as input data to capture strings that are not purified and used as dynamic SQL statements.
             
             
Use the right square brackets (] characters) as the input data to capture the use of user input in SQL identifiers without any input purification.
            
            
Detection of Truncation

            
            

             
             
Send long strings, just like sending strings to detect Buffer Overflow.
            
            
SQL modification detected by Truncation

            
            

             
             
A long string that sends single quotes (or double quotes. This will allow the replace and quotename functions to return the maximum length, and may block the command variables used to store SQL statements.