为什么要使用 QUOTENAME 函数?

2021-09-10 00:00:00 tsql sql-server

我熟悉了 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

相关文章