Why QUOTENAME is important

Question

Why QUOTENAME is important

Thom A

SSC Guru

Points: 99130
More actions
August 3, 2018 at 9:03 am

#355747

Comments posted to this topic are about the item Why QUOTENAME is important
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply

andycadley SSCertifiable Points: 5296 More actions · Answer 1

Using QUOTENAME around table names, schema name etc is a completely sound a sensible practice that should definitely be encouraged. I'll fully admit to being guilty of the '['+@tablename+']' myself (NEVER in user exposed parameters!) and should break the habit and do it right.

However, using it for anything else, such as trying to put quotes around text strings is rife with problems that you really don't want sneaking into your code base. Consider what happens with something like:

Declare @NewTable sysname, @ImportFile nvarchar(512), @Worksheet nvarchar(128) Set @NewTable = 'ImportTable2018' Set @ImportFile = '\\AValidServer\Folllowed\By\A\Long\But\Totally\Valid\File\Path\That\Exceeds\SQL\Server\Limits\For\Allowed\Table\Name\Lengths\Data.xlsx' Set @Worksheet = '2018Data'DECLARE @dsql nvarchar(MAX);
SET @dsql = N'SELECT @dWorkSheet AS WorkSheet, *' + NCHAR(10) +
N'INTO ' + QUOTENAME(@NewTable) + + NCHAR(10) +
N'FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',' + NCHAR(10) +
N' ' + QUOTENAME('Excel 12.0; Database=' + @ImportFile,'''') + ',' + NCHAR(10) +
N' ' + QUOTENAME(QUOTENAME(@Worksheet + '$','''')) + N');';

PRINT @dsql;

There's nothing wrong with the supplied parameters, all should be OK. Except that QUOTENAME knows how long a valid SQL identifier name is supposed to be and returns NULL if you try to escape something longer. I think wrapping file paths like this is one of the most common ways I've seen QUOTENAME subtly break somebodies code and leave them scratching their head as to why it suddenly stops working.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

Great article on QUOTENAME, Thom, especially with the considerations given for SQL Injection, which is still rampant in the industry.

Andy also brings up a great point. A lot of people fail to read the documentation and don't realize that "character string" input operand is of the SYSNAME datatype and, even if they do, they don't realize that the SYSNAME datatype is actually an alias for "just" NVARCHAR(128). That means that not only do you have to be concerned with the length of the "character string", you also have to make the realization that it could be the cause of implicit conversions that would force scans rather than seeks if QUOTENAME were used to precondition criteria in a JOIN or WHERE clause.

Cool undocumented info for parenthesis and braces. Didn't know that and it prompted the following test. Turns out that there are several other useful characters that can also be used.

WITH cteTryIt AS ( SELECT TheCharacter = CHAR(N) ,TestResult = QUOTENAME('Test',CHAR(N)) FROM dbo.Tally WHERE N <= 255 ) SELECT * FROM cteTryIt WHERE TestResult > '' ;

Here are the results from that snippet...
TheCharacter TestResult ------------ ---------- " "Test" ' 'Test' ( (Test) ) (Test) < <Test> > <Test> [ [Test] ] [Test] ` `Test` { {Test} } {Test}

The unfortunate part of that is that, once again, MS failed to provide complete documentation for something that could be incredibly useful just based on what their perception of what is "useful" might actually be. :sick: Either that or the people writing the documentation just don't know what the capabilities actually are.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99130 More actions · Answer 3

Jeff Moden - Sunday, August 5, 2018 8:53 AM
The unfortunate part of that is that, once again, MS failed to provide complete documentation for something that could be incredibly useful just based on what their perception of what is "useful" might actually be. :sick: Either that or the people writing the documentation just don't know what the capabilities actually are.

There's quite a few points in the documentation like that now, where it's not quite "accurate", or actually just completely misses some functionality (intended or not). I do wonder if Microsoft are almost wanting the community to do the updating, as you can submit an edit for an article via their Github (For example sql-docs/quotename).

andycadley - Sunday, August 5, 2018 1:40 AM
Using QUOTENAME around table names, schema name etc is a completely sound a sensible practice that should definitely be encouraged. I'll fully admit to being guilty of the '['+@tablename+']' myself (NEVER in user exposed parameters!) and should break the habit and do it right.

You're totally right here, and it should have been a caveat I mentioned when writing; but in all honesty it completely slipped my mind when writing it. 128 characters is normally enough for a filepath, but you could easily go over the limit with a UNC path with a large server and share name. The example does, at least, show case other uses for it, but I'll have a look at editing that particular in later. Thanks Andy.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99130 More actions · Answer 4

