存储过程 SQL 注入检查

到目前为止，您是安全的，因为您在主 sql 查询中应用之前先构建它。我建议添加这些行来检查您的@DataMapID@DataMapNamesort expression, maxrows and start index

    IF (@sortExpression NOT IN ('ASC', 'DESC'))
    BEGIN
            RAISERROR('invalid order expression', 16,1);
            RETURN;
    END;


    IF (TRY_CAST(@StartIndex as int) = null or TRY_CAST(@MaximumRows as int) = null)
    BEGIN
            RAISERROR('invalid startindex or maximum rows', 16,1);
            RETURN;
    END;

只需与参数一起使用。生成动态 T-SQL 语句，但需要添加 value .然后像这样调用例程：sp_executesql@parameter_name

EXEC sp_executesql @sql
                  ,N'@parameter_name1 INT, @parameter_name2 VARCHAR(128), @parameter_name3 BIT'
                  ,@parameter_name1, @parameter_name2, @parameter_name3;

添加 OPTION（RECOMPILE） 提示可以为每次执行的查询执行重新构建新的执行计划。在某些情况下，它可以帮助提高性能。但是，重新编译操作会使用内存和 CPU 资源来生成新的执行计划。因此，如果您不确定性能的效果，则不会使用它

感谢您的所有帮助，我重写了下面的代码。如果不行，请告诉我。 谢谢大家！

CREATE PROCEDURE DataMapMainQuery
    (@DataMapID VARCHAR(MAX),
     @DataMapIDName VARCHAR(MAX),
     @StartIndex INT,
     @MaximumRows INT,
     @sortExpression VARCHAR(MAX))
AS
BEGIN   
    DECLARE @FilteredTotalRows AS INT
    DECLARE @SqlString NVARCHAR(MAX)
    DECLARE @params NVARCHAR(MAX);
    DECLARE @WhereString1 NVARCHAR(MAX)
    DECLARE @WhereString2 NVARCHAR(MAX)

    IF (@DataMapID IS NULL)
        SET @WhereString1 = ' AND (DataMapID LIKE ' + '''%%''' + ' OR NULL IS NULL)'
    ELSE
        SET @WhereString1 = ' AND (DataMapID LIKE ' + '''%' + @DataMapID + '%''' + ' OR ''' + @DataMapID + ''' IS NULL)'

    IF (@DataMapIDName IS NULL)
        SET @WhereString2 = ' AND (DataMapIDName LIKE ' + '''%%''' + ' OR NULL IS NULL)'
    ELSE
        SET @WhereString2 = ' AND (DataMapIDName LIKE ' + '''%' + @DataMapIDName + '%''' + ' OR ''' + @DataMapIDName + ''' IS NULL)'

    IF (@sortExpression IS NULL)
        SET @sortExpression = 'DataMapID'

    SELECT
        @FilteredTotalRows = COUNT(*) 
    FROM
        DataMapMain
    WHERE
        1 = 1
        AND (DataMapID LIKE '%' + @DataMapID + '%' OR @DataMapID IS NULL)
        AND (DataMapIDName LIKE '%' + @DataMapIDName + '%' OR @DataMapIDName IS NULL)

    IF (@FilteredTotalRows < @StartIndex + 1)
    BEGIN
        SET @SqlString = '
                SELECT 
                    DataMapID, DataMapIDName,
                    DataMapGroup, DataMapGroupRemark,
                    CONVERT(BIGINT, TimeStamp) AS TimeStamp
                FROM 
                    (SELECT
                         ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowNumber,
                         DataMapID, DataMapIDName,
                         DataMapGroup, DataMapGroupRemark,
                         TimeStamp
                     FROM
                         DataMapMain
                     WHERE
                         1 = 1' 
                    + @WhereString1
                    + @WhereString2
                    + ') DataMapMain
                WHERE
                     RowNumber >= 1 
                     AND RowNumber < (1 + ' + CONVERT(NVARCHAR(10), @MaximumRows) + ')'
    END
    ELSE
    BEGIN
        SET @SqlString = '
                SELECT 
                    DataMapID
                    ,DataMapIDName
                    ,DataMapGroup
                    ,DataMapGroupRemark
                    ,CONVERT(bigint, TimeStamp) as TimeStamp
                FROM 
                     (
                     Select ROW_NUMBER() over (order by '  + @sortExpression + ') as RowNumber
                    ,DataMapID
                    ,DataMapIDName
                    ,DataMapGroup
                    ,DataMapGroupRemark
                    ,TimeStamp
                     From DataMapMain
                     WHERE
                        1 = 1'
                    + @WhereString1
                    + @WhereString2

                        + ') DataMapMain
                WHERE
                    RowNumber >= (' +  CONVERT(nvarchar(10),@StartIndex) + ' + 1) and RowNumber < (' + CONVERT(nvarchar(10),@StartIndex) + ' + 1 + ' + CONVERT(nvarchar(10),@MaximumRows) + ' )'
            END

    SET @params = '
        @DataMapID      VARCHAR(MAX)
        ,@DataMapIDName     VARCHAR(MAX)
        ,@StartIndex    INT
        ,@MaximumRows   INT
        ,@sortExpression VARCHAR(MAX)';

    EXEC sp_executesql 
        @SqlString
        ,@params
        ,@DataMapID
        ,@DataMapIDName
        ,@StartIndex
        ,@MaximumRows
        ,@sortExpression;
    END