摘要:[笔记]Parameters.AddWithValue & Parameters.Add

SQL Injection是个很显而易见的问题
很多前辈的一再提醒要重视这个问题
最直接的用法是用Parameters来处理
基本的用法大家都会用
但是还是有一些小细节要注意
自己就整理一下，免得以后遇到又临时找不到解法

常用用法

string cmdTestText = @"SELECT * FROM TEST WHERE NAME = @NAME";
SqlDataAdapter ad = new SqlDataAdapter(cmdTestText, conn);
ad.SelectCommand.Parameters.AddWithValue("NAME", name);
ad.Fill(dt);

　　或是

SqlCommand cmd = new SqlCommand(cmdTestText, conn);
cmd.Parameters.AddWithValue("NAME", name);
cmd.ExecuteNonQuery();

　　解析到SQL Server

exec sp_executesql N'SELECT * FROM TEST WHERE NAME = @NAME',N'@NAME nvarchar(1)',@NAME=N'毛'

　　解析出来的类型nvarchar(1)，当变量值变化时

exec sp_executesql N'SELECT * FROM TEST WHERE NAME = @NAME',N'@NAME nvarchar(11)',@NAME=N'毛毛毛毛毛毛毛毛毛毛毛'

(一) 参数类型

　　如果要自订类型

string cmdTestText = @"SELECT * FROM TEST WHERE NAME = @NAME";
SqlDataAdapter ad = new SqlDataAdapter(cmdTestText, conn);
ad.SelectCommand.Parameters.Add("NAME", SqlDbType.NVarChar, 20).Value = name;

　　解析出来

exec sp_executesql N'SELECT * FROM TEST WHERE NAME = @NAME',N'@NAME nvarchar(20)',@NAME=N'毛'

(二) SQL语法 : Like

string cmdTestText = @"SELECT * FROM TEST WHERE NAME LIKE @NAME";
SqlDataAdapter ad = new SqlDataAdapter(cmdTestText, conn);
ad.SelectCommand.Parameters.AddWithValue("NAME", "%" + "毛" + "%");

　　=>

exec sp_executesql N'SELECT * FROM TEST WHERE NAME LIKE @NAME',N'@NAME nvarchar(3)',@NAME=N'%毛%'

　　另种写法

string cmdTestText = @"SELECT * FROM TEST WHERE NAME LIKE @NAME";
SqlDataAdapter ad = new SqlDataAdapter(cmdTestText, conn);
ad.SelectCommand.Parameters.Add("NAME", SqlDbType.NVarChar, 20).Value = "%" + "毛" + "%";

　　=>

exec sp_executesql N'SELECT * FROM TEST WHERE NAME LIKE @NAME',N'@NAME nvarchar(20)',@NAME=N'%毛%'

(三) SQL语法：where in

　　想要达到这样的语法

SELECT * FROM TEST WHERE NAME IN ('1','2','3')

　　一开始自己试了几种串法

string name = @"'1','2','3'";
string cmdText = @"SELECT * FROM TEST WHERE NAME IN (@NAME)";
SqlDataAdapter ad = new SqlDataAdapter(cmdText, conn);
ad.SelectCommand.Parameters.AddWithValue("NAME", name);

　　=>

exec sp_executesql N'SELECT * FROM TEST WHERE NAME IN (@NAME)',N'@NAME nvarchar(11)',@NAME=N'''1'',''2'',''3'''

　　失败！！

string name = @"1','2','3";
string cmdText = @"SELECT * FROM TEST WHERE NAME IN (@NAME)";
SqlDataAdapter ad = new SqlDataAdapter(cmdText, conn);
ad.SelectCommand.Parameters.AddWithValue("NAME", name);

　　=>

exec sp_executesql N'SELECT * FROM TEST WHERE NAME IN (@NAME)',N'@NAME nvarchar(9)',@NAME=N'1'',''2'',''3'

　　失败！

string name = @"1,2,3";
string cmdText = @"SELECT * FROM TEST WHERE NAME IN (@NAME)";
SqlDataAdapter ad = new SqlDataAdapter(cmdText, conn);
ad.SelectCommand.Parameters.AddWithValue("NAME", name);

　　=>

exec sp_executesql N'SELECT * FROM TEST WHERE NAME IN (@NAME)',N'@NAME nvarchar(5)',@NAME=N'1,2,3'

　　失败！

　　只好去问问股狗大神，
　　找到这篇　
　　找到几种做法

　　１．调整SQL Statement

SELECT * FROM TEST WHERE '|1|2|3|' LIKE '%|' + NAME + '|%'

string[] name = new string[] { "1", "2", "3" };
string cmdText = @"SELECT * FROM TEST WHERE @NAME LIKE '%|' + NAME + '|%'";
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.Parameters.AddWithValue("NAME", "|" + string.Join("|", name) + "|");

　　=>

exec sp_executesql N'SELECT * FROM TEST WHERE @NAME LIKE ''%|'' + NAME + ''|%''',
N'@NAME nvarchar(7)',@NAME=N'|1|2|3|'

　　成功！

　　２．跑循环产生变量（这是我本来有想到的，想说应该有更聪明的做法才问股狗大神的）

string[] name = new string[] { "1", "2", "3" };
string cmdText = @"SELECT * FROM TEST WHERE NAME IN ({0})";

string[] parameters = name.Select((s, i) => "@NAME" + i.ToString()).ToArray();
cmdText = string.Format(cmdText, string.Join(",", parameters));

SqlCommand cmd = new SqlCommand(cmdText, conn);
for (int i = 0; i < parameters.Length; i++)
	cmd.Parameters.AddWithValue(parameters[i], name[i]);

　　=>

exec sp_executesql N'SELECT * FROM TEST WHERE NAME IN (@NAME0,@NAME1,@NAME2)',N'@NAME0 nvarchar(1),@NAME1 nvarchar(1),@NAME2 nvarchar(1)',@NAME0=N'1',@NAME1=N'2',@NAME2=N'3'

　　成功！

　　３．从SQL Server下手，写Store Procedure或Function
　　　　还要额外去处理的方法就不采用了。

　　４．Linq to SQL
　　　　

原文:大专栏  [笔记]Parameters.AddWithValue & Parameters.Add