SqlHelper中IN集合场景下的参数处理

我手头有个古老的项目,持久层用的是古老的ADO.net。前两天去昆明旅游,其中的一个景点是云南民族村,通过导游介绍知道了一个古老的民族——基诺族,“基”在这个族内代表舅舅,“基诺”意为“跟在舅舅后边”,加以引申即为“尊崇舅舅的民族”,很有意思吧,这是我国最后一个被发现并确认下来的少数民族,即第56个民族。  项目里的ado.net和基诺族一样古老。

SqlHelper中IN集合场景下的参数处理

话说,项目里数据访问层,好多都是拼的sql,这给sql注入提供了可乘之机,为了系统安全,决定在有限的时间内,将它改成参数化。

其中,有个根据多个订单号查询支付单的方法,签名如下:

public DataTable GetAlipayNotifyRecords(AlipayPaymentStatus status, params string[] trade_no)

那么,问题来了,因为sql里有in, 而 in(@no)的方式是行不通的。

怎么办呢?  首先想到的是对参数做处理:

public DataTable GetAlipayNotifyRecords(AlipayPaymentStatus status, params string[] trade_no)
{
string sql = @"select * from T_AlipayNotityRecord where trade_status=@trade_status and trade_no in(@trade_no)";
//string inValue = "'" + string.Join("','", trade_no) + "'";//= string.Join(",", trade_no)
string inValue = "";
trade_no.ToList().ForEach(no => inValue += " union all select '" + no+"'");
inValue = inValue.Substring(" union all".Length);
List<SqlParameter> paramList = new List<SqlParameter>()
{
new SqlParameter("@trade_status",status.ToString()),
new SqlParameter("@trade_no",inValue),
};
var ds = SqlHelper.SqlDataSet(ConfigFile.PayCenterConnection, sql, CommandType.Text, paramList.ToArray());
if (ds == null || ds.Tables.Count == )
return null;
return ds.Tables[];
}

经测试,无效。经分析可知,sqlhelper会把你参数值当成字符串,不会对其做转义。所以,不管怎么对参数值处理,都还是一串字符串。

按这样的原理往下想,只能是将单号分开来传递给sql了。那么正好sql的in可以通过如下几种方式等效实现:

  • sql里有临时表,可以in一个临时表--这时,可以考虑and trade_no in(select @p1 union all select @p2 union all...)的方式
  • 把sql的in集合,转换为一个用or拼接起来的集合---即,and (trade_no=@p1 or trade_no=@p2 or trade_no=@p3 or...)

如下代码是按照后者的思路解决了这个问题:

public DataTable GetAlipayNotifyRecords(AlipayPaymentStatus status, params string[] trade_no)
{
string sql = @"select * from T_AlipayNotityRecord where trade_status=@trade_status and ({0})"; List<SqlParameter> paramList = new List<SqlParameter>()
{
new SqlParameter("@trade_status",status.ToString()),
};
string sql1 = "";
for (int i=;i<trade_no.Length;i++)
{
sql1 += " or trade_no=@no" + i;
paramList.Add(new SqlParameter("@no" + i, trade_no[i]));
}
sql = string.Format(sql, sql1.Substring(" or ".Length)); var ds = SqlHelper.SqlDataSet(ConfigFile.PayCenterConnection, sql, CommandType.Text, paramList.ToArray());
if (ds == null || ds.Tables.Count == )
return null;
return ds.Tables[];
}

【结语】无意中从园子里看到一篇文章,不该活着的SqlHelper和DBHelper,很赞!

-----2016-12-13 09:39:32

【续】方法总比问题多

今天早上刷牙时,灵光一现,对于昨天的方案,不需要通过借助or或union的方式来更改sql的in了,即,可以直接生成如下的sql语句:

select * from T_AlipayNotityRecord where trade_status=@trade_status and trade_no in(@no0,@no1,...)

程序代码在上面的基础上稍做处理:

public DataTable GetAlipayNotifyRecords(AlipayPaymentStatus status, params string[] trade_no)
{
string sql = @"select * from T_AlipayNotityRecord where trade_status=@trade_status and trade_no in({0})"; List<SqlParameter> paramList = new List<SqlParameter>()
{
new SqlParameter("@trade_status",status.ToString()),
};
string sql1 = "";
for (int i = ; i < trade_no.Length; i++)
{
sql1 += ",@no" + i;
paramList.Add(new SqlParameter("@no" + i, trade_no[i]));
}
sql = string.Format(sql, sql1.Substring(",".Length)); var ds = SqlHelper.SqlDataSet(ConfigFile.PayCenterConnection, sql, CommandType.Text, paramList.ToArray());
if (ds == null || ds.Tables.Count == )
return null;
return ds.Tables[];
}

这样子生成的sql就很直观了。比上面方案的还简短。

上一篇:POJ 1015 Jury Compromise(双塔dp)


下一篇:在android工程中,res目录下又有anim、drawable、layout、menu、raw、values和xml文件夹,分别用来保存?