星空雅梦

SQL参数化查询

参数化查询(Parameterized Query 或 Parameterized Statement)是指在设计与数据库链接并访问数据时，在需要填入数值或数据的地方，使用参数 (Parameter) 来给值，这个方法目前已被视为最有效可预防SQL注入攻击 (SQL Injection) 的攻击手法的防御方式。

数据库参数化规律：在参数化SQL中参数名的格式跟其在存储过程中生命存储过程参数一致，例如在Oracle中存储过程参数一律以”:”开头，在MS SQL Server中存储过程参数一律以”@”开头，而在MySQL中存储过程（MySQL从5.0以后版本支持存储过程）参数一律以“？”开头，所以在参数化SQL语句中参数名有些不一样（记得在csdn上有朋友提到过不知道为什么MySQL中参数化SQL语句中要用“？”而不是和SQL Server一样使用”@”），如果那位朋友看过本文，我想他就会解开这个疑虑了。

在使用参数化查询的情况下，数据库服务器不会将参数的内容视为SQL指令的一部份来处理，而是在数据库完成 SQL 指令的编译后，才套用参数运行，因此就算参数中含有恶意的指令，由于已经编译完成，就不会被数据库所运行。有部份的开发人员可能会认为使用参数化查询，会让程序更不好维护，或者在实现部份功能上会非常不便，然而，使用参数化查询造成的额外开发成本，通常都远低于因为SQL注入攻击漏洞被发现而遭受攻击，所造成的重大损失。

在撰写 SQL 指令时，利用参数来代表需要填入的数值，例如:

MicrosoftSQLServer

Microsoft SQL Server 的参数格式是以 "@" 字符加上参数名称而成，SQL Server 亦支持匿名参数 "?"。

SELECT * FROM myTable WHERE myID = @myID

INSERT INTO myTable (c1, c2, c3, c4) VALUES (@c1, @c2, @c3, @c4)

在客户端代码中撰写使用参数的代码，例如:

ADO.NET

SqlCommand sqlcmd = new SqlCommand("INSERT INTO myTable (c1, c2, c3, c4) VALUES (@c1, @c2, @c3, @c4)", sqlconn);

sqlcmd.Parameters.AddWithValue("@c1", 1); // 设定参数 @c1 的值。

sqlcmd.Parameters.AddWithValue("@c2", 2); // 设定参数 @c2 的值。

sqlcmd.Parameters.AddWithValue("@c3", 3); // 设定参数 @c3 的值。

sqlcmd.Parameters.AddWithValue("@c4", 4); // 设定参数 @c4 的值。

sqlconn.Open();

sqlcmd.ExecuteNonQuery();

sqlconn.Close();

说来惭愧，工作差不多4年了，直到前些日子被DBA找上门让我优化一个CPU占用很高的复杂SQL语句时，我才突然意识到了参数化查询的重要性。

相信有很多开发者和我一样对于参数化查询认识比较模糊，没有引起足够的重视

错误认识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 whereUserID=@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();
}

当然了若是不使用参数化查询，直接拼接SQL，那样就更没有查询计划复用一说了，除非你每次拼的SQL都完全一样

总结，参数化查询意义及注意点

1.可以防止SQL注入

2.可以提高查询性能(主要是可以复用查询计划)，这点在数据量较大时尤为重要

3.参数化查询参数类型为可变长度时(varchar,nvarchar,char等)请指定参数类型及长度，若为值类型(int,bigint,decimal,datetime等)则仅指定参数类型即可

4.传值为varchar(max)或者nvarchar(max)时，参数长度指定为-1即可

5.看到有些童鞋对于存储过程是否要指定参数长度有些疑惑，这里补充下，若调用的是存储过程时，参数无需指定长度，如果指定了也会忽略，以存储过程中定义的长度为准，不会因为没有指定参数长度而导致重新编译，不过还是建议大家即便时调用存储过程时也加上长度，保持良好的变成习惯

一、以往的防御方式

以前对付这种漏洞的方式主要有三种：

字符串检测：限定内容只能由英文、数字等常规字符，如果检查到用户输入有特殊字符，直接拒绝。但缺点是，系统中不可避免地会有些内容包含特殊字符，这时候总不能拒绝入库。
字符串替换：把危险字符替换成其他字符，缺点是危险字符可能有很多，一一枚举替换相当麻烦，也可能有漏网之鱼。
存储过程：把参数传到存储过程进行处理，但并不是所有数据库都支持存储过程。如果存储过程中执行的命令也是通过拼接字符串出来的，还是会有漏洞。

二、什么是参数化查询?

一个简单理解参数化查询的方式是把它看做只是一个T-SQL查询，它接受控制这个查询返回什么的参数。通过使用不同的参数，一个参数化查询返回不同的结果。要获得一个参数化查询，你需要以一种特定的方式来编写你的代码，或它需要满足一组特定的标准。
有两种不同的方式来创建参数化查询。第一个方式是让查询优化器自动地参数化你的查询。另一个方式是通过以一个特定方式来编写你的T-SQL代码，并将它传递给sp_executesql系统存储过程，从而编程一个参数化查询。

