本文来自: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。