Блог

Рассмотрим опасную хранимую процедуру ниже:

 ALTER PROCEDURE [dbo].[ExecDynamicSQL] 
    @sqlToExec nvarchar(2000)
AS
BEGIN
    SET NOCOUNT ON;
    exec sp_sqlexec @sqlToExec;
END
 

Я понимаю, что это очень опасно, потому что оно очень подвержено SQL-инъекциям и что люди могут запускать вредоносные команды. Однако мне нужно выполнить инструкции INSERT или UPDATE, для которых у меня нет фиксированного набора параметров, и поэтому я не могу передать отдельные параметры процедуре.

Есть ли способ каким-то образом передать массив пар имя-значение в качестве одного параметра, а затем заставить хранимую процедуру безопасно построить запрос и выполнить его?

Есть ли альтернативный и безопасный способ добиться этого? Я рассмотрел возможность разделения запроса на разделы Tablename, SET и WHERE (для команд обновления) и передачи 3 параметров соответственно, но я не знаю, устранит ли это риск внедрения SQL.

Хотя я многое из этого рассмотрел в комментариях, я счел целесообразным дать ответ, чтобы дать больше объяснений.

Во-первых, как я уже упоминал, это не тот маршрут, по которому вам следует идти. Да, у вас могут быть процедуры, которые используют динамический SQL, но они не должны обрабатывать такие базовые вещи, как вставка данных в таблицу или обновление указанных данных.

При использовании динамического SQL сначала необходимо убедиться, что вы правильно цитируете свои динамические объекты. Для этого, что не слишком сложно, вы можете просто задать параметр для схемы и имени объекта, а затем, когда вы вводите их, оберните их QUOTENAME . Настоящая проблема возникает из-за последнего, «динамических» столбцов.

Во-первых, вам, похоже, требуется динамическое количество параметров; это серьезная проблема. Вы не можете тривиально или даже просто параметризовать динамические параметры. Вы также не сможете передать эти параметры как их правильный тип; например, вы не сможете передать a date как a date . Я могу представить решение, которое использует динамический динамический SQL (да, я дважды сказал динамический) и sql_variant тип объекта, но должны ли вы это делать? Нет. Если бы вы понимали, как поддерживать такое решение, я ни на секунду не думаю, что вы бы задали вопрос, который у вас есть; у вас было бы что-то, что находится на пути к этому, но нуждалось в некоторой помощи.

Итак, каково решение? Ну, опять же, как я уже говорил в комментариях, у вас должны быть отдельные процедуры для каждой таблицы. Вам также могут понадобиться отдельные операции для INSERT UPDATE операций и, но вы также можете использовать одну и реализовать логику «UPSERT»; есть много хороших статей о том, как это сделать, поэтому я не буду описывать это здесь.

Как я также упоминал в комментариях, это означает обновление ваших процедур при обновлении ваших объектов. Это нормально. Я регулярно обновляю процедуры, когда базовая таблица обновляется, чтобы иметь больше столбцов.

В то же время разработчикам ваших приложений потребуется обновить код своего приложения, чтобы обеспечить передачу новых параметров вашей процедуре. Хорошие devops и отношения между вашими администраторами баз данных, разработчиками SQL и разработчиками приложений являются ключевыми, но это все. Держите эти каналы связи открытыми и активными. Когда вы или ваш администратор базы данных изменяете таблицу, добавляя новые столбцы и исправляя индексы объектов (при необходимости) в вашей среде разработки, и информируете об этом разработчика SQL, вы можете выполнить ALTER необходимые процедуры. Затем вы можете сообщить об этом разработчику приложения, и он сможет обновить код приложения.

После этого завершите внутреннее тестирование, устраните все ошибки / непредвиденное поведение / проблемы с производительностью, а затем переходите к тестовой среде. Попросите своих пользователей подтвердить, что он работает так, как требуется, а затем он отправляется в производство. Другими словами, следуйте основам хорошего цикла разработки.

