是否可以使用带有参数的sp_executesql发出CREATE语句?

如果你执行你的命令 create trigger 你说你打印的声明。。。你会发现它不起作用。触发器主体中的print语句正在尝试输出 @tablename ,但从未定义,因此会出现错误:

必须声明标量变量“@tableName”。

但这不是你的主要问题。至于为什么不能用 execute_sql 对于参数,我找不到任何文档来解释为什么。。。但你和其他人的经验证明这很麻烦。我相信这篇文章有一个很好的理论: sp_executesql adds statements to executed dynamic script?

但是,使用动态sql语句可以使用DDL执行 EXECUTE 陈述所以你可以创建一个参数化的 sp_executesql 语句,该语句验证表名,然后创建要与 处决 陈述

它看起来不漂亮,但很管用:

DECLARE @tableName sysname = 'MyTable';
DECLARE @sql nvarchar(max) = 
N'
set @tableName = (SELECT name FROM sys.tables WHERE OBJECT_ID = OBJECT_ID(@tableName)) --validate table
DECLARE @CreateTriggerSQL as varchar(max) =
''
CREATE TRIGGER '' + QUOTENAME(''TR_'' + @tableName) + '' ON '' + QUOTENAME( @tableName) + '' FOR INSERT
AS
BEGIN
    PRINT '''''' + @tableName + ''''''
END
''
print isnull(@CreateTriggerSQL, ''INVALID TABLE'')
exec (@CreateTriggerSQL)
';

EXEC sp_executesql @sql, N'@tableName sysname', @tableName=@tableName;

您还可以将其转换为带有参数的存储过程,而不是运行 sp_executesql 如果方便的话。看起来更干净一些:

CREATE PROCEDURE sp_AddTriggerToTable (@TableName AS sysname) AS

set @tableName = (SELECT name FROM sys.tables WHERE OBJECT_ID = OBJECT_ID(@tableName)) --validate table
DECLARE @CreateTriggerSQL as varchar(max) =
'
CREATE TRIGGER ' + QUOTENAME('TR_' + @tableName) + ' ON ' + QUOTENAME( @tableName) + ' FOR INSERT
AS
BEGIN
    PRINT ''' + @tableName + '''
END
'
print isnull(@CreateTriggerSQL, 'INVALID TABLE')
exec (@CreateTriggerSQL)
GO

我强烈建议不要对表名使用动态SQL。您正在为自己设置一些严重的SQL注入问题。你应该验证任何进入 @tableName 变量

也就是说,在你的例子中。。。

DECLARE @tableName sysname = 'ContentItems';
DECLARE @sql nvarchar(max) = N'
    CREATE TRIGGER TR_' + @tableName + N' ON ' + @tableName + N' FOR INSERT
        AS
        BEGIN
            PRINT @tableName
        END';
EXEC sp_executesql @sql, N'@tableName sysname', @tableName=@tableName

... 您正在尝试输入您的声明 @表名 输入要为其创建的文本 @sql ,然后尝试传递一个参数 spexecutesql .这让你 @sql 尝试调用时无效。

你可以试试:

DECLARE @tableName sysname = 'ContentItems';
DECLARE @sql nvarchar(max) = N'
    CREATE TRIGGER TR_'' + @tableName + N'' ON '' + @tableName + N'' FOR INSERT
        AS
        BEGIN
            PRINT @tableName
        END';
EXEC sp_executesql @sql, N'@tableName sysname', @tableName=@tableName

... 这会给你线。。。

'
CREATE TRIGGER TR_' + @tableName + N' ON ' + @tableName + N' FOR INSERT
    AS
    BEGIN
        PRINT @tableName
    END'

... 然后可以接受你通过的参数。。。

EXEC sp_executesql @sql, N'@tableName sysname', @tableName=@tableName ;

同样,在将任何内容传递到使用动态表名的动态SQL之前,我会使用一些重验证(和白名单)。

sp_executesql() ,我认为参数化也是有限的。根据你的其他评论,听起来你并不是真的需要一个动态的过程,而是需要一种方法来为一些元素重复一个特定的任务。如果是这样的话,我的建议是手动复制/粘贴,然后执行语句。

因为SQL可以直接运行,也可以不提供 sp_executesql的参数,这似乎是我遇到了一个真正的问题 SQL引擎的局限性,但我在任何地方都没有看到它的文档。

这种行为是有记录的,尽管不是直观的。相关摘录自 the documentation 在触发限制主题下:

CREATE TRIGGER必须是批处理中的第一条语句

执行参数化查询时,参数声明将被视为批处理的一部分。因此 CREATE TRIGGER 批处理(以及其他用于可编程对象(如过程、函数等)的CREATE语句)不能作为参数化查询执行。

尝试运行时收到的无效语法错误消息 创建触发器 作为一个参数化的查询并不是特别有用。下面是使用未记录且不受支持的内部参数化查询语法的代码的简化版本。

EXECUTE(N'(@tableName sysname = N''MyTable'')CREATE TRIGGER TR_MyTable ON dbo.MyTable FOR INSERT AS');

这至少会导致调用 创建触发器 限制:

Msg 1050,15级,状态1,第73行此语法仅适用于 参数化查询。味精111,15级,状态1,第73行“创建” 触发器“”必须是查询批处理中的第一条语句。

同样,使用此方法执行另一个参数化语句也会成功运行:

EXECUTE (N'(@tableName sysname = N''MyTable'')PRINT @tableName');

