本文来自：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的
|
1
2
3
|
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，
方案二：使用临时表
这种方案比较繁琐
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
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();} |
方案三：拆分多个参数–推荐写法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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();} |
可以写成通用方法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
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写法
|
1
2
3
|
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、将%写到参数值中
|
1
2
3
4
5
6
7
8
9
10
|
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中拼接%
|
1
2
3
4
5
6
7
8
9
10
|
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。
