软件下载 | 资讯教程 | 最近更新 | 下载排行 | 一键转帖 | 发布投稿
您的位置:最火下载站 > 网络编程 > 编程综合 > Access sql语句构建(规避单引号)

Access sql语句构建(规避单引号)

简单的一个执行Update的语句,条件确定,当然这样很傻,尚待提高,代码如下:

public bool ExeHashTableSqlstr(Hashtable ht, string TableName, string where)
{
List<OleDbParameter> ParList = new List<OleDbParameter>();
foreach (DictionaryEntry item in ht)//key 是字段名 value是当前值,对应属性里有各自的数据类型
{
ParList.Add(new OleDbParameter("@" + item.Key.ToString(), item.Value));
}
#region 构建sqlStr
string contents = " ";
Int16 count = 0;
foreach (OleDbParameter item in ParList)
{
contents += item.ParameterName.Substring(1);
contents += "=";
contents += item.ParameterName;
count++;
if (count != ht.Count)
{
contents += ", ";
}
}
string sqlString = "UPDATE " + TableName + " SET " + contents;
if (where != "")
{
sqlString += " where " + where + ";";
}
else
{
sqlString += ";";
}
#endregion
Open();
OleDbCommand cmd = new OleDbCommand(sqlString, mConn);
foreach (OleDbParameter par in ParList)
{
cmd.Parameters.Add(par);
}
if (cmd.ExecuteNonQuery() >= 1)
{
Close();
return true;
}
else
{
Close();
return false;
}
}

这里有两个有意思的地方,

用OleDbParameter和OleDbCommand 两个对象来调用sql语句,不需要对任何字符串的sql语句预处理,如包含单引号,换行符等。

参考以下:OleDbParameter参数的使用

运用参数可防止一些sql攻击

public bool judIDPW(string CustomerName, string CustomerPassword)
{
OleDbParameter par1 = new OleDbParameter();
par1.ParameterName = "@CustomerName";
par1.Value = CustomerName;
OleDbParameter par2 = new OleDbParameter();
par2.ParameterName = "@CustomerPassword";
par2.Value =Security.Encrypt(CustomerPassword);

OleDbCommand cmd = new OleDbCommand("select CustomerID from Customers where CustomerName=@Customer and CustomerPassword=@CustomerPassword", con);
cmd.Parameters.Add(par1);
cmd.Parameters.Add(par2);
con.Open();
OleDbDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
con.Close();
return true;
}
else
{
con.Close();
return false;
}
}

使用用OleDbParameter(String, Object)的技巧:

List<OleDbParameter> ParList = new List<OleDbParameter>();
foreach (DictionaryEntry item in ht)//key 是字段名 value是当前值,对应属性里有各自的数据类型
{
ParList.Add(new OleDbParameter("@" + item.Key.ToString(), item.Value));
}

public bool ExeHashTableSqlstr(Hashtable ht, string TableName, string where)
{
List<OleDbParameter> ParList = new List<OleDbParameter>();
foreach (DictionaryEntry item in ht)//key 是字段名 value是当前值,对应属性里有各自的数据类型
{
ParList.Add(new OleDbParameter("@" + item.Key.ToString(), item.Value));
}
#region 构建sqlStr
string contents = " ";
Int16 count = 0;
foreach (OleDbParameter item in ParList)
{
contents += item.ParameterName.Substring(1);
contents += "=";
contents += item.ParameterName;
count++;
if (count != ht.Count)
{
contents += ", ";
}
}
string sqlString = "UPDATE " + TableName + " SET " + contents;
if (where != "")
{
sqlString += " where " + where + ";";
}
else
{
sqlString += ";";
}
#endregion
Open();
OleDbCommand cmd = new OleDbCommand(sqlString, mConn);
foreach (OleDbParameter par in ParList)
{
cmd.Parameters.Add(par);
}
if (cmd.ExecuteNonQuery() >= 1)
{
Close();
return true;
}
else
{
Close();
return false;
}
}

    相关阅读
    网友评论
    栏目导航
    推荐软件