SQL Server Truncation Attacks.

This article deals with a SQL injection attack that isn't very well known, it is called a truncation attack. The idea is simple: a programmer develops a stored procedure and declares fixed field values. He could use a T-SQL function like: QUOTENAME or REPLACE to delimit or replace single quotes, and thereby our programmer is trying to mitigate an SQL injection attack. With his new faith in stored procedures -which he thinks are security methods out of the box- he created a new vulnerable web application, that we could attack by truncating. I try to explain it as simple as possible for everyone, because this stuff gets complex very fast, please read closely to understand what happens here.

The stored procedure below is storing delimited strings into separate variables. The quoted variables declared as varchar(25) form the problem of this truncation attack on the T-SQL function QUOTENAME. It tries to delimit the single quotes and provides us to truncate the delimited string.

In the end our query becomes this:

update users set password='RGBvofJBTDzWMbywPqLXFvcV where username=' <SQL Injection>
By passing 24 characters as a new password: RGBvofJBTDzWMbywPqLXFvcV

@quoted_newpw becomes:

'RGBvofJBTDzWMbywPqLXFvcV
You'll see that the password has a leading single quote that was added by QUOTENAME. Observe carefully that there is no trailing single quote as it gets truncated which leaves us with exactly 25 characters which our password field would allow to insert.
The stored procedure used:
ALTER PROCEDURE sp_setPassword

@username varchar(25),
@old varchar(25),
@new varchar(25)

AS

DECLARE @quoted_username varchar(25)
DECLARE @quoted_oldpw varchar(25)
DECLARE @quoted_newpw varchar(25)
DECLARE @command varchar(250)

-- all the variables can only hold 25 characters,
-- notice: 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, '''')
SET @command= 'update Users set password=' + @quoted_newpw +
' where username=' + @quoted_username +
' AND password = ' + @quoted_oldpw
EXEC (@command)
GO
Learn more about truncation attacks by Bala Neerumalla