Fun With Dynamic SQL: Implicit Casting Can Allow SQL Injection

Remus Rusanu shows an example where implicit casting from NVARCHAR to VARCHAR can introduce a SQL injection vulnerability that you otherwise wouldn’t expect:

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.

Click through for the script.  The upside of this is that it’s entirely under your control and you should be able to get it right by using NVARCHAR consistently.

Related Posts

Finding Gaps in Dates

Jason Brimhall shows how you can find gaps in your data: This method is the much maligned recursive CTE method. In my testing it runs consistently faster with a lower memory grant but does cause a bit more IO to be performed. Some trade-off to be considered there. Both queries are returning the desired data-set […]

Read More

Order of Execution on SELECT Expressions

Bert Wagner digs into an interesting topic: Success! But as I was celebrating my dynamic SQL victory, I realized I was making an assumption about SQL Server that I had never thought about before: The above query only works because SQL Server is executing the variables in the SELECT list sequentially. I’m incrementing @CurrentRow only after processing […]

Read More


November 2017
« Oct Dec »