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

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