VBA执行SQL Insert语句时如何转义清洗单引号等特殊字符

解决方案 #

方案1：优先使用参数化查询（推荐） #

这是最安全、不需要手动处理任何转义字符的方案，同时可以彻底避免SQL注入风险。VBA中结合ADO使用参数化查询的逻辑示例如下：

' 需先引用Microsoft ActiveX Data Objects x.x Library
Sub InsertWithParams(arrHeaders, arrValues, tableIndex As enumTables)
    Dim conn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim TableName As String
    Dim i As Integer
    
    TableName = getTableName(tableIndex)
    conn.Open "你的数据库连接字符串"
    Set cmd.ActiveConnection = conn
    
    ' 拼接参数占位的SQL语句
    Dim placeholders As Variant
    ReDim placeholders(LBound(arrValues) To UBound(arrValues))
    For i = LBound(placeholders) To UBound(placeholders)
        placeholders(i) = "?"
    Next
    cmd.CommandText = "INSERT INTO " & TableName & " ([" & Join(arrHeaders, "],[") & "]) VALUES (" & _
        Join(placeholders, ",") & ")"
    
    ' 逐个绑定参数
    For i = LBound(arrValues) To UBound(arrValues)
        cmd.Parameters.Append cmd.CreateParameter(, adVarChar, adParamInput, 255, arrValues(i))
    Next
    
    cmd.Execute
    conn.Close
End Sub

方案2：拼接SQL前转义特殊字符 #

如果必须维持现有拼接SQL的逻辑，可以先对所有插入值做转义处理，核心规则是把值内的单个单引号替换为两个连续的单引号，你可以修改原函数如下：

Function getSqlInsertIntoQuery(arrHeaders, arrValues, tableIndex As enumTables)
    Dim dHeadersAndValues As New Scripting.Dictionary
    Dim TableName As String
    Dim escapedVals As Variant
    Dim i As Long
    
    TableName = getTableName(tableIndex)
    ' 转义所有值里的单引号
    ReDim escapedVals(LBound(arrValues) To UBound(arrValues))
    For i = LBound(arrValues) To UBound(arrValues)
        escapedVals(i) = Replace(CStr(arrValues(i)), "'", "''")
    Next
    
    getSqlInsertIntoQuery = "INSERT INTO " & TableName & " ( [" & _
         Join(arrHeaders, "]," & vbNewLine & "[") & "])" & _
        " VALUES( " & Chr(39) & Join(escapedVals, Chr(39) & "," & Chr(39)) & Chr(39) & ") ;"

    Debug.Print getSqlInsertIntoQuery
End Function

常见需要转义的SQL特殊字符清单 #

• 所有关系型数据库通用：单引号' 转义为 ''
• MySQL（非ANSI_QUOTES模式）：额外需要转义反斜杠\为\\
• 仅LIKE查询场景需要额外转义：百分号%、下划线_，INSERT/UPDATE等写入场景无需处理
• Access数据库特殊：日期类型值前后的井号#属于语法标识，文本类型值中出现的#无需转义

内容的提问来源于stack exchange，提问作者Dumitru Daniel