CODEWITHSUNDEEP
sql-servert-sql
user2216
user2216

Reputation: 839

Why we should use QUOTENAME function?

I get acquainted with QUOTENAME function. But I don't understand for what I can use it? Why it is so widely used?

select quotename('[abc]') -- '[[abc]]]'
select quotename('abc') -- '[abc]'
select '[' + 'abc' +']'  -- why it is not so good as previous?

Upvotes: 3

Views: 3243

Answers (2)

Martin Smith
Martin Smith

Reputation: 453628

Imagine the following script is scheduled to run regularly to clean up tables in schemas other than the dbo schema.

DECLARE @TABLE_SCHEMA SYSNAME,
        @TABLE_NAME   SYSNAME
DECLARE @C1 AS CURSOR;

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT TABLE_SCHEMA,
           TABLE_NAME
    FROM   INFORMATION_SCHEMA.TABLES
    WHERE  TABLE_SCHEMA <> 'dbo'

OPEN @C1;

FETCH NEXT FROM @C1 INTO @TABLE_SCHEMA, @TABLE_NAME;

WHILE @@FETCH_STATUS = 0
  BEGIN
      PRINT 'DROP TABLE [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + ']';

      EXEC ('DROP TABLE [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + ']');

      FETCH NEXT FROM @C1 INTO @TABLE_SCHEMA, @TABLE_NAME;
  END

If you create the following and run the script then all works as expected despite using the manual string concatenation approach. The table foo.bar is dropped.

CREATE SCHEMA foo
CREATE TABLE foo.bar(x int)

Now create the following and try

CREATE TABLE foo.[[abc]]](x int)

The script fails with an error

DROP TABLE [foo].[[abc]]
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string '[abc]'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '[abc]'.

So not using QUOTENAME has caused the script to fail. The closing bracket was not escaped properly by doubling it up. The correct syntax should have been

DROP TABLE [foo].[[abc]]]

Even worse news is that a malicious developer has come to know of the script's existence. They execute the following just before the script is scheduled to run.

CREATE TABLE [User supplied name]]; 
EXEC sp_addsrvrolemember 'SomeDomain\user2216', 'sysadmin';  --]
(
x int
)

Now the script that ends up being executed is

DROP TABLE [foo].[User supplied name]; 
EXEC sp_addsrvrolemember 'SomeDomain\user2216', 'sysadmin';  --]

The ] was interpreted as closing off the object name and the remainder as a new statement. The first statement returned an error message but not a scope terminating one and the second one was still executed. By not using QUOTENAME you have opened yourself up to SQL injection and the developer has successfully escalated their privileges

Upvotes: 7

Jens
Jens

Reputation: 3299

QUOTENAME can be used when generating dynamic SQL statements. It'll indeed place your column name between brackets but will also escape characters which would break your quoted column name and could cause SQL injection.

For example:

SELECT QUOTENAME('abc[]def');

Will return:

[abc[]]def]

For more info: QUOTENAME (MSDN)

Upvotes: 1

Related Questions