TL; DR: нужный вам маршрут неверен и никогда не будет масштабироваться. Придерживайтесь обычного цикла разработки и обновляйте базу данных и код приложения синхронно, чтобы можно было предоставлять новые функциональные возможности.

Хорошо, итак, я хотел сделать здесь что-то «тупое», и я имею в виду действительно тупое. Я хотел показать, как безумно выглядела бы такая реализация, чтобы попытаться достичь того, чего вы действительно хотите; и это делает.

Несколько замечаний по этому поводу:

• Никогда не используйте это в рабочей среде.
• Никогда не используйте это ни в какой среде, кроме изолированной среды, чтобы попытаться понять это и насколько это глупо
• Я написал только версию для INSERT . Я не заинтересован в написании UPDATE версии / Upsert.
• Он обрабатывает только вставку 1 строки за раз, не больше и не меньше.
• Никогда не используйте это в рабочей среде.
• Нет, я не буду писать версию для поддержки нескольких строк.
• Это использует sql_variant , и мы все знаем, что вы никогда не должны использовать это.
• Если вы этого не понимаете, не используйте его.
• НИКОГДА НЕ ИСПОЛЬЗУЙТЕ ЭТО В РАБОЧЕЙ СРЕДЕ.
• Я не объясняю, что это работает отдельно от того, что указано в комментариях в ответе.
• Мне также пришлось создать функцию для получения правильных имен объектов в кавычках
  • Таким образом, он должен поддерживать определенные пользователем скалярные типы данных
  • Я не проверял, поддерживает ли он определенные пользователем скалярные типы данных
• Он использовался FOR XML PATH для того, чтобы пользователи более старых версий могли его «протестировать».
• Я упоминал, никогда не используйте это в производстве?

Итак, вот оно. Я не буду поддерживать это, я не заинтересован в его поддержке, потому что вы НЕ должны его использовать. Это было просто то, что я хотел доказать, насколько глупа идея. Это так.

 CREATE DATABASE Demo;
GO
--Creating a new database for an easy "clean up"
USE Demo;
GO
--Single sample table
CREATE TABLE dbo.YourTable (SomeID int IDENTITY(1,1) NOT NULL,
                            SomeDate date NOT NULL,
                            SomeName nvarchar(30),
                            SomeNumber decimal(12,2),
                            EntryDate datetime2(1) NOT NULL DEFAULT SYSUTCDATETIME());

GO
--Create a type for inserting the data into
CREATE TYPE dbo.DataTable AS table (ColumnName sysname NOT NULL,
                                    ColumnValue sql_variant); --Yeah, you saw that right! sql_variant...
GO

--Create a function to return a delimit identified version of a sql_variant's data type
CREATE FUNCTION dbo.QuoteSqlvariant (@SQLVariant sql_variant) 
RETURNS nvarchar(258)
AS 
BEGIN
    RETURN QUOTENAME(CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')))  
           CASE WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'char',N'varchar') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'MaxLength')),N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'nchar',N'nvarchar') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'MaxLength'))/2,N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'datetime2',N'datetimeoffset',N'time') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'Scale')),N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'decimal',N'numeric',N'time') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'Precision')),N',',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'Scale')),N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'varbinary') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'TotalBytes'))-4,N')')
                ELSE N''
           END;
