SQL Security: New SQL Truncation Attacks And How To Avoid Them

Article
10/07/2019

SQL Security

New SQL Truncation Attacks And How To Avoid Them

Bala Neerumalla

This article discusses:

Delimiting identifiers and strings
Useful T-SQL functions
Truncation and modification attacks
Finding bugs and vulnerabilities

This article uses the following technologies:
SQL Server

Contents

Delimiting Identifiers and Strings
T-SQL Functions
SQL Injection Vulnerabilities
Modification by Truncation
SQL Injection by Truncation
Detecting Injection by Truncation

Exploits using SQL injection have drawn a lot of attention for their ability to get through firewalls and intrusion detection systems to compromise your data layers. Whether it's a first-order or second-order injection, if you look at the basic code pattern, it is similar to any other injection issue where you use untrusted data in the construction of a statement. Most developers have started mitigating these vulnerabilities in Web front ends by using parameterized SQL queries in conjunction with stored procedures at the back end, but there are some instances where developers still use dynamically constructed SQL, like in the construction of Data Definition Language (DDL) statements based on user input or for apps written in C/C++.

In this article I will discuss some new ideas that can result in either modifying SQL statements or injecting SQL code even if the code has escaped the delimiting characters. I will start with some best practices for constructing delimited identifiers and SQL literals, and then I'll show you new ways attackers can inject SQL code in order to help you protect your applications.

Delimiting Identifiers and Strings

In SQL Server™, there are two kinds of string variables: SQL identifiers that uniquely identify SQL objects like tables, views, and stored procedures, and literal strings that represent data. The way you delimit SQL identifiers is different from the way you delimit data strings. We'll discuss the best practices for constructing dynamic SQL that needs to use these data variables.

You will need to use delimited identifiers if keywords are used for the SQL object names or if special characters are contained in the object names. Say you want to drop a login with the name my_dbreader. To do so, you execute the following statement:

DROP LOGIN my_dbreader

What if you are trying to drop a login that uses DROP, also a keyword, as its name? If you use the following SQL statement, SQL Server will return an incorrect syntax error.

DROP LOGIN DROP

What if you want to drop a login with a name like my][dbreader? This will also throw an incorrect syntax error.

In both examples, because the login name is either a keyword or contains special characters, you will need to provide some beginning and ending markings so that SQL Server can identify the object name inside the SQL statement.

You can use either double quotes or square brackets as the delimiters for SQL identifiers, but you can only use double quotes when the QUOTED_IDENTIFIER setting is on, which is a connection-based setting. To keep things simple, it's a good practice to always use square brackets.

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

DROP LOGIN [DROP]

But what about the following statement?

DROP LOGIN [my][dbreader]

In this particular case, since the login name my][dbreader contains the delimiting characters, SQL thinks that [my] is the login name as it's enclosed within square brackets. Because [dbreader] followed the login name, this doesn't form a proper SQL statement and causes a syntax error. You can fix this problem by escaping the right square bracket with another right square bracket. So if you execute the following statement, SQL Server will successfully drop the login my][dbreader:

DROP LOGIN [my]][dbreader]

The escaping mechanism is simply doubling up the occurrences of right square brackets. You don't need to do anything with other characters, including left square brackets.

Preparing delimited literals is similar to preparing delimited SQL identifiers, the main difference being the delimiting character that needs to be used. Let's look at few examples before coming up with similar rules to form a delimited string literal.

Suppose you want to create a login name dbreader with P@$$w0rd as the password. You could use the following SQL statement:

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

In this statement, P@$$w0rd is string data delimited by single quotes so SQL knows where the string begins and ends. But what happens if the string data contains single quotes? SQL Server will throw an error as the statement will not be valid:

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

You will need to escape all the occurrences of single quotes within the string to form a valid SQL statement:

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

When you execute this statement, SQL Server will create a login dbreader with P@$$'w0rd as the password.

You can also use double quotes as the delimiter, but as I mentioned previously, the success of this approach is completely dependent on whether the QUOTED_IDENTIFIER setting is on. As a result, it's a much better idea to always use single quotes as the delimiters for string literals.

