Динамический запрос в хранимой процедуре не возвращает ошибку, но не может вставить запись

ProgramBox

Динамический запрос в хранимой процедуре не возвращает ошибку, но не может вставить запись

Post author:admin
Запись опубликована:7 мая, 2023
Post category:Вопросы по программированию

#sql #sql-server

#sql #sql-сервер

Вопрос:

Я пытаюсь написать хранимую процедуру, которая будет вставлять записи, в которых столбцы 1 и 2 являются «исправленными», в то время как столбцы 3, column4 и column5 изменяются. Иногда нет даже столбца3, или столбца4, или столбца5.

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

Вот мой код:

 ALTER PROCEDURE [dbo].[usp_Insert]
    @param1 nvarchar(MAX) = NULL, 
    @param2 nvarchar(MAX) = NULL, 
    @param3 nvarchar(MAX) = NULL, --optional
    @param4 nvarchar(MAX) = NULL, --optional
    @param5 nvarchar(MAX) = NULL, --optional
    @col3 nvarchar(50) = NULL, --optional
    @col4 nvarchar(50) = NULL, --optional
    @col5 nvarchar(50) = NULL  --optional

AS

SET NOCOUNT ON;
DECLARE @QRY NVARCHAR(MAX) = '';

BEGIN
    IF NOT EXISTS (Select ...)
        BEGIN TRY
            BEGIN TRANSACTION
            SET @QRY = @QRY   ' DECLARE @param1 nvarchar(MAX) = '   CHAR(39)   @param1   CHAR(39)   ';'
            SET @QRY = @QRY   ' DECLARE @param2 nvarchar(MAX) = '   CHAR(39)   @param2   CHAR(39)   ';'
            IF @param3 is not null
                SET @QRY = @QRY   ' DECLARE @param3 nvarchar(MAX) = '   CHAR(39)   @param3   CHAR(39)   ';'
            IF @param4 is not null
                SET @QRY = @QRY   ' DECLARE @param4 nvarchar(MAX) = '   CHAR(39)   @param4   CHAR(39)   ';'
            IF @param5 is not null
                SET @QRY = @QRY   ' DECLARE @param5 nvarchar(MAX) = '   CHAR(39)   @param5   CHAR(39)   ';'

            SET @QRY = @QRY   ' INSERT INTO tableName (column1, column2' 
            IF @col3 is not null
                SET @QRY = @QRY   ', '   @col3
            IF @col4 is not null
                SET @QRY = @QRY   ', '   @col4
            IF @col5 is not null
                SET @QRY = @QRY   ', '   @col5
            SET @QRY = @QRY   ') VALUES '
            SET @QRY = @QRY   ' (@param1, @param2 '
            IF @col3 is not null
                SET @QRY = @QRY   ', @param3'
            IF @col4 is not null
                SET @QRY = @QRY   ', @param4'
            IF @col5 is not null
                SET @QRY = @QRY   ', @param5'
            SET @QRY = @QRY   ')'

            EXEC (@QRY)

            COMMIT TRANSACTION
        END TRY
        BEGIN CATCH
            IF (@@TRANCOUNT > 0) 
                BEGIN  
                    ROLLBACK TRANSACTION   
                    RAISERROR('Exception occurred. Transaction rolled back.',16,1)
                END
        END CATCH
    ELSE
        BEGIN  
            RAISERROR('Duplicate record found. Record not inserted.',16,1)
        END
END

Если есть лучший способ достичь того, что я пытаюсь сделать, пожалуйста, просветите меня. Спасибо!

РЕДАКТИРОВАТЬ: Прошу прощения за путаницу. Да, в моем коде есть Begin и Commit. Кроме того, имена столбцов 3, 4 и 5 также передаются в качестве параметров хранимой процедуре. Вот почему я решил использовать динамический запрос. Я соответствующим образом отредактировал приведенный выше код. Мне очень жаль, что я пропустил эту очень важную часть!!

1. Какую СУБД вы используете? (Этот код зависит от продукта.)

2. Вы отладили то, что находится в @qry , прежде чем пытаться выполнить это?

3. если вы объединяете с переменной, которая является NULL , то вся конкатинация также будет равна null

4. возможно, если вы используете print (@QRY) и запустите его вручную, вы могли бы узнать больше

