ADO参数化（VBA）

ADO参数化的好处

1. 避免SQL注入攻击；2. 省去SQL字符串拼接；3. 避免出错

方式一

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 "......"   '数据库连接语句

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") '参数值   
  
Set con = New ADODB.Connection  
con.CursorLocation = adUseClient '客户端游标 
con.Open "....."   '连接数据库   
  
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

这个方法同样也可以用于sql数据库的存储过程，只是要注意二点：一是存储过程的用户第一个参数的序号是1，序号0参数已经用于存储过程内部的事务；二是输出参数不用赋值。