T-SQL Functions

As you can see, the rules for handling identifiers and strings are relatively simple, and you can delimit them manually if you know the strings beforehand. But what if you are constructing dynamic T-SQL statements based on user input? You will need automatic ways to do this. Two T-SQL functions that can help you prepare delimited strings are QUOTENAME and REPLACE.

QUOTENAME returns a Unicode string with the delimiters added to make the input string a valid identifier. The QUOTENAME function uses this syntax:

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

You pass QUOTENAME a string to be delimited and a one-character string to use as the delimiter. The delimiter can be a square bracket or a single or double quotation mark.

This function is mainly designed for preparing delimited SQL identifiers, which is why it only accepts the type sysname, which is nvarchar(128) in SQL Server. You can also use this function to prepare delimited SQL literal strings, but because of the argument length restriction it only works for strings of 128 characters or less, (this is where the REPLACE function will be of use). Figure 1 shows how sp_addlogin uses QUOTENAME to prepare delimited login names and password strings. As you can see, because both @loginname and @passwd are sysnames, the QUOTENAME function can be used to prepare a delimited SQL identifier and a delimited literal. So even if someone passes @loginname = 'my[]dbreader' and @passwd = 'P@$$''w0rd', there won't be any SQL injection opportunities as QUOTENAME properly escapes the delimiting characters:

create login [my[]]dbreader] with password = 'P@$$''w0rd'

Figure 1 Delimiting Strings with QUOTENAME

