浅析Sql Server参数化查询

关注

转载最新推荐文章于 2026-04-01 09:51:07 发布 2.3k 阅读 AI 写同款· GEO 优化 ›

SQL Server中参数化查询的挑战与解决方案 参数化查询在SQL Server开发中是一项重要的技术，可以提高查询的安全性、性能和可维护性。通过克服查询字符串拼接的风险，使用强制参数化和辅助工具来提高性能和可维护性，开发人员可以更好地应用参数化查询，并有效地解决相关的挑战。通过启用此选项，SQL Server会自动将查询中的常量值参数化，从而提高查询性能和缓存的复用性。在不使用参数化查询的情况下，查询字符串通常需要通过拼接来构建，将用户的输入直接嵌入到查询中。参数化查询通过将查询字符串和参数分离，将参数的值作为查询的输入，从而消除了SQL注入的风险。阅读详情

浅析Sql Server参数化查询

错误认识1.不需要防止sql注入的地方无需参数化
　　参数化查询就是为了防止SQL注入用的，其它还有什么用途不知道、也不关心，原则上是能不用参数就不用参数，为啥？多麻烦，我只是做公司内部系统不用担心SQL注入风险，使用参数化查询不是给自己找麻烦，简简单单拼SQL，万事OK

错误认识2.参数化查询时是否指定参数类型、参数长度没什么区别
　　以前也一直都觉的加与不加参数长度应该没有什么区别，仅是写法上的不同而已，而且觉得加参数类型和长度写法太麻烦，最近才明白其实两者不一样的，为了提高sql执行速度，请为SqlParameter参数加上SqlDbType和size属性，在参数化查询代码编写过程中很多开发者忽略了指定查询参数的类型，这将导致托管代码在执行过程中不能自动识别参数类型，进而对该字段内容进行全表扫描以确定参数类型并进行转换，消耗了不必要的查询性能所致。根据MSDN解释：如果未在size参数中显式设置Size，则从dbType参数的值推断出该大小。如果你认为上面的推断出该大小是指从SqlDbType类型推断，那你就错了，它实际上是从你传过来的参数的值来推断的，比如传递过来的值是"username"，则size值为8，"username1"，则size值为9。那么，不同的size值会引发什么样的结果呢？且经测试发现，size的值不同时，会导致数据库的执行计划不会重用，这样就会每次执行sql的时候重新生成新的执行计划，而浪费数据库执行时间。

下面来看具体测试

首先清空查询计划

DBCC FREEPROCCACHE

传值username，不指定参数长度，生成查询计划

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserName=@UserName";
    //传值 username，不指定参数长度
    //查询计划为(@UserName varchar(8))select * from Users where UserName=@UserName
    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar) { Value = "username" });
    comm.ExecuteNonQuery();
}

传值username1，不指定参数长度，生成查询计划

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserName=@UserName";
    //传值 username1，不指定参数长度
    //查询计划为(@UserName varchar(9))select * from Users where UserName=@UserName
    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar) { Value = "username1" });
    comm.ExecuteNonQuery();
}

传值username，指定参数长度为50，生成查询计划

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserName=@UserName";
    //传值 username，指定参数长度为50
    //查询计划为(@UserName varchar(50))select * from Users where UserName=@UserName
    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,50) { Value = "username" });
    comm.ExecuteNonQuery();
}

传值username1，指定参数长度为50，生成查询计划

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserName=@UserName";
    //传值 username1，指定参数长度为50
    //查询计划为(@UserName varchar(50))select * from Users where UserName=@UserName
    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,50) { Value = "username1" });
    comm.ExecuteNonQuery();
}

使用下面语句查看执行的查询计划

SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects 
WHERE sql LIKE '%Users%'  and sql not like '%syscacheobjects%'

结果如下图所示

可以看到指定了参数长度的查询可以复用查询计划，而不指定参数长度的查询会根据具体传值而改变查询计划，从而造成性能的损失。

这里的指定参数长度仅指可变长数据类型，主要指varchar，nvarchar,char,nchar等，对于int,bigint,decimal,datetime等定长的值类型来说，无需指定(即便指定了也没有用)，详见下面测试,UserID为int类型，无论长度指定为2、20、-1查询计划都完全一样为(@UserIDint)select*from Users where UserID=@UserID

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserID=@UserID";
    //传值 2，参数长度2
    //执行计划(@UserID int)select * from Users where UserID=@UserID
    comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 2) { Value = 2 });
    comm.ExecuteNonQuery();
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserID=@UserID";
    //传值 2，参数长度20
    //执行计划(@UserID int)select * from Users where UserID=@UserID
    comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 20) { Value = 2 });
    comm.ExecuteNonQuery();
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserID=@UserID";
    //传值 2，参数长度-1
    //执行计划(@UserID int)select * from Users where UserID=@UserID
    comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, -1) { Value = 2 });
    comm.ExecuteNonQuery();
}

这里提一下，若要传值varchar(max)或nvarchar(max)类型怎么传，其实只要设定长度为-1即可

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserName=@UserName";
    //类型为varchar(max)时，指定参数长度为-1
    //查询计划为 (@UserName varchar(max) )select * from Users where UserName=@UserName
    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,-1) { Value = "username1" });
    comm.ExecuteNonQuery();
}