public ActionResult SimpleQuery()
{
string startwhere = string.Empty;
string report = Request.Form["report"];
CommondController commond = new CommondController(_db);
string sqlValue = commond.GetSqlValue(report, isFillter: false); /*TODO: isFillter:false SimpleQuery*/
if (sqlValue.IsEmpty())
{
return(Content("no"));
}
string[] keys = Request.Form.AllKeys;
MYSQLInit init = new MYSQLInit();
try
{
SimpleSqlInjectMethod(init, sqlValue, keys);
int rowEf = commond.GetCount(sqlValue + init.GetCurrentSQL(), init.GetCurrentPara());
if (0 == rowEf)
{
return(Content("no"));
}
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
return(Content("error"));
}
Session["SqlValue"] = GetSimpleSql(report);
return(Content("ok"));
}
public static MYSQLInit Insert(this MYSQLInit @this, string tableName, List <string> fields, List <MySqlParameter> para)
{
if (fields.Count != para.Count)
{
throw new ArgumentOutOfRangeException("字段列表跟值列表长度不一致");
}
@this.Builder.Insert(tableName, fields);
@this.ParaList = para;
return(@this);
}
public static MYSQLInit Insert(this MYSQLInit @this, string tableName, List <string> fields, List <object> values)
{
if (fields.Count != values.Count)
{
throw new ArgumentOutOfRangeException("字段列表跟值列表长度不一致");
}
@this.Builder.Insert(tableName, fields);
int Index = 0;
foreach (var item in fields)
{
@this.ParaList.Add(new MySqlParameter(item, values[Index++]));
}
return(@this);
}
public ActionResult ETaoPhoto(string Id /*= "040427cf-0cb9-4ef2-8379-5b63df38e98a"*/)
{
if (string.IsNullOrEmpty(Id))
{
return(View());
}
MYSQLInit Sql = new MYSQLInit();
Sql.Append("select idCardImg1 as 'F_idCard',idCardImg2 as 'B_idCard' ,license as 'License' , storeImg1 as 'Store_1', storeImg2 as 'Store_2' ,storeImg3 as 'Store_3' ,`name` ,phone,authenticId from etao_authentic");
Sql.Where("authenticId =", Id);
DataTable T = new CommondController(_db).GetDataTableWithParam(Sql.GetCurrentSQL(), Sql.GetCurrentPara());
ETaoPhoto model = T.ConvertTo <ETaoPhoto>().FirstOrDefault();
return(View(model));
}
public static MYSQLInit Update(this MYSQLInit @this, string name, string value)
{
@this.Builder.Set(name, value, @this.ParaList);
return(@this);
}
public static MYSQLInit Limit(this MYSQLInit @this, string pageIndex, string pageSize)
{
@this.Builder.Limit(pageIndex, pageSize, @this.ParaList);
return(@this);
}
public static MYSQLInit OrderBy(this MYSQLInit @this, string name, string type)
{
@this.Builder.OrderBy(name, type, @this.ParaList);
return(@this);
}
public static MYSQLInit And(this MYSQLInit @this, string sql, object value, MySqlDbType dbType)
{
@this.Builder.And(sql, value, dbType, @this.ParaList);
return(@this);
}
public static MYSQLInit Where(this MYSQLInit @this, string sql, object value)
{
@this.Builder.Where(sql, value, @this.ParaList);
return(@this);
}
public static MYSQLInit Append(this MYSQLInit @this, string sql)
{
@this.Builder.AppendFormat(" {0} ", sql);
return(@this);
}
private void SimpleSqlInjectMethod(MYSQLInit init, string sqlValue, string[] keys)
{
#region 遍历表单值 排除report 跟订单状态
foreach (string name in keys)
{
if ("report" == name || "订单状态" == name || "__RequestVerificationToken" == name)
{
continue;
}
if (name.Contains("日期1") && Request.Form[name].IsNotEmpty())
{
var value = sqlValue.GetFieldSqlByName(name.Substring(0, name.Length - 1));
init.And(value + ">", Request.Form[name]);
continue;
}
if (name.Contains("日期2") && Request.Form[name].IsNotEmpty())
{
DateTime endTime = DateTime.Parse(Request.Form[name]).AddDays(1);
var dateStr = endTime.ToString("yyyy-MM-dd");
var value = sqlValue.GetFieldSqlByName(name.Substring(0, name.Length - 1));
init.And(value + "<", dateStr);
continue;
}
if (Request.Form[name].IsNotEmpty())
{
var value = sqlValue.GetFieldSqlByName(name);
init.And(value + " like ", "%" + Request.Form[name] + "%");
}
}
#endregion
#region 遍历订单状态
if (Request.Form["订单状态"].IsNotEmpty()) // keys.toStringMergeChar(',').Contains("订单状态")
{
var listValue = Request.Form["订单状态"].toStringArray();
var value = sqlValue.GetFieldSqlByName("订单状态");
int beginIndex = 500;
init.Builder.AppendFormat(" and {0} in (", value);
foreach (var item in listValue)
{
var indexStr = (beginIndex++).ToString();
init.Builder.Append("?Para" + indexStr + ",");
init.ParaList.Add(new MySqlParameter("?Para" + indexStr, item));
}
init.Builder.Length = init.Builder.Length - 1; // trim end , (去掉最后的 逗号)
init.Builder.Append(")");
}
#endregion
if (sqlValue.IndexOf("where", StringComparison.OrdinalIgnoreCase) < 0)
{
if (init.Builder.Length > 0)
{
if (init.Builder.ToString().IndexOf("and", StringComparison.OrdinalIgnoreCase) > -1)
{
//trimStart and
init.Builder.Remove(init.Builder.ToString().IndexOf("and", StringComparison.OrdinalIgnoreCase), 3).Insert(0, " where ");
}
}
}
}
/// <summary>
/// 解决注入 sql 攻击 高级搜索文本输入
/// <autor>郑万庚</autor>
/// </summary>
/// <param name="sqlValue"></param>
/// <param name="dic"></param>
/// <param name="init"></param>
private void SqlInjectMethod(string sqlValue, Dictionary <string, FormValue> dic, MYSQLInit init)
{
FormValue formValue = null;
string[] intStr = { "System.Single", "System.Double", "System.SByte", "System.Int32", "System.Int64", "System.UInt64", "System.Int16", "System.Int", "System.Decimal", "System.Single", "System.Double" };
string stringStr = "System.String";
string dateStr = "System.DateTime";
foreach (KeyValuePair <string, FormValue> item in dic)
{
var sqlField = "";
formValue = item.Value;
string key = item.Key;
#region 时间
if (key.Contains(dateStr))
{
sqlField = formValue.DateExit ? sqlValue.GetFieldSqlByName(formValue.name) : (formValue.SecondData ? sqlValue.GetFieldSqlByName(formValue.name) : formValue.name);
if (formValue.DateExit && formValue.SecondData)
{
init.And(sqlField + " >", formValue.value);
init.And(sqlField + " <", formValue.maxDataTime);
}
else
{
if (formValue.DateExit)
{
init.And(sqlField + " >", formValue.value);
}
else if (formValue.SecondData)
{
init.And(sqlField + " <", formValue.maxDataTime);
}
}
continue;
}
#endregion
#region 字符串
if (key.Contains(stringStr))
{
sqlField = formValue.DateExit ? sqlValue.GetFieldSqlByName(formValue.name) : formValue.name;
if (formValue.DateExit)
{
init.And(sqlField + " like", "%" + formValue.value + "%");
}
continue;
}
#endregion
#region 数字
if (intStr.InArray(key))
{
sqlField = formValue.DateExit ? sqlValue.GetFieldSqlByName(formValue.name) : formValue.name;
if (formValue.DateExit)
{
if (formValue.operatorstr.IsNotEmpty())
{
init.And(sqlField + formValue.operatorstr, formValue.value);
}
else
{
init.And(sqlField + " like", "%" + formValue.value + "%");
}
}
continue;
}
#endregion
}
if (sqlValue.IndexOf("where", StringComparison.OrdinalIgnoreCase) < 0)
{
if (init.Builder.Length > 0)
{
if (init.Builder.ToString().IndexOf("and", StringComparison.OrdinalIgnoreCase) > -1)
{
//trimStart and
init.Builder.Remove(init.Builder.ToString().IndexOf("and", StringComparison.OrdinalIgnoreCase), 3).Insert(0, " where ");
}
}
}
}
public ActionResult AdvancedQuery()
{
//高级查询 重新查询所有 （重要）
string typeInt = "System.Int32System.Int64System.UInt64System.Int16System.IntSystem.DecimalSystem.SingleSystem.DoubleSystem.SByteSystem.Decima";
string report = Request.Form["report"];
string[] ziduan = Request.Form["ziduan"].Split(new char[] { ',' });
string[] leixing = Request.Form["leixing"].Split(new char[] { ',' });
Dictionary <string, FormValue> dic = new Dictionary <string, FormValue>();
int i = 0;
foreach (var item in ziduan)
{
FormValue fv = new FormValue {
name = ziduan[i], DateExit = true, value = Request.Form[ziduan[i]]
};
if (leixing[i] == "System.DateTime")
{
#region System.DateTime
if (Request.Form[ziduan[i]].Trim() == string.Empty)
{
fv.DateExit = false;
}
if ((Request.Form[ziduan[i] + ziduan[i]]).Trim() == string.Empty)
{
fv.SecondData = false;
}
else
{
fv.SecondData = true;
string date = Request.Form[ziduan[i] + ziduan[i]];
fv.maxDataTime = DateTime.Parse(date).AddDays(1).ToString();
}
#endregion
}
else if (typeInt.Contains(leixing[i]))
{
//存在运算符
if (Request.Form[ziduan[i]].Trim() == string.Empty)
{
fv.DateExit = false;
}
else
{
fv.operatorstr = Request.Form[ziduan[i] + "selectname"];
}
}
else
{
if (Request.Form[ziduan[i]].Trim() == string.Empty)
{
fv.DateExit = false;
}
}
dic.Add(leixing[i] + i.ToString(), fv);
i++;
}
CommondController commond = new CommondController(_db);
string sql = commond.GetSqlValue(report, isFillter: false); /*TODO: isFillter:false AdvancedQuery*/
if (sql.IsNotEmpty())
{
//old method
//处理sql拼接
//sqlString = GetSqlValue(sql, dic, Request.Form["title"]); //getSqlByDict(sql, dic);
//var count=commond.GetCount(sqlString);
//if (0 == count)
//return Content("no");
////保存当前多条件查询的字符串
//Session["SqlValue"] = sqlString;
//return Content("ok");
MYSQLInit sqlInit = new MYSQLInit();
SqlInjectMethod(sql, dic, sqlInit);
var listcount = commond.GetCount(sql + sqlInit.GetCurrentSQL(), sqlInit.GetCurrentPara());
if (0 == listcount)
{
return(Content("no"));
}
else
{
Session["SqlValue"] = GetSqlValue(sql, dic, Request.Form["title"]);
return(Content("ok"));
}
}
else
{
return(Content("no"));
}
}