This is an old attack technique. More details can be found at
http://msdn.microsoft.com/en-us/magazine/cc163523.aspx
https://www.blackhat.com/presentations/bh-usa-06/BH-US-06-Neerumalla.pdf
The question is “If I use quotename() or replace() or wrap all my SQL parameters, are my dynamic SQL statements still subject to SQL injection?”
Even with quotename() or replace(), your dynamic SQL statements might be vulnerable to SQL injections due to truncations if you make these mistakes:
- The dynamics SQL is assigned to a variable whose buffer is not large enough. An attacker can force truncation by passing unexpectedly long strings to your stored procedure.
- The results returned by quotename() or replace() are saved into local variables whose buffer are not large enough. Those returned strings are truncated. An attacker can manipulate it to his advantages.
Here is the example showing truncation problems:
CREATE PROCEDURE sp_MySetPassword
@loginname varchar(128),
@old varchar(200),
@new varchar(128),
AS
DECLARE @login sysname
DECLARE @newpassword sysname
DECLARE @oldpassword sysname
-- the data stored in command might be truncated because its small size
DECLARE @command varchar(200)
-- the data stored in those temp variables might be truncated becuase
-- quotename() can return up to 258 characters and replace() can return up to 400 characters
SET @login = QUOTENAME(@loginname, '''')
SET @oldpassword = REPLACE(@old, '''', '''''')
SET @newpassword = QUOTENAME(@new, '''')
SET @command = 'UPDATE Users set password = ' + @newpassword
+ ' where username =' + @login + ' AND password = ' + @oldpassword;
EXEC (@command)
To avoid truncation issues, the best approach is to use parameterized queries in your stored procedures. If you must use quotename() or replace(), you can address those two issues: i) use a large buffer for the command variable or directly execute the dynamic T-SQL. ii) use large buffers for those temporary variables or call quotename() or replace() directly in dynamic SQL statement.
The above example can be fixed like this:
CREATE PROCEDURE sp_MySetPassword
@loginname varchar(128),
@old varchar(200),
@new varchar(128),
AS
DECLARE @command varchar(3000)
SET @command = 'UPDATE Users set password = ' + QUOTENAME(@new, '''')
+ ' where username =' + QUOTENAME(@loginname, '''') + ' AND password = ' + REPLACE(@old, '''', '''''');
EXEC (@command)
