[筆記]Parameters.AddWithValue & Parameters.Add

2012-11-13

32208
0

摘要:[筆記]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
　　　　

回首頁