使用安全存储过程插入(任何表的自定义命令)一组值

Question

这是我在@Kenneth Fisher的帮助下创建的一个存储过程，这真的很好用，而且是安全的（可以防止大多数sql注入风险）

我想以同样的方式插入数据库对这个代码进行一些修改后，是否可以使用它来插入数据？

CREATE PROCEDURE [dbo].[SafeSqlSP_SelectGivenTableWithOptionalColFilterVerB] 
    @columnList nvarchar(max) ='*',
    --@tableSchema sysname ='dbo' ,
    @tableName sysname ,
    @ColNameAsFilter1 nvarchar(255) ='',
    @ColNameAsFilter2 nvarchar(255) ='',
    @ColFilter1VAL nvarchar(max)='',
    @ColFilter2VAL nvarchar(max)=''       
    AS
BEGIN
    SET NOCOUNT ON;
    declare @tableSchema sysname ='dbo' ;
    IF ISNULL(@tableSchema,'') = ''
        SET @tableSchema = 'dbo'
    IF ISNULL(@columnList,'') = ''
        SET @columnList = '*'
    SET @tableName = ISNULL(@tableName,'')
    SET @ColNameAsFilter1 = ISNULL(@ColNameAsFilter1,'')
    SET @ColNameAsFilter2 = ISNULL(@ColNameAsFilter2,'')
    SET @ColFilter1VAL = ISNULL(@ColFilter1VAL,'')
    SET @ColFilter2VAL = ISNULL(@ColFilter2VAL,'')
    IF NOT EXISTS (
                    SELECT * 
                    FROM sys.all_objects
                    JOIN sys.schemas
                        ON sys.all_objects.schema_id = sys.schemas.schema_id
                    WHERE sys.all_objects.name = @tableName
                      AND sys.schemas.name = @tableSchema
                      AND [TYPE] IN ('S','U','V')
                    )
        BEGIN
            RAISERROR (N'Table %s.%s does not exist.',
                        16,
                        1,
                        @tableSchema,
                        @tableName)
            RETURN
        END
    IF @columnList LIKE '% FROM %'
        BEGIN
            RAISERROR (N'The column list passed in contains the word "FROM".  This is a SQL Injection risk and not allowed.',
                        16,
                        1)
            RETURN
        END
    IF @columnList LIKE '%;%'
        BEGIN
            RAISERROR (N'The column list passed in contains a semicolon.  This is a SQL Injection risk and not allowed.',
                        16,
                        1)
            RETURN
        END
    IF @ColNameAsFilter1 <> '' AND
        NOT EXISTS (SELECT * 
                    FROM sys.all_columns
                    WHERE object_id = OBJECT_ID(@tableSchema+'.'+@tableName)
                      AND name = @ColNameAsFilter1)
        BEGIN
            RAISERROR (N'Table %s.%s does not have a column %s.',
                        16,
                        1,
                        @tableSchema,
                        @tableName,
                        @ColNameAsFilter1)
            RETURN
        END
    IF @ColNameAsFilter2 <> '' AND
        NOT EXISTS (SELECT * 
                    FROM sys.all_columns
                    WHERE object_id = OBJECT_ID(@tableSchema+'.'+@tableName)
                      AND name = @ColNameAsFilter2)
        BEGIN
            RAISERROR (N'Table %s.%s does not have a column %s.',
                        16,
                        1,
                        @tableSchema,
                        @tableName,
                        @ColNameAsFilter2)
            RETURN
        END 
    DECLARE @sqlCommand nvarchar(max)
    SET @sqlCommand = 'SELECT ' + @columnList + CHAR(13) +
        ' FROM [' + @tableSchema + '].['+ @tableName + ']' + CHAR(13) + 
        ' WHERE 1=1 '
    IF @ColNameAsFilter1 != ''
        SET @sqlCommand = @sqlCommand + CHAR(13) + 
            ' AND ' + QUOTENAME(@ColNameAsFilter1) + ' = @ColFilter1VAL'
    IF @ColNameAsFilter2 != ''
        SET @sqlCommand = @sqlCommand + CHAR(13) + 
            ' AND ' + QUOTENAME(@ColNameAsFilter2) + ' = @ColFilter2VAL'
    EXECUTE sp_executesql @sqlCommand,
            N'@ColFilter1VAL nvarchar(75), @ColFilter2VAL nvarchar(75)', 
            @ColFilter1VAL, @ColFilter2VAL
END

Answer 1

先读这篇文章：何时（不）使用动态SQL 的常见情况