END
GO
--Sample outputs of the function for varying data types
SELECT dbo.QuoteSqlvariant(CONVERT(sql_variant,GETDATE())),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,N'Hello')),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,'Goodbye')),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,CONVERT(varbinary(10),N'Hello'))),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,CONVERT(varbinary(7),'Goodbye'))),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,SYSDATETIME())),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,SYSDATETIMEOFFSET())),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,1.23)),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,CONVERT(decimal(3,2),1.23)));
GO
--The "solution"
CREATE PROC dbo.CompletelyDynamicInsert @Schema sysname, @Table sysname, @Data dbo.DataTable READONLY, @EXEC nvarchar(MAX) = NULL OUTPUT, @SQL nvarchar(MAX) = NULL OUTPUT AS
BEGIN

    --Let the madness begin
    SET NOCOUNT ON;
    --First we need to create the initial INSERT INTO. This is the "Easy" part...
    DECLARE @CRLF nchar(2) = NCHAR(13)   NCHAR(10);
    SET @SQL = N'INSERT INTO '   QUOTENAME(@Schema)   N'.'   QUOTENAME(@Table)   N' ('  
               STUFF((SELECT N','   QUOTENAME(ColumnName)
                      FROM @Data
                      ORDER BY ColumnName ASC--Ordering is VERY important
                      FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,1,N'')   N')'   @CRLF  
               N'VALUES(';

    --Now for the VALUES clause
    SET @SQL = @SQL  
               STUFF((SELECT CONCAT(N',CONVERT(',dbo.QuoteSqlvariant(ColumnValue), N',@p', ROW_NUMBER() OVER (ORDER BY ColumnName ASC),N')',N' COLLATE '   CONVERT(sysname,SQL_VARIANT_PROPERTY(ColumnValue,'Collation')))
                      FROM @Data
                      ORDER BY ColumnName ASC
                      FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,1,N'')   N');'
    --But we need to parmetrise this, so we need to generate a parmeters parameter
    DECLARE @Params nvarchar(MAX);
    SET @Params = STUFF((SELECT CONCAT(N',@p', ROW_NUMBER() OVER (ORDER BY ColumnName ASC), ' ', dbo.QuoteSqlvariant(ColumnValue))
                         FROM @Data
                         ORDER BY ColumnName ASC
                         FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,1,N'');
    
    --But, we can't just pass the values from @Data, no... Now we need a dynamic dynamic statement. Oh yay..?
    
    SET @EXEC = N'DECLARE '   STUFF((SELECT CONCAT(N',@p', ROW_NUMBER() OVER (ORDER BY ColumnName ASC), ' ', dbo.QuoteSqlvariant(ColumnValue))
                                    FROM @Data
                                    ORDER BY ColumnName ASC
                                    FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,1,N'')   N';';
    SET @EXEC = @EXEC   @CRLF  
                STUFF((SELECT @CRLF   
                              CONCAT(N'SET ',N'@p', ROW_NUMBER() OVER (ORDER BY ColumnName ASC),N' = (SELECT MAX(CASE WHEN ColumnName = N',QUOTENAME(ColumnName,''''),N' THEN CONVERT(',dbo.QuoteSqlvariant(ColumnValue), N',ColumnValue) END) FROM @Data);')
                       FROM @Data
                       ORDER BY ColumnName ASC
                       FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,2,N'');

    SET @EXEC = @EXEC   @CRLF   
                N'EXEC sys.sp_executesql @SQL, @Params,'   
                STUFF((SELECT CONCAT(N', @p', ROW_NUMBER() OVER (ORDER BY ColumnName ASC))
                       FROM @Data
                       ORDER BY ColumnName ASC
                       FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,1,N'')   N';';
    
    EXEC sys.sp_executesql @EXEC, N'@SQL nvarchar(MAX), @Params nvarchar(MAX), @Data dbo.DataTable READONLY', @SQL, @Params, @Data;

END;
GO

DECLARE @Data dbo.DataTable;
INSERT INTO @Data (ColumnName,ColumnValue)
VALUES(N'SomeDate',CONVERT(sql_variant,CONVERT(date,'20210101'))), --yes, the insert into this will look dumb like this. YOu need to explicitly convert them all to a sql_variant
      (N'SomeName',CONVERT(sql_variant,N'Larnu')),
      (N'SomeNumber',CONVERT(sql_variant,CONVERT(decimal(12,2),1732.12)));


DECLARE @EXEC nvarchar(MAX),
        @SQL nvarchar(MAX);
EXEC dbo.CompletelyDynamicInsert N'dbo',N'YourTable', @Data, @EXEC OUTPUT, @SQL OUTPUT;

PRINT @EXEC;
PRINT @SQL;
GO

SELECT *
FROM dbo.YourTable;
GO

USE master;
GO

DROP DATABASE Demo;
 

db<>скрипка