SQL参数化

浏览数：20 / 时间：2015年06月12日

本文来自：caodonglin

一、SQL参数化为什么能防注入？

因为执行计划被重用了，所以可以防SQL注入。

下面有两段SQL

正常SQL：

1	`select` `COUNT(1)` `from` `CruiseSysUser` `where` `CSUPwd =` `‘aa‘` `and` `CSUUserName =` `‘bb‘`

被注入后的SQL：

1	`select` `COUNT(1)` `from` `CruiseSysUser` `where` `CSUPwd =` `‘aa‘` `and` `CSUUserName =` `‘bb‘` `or` `1=1—‘`

可以发现被注入后的SQL语义发生了变化，查询逻辑与正常SQL不一致。因为没有重用以前的执行计划，因为对注入后的SQL语句重新进行了编译，因为重新执行了语法解析。拼接的SQL基本上无法保证你写的SQL所表示的意思就是你要表达的意思。任何拼接的SQL都有被注入的风险。

SQL参数化：

1	`select` `COUNT(*)` `from` `CruiseSysUser` `where` `CSUPwd = @CSUPwd` `and` `CSUUserName =@CSUUserName`

如果参数化SQL后，每次执行的都是相同的SQL，SQL的语义不会变化，所以会重用到以前的执行计划。

SQL参数化后，等于是做填空题，不管输入什么条件，我所表达的意思都不变。

存储过程也是一样道理，可以重用执行计划。

二、修改注意点

1、exec动态执行SQL是不能防SQL的

comm.CommandText = "exec(‘select * from Users(nolock) where UserID in (‘+@UserID+‘)‘)";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();

这种方式跟拼接SQL没什么区别，SQL语义还是会变化，一样不会重用到执行计划

2、所有参数都需要参数化

不管是数值还是字符类型，都需要参数化，不只字符串类型能被注入，数值类型同样会被注入

如果前台是下拉框的查询条件，同样需要SQL参数化

3、where in参数化查询

where in语句是没法使用SQL参数化的，是会报错的。以下多种方案推荐使用方案三

方案一：多条件查询

将SQL改成多个条件，用or，

方案二：使用临时表

这种方案比较繁琐

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    string sql = @"
        declare @Temp_Variable varchar(max)
        create table #Temp_Table(Item varchar(max))
        while(LEN(@Temp_Array) > 0)
        begin
            if(CHARINDEX(‘,‘,@Temp_Array) = 0)
            begin
                set @Temp_Variable = @Temp_Array
                set @Temp_Array = ‘‘
            end
            else
            begin
                set @Temp_Variable = LEFT(@Temp_Array,CHARINDEX(‘,‘,@Temp_Array)-1)
                set @Temp_Array = RIGHT(@Temp_Array,LEN(@Temp_Array)-LEN(@Temp_Variable)-1)
            end    
        insert into #Temp_Table(Item) values(@Temp_Variable)
        end    
        select * from Users(nolock) where exists(select 1 from #Temp_Table(nolock) where #Temp_Table.Item=Users.UserID)
        drop table #Temp_Table";
    comm.CommandText = sql;
    comm.Parameters.Add(new SqlParameter("@Temp_Array", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
    comm.ExecuteNonQuery();
}

方案三：拆分多个参数–推荐写法

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    //为每一条数据添加一个参数
    comm.CommandText = "select * from Users(nolock) where UserID in (@UserID1,@UserId2,@UserID3,@UserID4)";
    comm.Parameters.AddRange(
    new SqlParameter[]{                        
        new SqlParameter("@UserID1", SqlDbType.Int) { Value = 1},
        new SqlParameter("@UserID2", SqlDbType.Int) { Value = 2},
        new SqlParameter("@UserID3", SqlDbType.Int) { Value = 3},
        new SqlParameter("@UserID4", SqlDbType.Int) { Value = 4}
    });
    comm.ExecuteNonQuery();
}

可以写成通用方法

private int GetData3(string id)        
{            
    string connectionString = "";            
    using (SqlConnection conn = new SqlConnection(connectionString))            
    {                
        var whereInParam = BuildWhereInStringAndParams<string>(id.Split(new char[] { ‘,‘ }, 
        StringSplitOptions.RemoveEmptyEntries), "name");                
        if (whereInParam == null) return 0;                
        conn.Open();                
        SqlCommand comm = new SqlCommand();                
        comm.Connection = conn;                
        comm.CommandText = string.Format("select * from CruiseSysUser where CSUUserTCNum in ({0})", whereInParam.Item1);                
        comm.Parameters.AddRange(whereInParam.Item2);                
        return comm.ExecuteNonQuery();            
    }        
}
     
private Tuple<string, SqlParameter[]> BuildWhereInStringAndParams<T>(T[] values, string nameFixed = "SplitName")
{            
    if (values == null || values.Length == 0) return null;            
    StringBuilder sbParams = new StringBuilder();            
    List<SqlParameter> paramList = new List<SqlParameter>();            
    for (int i = 0; i < values.Length; i++)            
    {                
        string paramName = string.Format("@{0}{1}", nameFixed, (i + 1).ToString());                
        if (sbParams.Length > 0) sbParams.Append(",");                
        sbParams.AppendFormat(paramName);                
        paramList.Add(new SqlParameter(paramName, values[i]));            
     }            
     return new Tuple<string, SqlParameter[]>(sbParams.ToString(), paramList.ToArray());        
}

方案四：Dapper写法

    string sql = "select * from TCCruisePublic.dbo.CruiseSysUser where CSUUserName in @names";
    dynamic param = new { names = new string[] { "aa‘", "bb", "cc" } };
    var res = context.Query(new SqlNoteInfo("", "", ""), sql, param as object);

其实dapper的写法，最终转换出来执行的SQL跟方案三是一样的，只是dapper自动做了个转换。

4、like参数化查询
1、将%写到参数值中

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    //将 % 写到参数值中
    comm.CommandText = "select * from Users(nolock) where UserName like @UserName";
    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, 200) { Value = "rabbit%" });
    comm.ExecuteNonQuery();
}

2、在SQL中拼接%

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    //SQL中拼接 %
    comm.CommandText = "select * from Users(nolock) where UserName like @UserName+‘%‘";
    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, 200) { Value = "rabbit" });
    comm.ExecuteNonQuery();
}

5、指定参数类型、参数长度
SqlParameter参数不加SqlDbType属性的话，托管代码在执行过程中不能自动识别参数类型，进而对该字段内容进行全表扫描以确定参数类型并进行转换，消耗了不必要的查询性能。如果未在size参数中显式设置Size，则从dbType参数的值推断出该大小，不同的Size会导致不重用数据库执行计划。所以一般情况下都要指定SqlDbType与Size。

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

SQL参数化