在存储过程中使用分隔参数是危险的

Question

我的C#端类似于：

if(Request.QueryString["ValuesFromUser"]!=null)
{
    ValuesFromUser_ = Request["ValuesFromUser"];
}
DataTable dtle = new DataTable();
SqlDataAdapter sqda;
sqda = new SqlDataAdapter("Checkforuserinput", Connection);
SqlParameter SQP = sqda.SelectCommand.Parameters.Add("@arg", SqlDbType.VarChar);
SQP.Direction = ParameterDirection.Input;
SQP.Value = "ValuesFromUser_";
sqda.Fill(dtle );

用户将传递一些参数，如"user1,user2,user3"

在我的sql方面：

Create PROC [dbo].[Checkforuserinput] @arg VARCHAR(50)= 'All'
As
    Select * 
    from UserData 
    where User in (SELECT * 
                   FROM SplitDelimiterString(@Arg, ','))

SplitDelimiterString函数类似于：

ALTER FUNCTION [dbo].[SplitDelimiterString] (@StringWithDelimiter VARCHAR(8000), @Delimiter VARCHAR(8))
RETURNS @ItemTable TABLE (Item VARCHAR(8000))
AS
BEGIN
    DECLARE @StartingPosition INT;
    DECLARE @ItemInString VARCHAR(8000);
    SELECT @StartingPosition = 1;
    --Return if string is null or empty
    IF LEN(@StringWithDelimiter) = 0 OR @StringWithDelimiter IS NULL RETURN; 
    WHILE @StartingPosition > 0
    BEGIN
        --Get starting index of delimiter .. If string
        --doesn't contain any delimiter than it will returl 0 
        SET @StartingPosition = CHARINDEX(@Delimiter,@StringWithDelimiter); 
        --Get item from string        
        IF @StartingPosition > 0                
            SET @ItemInString = SUBSTRING(@StringWithDelimiter,0,@StartingPosition)
        ELSE
            SET @ItemInString = @StringWithDelimiter;
        --If item isn't empty than add to return table    
        IF( LEN(@ItemInString) > 0)
            INSERT INTO @ItemTable(Item) VALUES (@ItemInString);            
        --Remove inserted item from string
        SET @StringWithDelimiter = SUBSTRING(@StringWithDelimiter,@StartingPosition + 
                     LEN(@Delimiter),LEN(@StringWithDelimiter) - @StartingPosition)
        --Break loop if string is empty
        IF LEN(@StringWithDelimiter) = 0 BREAK;
    END
    RETURN
END

此代码对于SQL注入是安全的还是有风险？

或者我应该使用这个：

Create PROC [dbo].[Checkforuserinput] @arg VARCHAR(50)= 'All'
As
    declare @query nvarchar(max)
    set @query = 'Select * from UserData where User in ('+@Arg+')'
    EXECUTE sp_executesql @query 

Answer 1

我认为split函数没有任何可预见的问题，但传递表值参数会更有效。

SQL

CREATE TYPE ArrayOfString AS TABLE
(
    Item VARCHAR(50)
);
GO
CREATE PROC [dbo].[Checkforuserinput] @arg ArrayOfString READONLY
    AS
        SELECT * 
        FROM UserData 
        WHERE User IN (SELECT Item FROM @arg)
GO

C#

编写一个助手函数，将任何IEnumerable(string)写入SQL Server能够理解的DataTable中。

public static class SqlExtensions
{
    public static DataTable ToSqlArray(this IEnumerable<string> collection)
    {
        var dt = new DataTable("ArrayOfString");
        dt.Columns.Add(new DataColumn("Item", typeof(string)));
        foreach(var item in collection)
        {
            var row = dt.NewRow();
            row[0] = item;
            dt.Rows.Add(row);
        }
        return dt;
    }
}

用法：

if(Request.QueryString["ValuesFromUser"]!=null)
{
    ValuesFromUser_ = Request["ValuesFromUser"];
}
var values = ValuesFromUser_.Split(",");
using(var adapter = new SqlDataAdapter("Checkforuserinput", Connection))
{
    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
    adapter.SelectCommand.Parameters.AddWithValue("@arg", values.ToSqlArray());
    adapter.Fill(dtle);
}

Answer 2

根据您的SQL Server版本，您可能需要考虑将表值参数传递给存储过程。

表值参数提供了一种简单的方法，可以将多行数据从客户端应用程序封送到SQL Server，而不需要多次往返或特殊的服务器端逻辑来处理数据。您可以使用表值参数在客户端应用程序中封装数据行，并在单个参数化命令中将数据发送到服务器。SqlParameter是使用AddWithValue方法填充的，SqlDbType设置为Structured。

以下链接提供了一个很好的概述：

http://msdn.microsoft.com/en-us/library/bb675163（v=vs.110）.aspx