ΪʲôҪʹÓà QUOTENAME º¯Êý?

ʱ¼ä£º2022-11-09
±¾ÎĽéÉÜÁËΪʲôҪʹÓà QUOTENAME º¯Êý?µÄ´¦Àí·½·¨£¬¶Ô´ó¼Ò½â¾öÎÊÌâ¾ßÓÐÒ»¶¨µÄ²Î¿¼¼ÛÖµ£¬ÐèÒªµÄÅóÓÑÃÇÏÂÃæËæןú°æÍøµÄС±àÀ´Ò»Æðѧϰ°É£¡

ÎÊÌâÃèÊö

ÎÒÊìϤÁË QUOTENAME ¹¦ÄÜ.µ«ÎÒ²»Ã÷°×ÎÒ¿ÉÒÔÓÃËü×öʲô?ΪʲôËü±»Èç´Ë¹ã·ºµØʹÓÃ?

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?

ÍƼö´ð°¸

¼ÙÉèÒÔϽű¾¼Æ»®¶¨ÆÚÔËÐУ¬ÒÔÇåÀí dbo ¼Ü¹¹ÒÔÍâµÄ¼Ü¹¹Öеıí.

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 

Èç¹ûÄú´´½¨ÒÔÏÂÄÚÈݲ¢ÔËÐнű¾£¬ÄÇô¾¡¹ÜʹÓÃÊÖ¶¯×Ö·û´®Á¬½Ó·½·¨£¬Ò»Çж¼°´Ô¤ÆÚ¹¤×÷.±í foo.bar ±»É¾³ý.

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)

½Å±¾Ê§°Ü²¢³öÏÖ´íÎó

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]'.

ËùÒÔ²»Ê¹Óà QUOTENAME µ¼Ö½ű¾Ê§°Ü.¹Ø±ÕÀ¨ºÅûÓÐͨ¹ý¼Ó±¶ÕýȷתÒå.ÕýÈ·µÄÓï·¨Ó¦¸ÃÊÇ

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
)

ÏÖÔÚ×îÖÕÖ´ÐеĽű¾ÊÇ

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

] ±»½âÊÍΪ¹Ø±Õ¶ÔÏóÃû³Æ£¬ÆäÓಿ·Ö×÷ΪÐÂÓï¾ä.µÚÒ»ÌõÓï¾ä·µ»ØÒ»Ìõ´íÎóÏûÏ¢£¬µ«Ã»ÓÐÖÕÖ¹·¶Î§£¬µÚ¶þÌõÓï¾äÈÔ±»Ö´ÐÐ.ͨ¹ý²»Ê¹Óà QUOTENAME£¬ÄúÒÑÏò SQL ×¢È볨¿ªÁË´óÃÅ£¬¿ª·¢ÈËÔ±Òѳɹ¦ÌáÉýÁËËûÃǵÄȨÏÞ

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

Õâƪ¹ØÓÚΪʲôҪʹÓà QUOTENAME º¯Êý?µÄÎÄÕ¾ͽéÉܵ½ÕâÁË£¬Ï£ÍûÎÒÃÇÍƼöµÄ´ð°¸¶Ô´ó¼ÒÓÐËù°ïÖú£¬Ò²Ï£Íû´ó¼Ò¶à¶àÖ§³Ö¸ú°æÍø£¡

ÉÏһƪ£ºLIKE Ö®¼äµÄ SQL Server ÈÕÆÚ ÏÂһƪ£ºÊ¹Óà WHILE ´´½¨ÐéÄâÊý¾Ý

Ïà¹ØÎÄÕÂ

×îÐÂÎÄÕÂ