SQL-Ex blog

Не используйте динамический SQL, если ваш оператор не является динамическим

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

CREATE PROC MyProc @ID int AS

BEGIN

    DECLARE @SQL nvarchar(MAX);

    SET @SQL = N'SELECT * FROM MyTable WHERE ID = ' + CONVERT(nvarchar(MAX),@ID);

    EXEC(@SQL);

END;

В этом операторе действительно нет ничего "динамического". @ID не требуется встраивать в оператор, как и не требуется использовать переменную для хранения оператора. Вы могли бы просто заменить все параметризованным оператором:

CREATE PROC MyProc @ID int AS

BEGIN

    SELECT * FROM MyTable WHERE ID = @ID;

END;

Правильно закавычивайте имена ваших объектов

При использовании динамического SQL весьма важно правильно закавычивать имена объектов. Это подразумевает, что вы не встраиваете необработанное значение вашего динамического объекта непосредственно в динамический оператор, как показано ниже.

SET @SQL = N'SELECT * FROM ' + @Table;

SET @SQL = N'SELECT * FROM [' + @Table + N']';

Оба эти оператора одинаково плохи; простое заключение имени динамического объекта в пару скобок не делают ваш код "защищенным" от инъекции. Точно так же, как и для литеральной строки, скобки могут быть экранированы, и злоумышленник может попытаться экранировать значение.

К счастью, SQL Server имеет удобную функцию для вашей безопасности, которой является QUOTENAME. Просто оберните в функцию вашу переменную с именем динамического объекта, и функция автоматически заключит имя в скобки и (что не менее важно) экранирует соответствующим образом любой символ.

SET @SQL = N'SELECT * FROM ' + QUOTENAME(@Table);

Не встраивайте значения параметров

При написании динамических операторов вам, весьма вероятно, также потребуется передать в запрос значение параметра. Это не значения динамических объектов, а нечто подобно части булева выражения в предложении WHERE. Способ передачи параметров не похож на приведенный ниже пример:

SET @SQL = N'SELECT * FROM ' + QUOTENAME(@MyTable) +

           N' WHERE ID = ' + CONVERT(nvarchar(MAX),@ID);

EXEC (@SQL);

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

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

SET @SQL = N'SELECT * FROM ' + QUOTENAME(@MyTable) +

           N' WHERE ID = @ID;';

EXEC sp_executesql @SQL, N'@ID int', @ID = @ID;

Это может также использоваться для вывода значений:

SET @SQL = N'SELECT @Count = COUNT(*) FROM ' + QUOTENAME(@MyTable) + N';';

EXEC sp_executesql @SQL, N'@Count bigint OUTPUT', @Count = @Count OUTPUT;

Параметризация динамического оператора имеет еще то преимущество, что план выполнения может использоваться повторно (когда значение динамического объекта то же самое). Если вы встраиваете значение параметра, то план не может использоваться повторно. Движок будет создавать отдельные планы запросов для предложений WHERE ID=1 и WHERE ID=2; однако при WHERE ID=@ID будет использован тот же самый план, независимо от значения @ID.

Найдите время для форматирования динамического SQL

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

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'INSERT INTO dbo.Emails(Email) ' +

           STUFF((SELECT N'UNION ALL ' + 

                         N'SELECT Email '+

                         N'FROM ' + QUOTENAME(t.[name])

                  FROM sys.tables t

                       JOIN sys.columns c ON t.object_id = c.object_id

                  WHERE c.[name] = N'Email'

                  FOR XML PATH(N'')),1,9,'') + N';';

EXEC sp_executesql @SQL;

Запрос выглядит хорошо отформатированным, и его достаточно легко читать. Проблема состоит в том, что динамический SQL возвращает ошибку (возможно, что-то типа неверного имени объекта). Следовательно, перед выполнением динамического оператора вы напечатаете его выражение, которое будет выглядеть примерно так:

INSERT INTO dbo.Emails(Email) SELECT Email FROM [MyTable_A] UNION ALL SELECT Email FROM [MyTable_B] UNION ALL SELECT Email FROM [MyTable_C] UNION ALL SELECT Email FROM [MyTable_D] UNION ALL SELECT Email FROM [MyTable_E] UNION ALL SELECT Email FROM [MyTable_F] UNION ALL SELECT Email FROM [MyTable_G] UNION ALL SELECT Email FROM [MyTable_H] UNION ALL SELECT Email FROM [MyTable_I] UNION ALL SELECT Email FROM [MyTable_J];

Теперь этот как бы "хорошо написанный" динамический оператор выглядит совершенно уродливо. Итак, как пофиксить это? Я предпочитаю добавлять переводы каретки и подачу строки, а также отступы в мои динамические операторы точно также, как и в любой "нормальный" запрос. Я склоняюсь к NCHAR(13) и NCHAR(10) для перевода каретки и подачи строки соответственно, но вы можете добавить значения, которые пожелаете. Теперь вышеприведенный запрос можно написать примерно так:

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'INSERT INTO dbo.Emails(Email)' + NCHAR(13) + NCHAR(10) +

           STUFF((SELECT NCHAR(13) + NCHAR(10) +

                         N'UNION ALL' + NCHAR(13) + NCHAR(10) +

                         N'SELECT Email' + NCHAR(13) + NCHAR(10) +

                         N'FROM ' + QUOTENAME(t.[name])

                  FROM sys.tables t

                       JOIN sys.columns c ON t.object_id = c.object_id

                  WHERE c.[name] = N'Email'

                  FOR XML PATH(N''),TYPE).value(N'.','nvarchar(MAX)'),1,13,'') + N';';