create procedure sys.sp_addlogin
    @loginame       sysname
   ,@passwd         sysname = Null
   ,@defdb          sysname = ‘master’      
   ,@deflanguage    sysname = Null
   ,@sid            varbinary(16) = Null
   ,@encryptopt     varchar(20) = Null
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    -- some code ----
    set @exec_stmt = ‘create login ‘ + quotename(@loginame, ‘[‘)

    if @passwd is null
        select @passwd = ‘‘

    if (@encryptopt is null)
        set @exec_stmt = @exec_stmt + ‘ with password = ‘ +
            quotename(@passwd, ‘‘‘‘)
    else
    -- some code
GO

The REPLACE function replaces all occurrences of a given string with a specified replacement string. Unlike QUOTENAME, there are no length restrictions on the arguments it accepts:

REPLACE ( 'string1' , 'string2' , 'string3' )

REPLACE takes three strings: string1 is the expression to be edited, string2 is the item within string1 to be replaced, and string3 is the item to be used in place of string2. Any of the string expressions can be of character or binary data.

For preparing delimited SQL literals, you can use REPLACE to double up the occurrences of single quotes, but you will need to add the delimiters manually (beginning and ending single quotes). Figure 2 shows how sp_attach_single_file_db uses this function to prepare an escaped physical name of a file. Since @physname is nvarchar(260), you cannot use QUOTENAME to prepare the delimited literal, which is the reason for using REPLACE. So even if someone passes a string with single quote they cannot break out of the SQL statement and inject any SQL code.

Figure 2 Delimiting Strings with REPLACE

create procedure sys.sp_attach_single_file_db
    @dbname sysname,
    @physname nvarchar(260)
as
    declare @execstring nvarchar (4000)
    -- some code --
    select @execstring = ‘CREATE DATABASE ‘
        + quotename( @dbname , ‘[‘)
        + ‘ ON (FILENAME =‘
        + ‘‘‘‘
        + REPLACE(@physname,N’’’’,N’’’’’’)
        + ‘‘‘‘
        + ‘ ) FOR ATTACH’
    EXEC (@execstring)
    -- some code --
GO

SQL Injection Vulnerabilities

Let's assume that we have an application that implements password management in a user-level table (you should never store passwords in clear text, but I've simplified this for illustrative purposes) and supports changing passwords using a stored procedure sp_setPassword (see Figure 3).

Figure 3 Changing a Password

CREATE PROCEDURE sp_setPassword
    @username varchar(25),
    @old varchar(25),
    @new varchar(25)
AS

DECLARE @command varchar(100)

SET @command=
    ‘update Users set password=‘‘‘ + @new + 
    ‘‘‘ where username=‘‘‘ + @username + 
    ‘‘‘ AND password=‘‘‘ + @old + ‘‘‘‘

EXEC (@command)
GO

A quick look at the stored procedure shows that none of the parameters are escaped for single quotes and, as such, this is vulnerable to SQL injection attacks. An attacker can pass a few specific arguments and modify the SQL statement to this:

update Users set password='NewP@ssw0rd' 
where username='admin' --' and password='dummy'

The result sets the password for the admin account (or any known account) without requiring the actual password. In T-SQL, you can fix this code by using either REPLACE or QUOTENAME. Figure 4 shows the corrected code using the REPLACE function.

Figure 4 Using REPLACE to Avoid Injection

CREATE PROCEDURE sp_setPassword
    @username varchar(25),
    @old varchar(25),
    @new varchar(25)
AS

-- Declare variables.
DECLARE @command varchar(100)

-- Construct the dynamic SQL
SET @command= 
    ‘update Users set password=‘‘‘ + REPLACE(@new, ‘‘‘‘, ‘‘‘‘‘‘) + ‘‘‘‘ + 
    ‘ where username=‘‘‘ + REPLACE(@username, ‘‘‘‘, ‘‘‘‘‘‘) + ‘‘‘‘ + 
    ‘ AND password = ‘‘‘ + REPLACE(@old, ‘‘‘‘, ‘‘‘‘‘‘) + ‘‘‘‘

-- Execute the command.
EXEC (@command)
GO

As you can see, REPLACE will double up all the occurrences of single quotes present in the parameters. So if an attacker passes the same arguments, the statement will instead become:

update Users set password='NewP@ssw0rd' 
where username='admin''--' and password='dummy'

This will not be susceptible to typical SQL injection issues.

Modification by Truncation

If you look closely at the stored procedure shown previously, you will notice that the @command variable can only hold 100 characters, but a REPLACE function on each variable of 25 characters can return 50 characters when all the characters are single quotes. SQL Server 2000 SP4 and SQL Server 2005 SP1 silently truncate the data if the variable doesn't have big enough buffers. This provides attackers an opportunity to truncate the command string.

In this example, if someone can truncate the command right after the expression username='username', then it's possible to change the password of a known user account without knowing the victim's current password.

Let's assume that an attacker knows that a user with the name administrator exists in the Web application (this could be any user account). The attacker will need to supply a new password with a length of 41 characters in order to make the command long enough to be properly truncated—41 characters because out of the 100 characters for the command, 27 characters are consumed by the update statement, 17 by the where clause, 13 by "administrator", and 2 single quotes surrounding the new password.

The attacker can only pass 25 characters for the new password. However, he can get around this by passing single quotes, which get doubled by the REPLACE function. So by passing 18 single quotes, 1 capital letter, 1 symbol, 2 lowercase letters, and 1 digit, the attacker will be able to truncate the command right after the where username='administrator' expression. If the attacker passes ''''''''''''''''''!Abb1 for the @new parameter and administrator as the username parameter, then @command will become:

update Users set password=
'''''''''''''''''''''''''''''''''''''!Abb1' where username='administrator'

Figure 5 uses QUOTENAME instead of REPLACE. The only difference between the previous example and this example is that, in the previous example, the developer has added the single quote delimiters for the username, new password, and old password, and in the current example, the QUOTENAME function adds them. Since there is no change to the user-supplied data, this can be exploited by the same attack string that is used for the previous example. Figure 6 is a shortened version of a C/C++ function written in a mid-tier application to achieve the same functionality. It is vulnerable to the same attack.

Figure 6 Truncation Problems in C++

DWORD ChangePassword(char* psUserName, char* psOld, char* psNew)
{
    char* psEscapedUserName = NULL;
    char* psEscapedOldPW = NULL;
    char* psEscapedNewPW = 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 query
    hr = StringCchPrintf(szSQLCommand, sizeof(szSQLCommand)/sizeof(char),
        "Update Users set password=‘%s’ where username=‘%s’"
        "AND password=‘%s’,
        psEscapedNewPW, psEscapedUserName, psEscapedOldPW);
    
    if (S_OK != hr)
    {
        // handle error cases
    }

    // Execute and return
}

Figure 5 Using QUOTENAME to Avoid Injection

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 ‘administrator’). But @new only takes 25 characters, which 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=‘administrator’
SET @command= ‘update Users set password=‘ + QUOTENAME(@new,’’’’) + 
‘ where username=‘ + QUOTENAME(@username,’’’’) + ‘ AND password = ‘ + QUOTENAME(@old,’’’’)

