ado的mysql参数化_ADO参数化（VBA）

ADO参数化的好处

避免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参数已经用于存储过程内部的事务；二是输出参数不用赋值。