a.字符串拼接(不使用参数化查询)
string Country = "UK";
string City = "London";
using (SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;User ID=sa;Password=sa"))
{
SqlCommand cmd = new SqlCommand(string.Format("Select count(*) From [Customers] Where Country='{0}' And City='{1}'",Country,City), conn);
conn.Open();
Response.Write(cmd.ExecuteScalar());
}在事件探查器中可以看到执行的SQL语句为：
Select count(*) From [Customers] Where Country='UK' And City='London'
b.参数化查询
string Country = "UK";
string City = "London";
using (SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;User ID=sa;Password=sa"))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 60;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select count(*) From [Customers] Where Country=@Country And City=@City";
SqlParameter[] parms = new SqlParameter[]{
new SqlParameter("@Country", SqlDbType.VarChar, 50),
new SqlParameter("@City", SqlDbType.VarChar, 50)
};
parms[0].Value = Country;
parms[1].Value = City;
for (int i = 0; i < parms.Length; i++)
cmd.Parameters.Add(parms[i]);
//cmd.Parameters.Add(new SqlParameter("@Country", Country));
//cmd.Parameters.Add(new SqlParameter("@City", City));
conn.Open();
Response.Write(cmd.ExecuteScalar());
}在事件探查器中可以看到执行的SQL语句为：
exec sp_executesql N'Select count(*) From [Customers] Where Country=@Country And City=@City', N'@Country nvarchar(2),@City nvarchar(6)', @Country = N'UK', @City = N'London'
查看执行计划，两个结果完全一样，问了一些人也说没什么区别。今天在看一篇防止SQL注入的文章时发现了他们有一点区别：
使用参数化查询时，ADO.NET会对一些特殊字符进行转义，这样可以在一定程序上防止SQL注入，提高程序执行效率。
将上边两例中Country值设置为"U''K"(中间两个单引号)，我们再看看最终执行的SQL语句：
例a:
Select count(*) From [Customers] Where Country='U''K' And City='London'
例b:
exec sp_executesql N'Select count(*) From [Customers] Where Country=@Country And City=@City', N'@Country nvarchar(4),@City nvarchar(6)', @Country = N'U''''K', @City = N'London'
可以看到，例a中未进行转义，而例b中将每个单引号转成了两个单引号！
评论（0）
暂无评论。