Thom A - Monday, August 6, 2018 2:22 AM
Jeff Moden - Sunday, August 5, 2018 8:53 AM
The unfortunate part of that is that, once again, MS failed to provide complete documentation for something that could be incredibly useful just based on what their perception of what is "useful" might actually be. :sick: Either that or the people writing the documentation just don't know what the capabilities actually are.

There's quite a few points in the documentation like that now, where it's not quite "accurate", or actually just completely misses some functionality (intended or not). I do wonder if Microsoft are almost wanting the community to do the updating, as you can submit an edit for an article via their Github (For example sql-docs/quotename).

Seems that that is Microsoft's attitude. Submitted a Pull Request on Monday, and they've already updated the documentation with the new wording I submitted:

Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [] ), a double quotation mark ( " ), a left or right parenthesis ( () ), a greater than or less than sign ( >< ), a left or right brace ( {} ) or a backtick ( ` ). NULL returns if an unacceptable character is supplied. If quote_character is not specified, brackets are used.

I'm actually pleasantly surprised at how quick the turnaround time on that was. I was expecting it to take at least a month or 2; or at worst after the image, text and ntext datatypes were finally removed in SQL Server 2052. ðŸ¤£

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

Thom A - Wednesday, August 8, 2018 7:12 AM
Thom A - Monday, August 6, 2018 2:22 AM
Jeff Moden - Sunday, August 5, 2018 8:53 AM
The unfortunate part of that is that, once again, MS failed to provide complete documentation for something that could be incredibly useful just based on what their perception of what is "useful" might actually be. :sick: Either that or the people writing the documentation just don't know what the capabilities actually are.

There's quite a few points in the documentation like that now, where it's not quite "accurate", or actually just completely misses some functionality (intended or not). I do wonder if Microsoft are almost wanting the community to do the updating, as you can submit an edit for an article via their Github (For example sql-docs/quotename).
Seems that that is Microsoft's attitude. Submitted a Pull Request on Monday, and they've already updated the documentation with the new wording I submitted:
Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [] ), a double quotation mark ( " ), a left or right parenthesis ( () ), a greater than or less than sign ( >< ), a left or right brace ( {} ) or a backtick ( ` ). NULL returns if an unacceptable character is supplied. If quote_character is not specified, brackets are used.
I'm actually pleasantly surprised at how quick the turnaround time on that was. I was expecting it to take at least a month or 2; or at worst after the image, text and ntext datatypes were finally removed in SQL Server 2052. ðŸ¤£

Heh... I love the ( >< ) notation. It reminds me of the word "DOH!". 😀

Strange as it may seem, I've not bothered with the GitHub aspect of MS documentation. Is there a link for instructions on how to make such a submittal?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99130 More actions · Answer 6

Jeff Moden - Wednesday, August 8, 2018 7:39 AM
Heh... I love the ( >< ) notation. It reminds me of the word "DOH!". 😀
Strange as it may seem, I've not bothered with the GitHub aspect of MS documentation. Is there a link for instructions on how to make such a submittal?

I didn't see a Microsoft one, but the GitHub Help page is very comphrehesive. Proposing changes to your work with pull requests seems to be the relevant help page here.

Also, I'm not going to lie, I intentionally used >< as opposed to <> for that very reason. 😉

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

Thom A - Wednesday, August 8, 2018 7:49 AM
Jeff Moden - Wednesday, August 8, 2018 7:39 AM
Heh... I love the ( >< ) notation. It reminds me of the word "DOH!". 😀
Strange as it may seem, I've not bothered with the GitHub aspect of MS documentation. Is there a link for instructions on how to make such a submittal?
I didn't see a Microsoft one, but the GitHub Help page is very comphrehesive. Proposing changes to your work with pull requests seems to be the relevant help page here.
Also, I'm not going to lie, I intentionally used >< as opposed to <> for that very reason. 😉

Very cool. Thanks, Thom. Great info.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 8

Why did you use a sysname data type in your example? I've used this approach before, but the variables where char or varchar with not too long a length, e.g. @TableName varchar(10). Nothing against the use of the function. It looks useful. But your example seems to be something of a straw man.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

RonKyle - Monday, August 13, 2018 6:22 AM
Why did you use a sysname data type in your example? I've used this approach before, but the variables where char or varchar with not too long a length, e.g. @TableName varchar(10). Nothing against the use of the function. It looks useful. But your example seems to be something of a straw man.

I'm guessing that's because objects names in SQL Server use that data type which is basically a synonym for nvarchar(128).
Since that's the maximum length accepted for QUOTENAME, it seems logical to use it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Thom A SSC Guru Points: 99130 More actions · Answer 10

Luis Cazares - Monday, August 13, 2018 7:34 AM
RonKyle - Monday, August 13, 2018 6:22 AM
Why did you use a sysname data type in your example? I've used this approach before, but the variables where char or varchar with not too long a length, e.g. @TableName varchar(10). Nothing against the use of the function. It looks useful. But your example seems to be something of a straw man.
I'm guessing that's because objects names in SQL Server use that data type which is basically a synonym for nvarchar(128).
Since that's the maximum length accepted for QUOTENAME, it seems logical to use it.

Luis is exactly right here. Although I would never suggest having an object (especially a table/view/function) with a name consisting of 128 characters, SQL Server does permit it. I would very much not be surprised if there are people out there that do use very long object names. Using sysname seems the logical choice here, therefore, when dealing with a dynamic object, as (like Luis said) it is basically a synonym for nvarchar(128).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 11

Thom A - Monday, August 13, 2018 9:08 AM
Luis Cazares - Monday, August 13, 2018 7:34 AM
RonKyle - Monday, August 13, 2018 6:22 AM
Why did you use a sysname data type in your example? I've used this approach before, but the variables where char or varchar with not too long a length, e.g. @TableName varchar(10). Nothing against the use of the function. It looks useful. But your example seems to be something of a straw man.
I'm guessing that's because objects names in SQL Server use that data type which is basically a synonym for nvarchar(128).
Since that's the maximum length accepted for QUOTENAME, it seems logical to use it.
Luis is exactly right here. Although I would never suggest having an object (especially a table/view/function) with a name consisting of 128 characters, SQL Server does permit it. I would very much not be surprised if there are people out there that do use very long object names. Using sysname seems the logical choice here, therefore, when dealing with a dynamic object, as (like Luis said) it is basically a synonym for nvarchar(128).

I can see the point. But if I know my table names don't exceed 20 characters, using that instead of the sysname data type goes a long way towards hindering SQL injection as the query simply can't be long enough. I'm still trying to think this through, however, and may change my opinion.

Thom A SSC Guru Points: 99130 More actions · Answer 12

RonKyle - Monday, August 13, 2018 11:19 AM
type your messageI can see the point. But if I know my table names don't exceed 20 characters, using that instead of the sysname data type goes a long way towards hindering SQL injection as the query simply can't be long enough. I'm still trying to think this through, however, and may change my opinion.

I think you missed the point of QUOTENAME then. 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

peter.row SSCarpal Tunnel Points: 4319 More actions · Answer 13

What I'd really like is a QUOTENAME function that only quoted if the name required it because the name being used had spaces, represents a keyword etc...
Why? Because I have some scaffolding scripts that generate SQL scripts based on a table schema etc... The resulting script is then saved and used and is under version control.

I really hate reading through SQL full of [tablename].[column] formatting it is totally distracting to me. I only quote stuff when needed for SQL to understand it or for SSMS/SOS not to highlight it as a keyword.

I use sp_executesql @theSQLCommand, @ParamsAsString, @Param1Value, @Parm2Value... ParmXValue to avoid SQL injection when using dynamic SQL.

Thom A SSC Guru Points: 99130 More actions · Answer 14

peter.row - Tuesday, August 14, 2018 4:06 AM
What I'd really like is a QUOTENAME function that only quoted if the name required it because the name being used had spaces, represents a keyword etc...
Why? Because I have some scaffolding scripts that generate SQL scripts based on a table schema etc... The resulting script is then saved and used and is under version control.
I really hate reading through SQL full of [tablename].[column] formatting it is totally distracting to me. I only quote stuff when needed for SQL to understand it or for SSMS/SOS not to highlight it as a keyword.
I use sp_executesql @theSQLCommand, @ParamsAsString, @Param1Value, @Parm2Value... ParmXValue to avoid SQL injection when using dynamic SQL.

I don't agree with that mentality on the function, nor am I sure that could even work. Key and reserved words can (and do) change. If you were using a linked server you might end up not quoting an object name that in another version needs to be quoted (as the objects name is now a reserved word, or perhaps no longer is (although I doubt the latter would happen)). I admit, I'm not a massive fan of [Server].[Database].[Schema].[Object] either, however, I'd much rather have all of them quoted and it work 100% of the time, than only have the object quoted when SQL Server deemed it "needed". You could then have a scenario in the future that due to a new keyword a breaking change is introduced, and QUOTENAME won't handle it.

Far better do do something 100% of the time, and thus ensure 100% functionality, than do it only when you think "it is needed", and then miss a time when it is (and thus cause a failure).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk