由於我們的網站放了防止SQL注入代碼，現在只要一出現例如 「or select 」的字元就報錯。這樣給客戶帶來不友好的影響。
帶參數 連接資料庫 執行SQL語句 或者預存程序
C# code
private DataTable ExecuteDataTable(string SqlStr, Hashtable SqlParameters,CommandType temType)
{
String getConnectionString = "Application Name=sss;Initial Catalog=DEVDB;Data Source=10.3.1.218;User ID=sa;password=sa;Pooling=True";
SqlConnection sqlConn = new SqlConnection(getConnectionString);
SqlCommand sqlCmd = new SqlCommand(SqlStr);
SqlDataAdapter sqlDA =new SqlDataAdapter();
DataTable dtSql = new DataTable();
try
{
sqlConn.Open();
sqlCmd.Connection = sqlConn;
sqlCmd.CommandType = temType;
if (SqlParameters != null)
{
IDictionaryEnumerator hsEnum = SqlParameters.GetEnumerator();
while (hsEnum.MoveNext())
{
sqlCmd.Parameters.AddWithValue(hsEnum.Key.ToString(), hsEnum.Value);
}
}
sqlDA.SelectCommand = sqlCmd;
sqlDA.Fill(dtSql);
return dtSql;
}
catch (Exception exExact)
{
string error = exExact.Message;
throw new Exception(error, exExact);
}
finally
{
sqlConn.Close();
}
}
protected void Button2_Click(object sender, EventArgs e)
{
Hashtable htParam = new Hashtable();
htParam.Add("@Language", "Chi");
htParam.Add("@CurrencyCode", "RMB");
htParam.Add("@CurrencyUnit", "1.0");
htParam.Add("@Region", "42");
string sqlstr = "spr_Channellist";
DataTable mytable = ExecuteDataTable(sqlstr, htParam, CommandType.StoredProcedure);
this.GridView1.DataSource = mytable;
GridView1.DataBind();
}
private String ExecuteDataValue(string SqlStr, Hashtable SqlParameters)
{
String getConnectionString = "Application Name=IPTV;Initial Catalog=IPTVDEVDB;Data Source=10.3.1.218;User ID=sa;password=sa;Pooling=True";
SqlConnection sqlConn = new SqlConnection(getConnectionString);
SqlCommand sqlCmd = new SqlCommand(SqlStr);
string strRtrn ;
try
{
sqlConn.Open();
sqlCmd.Connection = sqlConn;
sqlCmd.CommandType = CommandType.Text;
if(SqlParameters != null)
{
IDictionaryEnumerator hsEnum = SqlParameters.GetEnumerator();
while(hsEnum.MoveNext())
{
sqlCmd.Parameters.AddWithValue(hsEnum.Key.ToString(), hsEnum.Value);
}
}
strRtrn = Convert.ToString(sqlCmd.ExecuteScalar());
return strRtrn;
}
catch(Exception exExact)
{
string error = exExact.Message;
throw new Exception(error, exExact);
}
finally
{
sqlConn.Close();
}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
OleDbConnection conn = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=" + Server.MapPath("") + "CODEDB.mdb");
string sql = "select * from Code ";
OleDbDataAdapter oda = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet();
oda.Fill(ds);
this.GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
private bool ProcessSqlStr(string Str)
{
bool ReturnValue = true;
try
{
if (Str.Trim() != "")
{
string SqlStr = "and |exec |insert |select |delete |update |count |* |chr |mid |master |truncate |char |declare";
string[] anySqlStr = SqlStr.Split('|');
foreach (string ss in anySqlStr)
{
if (Str.ToLower().IndexOf(ss) >= 0)
{
ReturnValue = false;
break;
}
}
}
}
catch
{
ReturnValue = false;
}
return ReturnValue;
}
網站本身用的是預存程序
public bool InsertAdmin(string userName, string password, string remark, string mail, int departId, int power)
{
string sql = "insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(:UserName,:Password,:Remark,:Mail,:DepartId,:Power)";
OracleConnection connection = new OracleConnection();
connection.ConnectionString = "";//此處設置連結字串
OracleCommand command = new OracleCommand(sql, connection);
command.Parameters.Add(":UserName", OracleType.NVarChar, 60).Value = userName;
command.Parameters.Add(":Password", OracleType.NVarChar, 60).Value =password;
command.Parameters.Add(":Remark", OracleType.NVarChar, 60).Value = remark;
command.Parameters.Add(":Mail", OracleType.NVarChar, 60).Value =mail;
command.Parameters.Add(":DepartId", OracleType.Int32, 4).Value =departId;
command.Parameters.Add(":Power", OracleType.Int32, 4).Value = power;
connection.Open();
int rowsAffected=command.ExecuteNonQuery();
connection.Close();
command.Dispose();
return rowsAffected > 0;
}
}
全站熱搜
留言列表