这样的解释还是有点模糊，先看一例：

例一：参数化查询

参数化查询（Parameterized Query 或 Parameterized Statement）是访问数据库时，在需要填入数值或数据的地方，使用参数 (Parameter) 来给值。

在使用参数化查询的情况下，数据库服务器不会将参数的内容视为SQL指令的一部份来处理，而是在数据库完成SQL指令的编译后，才套用参数运行，因此就算参数中含有指令，也不会被数据库运行。Access、SQL Server、MySQL、SQLite等常用数据库都支持参数化查询。

[csharp] view plain copy

//在ASP.NET程序中使用参数化查询
//ASP.NET环境下的查询化查询也是通过Connection对象和Command对象完成。如果数据库是SQL Server，就可以用有名字的参数了，格式是“@”字符加上参数名。
SqlConnection conn = new SqlConnection("server=(local)\SQL2005;user id=sa;pwd=12345;initial catalog=TestDb");
conn.Open();
SqlCommand cmd = new SqlCommand(“SELECT TOP 1 * FROM [User] WHERE UserName = @UserName AND Password = @Password“);
cmd.Connection = conn;
cmd.Parameters.AddWithValue(”UserName”, “user01″);
cmd.Parameters.AddWithValue(”Password”, “123456″);
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
int userId = reader.GetInt32(0);
reader.Close();
conn.Close();

参数化查询被喻为最有效防止SQL注入的方法，那么存储过程一定是参数化过后的吗？

如果存储过得利用传递进来的参数，再次进行动态SQL拼接，这样还算做是参数化过后的吗？如果存储过程一定是参数化过后的，那么是不是意味着，只要使用存储过程就具有参数化查询的全部优点了？
如下存储过程：

[sql] view plain copy

create procedure pro_getCustomers
(
@whereSql nvarchar(max)
)
as
declare @sql nvarchar(max)
set @sql=N'select * from dbo.Customer ' + @whereSql
exec(@sql)
Go
--如果我要在ADO.NET中参数化查询这个存储过程，以防止SQL注入，我该怎么办呢？比如：
exec pro_getCustomers 'where Name=@name'

这种方法没有办法防止注入，你能做的就是对字符串进行过滤.

拼接SQL是：

[sql] view plain copy

"select * from customer where 1=1" + " and name=@name" + " and sex=@sex"

也就是判断参数化查询。只不过是动态地组装查询限制条件。

动态拼接SQL，而且是参数化查询的SQL语句是没有问题的。

ADO.NET中被SQL注入的问题，必须过于关键字。原作者的测试代码如下：

[sql] view plain copy

USE [B2CShop]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[pro_getCustomers]
(
@whereSql nvarchar(max),
@paramNameList nvarchar(max),
@paramValueList nvarchar(max)
)
as
declare @sql nvarchar(max)
set @sql=N'select * from dbo.Customer ' + @whereSql
exec sp_executesql @sql, @paramNameList , @paramValueList
go

[csharp] view plain copy

/// <summary>
/// 动态执行存储过程
/// </summary>
/// <param name="searchedName">要查询的姓名的关键字</param>
/// <returns>实体集合</returns>
public static List<Customer> ExecDynamicProc(string searchedName)
{
SqlParameter[] values = new SqlParameter[]
{
new SqlParameter("@whereSql", "where name like @name"),
new SqlParameter("@paramNameList","@name nvarchar(50)"),
new SqlParameter("@paramValueList","@name='%"+ searchedName +"%'")
};
return DBHelper.ExecuteProc("proc_GetCustomerPagerBySearch",values);
}

[csharp] view plain copy

/// <summary>
/// 从搜索类里面拼接参数化的SQL字符串
/// </summary>
/// <param name="search">搜索类</param>
/// <param name="sqlParams">搜索的参数，不能传入Null</param>
/// <returns>安全的SQL语句</returns>
private static string GetSafeSqlBySearchItem(CustomerSearch search, ref List<SqlParameter> sqlParams)
{
StringBuilder safeSqlAppend = new StringBuilder();
if (search != null)
{
if (!string.IsNullOrEmpty(search.NameEquals))
{
safeSqlAppend.Append(" and Name=@nameEquals");
sqlParams.Add(new SqlParameter("@nameEquals", search.NameEquals));
}
if (!string.IsNullOrEmpty(search.NameContains))
{
safeSqlAppend.Append(" and Name like @nameContains");
sqlParams.Add(new SqlParameter("@nameContains", "%" + search.NameContains + "%"));
}
}
return safeSqlAppend.ToString();
}

[csharp] view plain copy

