public OrmLitePageResult <Announcement> SelectList(string title, int pageIndex, int pageSize)
{
StringBuilder sb = new StringBuilder();
var ps = DictBuilder.New();
sb.Append("1=1");
var orderBy = "id desc";
title = SqlInjection.Filter(title);
if (!string.IsNullOrEmpty(title))
{
sb.AppendFormat(" and Title like '%{0}%'", title);
}
var factor = new OrmLitePageFactor
{
Conditions = sb.ToString(),
PageIndex = pageIndex,
PageSize = pageSize,
OrderBy = orderBy,
Params = ps
};
return(announcementDao.SelectPage(factor));
}
// <summary>
// Get values for the giving account
// </summary>
public void GetValues(string token)
{
// MySQL query
const string result = "SELECT id, voornaam, tussenvoegsel, achternaam, email, pepper " +
"FROM meok2_bibliotheek_gebruikers";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var showresult = new MySqlCommand(result, empConnection))
{
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
using (var reader = showresult.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
// Check if the MD5 hash mathes
using (var md5Hash = MD5.Create())
{
if (!Crypt.VerifyMd5Hash(md5Hash, reader.GetValue(4).ToString(), token))
{
continue;
}
// Save the values
var id = reader.GetValue(0).ToString();
var pepper = reader.GetValue(5).ToString();
if (id != "-1")
{
// Save values to the model
Id = Convert.ToInt16(id);
Firstname =
SqlInjection.SafeSqlLiteralRevert(
Crypt.StringDecrypt(reader.GetValue(1).ToString(), pepper));
Affix =
SqlInjection.SafeSqlLiteralRevert(
Crypt.StringDecrypt(reader.GetValue(2).ToString(), pepper));
Lastname =
SqlInjection.SafeSqlLiteralRevert(
Crypt.StringDecrypt(reader.GetValue(3).ToString(), pepper));
Mail = SqlInjection.SafeSqlLiteralRevert(reader.GetValue(4).ToString());
Pepper = pepper;
}
}
}
}
}
catch (MySqlException)
{
// MySqlException bail out
}
finally
{
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
}
public List <MachinesInfo> GetMachines(string NWIP, string WWIP, string JQRT, string GLY, string ZG, PagerInfo info)
{
if (NWIP != null && SqlInjection.GetString(NWIP))
{
return(null);
}
if (WWIP != null && SqlInjection.GetString(WWIP))
{
return(null);
}
if (JQRT != null && SqlInjection.GetString(JQRT))
{
return(null);
}
if (GLY != null && SqlInjection.GetString(GLY))
{
return(null);
}
if (ZG != null && SqlInjection.GetString(ZG))
{
return(null);
}
if (machinesDal == null)
{
machinesDal = baseDal as IMachines;
}
return(machinesDal.GetMachines(NWIP, WWIP, JQRT, GLY, ZG, info));
}
public Int32 GetMachinesCount(string NWIP, string WWIP, string JQRT, string GLY, string ZG)
{
if (NWIP != null && SqlInjection.GetString(NWIP))
{
return(-1);
}
if (WWIP != null && SqlInjection.GetString(WWIP))
{
return(-1);
}
if (JQRT != null && SqlInjection.GetString(JQRT))
{
return(-1);
}
if (GLY != null && SqlInjection.GetString(GLY))
{
return(-1);
}
if (ZG != null && SqlInjection.GetString(ZG))
{
return(-1);
}
if (machinesDal == null)
{
machinesDal = baseDal as IMachines;
}
return(machinesDal.GetMachinesCount(NWIP, WWIP, JQRT, GLY, ZG));
}
/// <summary>
/// 获取测试数据
/// </summary>
/// <param name="key"></param>
/// <param name="PageSize"></param>
/// <param name="CurrentCount"></param>
/// <param name="TotalCount"></param>
/// <returns></returns>
public List <ArticleEntity> GetArticleList(string key, int PageSize, int CurrentCount, out int TotalCount)
{
string tabName = string.Format("Article");
string strWhere = " 1=1";
if (!string.IsNullOrEmpty(key))
{
//SQL关键字过滤 包含关键字则不拼接SQL
if (!SqlInjection.GetString(key))
{
strWhere += string.Format(" AND (Title LIKE '%{0}%' OR Content LIKE '%{0}%')", key);
}
}
string Order = string.Format("ID DESC");
DataSet ds = SqlHelper.GetList(SqlHelper.connStr, Order, PageSize, CurrentCount, tabName, strWhere, out TotalCount);
List <ArticleEntity> list = new List <ArticleEntity>();
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
ArticleEntity model = new ArticleEntity();
model.ID = Convert.ToInt32(dr["ID"]);
model.Title = dr["Title"].ToString();
model.Content = dr["Content"].ToString();
list.Add(model);
}
}
return(list);
}
/// <summary>
/// 分页查询
/// </summary>
/// <returns></returns>
public ActionResult GetAllResumeInfo()
{
string strWhere = "1=1";
string sort = Request["sort"] == null ? "Id" : Request["sort"];
string order = Request["order"] == null ? "asc" : Request["order"];
//首先获取前台传递过来的参数
int pageindex = Request["page"] == null ? 1 : Convert.ToInt32(Request["page"]);
int pagesize = Request["rows"] == null ? 10 : Convert.ToInt32(Request["rows"]);
if (!string.IsNullOrEmpty(Request["RealName"]) && !SqlInjection.GetString(Request["RealName"]))
{
strWhere += " and RealName like '%" + Request["RealName"] + "%'";
}
if (!string.IsNullOrEmpty(Request["IdCard"]) && !SqlInjection.GetString(Request["IdCard"]))
{
strWhere += " and IdCard like '%" + Request["IdCard"] + "%'";
}
int totalCount; //输出参数
string strJson = bll.GetPager("Resume_Base", "Id,RealName,Sex,Nation,IdCard,PoliticalAffiliation,Education,Major,IsMarry,IsBred,Residence,ResidenceType,Address,FillingDate ", sort + " " + order, pagesize, pageindex, strWhere, out totalCount);
var jsonResult = new { total = totalCount.ToString(), rows = strJson };
return(Content("{\"total\": " + totalCount.ToString() + ",\"rows\":" + strJson + "}"));
}
/// <summary>
/// 获取项目的节点详情
/// </summary>
/// <returns></returns>
public ActionResult GetPMNodeInfo()
{
//首先获取前台传递过来的参数
string strWhere = "1=1";
string view = Request["view"] == null ? "PMMaintain" : Request["view"];
string ProjectID = Request["ProjectID"] == null ? "" : Request["ProjectID"];
string ProjectNo = Request["ProjectNo"] == null ? "" : Request["ProjectNo"];
string ProjectName = Request["ProjectName"] == null ? "" : Request["ProjectName"];
if (ProjectID.Trim() != "" && !SqlInjection.GetString(ProjectID)) //防止sql注入
{
strWhere += string.Format(" and ProjectID = '{0}'", ProjectID.Trim());
}
string content = "";
try
{
string sqlstr = string.Format("select * from tbMgrNodeInfo where ProjectID='{0}'", ProjectID);
DataTable dt = AchieveCommon.SqlHelper.GetDataTable(SqlHelper.connStr, sqlstr);
string strJson = AchieveCommon.JsonHelper.ToJson(dt);
content = "{\"success\": true ,\"rows\":" + strJson + "}";
}
catch (Exception ex)
{
return(Content("{\"msg\":\"获取数据失败," + ex.Message.Trim().Replace("\r", "").Replace("\n", "") + "\",\"success\":false}"));
}
return(Content(content));
}
public OrmLitePageResult <TimedTask> SearchList(string name, bool?enable, int pageIndex, int pageSize)
{
var condition = "1=1";
name = SqlInjection.Filter(name);
if (!string.IsNullOrEmpty(name))
{
condition += " and Name like '%" + name + "%'";
}
if (enable != null)
{
condition += " and Enable=" + (enable.Value ? "1" : "0");
}
using (var db = Open())
{
return(db.SelectPage <TimedTask>(new OrmLitePageFactor
{
PageIndex = pageIndex,
PageSize = pageSize,
OrderBy = "id desc",
Conditions = condition,
}));
}
}
// <summary>
// Select all users from database
// </summary>
public static List <String> AllUsers()
{
// Initial vars
var list = new List <String>();
// MySQL query
const string result = "SELECT id, voornaam, tussenvoegsel, achternaam, pepper, email " +
"FROM meok2_bibliotheek_gebruikers";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var showresult = new MySqlCommand(result, empConnection))
{
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
using (var myDataReader = showresult.ExecuteReader(CommandBehavior.CloseConnection))
{
while (myDataReader.Read())
{
// Save the values
var id = myDataReader.GetString(0);
var pepper = myDataReader.GetString(4);
var name =
SqlInjection.SafeSqlLiteralRevert(Crypt.StringDecrypt(myDataReader.GetString(1),
pepper));
var affix = myDataReader.GetString(2);
var lastname =
SqlInjection.SafeSqlLiteralRevert(Crypt.StringDecrypt(myDataReader.GetString(3),
pepper));
var email = myDataReader.GetString(5);
if (!String.IsNullOrEmpty(affix))
{
name = name + " " +
SqlInjection.SafeSqlLiteralRevert(Crypt.StringDecrypt(affix, pepper));
}
name = name + " " + lastname;
list.Add(id);
list.Add(name);
list.Add(email);
}
}
}
catch (MySqlException)
{
// MySqlException bail out
}
finally
{
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
return(list);
}
public ActionResult GetAllProduceInfo()
{
string strWhere = "1=1";
string sort = Request["sort"] == null ? "ID" : Request["sort"];
string order = Request["order"] == null ? "asc" : Request["order"];
//首先获取前台传递过来的参数
int pageindex = Request["page"] == null ? 1 : Convert.ToInt32(Request["page"]);
int pagesize = Request["rows"] == null ? 10 : Convert.ToInt32(Request["rows"]);
string userid = Request["accountid"] == null ? "" : Request["accountid"];
string username = Request["username"] == null ? "" : Request["username"];
string isable = Request["isable"] == null ? "" : Request["isable"];
string ifchangepwd = Request["ifchangepwd"] == null ? "" : Request["ifchangepwd"];
string userperson = Request["userperson"] == null ? "" : Request["userperson"];
string adddatestart = Request["adddatestart"] == null ? "" : Request["adddatestart"];
string adddateend = Request["adddateend"] == null ? "" : Request["adddateend"];
if (userid.Trim() != "" && !SqlInjection.GetString(userid)) //防止sql注入
{
strWhere += string.Format(" and AccountName like '%{0}%'", userid.Trim());
}
if (username.Trim() != "" && !SqlInjection.GetString(username))
{
strWhere += string.Format(" and RealName like '%{0}%'", username.Trim());
}
if (isable.Trim() != "select" && isable.Trim() != "")
{
strWhere += " and IsAble = '" + isable.Trim() + "'";
}
if (ifchangepwd.Trim() != "select" && ifchangepwd.Trim() != "")
{
strWhere += " and IfChangePwd = '" + ifchangepwd.Trim() + "'";
}
if (adddatestart.Trim() != "")
{
strWhere += " and CreateTime > '" + adddatestart.Trim() + "'";
}
if (adddateend.Trim() != "")
{
strWhere += " and CreateTime < '" + adddateend.Trim() + "'";
}
int totalCount; //输出参数
DataTable dt = AchieveCommon.SqlPagerHelper.GetPagerK3("ICMO", "FBillNo,FStatus,FQty,FCommitQty,FPlanCommitDate,FPlanFinishDate,FStartDate,FFinishDate,FType,FWorkShop,FItemID", sort + " " + order, pagesize, pageindex, strWhere, out totalCount);
dt.Columns.Add(new DataColumn("FModel"));
dt.Columns.Add(new DataColumn("FName"));
for (int i = 0; i < dt.Rows.Count; i++)
{
DataTable dticitemcore = GetFNameByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
dt.Rows[i]["FModel"] = AchieveCommon.JsonHelper.ColumnToJson(dticitemcore, 0);
dt.Rows[i]["FName"] = AchieveCommon.JsonHelper.ColumnToJson(dticitemcore, 1);
}
string strJson = AchieveCommon.JsonHelper.ToJson(dt);
var jsonResult = new { total = totalCount.ToString(), rows = strJson };
return(Content("{\"total\": " + totalCount.ToString() + ",\"rows\":" + strJson + "}"));
}
public void Replace(string input, string expected)
{
var manip = new SqlInjection(12);
var result = manip.Manipulate(input);
Assert.NotNull(result);
Assert.Equal(expected, result);
}
public SqlInjection GetOrSetSqlInjection(Type entityType, SqlInjection sqlInjection = null)
{
if (!_sqlInjections.TryGetValue(entityType, out SqlInjection value) && sqlInjection != null)
{
value = sqlInjection;
_sqlInjections[entityType] = value;
}
return(value);
}
public ActionResult GetAllProduceInfo()
{
string strWhere = "1=1";
string sort = Request["sort"] == null ? "FPlanCommitDate" : Request["sort"];
string order = Request["order"] == null ? "desc" : Request["order"];
//首先获取前台传递过来的参数
int pageindex = Request["page"] == null ? 1 : Convert.ToInt32(Request["page"]);
int pagesize = Request["rows"] == null ? 10 : Convert.ToInt32(Request["rows"]);
string FBillNo = Request["FBillNo"] == null ? "" : Request["FBillNo"];
string FItemID = Request["FItemID"] == null ? "" : Request["FItemID"];
string isable = Request["isable"] == null ? "" : Request["isable"];
string ifchangepwd = Request["ifchangepwd"] == null ? "" : Request["ifchangepwd"];
string userperson = Request["userperson"] == null ? "" : Request["userperson"];
string FPlanCommitDate = Request["FPlanCommitDate"] == null ? "" : Request["FPlanCommitDate"];
string FPlanFinishDate = Request["FPlanFinishDate"] == null ? "" : Request["FPlanFinishDate"];
if (FBillNo.Trim() != "" && !SqlInjection.GetString(FBillNo)) //防止sql注入
{
strWhere += string.Format(" and FBillNo like '%{0}%'", FBillNo.Trim());
}
//FName为非主表字段，暂不支持直接查询；
//后期解决思路，先根据FName在子表中查询对应的FItemID，可能有多个，则将这多个拼接成where条件；
//例如 FItemID = id1 and FItemID = id2 and FItemID = id3...
//if (FName.Trim() != "" && !SqlInjection.GetString(FName))
// strWhere += string.Format(" and FName like '%{0}%'", FName.Trim());
if (FItemID.Trim() != "" && !SqlInjection.GetString(FItemID))
{
strWhere += string.Format(" and FItemID like '%{0}%'", FItemID.Trim());
}
if (isable.Trim() != "select" && isable.Trim() != "")
{
strWhere += " and IsAble = '" + isable.Trim() + "'";
}
if (ifchangepwd.Trim() != "select" && ifchangepwd.Trim() != "")
{
strWhere += " and IfChangePwd = '" + ifchangepwd.Trim() + "'";
}
if (FPlanCommitDate.Trim() != "")
{
strWhere += " and FPlanCommitDate > '" + FPlanCommitDate.Trim() + "'";
}
if (FPlanFinishDate.Trim() != "")
{
strWhere += " and FPlanFinishDate < '" + FPlanFinishDate.Trim() + "'";
}
//抽取主作业计划单,规则不包含-、_两种连接符
strWhere += "and Fbillno not like '%v_%' ESCAPE 'v' and Fbillno not like '%v-%' ESCAPE 'v'";
int totalCount; //输出参数
string strJson = new ProjectBLL().GetJsonPager("ICMO", "FBillNo,FStatus,FQty,FCommitQty,FPlanCommitDate,FPlanFinishDate,FStartDate,FFinishDate,FType,FWorkShop,FItemID", sort + " " + order, pagesize, pageindex, strWhere, out totalCount);
//var jsonResult = new { total = totalCount.ToString(), rows = strJson };
return(Content("{\"total\": " + totalCount.ToString() + ",\"rows\":" + strJson + "}"));
}
// <summary> Adds a new user to the database </summery>
public void AddUser()
{
// Run model through sql injection prevention
var fullName = SqlInjection.SafeSqlLiteral(Name);
var username = SqlInjection.SafeSqlLiteral(StringManipulation.ToLowerFast(Username));
var salt = Crypt.GetRandomSalt();
// TFA code
var buffer = new byte[9];
using (var rng = RandomNumberGenerator.Create())
{
rng.GetBytes(buffer);
}
// Generates a 10 character string of A-Z, a-z, 0-9 Don't need to worry about any =
// padding from the Base64 encoding, since our input buffer is divisible by 3
var secret = Convert.ToBase64String(buffer).Substring(0, 10).Replace('/', '0').Replace('+', '1');
// MySql query
const string insertStatement = "INSERT INTO users " +
"(Name, Username, Password, Salt, Secret) " +
"VALUES (?, ?, ?, ?, ?)";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var insertCommand = new MySqlCommand(insertStatement, empConnection))
{
// Bind parameters
insertCommand.Parameters.Add("Name", MySqlDbType.VarChar).Value = fullName;
insertCommand.Parameters.Add("Username", MySqlDbType.VarChar).Value = username;
insertCommand.Parameters.Add("Password", MySqlDbType.VarChar).Value = Crypt.HashPassword(Password,
salt);
insertCommand.Parameters.Add("Salt", MySqlDbType.VarChar).Value = salt;
insertCommand.Parameters.Add("Secret", MySqlDbType.VarChar).Value = secret;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
insertCommand.ExecuteNonQuery();
}
catch (MySqlException)
{
// MySqlException bail out
}
finally
{
// Always close the connection
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
Done = true;
}
public bool Login()
{
var email = SqlInjection.SafeSqlLiteral(StringManipulation.ToLowerFast(Email));
var password = Password;
var savedPassword = String.Empty;
var savedSalt = String.Empty;
var savedId = String.Empty;
// MySQL query
const string result = "SELECT id, password, salt, admin " +
"FROM meok2_bibliotheek_gebruikers " +
"WHERE email = ?";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var showresult = new MySqlCommand(result, empConnection))
{
// Bind parameters
showresult.Parameters.Add("email", MySqlDbType.VarChar).Value = email;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
using (var myDataReader = showresult.ExecuteReader(CommandBehavior.CloseConnection))
{
while (myDataReader.Read())
{
// Save the values
savedId = myDataReader.GetValue(0).ToString();
savedPassword = myDataReader.GetString(1);
savedSalt = myDataReader.GetString(2);
Admin = Convert.ToInt16(myDataReader.GetValue(3));
}
}
// Hash the password and check if the hash is the same as the saved password
if (Crypt.ValidatePassword(password, savedPassword, savedSalt))
{
Cookies.MakeCookie(email, savedId, Admin.ToString(CultureInfo.InvariantCulture));
return(true);
}
}
catch (MySqlException)
{
// MySqlException bail out
return(false);
}
finally
{
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
return(false);
}
public string MailCheck(string input)
{
// Validate email
if (ValidateEmail.IsValidEmail(input))
{
return(RegisterModel.CheckMail(SqlInjection.SafeSqlLiteral(StringManipulation.ToLowerFast(input))) > 0
? "Deze email is al bezet"
: "Deze email is nog niet bezet");
}
return("Dit is geen geldig email adres");
}
public void Existing_WithoutValue_ReturningValue()
{
var value = new SqlInjection {
EntityType = typeof(SqlInjectionTest), Property = "foo", Sql = "select foo from dual"
};
_ = Configuration.GetOrSetSqlInjection(typeof(SqlInjectionTest), value);
var result = Configuration.GetOrSetSqlInjection(typeof(SqlInjectionTest), null);
result.Should().Be(value);
}
public List <SystemRole> SelectAll(string name)
{
name = SqlInjection.Filter(name);
if (string.IsNullOrEmpty(name))
{
return(systemRoleDao.SelectAll());
}
else
{
return(systemRoleDao.Select($"name like '%{name}%'", null));
}
}
/// <summary>
/// 获取权限下的用户（分页）
/// </summary>
public string GetPagerRoleUser(int roleId, string order, int pageSize, int pageIndex)
{
if (SqlInjection.GetString(order)) //简单的sql注入过滤
{
order = "CreateTime asc";
}
int totalCount = dal.GetRoleUserCount(roleId);
DataTable dt = dal.GetPagerRoleUser(roleId, order, pageSize, pageIndex);
string strjson = JsonHelper.ToJson(dt);
return("{\"total\": " + totalCount.ToString() + ",\"rows\":" + strjson + "}");
}
public void NotExisting_WithValue_ReturningNull()
{
var value = new SqlInjection {
EntityType = typeof(SqlInjectionTest), Property = "foo", Sql = "select foo from dual"
};
var result = Configuration.GetOrSetSqlInjection(typeof(SqlInjectionTest), value);
Assert.IsNotNull(result);
Assert.AreSame(value, result);
Assert.AreEqual("foo", value.Property);
Assert.AreEqual("select foo from dual", value.Sql);
Assert.AreEqual(value.EntityType, result.EntityType);
}
// <summary>
// Add book to the database
// </summary>
public bool AddBook()
{
// Run model through sql prevention and save them to vars
var title = SqlInjection.SafeSqlLiteral(Title);
var author = SqlInjection.SafeSqlLiteral(Author);
var genre = Genre;
var isbn = SqlInjection.SafeSqlLiteral(Isbn);
var floor = Floor;
var rack = Rack;
var dateAdded = StringManipulation.DateTimeToMySql(DateTime.Now);
// MySQL query Insert book in the database
const string insertStatement = "INSERT INTO meok2_bibliotheek_boeken " +
"(titel, auteur, genre, isbn, verdieping, rek, dateadded) " +
"VALUES (?, ?, ?, ?, ?, ?, ?)";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var insertCommand = new MySqlCommand(insertStatement, empConnection))
{
// Bind parameters
insertCommand.Parameters.Add("titel", MySqlDbType.VarChar).Value = title;
insertCommand.Parameters.Add("auteur", MySqlDbType.VarChar).Value = author;
insertCommand.Parameters.Add("genre", MySqlDbType.Int16).Value = genre;
insertCommand.Parameters.Add("isbn", MySqlDbType.VarChar).Value = isbn;
insertCommand.Parameters.Add("verdieping", MySqlDbType.Int16).Value = floor;
insertCommand.Parameters.Add("rek", MySqlDbType.Int16).Value = rack;
insertCommand.Parameters.Add("dateadded", MySqlDbType.Date).Value = dateAdded;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
insertCommand.ExecuteNonQuery();
// Return
return(true);
}
catch (MySqlException)
{
// MySqlException bail out
return(false);
}
finally
{
// Make sure to close the connection
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
}
public DataTable GetZG(string key)
{
// 检查一下key是否含有不合法的字符串
if (SqlInjection.GetString(key))
{
return(null);
}
if (machinesDal == null)
{
machinesDal = baseDal as IMachines;
}
return(machinesDal.GetZG(key));
}
public ActionResult GetAllUserInfo()
{
string strWhere = "1=1";
string sort = Request["sort"] == null ? "ID" : Request["sort"];
string order = Request["order"] == null ? "asc" : Request["order"];
//首先获取前台传递过来的参数
int pageindex = Request["page"] == null ? 1 : Convert.ToInt32(Request["page"]);
int pagesize = Request["rows"] == null ? 10 : Convert.ToInt32(Request["rows"]);
string userid = Request["accountid"] == null ? "" : Request["accountid"];
string username = Request["username"] == null ? "" : Request["username"];
string isable = Request["isable"] == null ? "" : Request["isable"];
string ifchangepwd = Request["ifchangepwd"] == null ? "" : Request["ifchangepwd"];
string userperson = Request["userperson"] == null ? "" : Request["userperson"];
string adddatestart = Request["adddatestart"] == null ? "" : Request["adddatestart"];
string adddateend = Request["adddateend"] == null ? "" : Request["adddateend"];
if (userid.Trim() != "" && !SqlInjection.GetString(userid)) //防止sql注入
{
strWhere += string.Format(" and AccountName like '%{0}%'", userid.Trim());
}
if (username.Trim() != "" && !SqlInjection.GetString(username))
{
strWhere += string.Format(" and RealName like '%{0}%'", username.Trim());
}
if (isable.Trim() != "select" && isable.Trim() != "")
{
strWhere += " and IsAble = '" + isable.Trim() + "'";
}
if (ifchangepwd.Trim() != "select" && ifchangepwd.Trim() != "")
{
strWhere += " and IfChangePwd = '" + ifchangepwd.Trim() + "'";
}
if (adddatestart.Trim() != "")
{
strWhere += " and CreateTime > '" + adddatestart.Trim() + "'";
}
if (adddateend.Trim() != "")
{
strWhere += " and CreateTime < '" + adddateend.Trim() + "'";
}
int totalCount; //输出参数
string strJson = new UserBLL().GetPager("tbUser", "ID,AccountName,[Password],RealName,MobilePhone,Email,IsAble,IfChangePwd,[Description],CreateTime,CreateBy,UpdateTime,UpdateBy", sort + " " + order, pagesize, pageindex, strWhere, out totalCount);
var jsonResult = new { total = totalCount.ToString(), rows = strJson };
return(Content("{\"total\": " + totalCount.ToString() + ",\"rows\":" + strJson + "}"));
}
// <summary>
// select book from the database
// </summary>
public static List <String> SelectBookById(String id)
{
// Initial vars
var list = new List <String>();
// MySQL query
const string result = "SELECT titel, auteur, genre, isbn, verdieping, rek, amount " +
"FROM meok2_bibliotheek_boeken " +
"WHERE id = ?";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var showresult = new MySqlCommand(result, empConnection))
{
// Bind parameters
showresult.Parameters.Add("id", MySqlDbType.VarChar).Value = SqlInjection.SafeSqlLiteral(id);
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
using (var myDataReader = showresult.ExecuteReader(CommandBehavior.CloseConnection))
{
while (myDataReader.Read())
{
// Save the values
list.Add(SqlInjection.SafeSqlLiteralRevert(myDataReader.GetString(0)));
list.Add(SqlInjection.SafeSqlLiteralRevert(myDataReader.GetString(1)));
list.Add(myDataReader.GetString(2));
list.Add(SqlInjection.SafeSqlLiteralRevert(myDataReader.GetString(3)));
list.Add(myDataReader.GetString(4));
list.Add(myDataReader.GetString(5));
list.Add(myDataReader.GetString(6));
}
}
}
catch (MySqlException)
{
// MySqlException bail out
}
finally
{
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
return(list);
}
/// <summary>
/// 获取部门下的用户（分页）
/// </summary>
public string GetPagerDepartmentUser(string departmentIds, string order, int pageSize, int pageIndex)
{
if (SqlInjection.GetString(departmentIds)) //简单sql防注入
{
departmentIds = "";
}
if (SqlInjection.GetString(order))
{
order = "CreateTime asc";
}
int totalCount = dal.GetDepartmentUserCount(departmentIds);
DataTable dt = dal.GetPagerDepartmentUser(departmentIds, order, pageSize, pageIndex);
string strjson = JsonHelper.ToJson(dt);
return("{\"total\": " + totalCount.ToString() + ",\"rows\":" + strjson + "}");
}
public ActionResult GetAllFieldsInfo()
{
string strWhere = "1=1";
string sort = Request["sort"] == null ? "Id" : Request["sort"];
string order = Request["order"] == null ? "asc" : Request["order"];
if (!string.IsNullOrEmpty(Request["FieldName"]) && !SqlInjection.GetString(Request["FieldName"]))
{
strWhere += " and FieldName like '%" + Request["FieldName"] + "%'";
}
if (!string.IsNullOrEmpty(Request["FieldViewName"]) && !SqlInjection.GetString(Request["FieldViewName"]))
{
strWhere += " and FieldViewName like '%" + Request["FieldViewName"] + "%'";
}
if (!string.IsNullOrEmpty(Request["SelTabId"]))
{
strWhere += " and TabId = '" + Request["SelTabId"] + "'";
}
//首先获取前台传递过来的参数
int pageindex = Request["page"] == null ? 1 : Convert.ToInt32(Request["page"]);
int pagesize = Request["rows"] == null ? 10 : Convert.ToInt32(Request["rows"]);
int totalCount = 0;
string strJson = ""; //输出结果
if (order.IndexOf(',') != -1) //如果有","就是多列排序（不能拿列判断，列名中间可能有","符号）
{
//多列排序：
//sort：ParentId,Sort,AddDate
//order：asc,desc,asc
string sortMulti = ""; //拼接排序条件，例：TabId desc,Sort asc
string[] sortArray = sort.Split(','); //列名中间有","符号，这里也要出错。正常不会有
string[] orderArray = order.Split(',');
for (int i = 0; i < sortArray.Length; i++)
{
sortMulti += sortArray[i] + " " + orderArray[i] + ",";
}
strJson = new FieldsBLL().GetPager("vw_Fields", "Id,TabId,FieldName,FieldViewName,FieldDataTypeId,IsActive,IsSearch,Sort,CreateTime,CreateBy,UpdateTime,UpdateBy,DataType,DataTypeName,TabName,TabViewName", sortMulti.Trim(','), pagesize, pageindex, strWhere, out totalCount);
}
else
{
strJson = new FieldsBLL().GetPager("vw_Fields", "Id,TabId,FieldName,FieldViewName,FieldDataTypeId,IsActive,IsSearch,Sort,CreateTime,CreateBy,UpdateTime,UpdateBy,DataType,DataTypeName,TabName,TabViewName", sort + " " + order, pagesize, pageindex, strWhere, out totalCount);
}
var jsonResult = new { total = totalCount.ToString(), rows = strJson };
return(Content("{\"total\": " + totalCount.ToString() + ",\"rows\":" + strJson + "}"));
}
public OrmLitePageResult <Ad> SelectList(string title, int?adTypeId, DateTime?start, DateTime?end,
int pageIndex, int pageSize)
{
StringBuilder sb = new StringBuilder();
var ps = DictBuilder.New();
sb.Append("1=1");
var orderBy = "id desc";
title = SqlInjection.Filter(title);
if (!string.IsNullOrEmpty(title))
{
sb.AppendFormat(" and Title like '%{0}%'", title);
}
if (adTypeId > 0)
{
orderBy = "OrderNum";
sb.AppendFormat(" and TypeId={0}", adTypeId);
}
if (start.HasValue)
{
sb.AppendFormat(" and EndTime>@start");
ps.EQ("start", start.Value);
}
if (end.HasValue)
{
sb.Append(" and EndTime<@end");
ps.EQ("end", end.Value.AddDays(1));
}
var factor = new OrmLitePageFactor
{
Conditions = sb.ToString(),
PageIndex = pageIndex,
PageSize = pageSize,
OrderBy = orderBy,
Params = ps
};
return(_adDao.SelectPage(factor));
}
public ActionResult GetAllRequestionTypeInfo()
{
string strWhere = "1=1";
string sort = Request["sort"] == null ? "id" : Request["sort"];
string order = Request["order"] == null ? "asc" : Request["order"];
if (!string.IsNullOrEmpty(Request["FTypeName"]) && !SqlInjection.GetString(Request["FTypeName"]))
{
strWhere += " and ftypename like '%" + Request["FTypeName"] + "%'";
}
//首先获取前台传递过来的参数
int pageindex = Request["page"] == null ? 1 : Convert.ToInt32(Request["page"]);
int pagesize = Request["rows"] == null ? 10 : Convert.ToInt32(Request["rows"]);
int totalCount = 0; //输出参数
string strJson = new RequestionTypeBLL().GetPager("tbRequestionType", "[id],[ftypename],fsort,CreateTime,CreateBy,UpdateTime,UpdateBy", sort + " " + order, pagesize, pageindex, strWhere, out totalCount);
return(Content("{\"total\": " + totalCount.ToString() + ",\"rows\":" + strJson + "}"));
}
public ActionResult GetAllButtonInfo()
{
string strWhere = "1=1";
string sort = Request["sort"] == null ? "id" : Request["sort"];
string order = Request["order"] == null ? "asc" : Request["order"];
if (!string.IsNullOrEmpty(Request["FButtonName"]) && !SqlInjection.GetString(Request["FButtonName"]))
{
strWhere += " and Name like '%" + Request["FButtonName"] + "%'";
}
//首先获取前台传递过来的参数
int pageindex = Request["page"] == null ? 1 : Convert.ToInt32(Request["page"]);
int pagesize = Request["rows"] == null ? 10 : Convert.ToInt32(Request["rows"]);
int totalCount = 0; //输出参数
string strJson = JsonHelper.ToJson(SqlPagerHelper.GetPager("tbButton", "Id,Name,Code,Icon,Sort,Description,CreateTime,CreateBy,UpdateTime,UpdateBy", sort + " " + order, pagesize, pageindex, strWhere, out totalCount));
return(Content("{\"total\": " + totalCount.ToString() + ",\"rows\":" + strJson + "}"));
}
public OrmLitePageResult <SystemRes> SelectList(string name, int pageIndex, int pageSize)
{
name = SqlInjection.Filter(name);
var condition = "";
if (!string.IsNullOrEmpty(name))
{
condition = " Name LIKE '%" + name + "%'";
}
return(SelectPage(new OrmLitePageFactor
{
Conditions = condition,
OrderBy = "ID asc",
PageIndex = pageIndex,
PageSize = pageSize,
}));
}
