問題描述

我熟悉了 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 函數?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！