5. @GuidoG — Хорошее замечание, я думал, что все переменные проверялись перед объединением, но @param1 и @param2 это не так.

Ответ №1:

Одна проблема, которую я вижу (благодаря @GuidoG), заключается в том, что если @param1 или @param2 являются, то NULL ваша @QRY строка будет NULL .

Вы могли бы заменить эти строки…

         SET @QRY = @QRY   ' DECLARE @param1 nvarchar(MAX) = '   CHAR(39)   ISNULL(@param1, 'NULL')   CHAR(39)   ';'
        SET @QRY = @QRY   ' DECLARE @param2 nvarchar(MAX) = '   CHAR(39)   ISNULL(@param2, 'NULL')   CHAR(39)   ';'

Лично я бы все равно пропустил части параметров. Вы не избегаете их, поэтому открыты для атак SQL-инъекций и / или неожиданных сбоев.

sp_executesql позволяет избежать этих проблем…

         SET @QRY = @QRY   ' INSERT INTO tableName (column1, column2' 
        IF @param3 is not null
            SET @QRY = @QRY   ', column3'
        IF @param4 is not null
            SET @QRY = @QRY   ', column4'
        IF @param5 is not null
            SET @QRY = @QRY   ', column5'
        SET @QRY = @QRY   ') VALUES '
        SET @QRY = @QRY   ' (@param1, @param2 '
        IF @param3 is not null
            SET @QRY = @QRY   ', @param3'
        IF @param4 is not null
            SET @QRY = @QRY   ', @param4'
        IF @param5 is not null
            SET @QRY = @QRY   ', @param5'
        SET @QRY = @QRY   ')'

        EXEC sp_executesql
          @QRY,  
          N'@param1 NVARCHAR(MAX),
            @param2 NVARCHAR(MAX),
            @param3 NVARCHAR(MAX),
            @param4 NVARCHAR(MAX),
            @param5 NVARCHAR(MAX)',
          @param1,
          @param2,
          @param3,
          @param4,
          @param5

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

Редактировать:

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

 INSERT INTO
    tableName(
      column1,
      column2,
      column3,
      column4,
      column5
    )
SELECT
    @param1,
    @param2,
    ISNULL(@param3, MAX(CASE WHEN COLUMN_NAME = 'column3' THEN COLUMN_DEFAULT END)),
    ISNULL(@param4, MAX(CASE WHEN COLUMN_NAME = 'column4' THEN COLUMN_DEFAULT END)),
    ISNULL(@param5, MAX(CASE WHEN COLUMN_NAME = 'column5' THEN COLUMN_DEFAULT END))
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
        TABLE_SCHEMA = 'dbo'        -- or whatever it really is in your case 
    AND TABLE_NAME   = 'tableName'
;

Для этого вообще не нужен динамический SQL. Но я не уверен, что это лучше, чем динамический SQL.

ПРАВКА2:

О!!!

Ваш код показывает ROLLBACK при обработке ошибок, что подразумевает, что в коде есть BEGIN TRANSACTION то, что вы нам не показываете?

У вас действительно есть COMMIT TRANSACTION где-нибудь???

1. Да, отсутствующий запрос COMMIT может просто «убрать» данные под ковер и никогда не сохранять их в таблице ….. хороший улов!

2. Я использовал sp_executesql. Я не знал, что динамические запросы в хранимых процедурах уязвимы для внедрения SQL. Я просто подумал, что это не так, поскольку я все равно все еще использую параметры. Спасибо, что дали мне знать.

3. @Artemis — Объединение строк для встраивания значений параметров в строку SQL всегда приводит к одному и тому же результату; если вы не перепрыгнете через препятствия, такие символы, как ' в строке, вызовут у вас проблемы. И эти проблемы выявляют уязвимости. Фактическая параметризация является наиболее надежным способом избежать этих проблем / уязвимостей, независимо от того, создаете ли вы строку SQL на Python или в TSQL.

4. @Artemis — Прочитав вашу правку к вопросу, использование вами параметров для определения имен столбцов вызывает ту же уязвимость при атаке sql-инъекцией. Вы должны явно доверять источнику, который передает имя столбца, потому что, если источник хотел злоупотребить им и запустить любой произвольный код, они могли бы. Лучший подход — сверить имена входящих столбцов со списком допустимых значений (белый список) . Возможно, даже сверяется с информационной схемой, чтобы убедиться, что столбцы действительно существуют.

