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;
}
}