-- Execute the command.
EXEC (@command)
GO

SQL Injection by Truncation

Figure 7 shows one more variant of the same problem, but uses separate variables to fix first-order SQL injection issues. As you can see, this code stores the escaped strings into separate variables, and @command has enough buffers to hold the whole string. @escaped\_username, @escaped\_oldpw and @escaped\_newpw are declared as varchar(25), but they would need to hold 50 characters in the case where all of the characters in @username, @old, and @new are 25 single-quote characters. This gives the opportunity to truncate the escaped character string.

Figure 7 Using Separate Variables to Avoid Injection

CREATE PROCEDURE sp_setPassword
    @username varchar(25),
    @old varchar(25),
    @new varchar(25)
AS
-- Declare variables.
DECLARE @escaped_username varchar(25)
DECLARE @escaped_oldpw varchar(25)
DECLARE @escaped_newpw varchar(25)
DECLARE @command varchar(250)

SET @escaped_username = REPLACE(@username, ‘‘‘‘, ‘‘‘‘‘‘)
SET @escaped_oldpw = REPLACE(@old, ‘‘‘‘, ‘‘‘‘‘‘)
SET @escaped_newpw = REPLACE(@new, ‘‘‘‘, ‘‘‘‘‘‘)

SET @command = 
    ‘update Users set password=‘‘‘ + @escaped_newpw + ‘‘‘‘ + 
    ‘ where username=‘‘‘ + @escaped_username + ‘‘‘‘ + 
    ‘ AND password = ‘‘‘ + @escaped_oldpw + ‘‘‘‘
EXEC (@command)
GO

An attacker can pass 123...n' as the new password, where n is 24th character, make @escaped\_newpw also 123...n' (the second single-quote character returned by the REPLACE function gets truncated), and make the final query look like the following, which he can exploit by injecting code through username field:

update users set password='123...n'' 
where username='<SQL Injection here using Username>

This code pattern is more dangerous, as this provides the opportunity to inject SQL code rather than just truncate existing SQL.

Figure 8 provides another example of the same variant that uses the QUOTENAME function instead of REPLACE. Since QUOTENAME adds the delimiters, the payload will be different, but it's still vulnerable to SQL injection attacks.

Figure 8 Using QUOTENAME with Separate Variables

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_oldpw
EXEC (@command)
GO

In this case, the code is storing the delimited strings into separate variables and @command has enough buffers to hold the whole command string. Just like the previous example, the problem lies in the quoted variables @quoted\_username, @quoted\_oldpw, and @quoted\_newpw. They are declared as varchar(25), but they will need 52 characters in the case where all the characters in @username, @old, and @new are 25 single-quote characters. (QUOTENAME will also add the beginning and ending delimiter.) This gives the attacker an opportunity to truncate the delimited character string.

An attacker can pass 123...n (where n is the 24th character) as the new password, make @escaped\_newpw also '123...n (beginning single quote is added by QUOTENAME), and make the final query to look like this, which he can exploit by injecting code through username field:

update users set 
password='123...n where 
username=' <SQL Injection here using Username>

Figure 9 is a shortened version of this code in C/C++ to achieve the same functionality. Again, it is vulnerable in the same way.

