Problems with QUOTENAME

So, lets get one thing out of the way before we begin.... Dynamic SQL is generally a bad plan. That being said there are a few scenarios where it is genuinely the best choice. For example in catch all type queries commonly encountered on web forms. (as this excellent post by Gail Shaw illustrates).

A major concern whenever you use dynamic SQL is SQL Injection. To secure yourself from this you have to validate your inputs for any DML code - (If you use parameterised dynamic sql with sp_executeSql then you are secure from a pure parameter standpoint)

XKCD's most awesome cartoon ever.

Sql Server has a built in function called QUOTENAME() that will escape characters that you tell it to. It does a good job of filtering such strings. For example:

Quotename_Injection.sql

The result for QUOTENAME is a nicely escaped version of the string that is completely safe to use in dynamic string construction. If we had just taken the string as given we would run the risk of SQL Injection attack!

So QUOTENAME() is awesome right? Well it does have one pretty horrible drawback.... If you pass QUOTENAME() a string of more than 128 characters it will silently yield a null. If you aren't expecting this then you can experience some very strange bugs which can be extremely hard to track down.....

So I thought I'd write a replacement QUOTENAME() function and did a bit of research. The best article I could find is this one from msdn which I recommend you read.

ufn_EscapeString.sql

This is sufficient for my needs and deals with strings up to 2^31 characters