通用权限管理系统底层的数据访问方法支持参数化查询。
先前没有使用参数化查询的语句是这样的
string conmmondText = " SELECT A.SITE_ID AID, A.SITE_NAME ANAME, B.SITE_ID BID, B.SITE_NAME BNAME" + " FROM (SELECT 1 AS ID, SITE_NAME, SITE_ID" + " FROM AREA_SUB" + " WHERE AREA_ID = ‘{0}‘" + " AND DELETIONSTATECODE = 0) A" + " LEFT JOIN (SELECT 1 AS ID, SITE_NAME, SITE_ID" + " FROM AREA_SUB" + " WHERE AREA_ID = ‘{1}‘" + " AND DELETIONSTATECODE = 0) B ON A.ID = B.ID"; conmmondText= string.Format(conmmondText, array[0], array[1]); var dt = dbHelper.Fill(conmmondText);
现数据库管理员要求SQL语句要使用参数化查询，改造后的语句
string conmmondText= " SELECT A.SITE_ID AID, A.SITE_NAME ANAME, B.SITE_ID BID, B.SITE_NAME BNAME" + " FROM (SELECT 1 AS ID, SITE_NAME, SITE_ID" + " FROM AREA_SUB" + " WHERE AREA_ID = {0}" + " AND DELETIONSTATECODE = 0) A" + " LEFT JOIN (SELECT 1 AS ID, SITE_NAME, SITE_ID" + " FROM AREA_SUB" + " WHERE AREA_ID = {1}" + " AND DELETIONSTATECODE = 0) B ON A.ID = B.ID"; IDbDataParameter[] dbParameters = new IDbDataParameter[] { ztoQuotePriceManager.DbHelper.MakeParameter("AREA_ID_0", array[0]), ztoQuotePriceManager.DbHelper.MakeParameter("AREA_ID_1", array[1]) }; var dt = dbHelper.Fill(conmmondText, dbParameters);
这样改造后可以防止SQL注入。
另外一个改造的语句参考
//List<AREA_SUBEntity> sendAreaSubList = areaSubManager.GetList<AREA_SUBEntity>(AREA_SUBEntity.FieldAREA_ID + "=‘" + ztoQuotePriceEntity.SEND_AREA_ID + "‘"); //List<AREA_SUBEntity> dispAreaSubList = areaSubManager.GetList<AREA_SUBEntity>(AREA_SUBEntity.FieldAREA_ID + "=‘" + ztoQuotePriceEntity.DISP_AREA_ID + "‘"); List<AREA_SUBEntity> sendAreaSubList = areaSubManager.GetList<AREA_SUBEntity>(new KeyValuePair<string, object>(AREA_SUBEntity.FieldAREA_ID, ztoQuotePriceEntity.SEND_AREA_ID)); List<AREA_SUBEntity> dispAreaSubList = areaSubManager.GetList<AREA_SUBEntity>(new KeyValuePair<string, object>(AREA_SUBEntity.FieldAREA_ID, ztoQuotePriceEntity.DISP_AREA_ID));
建议大家在使用底层时，尽量使用带参数化查询的方法。
原文：http://www.cnblogs.com/hnsongbiao/p/4469893.html