Figure 9 Variable Truncation Issues in C++

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 data
    Replace(psUserName, "’", "’’", szEscapedUserName, 
            sizeof(szEscapedUserName));
    Replace(psPassword, "’", "’’", szEscapedOldPW, 
            sizeof(szEscapedOldPW));
    Replace(psPassword, "’", "’’", szEscapedNewPW, 
            sizeof(szEscapedNewPW));
    
    // Construct the query
    StringCchPrintf(szSQLCommand, sizeof(szSQLCommand)/sizeof(char),
        "Update Users set password=‘%s’ where username=‘%s’"
        "AND password=‘%s’,
        szEscapedNewPW, szEscapedUserName,szEscapedOldPW);

    // Execute and return
}

Even though I used T-SQL code for demonstration purposes, in reality you don't need to use dynamic SQL for Data Manipulation Language (DML) statements, so most of the apps that contain DML code will not be vulnerable to these issues.

Let's look at another example that constructs a dynamic DDL statement based on user input, as in Figure 10. Just like earlier examples, the following statements have truncation issues:

set @escaped_oldpw = quotename(@old, '''')
set @escaped_newpw = quotename(@new, '''')

They can be exploited by passing @new = '123...' where the 127th character (no single quotes) begins @old = '; SQL Injection', which will make the SQL statement look like this:

alter login [loginname] 
with password = '123... old_password = '; SQL
Injection

Figure 10 Creating a Dynamic DDL Statement

create procedure sys.sp_password
    @old sysname = NULL,        -- the old (current) password
    @new sysname,               -- the new password
    @loginame sysname = NULL    -- user to change password on
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @exec_stmt nvarchar(4000)
    declare @escaped_oldpw sysname
    declare @escaped_newpw sysname

    set @escaped_oldpw = quotename(@old, ‘‘‘‘)
    set @escaped_newpw = quotename(@new, ‘‘‘‘)

    set @exec_stmt = ‘alter login ‘ + quotename(@loginame) +
        ‘ with password = ‘ + @escaped_newpw + ‘ old_password = ‘ + 
        @escaped_old

    exec (@exec_stmt)

    if @@error <> 0
        return (1)

    -- RETURN SUCCESS --
    return  (0)    -- sp_password

Even though stored procedures seem to be the more likely place where these issues show up, not all of them result in security vulnerabilities. Let's see which ones need to be reviewed carefully.

In SQL Server all stored procedures get executed under the context of the caller by default. So even if a procedure has SQL injection issues, a malicious local user who has execute permission on the procedure will not be able to elevate his privileges, and the code injected will get executed under his context. But if you have an internal maintenance script that gets executed as the owner or as some other specific user (by using the EXECUTE AS feature), then the caller has the ability to execute code under a different user context and elevate himself to that user's privileges.

All truncation issues are definitely bugs, but they may not necessarily be security vulnerabilities. But it's better to fix them as you don't know who might find and use them in future.

There are other steps you can take to mitigate injection vulnerabilities in your SQL code. First, avoid using dynamic SQL for DML statements in stored procedures. If you can't avoid dynamic SQL, then use sp_executesql. Second, as illustrated by the examples shown in this article, you need to calculate buffer lengths properly. And finally, in C/C++ code, check string operation return values and see if the string has been truncated or not, failing appropriately if it has been. See the sidebar "Techniques for Detecting Vulnerabilities" for a summary of the steps you can take.

Detecting Injection by Truncation

Detecting SQL injection by truncation issues using automated tools requires a good understanding of all the code patterns that will result in truncation opportunities. You can employ different string data to target different particular code patterns. In the following scenarios, assume that n is the length of the input buffer.

For detecting QUOTENAME delimiting issues, let's start by assuming QUOTENAME (or a similar function for C/C++ apps) is used to prepare a delimited identifier or literal and that the delimited string buffer size is less than 2*n + 2. To catch these issues when the delimited string buffer length equals n, pass long strings of nondelimiting characters. The trailing delimiter will get truncated and you will have an injection opportunity using some other input variable.

To catch these issues when the delimited buffer length is odd, pass long strings of single-quote characters (or right square brackets or double quotes). Since QUOTENAME will double up all the occurrences of delimiters and adds a beginning delimiting character, the trailing delimiter gets truncated as the escaped string buffer can only hold an odd number of characters.

To catch these issues when the delimited buffer length is an even number, pass strings like 1', 1'', 1''', 1'''' and so on, incrementing the number of single quotes (or right square brackets) on each iteration. Because QUOTENAME will double up all the occurrences of single quotes, you will have an even number of single quotes in the return string and, along with a beginning delimiter and 1, you will have an even number of characters. As a result, the trailing delimiter gets truncated.

You can also detect these issues when REPLACE (or a similar function in C/C++ apps) is used to prepare the escaped string and when the escaped string buffer size is less than 2*n. To catch these issues when the escaped string buffer length equals n, pass strings like 1', 12', 123' and 123...n', incrementing the length of the input string on each iteration. In this case, if you hit the right length, then the REPLACE function will double up the last single quote character. Because the escaped string variable doesn't have enough buffer space, the last single quote gets truncated and gets stored as passed, providing an opportunity to break out of the SQL statement.

To catch issues with REPLACE when the escaped buffer length is odd, pass incremental lengths of single quote character strings like ', '', ''' and ''''...' (or simply pass long strings of single quote characters). In this case, REPLACE will double up all the occurrences of single quotes. However, because there is an odd-length buffer size, the last single quote gets truncated providing the opportunity to break out of the statement.

To catch these issues when the escaped buffer length is an even number, pass strings like 1', 1'', 1''', 1'''' and so on incrementing the number of single quotes (or right square brackets) on each iteration. The return value without the beginning 1 will contain an even number of characters, so the entire return value will have an odd number of characters. Because the buffer length is even, the trailing single quote gets truncated, providing the opportunity to break out of SQL statement.

Techniques for Detecting Vulnerabilities

Using Code Reviews If you are performing code reviews, here are some techniques for detecting issues in your SQL statements.

Detecting First or Second-Order SQL Injections

Identify the APIs used to execute dynamic SQL statements.
Review whether any data validation is performed on the data used in the dynamic SQL statement.
If data validation is not performed, review whether the data is escaped for delimiting characters (single quotes for string literals and right square brackets for SQL identifiers).

Detecting SQL Modification by Truncation Issues

Review the buffer lengths used that are to store the final dynamic SQL statement.
Calculate the maximum buffer required to hold the SQL statement when the input is maxed out and see if the buffer used to hold the SQL statement is big enough.
Pay special attention to the return values from QUOTENAME or REPLACE functions; if the length of input data is n characters, these functions will return 2*n + 2 or 2*n when all the input characters are delimiting characters.
For C/C++ applications, check whether the return values from APIs like StringCchPrintf that are used to prepare the SQL statement are checked for insufficient buffer errors.

Detecting SQL Injection by Truncation Issues

Review the buffer lengths that are used to store the delimited strings or escaped strings.
If n is the length of the input string, then you will need 2*n + 2 for storing the return value from QUOTENAME and 2*n for storing the return value from REPLACE.
For C/C++ applications, check whether the return values from replace equivalent functions are checked for insufficient buffer errors.

Using Black Box Methods If you have automated tools or smart fuzzers, then here are some techniques for detecting issues in your SQL statements.

Detecting SQL Injection Issues

Send single quotes as the input data to catch the instances where the user input is not sanitized and used as string literals inside a dynamic SQL statement.
Use right square brackets (the ] character) as the input data to catch instances where the user input is used as part of a SQL identifier without any input sanitization.

Detecting Truncation Issues

Send long strings, just as you would send strings to detect buffer overruns.

Detecting SQL Modification by Truncation Issues

Send long strings of single quote characters (or right square brackets or double quotes). These will max out the return values from REPLACE and QUOTENAME functions and might truncate the command variable used to hold the SQL statement.

Bala Neerumalla works as a security software developer at Microsoft. He specializes in finding application security vulnerabilities.

Additional resources