大家好,又见面了,我是你们的朋友全栈君。
SQL参数化查询
一、以往的防御方式
- 字符串检测:限定内容只能由英文、数字等常规字符,如果检查到用户输入有特殊字符,直接拒绝。但缺点是,系统 中不可避免地会有些内容包含特殊字符,这时候总不能拒绝入库。
- 字符串替换:把危险字符替换成其他字符,缺点是危险字符可能有很多,一一枚举替换相当麻烦,也可能有漏网之 鱼。
- 存储过程:把参数传到存储过程进行处理,但并不是所有数据库都支持存储过程。如果存储过程中执行的命令也是通 过拼接字符串出来的,还是会有漏洞。
二、什么是参数化查询?
有两种不同的方式来创建参数化查询。第一个方式是让查询优化器自动地参数化你的查询。另一个方式是通过以一个特定方式来编写你的T-SQL代码,并将它传递给sp_executesql系统存储过程,从而编程一个参数化查询。
这样的解释还是有点模糊,先看一例:
例一:参数化查询
在使用参数化查询的情况下,数据库服务器不会将参数的内容视为SQL指令的一部份来处理,而是在数据库完成SQL指令的编译后,才套用参数运行,因此就算参数中含有指令,也不会被数据库运行。Access、SQL Server、MySQL、SQLite等常用数据库都支持参数化查询。
//在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拼接,这样还算做是参数化过后的吗?如果存储过程一定是参数化过后的,那么是不是意味着,只要使用存储过程就具有参数化查询的全部优点了?
如下存储过程:
create procedure pro_getCustomers( @whereSql nvarchar(max))asdeclare @sql nvarchar(max)set @sql=N'select * from dbo.Customer ' + @whereSqlexec(@sql)Go--如果我要在ADO.NET中参数化查询这个存储过程,以防止SQL注入,我该怎么办呢?比如:exec pro_getCustomers 'where Name=@name'
这种方法没有办法防止注入,你能做的就是对字符串进行过滤.
"select * from customer where 1=1" + " and name=@name" + " and sex=@sex"
也就是判断参数化查询。只不过是动态地组装查询限制条件。
动态拼接SQL,而且是参数化查询的SQL语句是没有问题的。
USE [B2CShop]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[pro_getCustomers]( @whereSql nvarchar(max), @paramNameList nvarchar(max), @paramValueList nvarchar(max))asdeclare @sql nvarchar(max)set @sql=N'select * from dbo.Customer ' + @whereSqlexec sp_executesql @sql, @paramNameList , @paramValueListgo
/// <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);
}
/// <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();
}
/// <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注入
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();
}
}
}
}
}
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/151238.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...