SQL Injection: casting can introduce additional single quotes

November 24th, 2017

declare @inject nvarchar(4000) = NCHAR(0x02bc) + N'; select 1/0; select ' + nchar(0x02bc);
declare @safe nvarchar(4000) = REPLACE(@inject, N'''', N'''''');
declare @sql varchar(4000) = N'SELECT ''' + @safe + N'''';

print @inject;
print @safe;
print @sql;

exec(@sql);
go

declare @inject nvarchar(4000) = NCHAR(0x02bc) + N'; select 1/0; select ' + nchar(0x02bc);
declare @safe nvarchar(4000) = QUOTENAME(@inject, N'''');
declare @sql varchar(8000) = N'SELECT ' + @safe;

print @inject;
print @safe;
print @sql;

exec(@sql);
go

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

In both examples above the SQL executed apparently should had been safe from SQL injection, but it isn’t. Neither REPLACE nor QUOTENAME were able to properly protect and the injected division by zero was executed. The problem is the Unicode MODIFIER LETTER APOSTROPHE (NCHAR(0x02bc)) character that I used in constructing the NVARCHAR value, which is then implicitly cast to VARCHAR. This cast is converting the special ‘modifier letter apostrophe’ character to a plain single quote. This introduces new single quotes in the value, after the supposedly safe escaping occurred. The result is plain old SQL Injection.

In order for this problem to occur, there has to exist an implicit or explicit conversion (a cast) from Unicode to ASCII (ie. NVARCHAR to VARCHAR) and this conversion must occur after the single quote escaping occurred.

Credit: Why doubling single quotes is not enough to protect from SQL Injection.

Comments are closed.