PRINT @SQL;

Если вы затем напечатаете оператор из этого запроса, то результат будет значительно более читабельным.

INSERT INTO dbo.Emails(Email)

SELECT Email FROM [MyTable_A]

UNION ALL

SELECT Email FROM [MyTable_B]

UNION ALL

SELECT Email FROM [MyTable_C]

UNION ALL

SELECT Email FROM [MyTable_D]

UNION ALL

SELECT Email FROM [MyTable_E]

UNION ALL

SELECT Email FROM [MyTable_F]

UNION ALL

SELECT Email FROM [MyTable_G]

UNION ALL

SELECT Email FROM [MyTable_H]

UNION ALL

SELECT Email FROM [MyTable_I]

UNION ALL

SELECT Email FROM [MyTable_J];

Когда вы пишете более "традиционный" запрос, применяется та же логика. Подобный запрос "выглядит" хорошо отформатированным:

DECLARE @TableName sysname = N'MyTable';

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT @TableName AS TableName,' +

           N'ID,' +

           N'[name] AS CustomerName ' + 

           N'FROM ' + QUOTENAME(@TableName) + N' ' +

           N'WHERE ID = @ID ' +

           N'AND Status = ''Active'';';

PRINT @SQL;

--EXEC sp_executesql @SQL, N'@ID int', @ID = @ID;

Но, напечатав, превращается в однострочный SQL, который может стать трудным для отладки. Если же вы также добавите форматирование в динамический оператор, то он станет значительно более читабельным, например:

DECLARE @TableName sysname = N'MyTable';

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT @TableName AS TableName,' + NCHAR(13) + NCHAR(10) +

           N'       ID,' + NCHAR(13) + NCHAR(10) +

           N'       [name] AS CustomerName' + NCHAR(13) + NCHAR(10) +

           N'FROM ' + QUOTENAME(@TableName) + NCHAR(13) + NCHAR(10) +

           N'WHERE ID = @ID' + NCHAR(13) + NCHAR(10) +

           N'  AND Status = ''Active'';';

PRINT @SQL;

--EXEC sp_executesql @SQL, N'@ID int', @ID = @ID;

Форматирование может кому-то показаться несколько странным, но я считаю его неоценимым при работе с очень сложными динамическими операторами. Вышеприведенные операторы на самом деле не теряют читабельности, хотя могут показаться показаться несколько запутанными для новичков. Но что вы предпочтете, некоторое усложнение в точке генерации, или нечитабельную кучу кода, когда вы распечатываете значение динамического оператора? Я бы предпочел первое.

Выполняйте проверку по белому списку, чтобы избежать ошибок о несуществующих объектах и сделать ваши операторы более безопасными

Это особенно важно, если вы хотите избежать проникновения ошибок обратно на уровень представления. При динамических именах объектов этого можно достичь проверкой значений динамических объектов в системных таблицах, например, sys.tables или INFORMATION_SCHEMA.COLUMNS, или в вашей собственной таблице "допустимых" значений. Передача значения NULL в sp_executesql не вызывает ошибки, но также ничего не выполняет; что делает ваш оператор даже более безопасным от попыток инъекции.

DECLARE @TableName sysname = N'MyTable]; CREATE DATABASE Test;--';

DECLARE @SQL nvarchar(MAX);

SELECT @SQL = N'SELECT *' + NCHAR(13) + NCHAR(10) +

              N'FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';'

FROM sys.tables t

     JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE t.[name] = @TableName

  AND s.[name] = N'dbo';

PRINT @SQL;

EXEC sp_executesql @SQL;

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

Не используйте nvarchar(MAX) для параметра с именем объекта

Максимальная длина имени объекта в SQL Server составляет 128 символов, поэтому зачем вам необходимо пространство в 2Гб? Очевидно, не нужно. Как можно увидеть, я использовал тип данных sysname, что является синонимом для nvarchar(128). Если вы знаете, что имена ваших объектов будут короче 128 символов, то однозначно используйте меньшую длину, но никогда нет необходимости в длине свыше 128 символов, если вы работаете с объектами SQL Server. Я рекомендую использовать nvarchar, а не varchar, поскольку объект может содержать символы Юникода. Просто потому, что вы "знаете", что ваши объекты не содержат никаких из этих символов в данный момент, не означает, что они не появятся в будущем (а параметр также должен неявно преобразовываться к nvarchar в соответствии с требованиями к допустимым системным объектам).

Никогда не отлаживайте код, который создает динамический SQL, до предварительной отладки сгенерированного оператора SQL

Продолжая мой прежний тезис о том, чтобы сделать ваш SQL легче для отладки с помощью форматирования внутри динамического оператора, добавлю, что зачастую легче сначала заниматься отладкой сгенерированного оператора, а затем уже кода, который его создает. Используя Print (или оператор SELECT, если ваш динамический SQL превышает 4000 символов), вы можете проверить SQL, который будет запущен. Вы можете затем выполнить этот код самостоятельно, чтобы посмотреть, где генерируется ошибка и, что более важно, почему. Выяснив, почему он сбоит, вы можете затем распространить решение на ваш SQL, который создает динамический оператор.

Иногда причины ошибок могут быть очень простыми, типа пропущенного пробела между двумя таблицами. Это может быть очень сложно заметить, когда ваш оператор подобен литеральной строке. Получение значения вашего динамического оператора означает, что вы сможете увидеть код в том виде, в каком он должен выполняться, со всеми теми необычными цветами, которые вы предпочитаете в своем UI, что сделает процесс отладки кода еще проще.