/// <summary>
/// 得到分页用的SQL语句
/// </summary>
/// <param name="columnNameItems">要查询的列名，多个列名用逗号分隔。传入Empty或Null时，则默认查询出所有的列</param>
/// <param name="tableName">表名，不能为Null和Empty，默认的SQL别名为a</param>
/// <param name="joinOtherTable">连接其他的表，可以传入Null或Empty。调用的时候，可以类似如：inner join departInfo as b on a.departInfoId=b.Id</param>
/// <param name="whereSql">搜索条件，即在“where 1=1 ”后面写条件，可以传入Null或Empty。调用的时候，可以类似如：and b.Price=@beginPrice </param>
/// <param name="orderColumnNameAndAscOrDesc">排序的列名以及Asc或Desc，即在“order by”后面写排序项，不能为Null和Empty。比如“Id asc, name desc”</param>
/// <param name="pageNumber">当前页的页码，最小值应该为1</param>
/// <param name="pageSize">每页显示的记录数，最小值应该为1</param>
/// <returns>SQL语句</returns>
internal static string GetPagerTSql(string columnNameItems, string tableName, string joinOtherTable, string whereSql, string orderColumnNameAndAscOrDesc, int pageNumber, int pageSize)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("tableName", String.Format(CultureInfo.CurrentCulture, DALResource.Common_NullOrEmpty));
}
if (string.IsNullOrEmpty(orderColumnNameAndAscOrDesc))
{
throw new ArgumentNullException("orderColumnNameAndAscOrDesc", String.Format(CultureInfo.CurrentCulture, DALResource.Common_NullOrEmpty));
}
if (string.IsNullOrEmpty(columnNameItems))
{
columnNameItems = "a.*";
}
if (pageNumber < 1)
{
pageNumber = 1;
}
if (pageSize < 1)
{
pageSize = 1;
}
int beginNumber = (pageNumber - 1) * pageSize + 1;
int endNumber = pageNumber * pageSize;
string sqlPager = string.Format("select * from (select row_number() over(order by {1}) as __MyNewId, {0} from {2} as a {3} where 1=1 {4}) as __MyTempTable where __MyNewId between {5} and {6} order by __MyNewId asc;", columnNameItems, orderColumnNameAndAscOrDesc, tableName, joinOtherTable, whereSql, beginNumber, endNumber);
string sqlPagerCount = string.Format("select @__returnCount=COUNT(*) from {0} as a {1} where 1=1 {2};",tableName, joinOtherTable, whereSql);
return sqlPager + sqlPagerCount;
}

例二：登录错误次数限制及参数化传递防止SQL注入

[csharp] view plain copy

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
namespace 复习登录
{
public partial class login : Form
{
public login()
{
InitializeComponent();
}
string str = ConfigurationManager.ConnectionStrings["sqlserver2008"].ConnectionString;
DateTime dt1;
private void btn_login_Click(object sender, EventArgs e)
{
using(SqlConnection cnn=new SqlConnection(str))
{
using (SqlCommand cmd=cnn.CreateCommand())
{
cmd.CommandText = "select * from T_User where username=@username";
cmd.Parameters.AddWithValue("@username", txt_username.Text);
cnn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
int Error = Convert.ToInt32(reader["Error"].ToString());
if (Error >= 3)
{
string sqltime = reader["Errortime"].ToString();
dt1 = DateTime.Parse(sqltime);
DateTime dt2 = DateTime.Now;
TimeSpan ts = dt2 - dt1;
if (ts.TotalMinutes < 5)
{
MessageBox.Show("对不起，你已经输入3次连续错误密码，系统已经将账户冻结，请在五分钟后再试");
return;
}
else
{
clearerror();
}
}
string sqlpassword = reader["Password"].ToString();
if (sqlpassword == txt_password.Text)
{
clearerror();
if (txt_username.Text.ToUpper() == "ADMIN")
{
this.Hide();
main m = new main();
m.Show();
}
else
{
MessageBox.Show("登录成功");
}
}
else
{
MessageBox.Show("密码错误");
adderror();
}
}
else
{
MessageBox.Show("用户名不存在");
}
}
}
}
}
private void adderror()
{
dt1 = DateTime.Now;
using (SqlConnection cnn=new SqlConnection(str))
{
using (SqlCommand cmd=cnn.CreateCommand())
{
cnn.Open();
cmd.CommandText = "update T_User set Error=Error+1,Errortime=@Errortime where username=@username";
cmd.Parameters.AddWithValue("@Errortime", dt1);
cmd.Parameters.AddWithValue("@username", txt_username.Text);
cmd.ExecuteNonQuery();
}
}
}
private void clearerror()
{
using (SqlConnection cnn=new SqlConnection(str))
{
using (SqlCommand cmd=cnn.CreateCommand())
{
cnn.Open();
cmd.CommandText = "update T_User set Error=0 where username=@username";
cmd.Parameters.Add(new SqlParameter("username", txt_username.Text));
cmd.ExecuteNonQuery();
}
}
}
}
}