ADO參數(shù)化的好處
- 避免SQL注入攻擊反浓；2. 省去SQL字符串拼接赞哗；3. 避免出錯(cuò)
方式一
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Dim Sql As String
sql = "Select * From tb Where userName = ? And age = ? "
Set cn = New ADODB.Connection
cn.Open "......" '數(shù)據(jù)庫(kù)連接語(yǔ)句
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = sql
Set param = .CreateParameter("?", adVarchar, adParamInput, 20, txtName.Text)
.Parameters.Append param
Set param = .CreateParameter("?", adInteger, adParamInput, 4, txtAge.Text)
.Parameters.Append param
Set rs = .Execute
End With
'.......
Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing
方式二
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim Sql As String
Dim arrValue
Dim i As Long
Sql = "INSERT INTO tb(descr,qty,dt) VALUES(?,?,?)"
arrValue = Array("Test", 152, "2010-11-28") '參數(shù)值
Set con = New ADODB.Connection
con.CursorLocation = adUseClient '客戶端游標(biāo)
con.Open "....." '連接數(shù)據(jù)庫(kù)
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = con
.CommandText = Sql
.CommandType = adCmdText
For i = 0 To .Parameters.Count - 1
.Parameters(i) = arrValue(i)
Next
.Execute
End With
Set cmd = Nothing
Set con = Nothing
這個(gè)方法同樣也可以用于sql數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程懈玻，只是要注意二點(diǎn)：一是存儲(chǔ)過(guò)程的用戶第一個(gè)參數(shù)的序號(hào)是1乾颁，序號(hào)0參數(shù)已經(jīng)用于存儲(chǔ)過(guò)程內(nèi)部的事務(wù)；二是輸出參數(shù)不用賦值英岭。
