嗨,我对MVc和C#相当新,我想知道我是否可以使用模型安全性来阻止SQL注入?我创建了一个模型,其中包含我们从客户端输入接收的变量,然后从它们形成一个SQL语句.我想知道MVC中的内置安全性是否足以阻止SQL注入?请查看代码,非常感谢任何建议.
模型
using System; using System.Collections.Generic; using System.Linq; using System.Web; namespace DataBaseTest.Models { public class BedroomModel { public string YrBlt1 { get; set; } public string YrBlt2 { get; set; } public string TotLivArea1 { get; set; } public string TotLivArea2 { get; set; } public string LotArea1 { get; set; } public string LotArea2 { get; set; } public string Bedrooms { get; set; } public string SalePrice1 { get; set; } public string SalePrice2 { get; set; } public string SaleDate { get; set; } public string AssesVal1 { get; set; } public string AssesVal2 { get; set; } public string Style { get; set; } public string ArchStyle { get; set; } public string TaxUnit { get; set; } // Criteria added during SQL Queries public string YearBuilt { get; set; } public string LivingArea{ get; set; } public string LotArea { get; set; } public string SalePriceA { get; set; } public string SaleDateA { get; set; } public string AssesVal { get; set; } public string StyleA { get; set; } public string ArchStyleA { get; set; } public string ParcelId { get; set; } public string QuickRefId { get; set; } public string TaxunitA { get; set; } public string Address { get; set; } public string ValCode { get; set; } public string BedroomA { get; set; }
然后我们的SQL
[HttpPost] public ActionResult Index(DataBaseTest.Models.BedroomModel user,DataTable dtFindResults) { StringBuilder sbSQL = new StringBuilder(); //// define a list of CustomerModel objects DataSet tempDS = new DataSet(); //string xSQL = "SELECT PropertyAddress,PropertyTypeDesc,PropertyID FROM KDOR_vwPropertyGeneral ORDER BY PropertyAddress"; System.Data.SqlClient.SqlDataAdapter DbCmd = new System.Data.SqlClient.SqlDataAdapter(); string sqlWhereCOnt= " WHERE "; sbSQL.Append("SELECT "); //sbSQL.Append(SessionHandler.AddressPointsPointsIDColumn + " AS PointsID,"); sbSQL.Append("pg.PropertyNumberSearch,"); sbSQL.Append("pg.QuickRefID,"); sbSQL.Append("pg.PropertyAddress,"); sbSQL.Append("crb.fmsstyle,"); sbSQL.Append("srb.farchstyle,"); sbSQL.Append("pt.TransferValidityCode,"); sbSQL.Append("pg.TaxingUnitGroupCode,"); sbSQL.Append("pt.Price,"); sbSQL.Append("pt.SaleDate,"); sbSQL.Append("crb.fyrblt,"); sbSQL.Append("crb.vResBldgDep_tla_value,"); sbSQL.Append("lm.facres,"); sbSQL.Append("crb.frmbed"); sbSQL.Append(" FROM KDOR_vwPropertyGeneral pg "); sbSQL.Append(" Left Join cama_ResBldg crb ON pg.PropertyID = crb.PropertyID And pg.AdHocTaxYear = crb.AdHocTaxYear "); sbSQL.Append(" Left Join sales_ResBldg srb ON pg.PropertyID = srb.PropertyID"); sbSQL.Append(" Left Join KDOR_vwPropertyTransfer pt On pg.PropertyID = pt.PropertyID"); sbSQL.Append(" Left Join cama_LandMkt lm ON pg.PropertyID = lm.PropertyID And pg.AdHocTaxYear = lm.AdHocTaxYear"); if (!string.IsNullOrEmpty(user.YrBlt1)||!string.IsNullOrEmpty(user.YrBlt2)) { //sbSQL.Append(sqlWhereCont +"PropertyAddress = '" + user.Address + "'"); //sqlWhereCOnt= "AND "; sbSQL.Append(sqlWhereCont +"crb.fyrblt >="+ user.YrBlt1+ " And crb.fyrblt <= " + user.YrBlt2 ); sqlWhereCOnt= "AND "; } if (!string.IsNullOrEmpty(user.TotLivArea1) || !string.IsNullOrEmpty(user.TotLivArea2)) { //sbSQL.Append(sqlWhereCont +"PropertyAddress = '" + user.Address + "'"); //sqlWhereCOnt= "AND "; sbSQL.Append(sqlWhereCont + "crb.vResBldgDep_tla_value >=" + user.TotLivArea1 + " And crb.vResBldgDep_tla_value <= " + user.TotLivArea2 ); sqlWhereCOnt= "AND "; } if (!string.IsNullOrEmpty(user.LotArea1) || !string.IsNullOrEmpty(user.LotArea2)) { //sbSQL.Append(sqlWhereCont +"PropertyAddress = '" + user.Address + "'"); //sqlWhereCOnt= "AND "; sbSQL.Append(sqlWhereCont + "lm.facres >=" + user.LotArea1 + " And lm.facres <= " + user.LotArea2 ); sqlWhereCOnt= "AND "; } if (!string.IsNullOrEmpty(user.Bedrooms)) { sbSQL.Append(sqlWhereCont + "crb.frmbed = '" + user.Bedrooms + "'"); sqlWhereCOnt= "AND "; } if (!string.IsNullOrEmpty(user.SalePrice1) || !string.IsNullOrEmpty(user.SalePrice2)) { //sbSQL.Append(sqlWhereCont +"PropertyAddress = '" + user.Address + "'"); //sqlWhereCOnt= "AND "; sbSQL.Append(sqlWhereCont + "pt.Price >=" + user.SalePrice1 + " And pt.Price <= " + user.SalePrice2 ); sqlWhereCOnt= "AND "; } if (!string.IsNullOrEmpty(user.SaleDate)) { sbSQL.Append(sqlWhereCont + "pt.SaleDate = '" + user.SaleDate + "'"); sqlWhereCOnt= "AND "; } if (!string.IsNullOrEmpty(user.AssesVal1) || !string.IsNullOrEmpty(user.AssesVal2)) { //sbSQL.Append(sqlWhereCont +"PropertyAddress = '" + user.Address + "'"); //sqlWhereCOnt= "AND "; sbSQL.Append(sqlWhereCont + "crb.vResBldgDep_tla_value >=" + user.AssesVal1 + " And crb.vResBldgDep_tla_value <= " + user.AssesVal2 ); sqlWhereCOnt= "AND "; } if (!string.IsNullOrEmpty(user.Style)) { sbSQL.Append(sqlWhereCont + "crb.fmsstyle = '" + user.Style + "'"); sqlWhereCOnt= "AND "; } if (!string.IsNullOrEmpty(user.ArchStyle)) { sbSQL.Append(sqlWhereCont + "srb.farchstyle = '" + user.ArchStyle + "'"); sqlWhereCOnt= "AND "; } if (!string.IsNullOrEmpty(user.TaxUnit)) { sbSQL.Append(sqlWhereCont + "pg.TaxingUnitGroupCode = '" + user.TaxUnit + "'"); sqlWhereCOnt= "AND "; } sbSQL.Append(" ORDER BY "); sbSQL.Append(" pg.QuickRefID "); //// populate a list of CustomerModel objects from database string MyCOnnectionString= ConfigurationManager.ConnectionStrings["WLConnection"].ConnectionString; System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(MyConnectionString); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sbSQL.ToString(), cnn); cmd.CommandTimeout = 30000; DbCmd.SelectCommand = cmd; DbCmd.Fill(tempDS, "ResultSet"); DataTable resultSet = tempDS.Tables["ResultSet"]; var vm = new List(); foreach (DataRow dr in tempDS.Tables[0].Rows) { vm.Add(new BedroomModel { BedroomA = dr.ItemArray[12].ToString(), YearBuilt = dr.ItemArray[9].ToString(), LivingArea = dr.ItemArray[7].ToString(), LotArea = dr.ItemArray[3].ToString(), SaleDateA = dr.ItemArray[8].ToString(), SalePriceA = dr.ItemArray[10].ToString(), AssesVal = dr.ItemArray[5].ToString(), StyleA = dr.ItemArray[3].ToString(), ArchStyleA = dr.ItemArray[4].ToString(), ParcelId = dr.ItemArray[0].ToString(), QuickRefId = dr.ItemArray[1].ToString(), TaxunitA = dr.ItemArray[6].ToString(), Address = dr.ItemArray[2].ToString(), ValCode = dr.ItemArray[5].ToString(), }); } //DbCmd.Fill(dtFindResults); //var x = dtFindResults.Rows.Count; cnn.Close(); return View("Result",vm); //// return the list of CustomerModel objects to our View //return View("Result", resultSet); //return View(ViewBag.data); }
Tim Medora.. 5
参数化查询是必须的.但是,这并不妨碍您构建动态查询,您只需要以不同方式处理它.
根据用户输入做出决定是很好的; 将这些值连接到查询中并不好.
一个简单的例子:
using( IDbCommand cmd = GetCommand() ) { string lotSize = "12345"; bool includeLotSize = !string.IsNullOrWhiteSpace( lotSize ); var sb = new StringBuilder(); sb.AppendLine( "SELECT Col1, Col2 FROM dbo.Foo" ); // you might also vary the columns returned based on what the user asked for if( includeLotSize ) { sb.AppendLine( "WHERE LotSize = @LotSize" ); // The query will expect the lot size, so add a parameter here to pass // the lot size value. cmd.Parameters.Add( new SqlParameter( "LotSize", lotSize ) ); } }
请注意,许多字符串属性看起来可能是更具体的类型(int,float,指向数据库查找的int等).这不会阻止SQL注入,但它可以用于验证(以及使您的视图模型更清晰).
另请注意,有许多不同的方法可以连接到.Net中的数据库,但请确保正确处理您的资源(请注意using
我添加的语句).
参数化查询是必须的.但是,这并不妨碍您构建动态查询,您只需要以不同方式处理它.
根据用户输入做出决定是很好的; 将这些值连接到查询中并不好.
一个简单的例子:
using( IDbCommand cmd = GetCommand() ) { string lotSize = "12345"; bool includeLotSize = !string.IsNullOrWhiteSpace( lotSize ); var sb = new StringBuilder(); sb.AppendLine( "SELECT Col1, Col2 FROM dbo.Foo" ); // you might also vary the columns returned based on what the user asked for if( includeLotSize ) { sb.AppendLine( "WHERE LotSize = @LotSize" ); // The query will expect the lot size, so add a parameter here to pass // the lot size value. cmd.Parameters.Add( new SqlParameter( "LotSize", lotSize ) ); } }
请注意,许多字符串属性看起来可能是更具体的类型(int,float,指向数据库查找的int等).这不会阻止SQL注入,但它可以用于验证(以及使您的视图模型更清晰).
另请注意,有许多不同的方法可以连接到.Net中的数据库,但请确保正确处理您的资源(请注意using
我添加的语句).