5. @MatBailie — Не защитит ли sp_executesql меня от атак sql-инъекций полностью?

Ответ №2:

Вы должны проверить свои параметры на наличие NULL, посмотрите на этот пример

 declare @param1 nvarchar(10) = null
declare @QRY nvarchar(100)

set @QRY = 'test '   @param1

select @QRY

результатом будет NULL потому что одно из значений конкатинации было null

Поэтому вам следует проверить наличие null и заменить текстом ‘NULL’ или каким-либо другим значением,
если мы это сделаем, то нашей конкатинации больше не будет NULL , посмотрите на этот пример еще раз

 declare @param1 nvarchar(10) = null
declare @QRY nvarchar(100)

set @QRY = 'test '   isnull(@param1, 'null')

select @QRY

это приведет к test null

Возможно, именно в этом заключается ваша проблема

Поэтому я рекомендую изменить это на

 SET @QRY = @QRY   ' DECLARE @param1 nvarchar(MAX) = '   CHAR(39)   ISNULL(@param1, 'NULL')   CHAR(39)   ';'
SET @QRY = @QRY   ' DECLARE @param2 nvarchar(MAX) = '   CHAR(39)   ISNULL(@param2, 'NULL')   CHAR(39)   ';

Ответ №3:

используйте sp_executesql и передайте параметры в качестве parameters:

 ALTER PROCEDURE [dbo].[usp_Insert]
    @param1 nvarchar(MAX) = NULL, 
    @param2 nvarchar(MAX) = NULL, 
    @param3 nvarchar(MAX) = NULL, --optional
AS

SET NOCOUNT ON;
DECLARE @QRY NVARCHAR(MAX);

BEGIN
    IF NOT EXISTS (Select ...)
            SET @QRY = N'INSERT INTO tableName (column1, column2' 
            IF @param3 is not null
                SET @QRY = @QRY   N', column3'
            SET @QRY = @QRY   N') VALUES '
            SET @QRY = @QRY   N' (@p1, @p2 '
            IF @param3 is not null
                SET @QRY = @QRY   N', @p3'
            SET @QRY = @QRY   N')'

            exec sp_executesql @QRY, N'@p1 NVARCHAR(MAX), @p2 NVARCHAR(MAX), @p3 NVARCHAR(MAX)', @param1, @param2, @param3;

    END
END

@param... являются локально сохраненными параметрами процедуры. @p1 … @px являются динамическими параметрами контекста SQL. Можно ли передавать дополнительные параметры, которые не используются ( @p3 когда NULL).

Также ваша ПОПЫТКА НАЧАТЬ … Неправильная обработка транзакции CATCH. Смотрите http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions / для правильного шаблона.

Ответ №4:

Я предполагаю, что вы делаете это, чтобы вставить значения по умолчанию для столбцов 3,4,5

Этот способ намного проще и предотвращает внедрение

 ALTER PROCEDURE [dbo].[usp_Insert]
    @param1 nvarchar(MAX) = NULL, 
    @param2 nvarchar(MAX) = NULL, 
    @param3 nvarchar(MAX) = NULL, --optional
    @param4 nvarchar(MAX) = NULL, --optional
    @param5 nvarchar(MAX) = NULL  --optional

AS

SET NOCOUNT ON;
DECLARE @QRY NVARCHAR(MAX) = '';

BEGIN
    IF NOT EXISTS (Select ...)
        BEGIN TRY

            SET @QRY = 'INSERT INTO TABLE_NAME (Col1, Col2, Col3, Col4, Col5) 
                VALUES (@Param1, @Param2, @Param3, @Param4, @Param5)'
            IF @Param3 IS NULL
                SET @QRY = REPLACE(@Qry, '@Param3', 'DEFAULT')
            IF @Param4 IS NULL
                SET @QRY = REPLACE(@Qry, '@Param4', 'DEFAULT')
            IF @Param5 IS NULL
                SET @QRY = REPLACE(@Qry, '@Param5', 'DEFAULT')
            sp_executesql @QRY, 
                 N'@param1 nvarchar(MAX), 
                 @param2 nvarchar(MAX),
                 @param3 nvarchar(MAX),
                 @param4 nvarchar(MAX),
                 @param5 nvarchar(MAX)',
                 @param1, @param2, @param3, @param4, @param5

        END TRY
        BEGIN CATCH
            IF (@@TRANCOUNT > 0) 
                BEGIN  
                    ROLLBACK TRANSACTION   
                    RAISERROR('Exception occurred. Transaction rolled back.',16,1)
                END
        END CATCH
    ELSE
        BEGIN  
            RAISERROR('Dulicate record found. Record not inserted.',16,1)
        END
