最近因項目需要用ACCESS做數據庫開發WEB項目 看論壇上還許多人問及ACCESS被注入的安全問題 許多人解決的方法仍然是用Replace替換特殊字符，然而這樣做也并沒有起到太大做用 今天我就把我用ACCESS參數化查詢的一些方法和經驗和大家分享 希望對大家有所啟發，有寫的不對的地方希望高手們多多指教ASP.NET 用OleDbCommand的new OleDbParameter創建參數貨查詢 ASP用Command的CreateParameter 方法創建參數化查詢 (SQL儲存過程查詢也是用這個方法建立的)ASP.NET C#語法OleDbParameter parm = new OleDbParameter(Name, Type, Direction, Size, Value); (實際上它有七重載大家具體大家可以在VS.net里面就可以看到) 參數 Name 可選，字符串，代表 Parameter 對象名稱。 Type 可選，長整型值，指定 Parameter 對象數據類型。 Direction 可選，長整型值，指定 Parameter 對象類型。。 Size 可選，長整型值，指定參數值最大長度（以字符或字節數為單位）。 Value 可選，變體型，指定 Parameter 對象的值。 以下是實例，查詢news表中所有tsing發表的新聞 ------------------------------------------------------- sql="select * from newss where username=? order by id" //注意查詢的條件均用?號表示 OleDbConnection conn = new OleDbConnection(connString); OleDbCommand cmd = new OleDbCommand(sql,conn); OleDbParameter parm = new OleDbParameter("temp",OleDbType.VarChar, 50); //temp為Parameter對象可隨便定義,OleDbType.VarChar指定為字符串,長度50 parm.Direction = ParameterDirection.Input; //指定其類型輸入參數 cmd.Parameters.Add(parm); cmd.Parameters["temp"].Value = "tsing"; //查詢tsing,也可以寫成cmd.Parameters[0] conn.Open(); cmd.ExecuteReader();ASP VBSCRIPT語法Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value) 參數同上 以下是實例，查詢news表中所有tsing發表的新聞 ------------------------------------------------------ et conn = Server.CreateObject("Adodb.Connection") conn.ConnectionString = connString conn.open() set mycmd = Server.CreateObject("ADODB.Command") mycmd.ActiveConnection=conn mycmd.CommandText=sql mycmd.Prepared = true set mypar = mycmd.CreateParameter("temp",129,1,50,"tsing") mycmd.Parameters.Append mypar set myrs = mycmd.Execute與上面基本相同不同的地方法是asp在對參數的表達上面不同 129為adChar，1就是指示輸入參數(是其實是默認值) 大家請參閱MICROSOFT的ADOVB.Inc： '---- ParameterDirectionEnum Values ---- Const adParamUnknown = 0 Const adParamInput = 1 Const adParamOutput = 2 Const adParamInputOutput = 3 Const adParamReturnValue = 4 '---- DataTypeEnum Values ---- Const adEmpty = 0 Const adTinyInt = 16 Const adSmallInt = 2 Const adInteger = 3 Const adBigInt = 20 Const adUnsignedTinyInt = 17 Const adUnsignedSmallInt = 18 Const adUnsignedInt = 19 Const adUnsignedBigInt = 21 Const adSingle = 4 Const adDouble = 5 Const adCurrency = 6 Const adDecimal = 14 Const adNumeric = 131 Const adBoolean = 11 Const adError = 10 Const adUserDefined = 132 Const adVariant = 12 Const adIDispatch = 9 Const adIUnknown = 13 Const adGUID = 72 Const adDate = 7 Const adDBDate = 133 Const adDBTime = 134 Const adDBTimeStamp = 135 Const adBSTR = 8 Const adChar = 129 Const adVarChar = 200 Const adLongVarChar = 201 Const adWChar = 130 Const adVarWChar = 202 Const adLongVarWChar = 203 Const adBinary = 128 Const adVarBinary = 204 Const adLongVarBinary = 205 附我寫的C#類，和VBSCRIPT函數，希望對大家有幫助using System; using System.Data; using System.Configuration; using System.Web; using System.Data.OleDb; namespace acc_select { /// summary /// accselect 的摘要說明 /// /summary html' target='_blank'>publicclass accselect { //"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:/dq/db1.mdb" private string conn = ConfigurationManager.ConnectionStrings["tsingConnectionString"].ToString(); public string sql = string.Empty; public int t = 4; public object v = null; public accselect() { } /// summary /// 構造函數，傳遞ACC參數查詢語句 /// /summary /// param name="strsql" strsql字符型 /param public accselect(string strsql) { sql = strsql; } /// summary /// 構造函數，傳遞ACC參數查詢語句 /// /summary /// param name="strsql" 參數查詢語句 /param /// param name="total" 字節數 /param public accselect(string strsql, int total) { sql = strsql; t = total; } /// summary /// 構造函數 /// /summary /// param name="strsql" 參數查詢語句 /param /// param name="total" 字節數 /param /// param name="value" OBJECT值 /param public accselect(string strsql, int total, object value) { sql = strsql; t = total; v = value; } /// summary /// getOdd方法返回OleDbDataReader /// /summary /// param name="odt" 定義OleDbType類型 /param /// returns /returns public OleDbDataReader getOdd(OleDbType odt) { OleDbConnection conns = new OleDbConnection(this.conn); OleDbCommand cmd = new OleDbCommand(this.sql, conns); OleDbParameter parm = new OleDbParameter("temp", odt, this.t); parm.Direction = ParameterDirection.Input; cmd.Parameters.Add(parm); cmd.Parameters[0].Value = this.v; conns.Open(); OleDbDataReader oda = cmd.ExecuteReader(); cmd.Dispose(); return oda; } string Sql { get { return sql; } set { sql = value; } } int T { get { return t; } set { t = value; } } object V { get { return v; } set { v = value; } } } } //調用方法 //accselect acc = new accselect(); //acc.sql = "select * from dtt where d_id=?"; //acc.t = 10; //acc.v = 1; //OleDbDataReader oda = acc.getOdd(OleDbType.VarChar); //Repeater1.DataSource = oda; //Repeater1.DataBind();function acc_sql(sql,adotype,adodct,strlong,values) dim connstring,mycmd,myrs,conn
connString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" Server.MapPath("db1.mdb") set conn = Server.CreateObject("Adodb.Connection") conn.ConnectionString = connString conn.open() set mycmd = Server.CreateObject("ADODB.Command") mycmd.ActiveConnection=conn mycmd.CommandText=sql mycmd.Prepared = true set mypar = mycmd.CreateParameter("temp",adotype,adodct,strlong,values) mycmd.Parameters.Append mypar set myrs = mycmd.Execute set acc_sql=myrs end function '調用方法 'dim rs 'sql="select * from users where id=? order by id" 'set rs=acc_sql(sql,3,1,4,1) 'if not rs.eof then 'response.Write(rs(1)) 'end if 本文作者:html教程