但是,如果在批处理中没有实际使用该参数,则会导致错误

EXECUTE (N'(@tableName sysname = N''MyTable'')PRINT ''done''');

Msg 1050,15级,状态1,第75行此语法仅适用于 参数化查询。

底线是你需要建立 创建触发器 语句作为不带参数的字符串,并作为非参数化查询执行该语句以创建触发器。

是否可以使用sp_executesql和 参数?

简单的答案是 “没有” 你不能

根据 MSDN

通常,参数仅在数据操作语言中有效 (DML)语句,而不是数据定义语言(DDL)语句

你可以查看更多细节 Statement Parameters

问题是什么?

参数只能代替标量文字,如带引号的字符串、日期或数值。你不能参数化一个 DDL 活动

我们能做些什么?

我相信你想要使用参数化 sp_executesql 是为了避免任何 SQL注入攻击 .为了实现这一目标 DDL 行动中你可以做以下事情,以尽量减少攻击的可能性。

1. 使用分隔符: 你可以用 QUOTENAME() 对于 SYSNAME 触发器名称、表名和列名等参数。
2. 限制权限 :用于运行动态数据库的用户帐户 DDL ,应该只有有限的权限。就像在一个 仅使用 CREATE 准许
3. 隐藏错误消息 :不要向用户抛出实际错误。SQL注入主要通过尝试和错误的方法执行。如果 你很难隐藏真正的错误。
4. 输入验证 :您总是可以使用一个函数来验证输入字符串、转义所需字符、检查 对于特定的关键字,如 DROP .

有解决办法吗?

如果要使用 sp_executesql ,在这种情况下,您可以在 OUTPUT 变量并在下一条语句中运行查询,如下所示。

这是第一次打电话给 sp_executesql 将参数化您的查询,实际执行将由对的第二次调用执行 sp_executesql

例如

DECLARE @TableName VARCHAR(100) = 'MyTable' 
DECLARE @returnStatement NVARCHAR(max); 
DECLARE @sql1 NVARCHAR(max)= 
N'SELECT @returnStatement = ''CREATE TRIGGER TR_''                                          
    +  @TableName + '' ON '' +  @TableName  +  '' FOR INSERT AS BEGIN PRINT 1 END'''

EXEC Sp_executesql 
  @sql1, 
  N'@returnStatement VARCHAR(MAX) OUTPUT, @TableName VARCHAR(100)', 
  @returnStatement output, 
  @TableName 

EXEC Sp_executesql @returnStatement 

是否可以使用sp_executesql和 参数?

答案是 “是的” ,但需要稍作调整:

USE msdb

DECLARE @tableName sysname = 'sysjobsteps';

DECLARE @sql nvarchar(max) = N'
EXECUTE (''                              -- Added nested EXECUTE()
    CREATE TRIGGER [TR_'' + @tableName + N''] ON ['' + @tableName + N''] FOR INSERT
        AS
        BEGIN
            PRINT '''''+@tableName+'''''
        END''
        )'                            -- End of EXECUTE()


EXEC sp_executesql @sql, N'@tableName sysname', @tableName=@tableName

调整列表:

1. 下面的评论解释了原因
2. 增加了额外的方括号,使SQL注入稍微困难一些

我正在寻找特定的(理想情况下,有文件记录的)限制 带有参数的sp_executesql,以及 这些特定限制(不使用参数除外)

在这种情况下,这是DDL命令的限制,而不是sp_executesql。DDL语句不能使用变量参数化。Microsoft文档显示:

变量只能在表达式中使用,不能代替 对象 名字 或者关键词。要构造动态SQL语句,请使用EXECUTE。

资料来源: DECLARE (Transact-SQL)

因此,使用EXECUTE的解决方案是我提供的一种解决方案

就我个人而言,我讨厌触发器,大多数时候都会尽量避免;)

然而,如果你真的,真的需要这种动态的东西,你应该使用 sp_mForeachTable 并避免以任何代价注射(正如肖恩所指出的):

EXEC sys.sp_MSforeachtable
  @command1 = '
        DECLARE @sql NVARCHAR(MAX)
        SET @sql = CONCAT(''CREATE TRIGGER TR_''
            , REPLACE(REPLACE(REPLACE(''?'', ''[dbo].'', ''''),''['',''''),'']'','''')
            , '' ON ? FOR INSERT
    AS
    BEGIN
        PRINT ''''?'''';
    END;'');
    EXEC sp_executesql @sql;'
  , @whereand = ' AND object_id IN (SELECT object_id FROM sys.objects
WHERE name LIKE ''%ContentItems%'')';

如果要将参数用作字符串,请在参数名称前后添加double

这样地 :

DECLARE @tableName sysname = 'ContentItems'; 

DECLARE @sql nvarchar(max) = N'
        CREATE TRIGGER TR_' + @tableName + N' ON ' + @tableName + N' FOR INSERT
            AS
            BEGIN
               print ''' + @tableName
            +''' END';


    EXEC sp_executesql @sql

如果要将其用作表名,请使用select而不是print,

这样地:

DECLARE @tableName sysname = 'ContentItems';

DECLARE @sql nvarchar(max) = N'
    CREATE TRIGGER TR_' + @tableName + N' ON ' + @tableName + N' FOR INSERT
        AS
        BEGIN
            select * from ' + @tableName
        +' END';


EXEC sp_executesql @sql