END

если необязательные параметры равны null, ваш динамический sql будет выглядеть следующим образом

 INSERT INTO TABLE_NAME (Col1, Col2, Col3, Col4, Col5) 
VALUES (@Param1, @Param2, DEFAULT, DEFAULT, DEFAULT)

Что является полностью допустимым

Ответ №5:

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

Кажется, что вы просто хотите вставить от двух до пяти значений полей в новую запись в одной из ваших таблиц.

Мой самый важный вопрос здесь заключается в том, имеют ли эти три необязательных поля какие-либо значения по умолчанию, отличные от NULL (с использованием ограничения по УМОЛЧАНИЮ). Если это так, я бы также предоставил эти значения по умолчанию в качестве значений по умолчанию для параметров вашей хранимой процедуры.

И я бы удалил значения по умолчанию из первых двух обязательных параметров.

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

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

 ALTER PROCEDURE [dbo].[usp_Insert]
    @param1 NVARCHAR(MAX), 
    @param2 NVARCHAR(MAX), 
    @param3 NVARCHAR(MAX) = NULL, --optional
    @param4 NVARCHAR(MAX) = NULL, --optional
    @param5 NVARCHAR(MAX) = N'Some default value other than NULL'  --optional
AS
    SET NOCOUNT ON;
BEGIN
    IF NOT EXISTS (SELECT ...)
        BEGIN TRY
            INSERT INTO tableName (column1, column2, column3, column4, column5)
            VALUES (@param1, @param2, @param3, @param4, @param5);
        END TRY
        BEGIN CATCH
            IF (@@TRANCOUNT > 0) 
                BEGIN  
                    ROLLBACK TRANSACTION   
                    RAISERROR('Exception occurred. Transaction rolled back.', 16, 1);
                END;
        END CATCH;
    ELSE
        BEGIN  
            RAISERROR('Dulicate record found. Record not inserted.', 16, 1);
        END;
END;
GO

Но это всего лишь мои личные предпочтения…

Дополнительное примечание:

Мне действительно нравится ваша структура SP, в которой вы устанавливаете любые инструкции preparation перед первой инструкцией BEGIN. Я лично всегда использую BEGIN…ЗАВЕРШАЮЩИЙ блок после AS, но вы можете добавить дополнительное разделение между подготовкой и выполнением. Приятно. 😉

1. Я частично согласен, но параметры хранимой процедуры и столбцы таблицы не имеют одинакового поведения, когда дело доходит до обнуляемости, значений по умолчанию, ограничений и т.д…. Если эти параметры уже ограничены в схеме базы данных, любое изменение подразумевает выполнение этого и в хранимых процедурах. Если бы было допустимо что-то вроде ISNULL(@Param, DEFAULT), наша жизнь была бы проще…

2. Верно. Но это недопустимо. Итак, вам нужен обходной путь или редизайн. Я просто привел свою лучшую (самую короткую и простую) альтернативу, как я ее вижу.

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

4. Да, я понимаю дилемму. Но именно поэтому я просто стараюсь вообще избегать ограничений по УМОЛЧАНИЮ, поскольку, по моему мнению, они вообще не влияют на структуру данных и целостность. ИМХО, логика клиента должна отвечать за заполнение / ввод данных, а база данных — за проверку целостности данных. Если Artemis не использует никаких ограничений по умолчанию для этих полей (на что я надеюсь), все в порядке. Помимо всего этого, я предполагаю, что хранимая процедура со статическим SQL также работает лучше, чем с динамическим SQL. Вот почему я бы принял эти дублированные значения по умолчанию (при необходимости) как должное.

5. Я думаю, что уже использует значения по умолчанию, это единственная возможная причина попробовать вставить таким образом