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 abusing SQL server truncating. SQL Server 2000 SP4 and SQL Server 2005 SP1 silently truncate the data if the variable does not have big enough buffers. I try to explain it as simple as possible for everyone, because this stuff can get 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 SQL server automatically provides us to truncate the delimited string, thereby chopping off a single quote to inject some new SQL statements in the username field.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.This is our web application asking for a username and password:username:<SQL Injection here>password: RGBvofJBTDzWMbywPqLXFvcV# 24 chars, pass is set to varchar(25)The stored procedure used:ALTER PROCEDURE sp_setPassword@username varchar(25),@old varchar(25),@new varchar(25)ASDECLARE @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_oldpwEXEC (@command)GOLearn more about truncation attacks by Bala Neerumalla