Можно ли выдавать операторы CREATE, используя sp_executesql с параметрами?

Ответ 1

Если вы выполните оператор create trigger который, как вы сказали, вы напечатали... вы обнаружите, что он не работает. Оператор print в теле триггера пытается вывести @tablename, но никогда не определяется, поэтому вы получите сообщение об ошибке:

Необходимо объявить скалярную переменную "@tableName".

Но это не ваша главная проблема. Что касается того, почему вы не можете выполнить инструкцию DDL с execute_sql с параметрами, я не смог найти документацию, объясняющую почему... но ваш опыт и другие доказывают, что это хлопотно. Я считаю, что этот пост имеет довольно хорошую теорию: sp_executesql добавляет операторы в исполняемый динамический скрипт?

Однако вы можете выполнить динамический sql с операторами DDL, используя инструкцию EXECUTE. Поэтому вы можете создать параметризованный оператор sp_executesql который проверяет имя вашей таблицы, а затем создает динамическую строку sql для выполнения с оператором EXECUTE.

Это не выглядит красиво, но это работает:

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', @[email protected];

Вы также можете преобразовать это в хранимую процедуру с параметрами вместо запуска 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

Ответ 2

Я настоятельно рекомендую использовать Dynamic 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', @[email protected]

... вы пытаетесь ввести ваше объявленное @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', @[email protected]

... который даст вам строку...

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

... который может затем принять параметр, через который вы проходите...

EXEC sp_executesql @sql, N'@tableName sysname', @[email protected] ;

Опять же, я бы использовал некоторую тщательную проверку (и белый список) перед передачей чего-либо в динамический SQL, который будет использовать имя динамической таблицы.

ПРИМЕЧАНИЕ. Как отмечено ниже, я считаю, что вы ограничены в операторах DML, которые могут выполняться с помощью sp_executesql(), и я думаю, что параметризация также ограничена. И, судя по вашим другим комментариям, это не похоже на то, что вам действительно нужен динамический процесс, а способ повторить определенную задачу для нескольких элементов. Если это так, я рекомендую сделать это вручную с помощью копирования/вставки, а затем выполнить операторы.

Ответ 3

Так как SQL работает нормально либо напрямую, либо когда не передаются параметры для sp_executesql, похоже, что я столкнулся с настоящим ограничением в механизме SQL, но я нигде не вижу его документированного.

Такое поведение задокументировано, хотя и не интуитивно понятно. Соответствующая выдержка из документации по теме ограничений триггера:

CREATE TRIGGER должен быть первым оператором в пакете

Когда вы выполняете параметризованный запрос, объявления параметров считаются частью пакета. Следовательно, пакет CREATE TRIGGER (и другие операторы CREATE для объектов программируемости, таких как процессы, функции и т.д.) Не могут быть выполнены как параметризованный запрос.

Недопустимое сообщение об ошибке синтаксиса, которое вы получаете при попытке запустить CREATE TRIGGER в качестве параметризованного запроса, не особенно полезно. Ниже приведена упрощенная версия вашего кода с использованием недокументированного и неподдерживаемого внутреннего параметризованного синтаксиса запроса.

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

По крайней мере, это приводит к ошибке, вызывающей ограничение CREATE TRIGGER:

Сообщение 1050, уровень 15, состояние 1, строка 73 Этот синтаксис разрешен только для параметризованных запросов. Сообщение 111, уровень 15, состояние 1, строка 73 "CREATE TRIGGER" должно быть первым оператором в пакете запроса.

Аналогично, выполнение другого параметризованного оператора с помощью этого метода выполняется успешно:

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

Но если вы на самом деле не используете параметр в пакете, возникает ошибка

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

Сообщение 1050, уровень 15, состояние 1, строка 75 Этот синтаксис разрешен только для параметризованных запросов.

Суть в том, что вам нужно построить оператор CREATE TRIGGER в виде строки без параметров и выполнить оператор как непараметрический запрос для создания триггера.

Ответ 4

Можно ли выдавать операторы CREATE, используя sp_executesql с параметрами?

Простой ответ "Нет", вы не можете

По данным MSDN

Как правило, параметры действительны только в инструкциях языка манипулирования данными (DML), но не в операторах языка определения данных (DDL)

Вы можете проверить более подробную информацию об этом утверждении Параметры

В чем проблема?

Параметры допускаются только вместо скалярных литералов, таких как строки или даты в кавычках или числовые значения. Вы не можете параметризовать операцию 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 

Ответ 5

Вы не должны объединять @tableName для sp_executesql а вместо этого делать его частью строки. Это будет переменная, которую вы объявляете для sp_executesql. Я бы также изменил имя параметра, которому вы присваиваете значение, чтобы они не совпадали.

Что-то вроде:

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

Возможно, вам понадобится добавить несколько кавычек вокруг @tableName для оператора PRINT но, вероятно, нет.

Использование QUOTENAME() никогда не является плохой идеей.

Ответ 6

Лично я ненавижу триггеры и стараюсь избегать их большую часть времени;)

Однако, если вам действительно нужен этот динамический материал, вы должны использовать sp_MSforeachtable и избегать инъекций (как указал Шон) любой ценой:

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%'')';

Ответ 7

Если вы хотите использовать параметр в качестве строки, добавьте 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

Ответ 8

Можно ли выдавать операторы CREATE, используя 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 '''''[email protected]+'''''
        END''
        )'                            -- End of EXECUTE()


EXEC sp_executesql @sql, N'@tableName sysname', @[email protected]

Список настроек:

1. Требуется дополнительная EXECUTE, комментарий ниже объясняет почему
2. Добавлены дополнительные квадратные скобки, чтобы сделать SQL-инъекции немного сложнее

Я ищу конкретные (в идеале, документированные) ограничения sp_executesql с параметрами, и если есть какие-либо обходные пути для этих конкретных ограничений (за исключением не использования параметров)

в этом случае это ограничение DDL-команд, а не sp_executesql. Операторы DDL не могут быть параметризованы с помощью переменных. Документация Microsoft гласит:

Переменные можно использовать только в выражениях, а не вместо имен объектов или ключевых слов. Чтобы создать динамические операторы SQL, используйте EXECUTE.

источник: DECLARE (Transact-SQL)

Поэтому решение с EXECUTE предоставляется мной в качестве обходного пути.