Watchout when using QUOTENAME()
by Sankar Reddy
As a best practise, one should wrap the object names with QUOTENAME() when using dynamic sql to avoid SQL Injection and more to follow on this topic. QUOTENAME() works correctly for object names that are less than or equal to 128 characters in length but returns NULL if the length is more. This fact is incorrectly shown as 258 on BOL so watchout when you are using QUOTENAME and use REPLACE() instead. The only difference between these is QUOTENAME adds the brackets automatically but using replace you have to manually add them.
--This works correctly
select
quotename(replicate('a', 128))
/*
[aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa]
*/
--This returns null
select
quotename(replicate('a', 129))
/*
NULL
*/
Cheat Sheet:
| Object Type | Recommended Function | Comments |
| Working with Object Names |
QUOTENAME (@var) | Object Names are by default 128 characters |
| Working with Strings <= 128 characters |
QUOTENAME (@var, '''') | String <=128 will work just fine with QUOTENAME |
| Working with Strings > 128 characters |
REPLACE (@var, '''', '''''') | String >128 will NOT work fine with QUOTENAME |
