在Access SQL中转义单引号

Question

我正在尝试使用以下代码在VBA中进行域名查找：

DLookup("island", "villages", "village = '" & txtVillage & "'")

当txtVillage为"Dillon's Bay"这样带有撇号的字符串时，程序会将撇号解释为单引号导致运行时错误。

我写了一个简单的函数来转义单引号——它将"'"替换为"''"。这似乎是一个经常出现的问题，但我找不到任何内置函数来完成相同的功能。我错过了什么吗？

- inglesp

Answer 1

"Replace" 函数应该可以解决问题。根据您上面的代码：

DLookup("island", "villages", "village = '" & Replace(txtVillage, "'", "''") & "'")

- Matt

---

请注意，在较旧版本的Access（即Access 97）中似乎不存在Replace()函数。 - apenwarr

Replace() 函数是在 Access 2000 中引入的。 - David-W-Fenton

@David W. Fenton：只是加一条注释来澄清您对Access2000的使用，如果可以的话 :) Replace()函数是在VBA6中引入的。 VBA6从Access2000开始在Access UI中引入。 Access数据库引擎SQL不支持“replace”函数，无论是本地还是通过Jet Expression Services。仅当使用Access UI时，才能在SQL中使用Replace()（尽管我不知道如何使用），否则它将显示语法错误。 - onedaywhen

因此，在持久化数据库对象（如查询/视图/过程）以及特别是验证规则和CHECK约束中，最好避免使用Replace()。 - onedaywhen

Answer 2

情况比你想象的更糟。如果有人输入了这样的值，而您没有转义任何内容，请考虑会发生什么：

'); DROP TABLE [YourTable]

不太美观。

没有内置函数可以简单地转义撇号的原因是正确处理此问题的方法是使用查询参数。对于Ole/Access风格的查询，您应将其设置为查询字符串：

DLookup("island", "village", "village = ? ")

然后单独设置参数。我不知道您如何从vba设置参数值。

- Joel Coehoorn

---

你如何单独设置参数？ - StockB

使用预处理语句。请参见 https://dev59.com/CWw15IYBdhLWcg3wfr9y 或 http://technet.microsoft.com/en-us/library/aa905910(v=sql.80).aspx。该文章是关于 SQL Server 的，但我怀疑它也适用于 Access。 - Trevor Dixon

Access不允许在一个语句中进行连续的SQL操作，因此SQL注入更难实现，但并非不可能。只是注入必须导致单个有效的SQL操作。而域聚合函数不能改变存储的数据。 - June7

Answer 3

尽管像DLookup这样的速记域函数很诱人，但它们也有缺点。等效的Jet SQL大致如下：

SELECT FIRST(island)
FROM villages
WHERE village = ?;

如果有多个匹配的候选者，它会选择“第一个”，“第一个”的定义取决于实现（SQL引擎），对于Jet/ACE引擎未定义。您知道哪一个会是第一个吗？如果不知道，请避免使用DLookup :)

[有趣的是，对于Jet/ACE，答案将是基于最后一次压缩数据库文件时聚簇索引的最小值，或者如果数据库从未被压缩，则是第一个（有效时间）插入的值。聚簇索引又由PRIAMRY KEY确定，否则在NOT NULL列上定义的UNIQUE约束或索引，否则为第一个（有效时间）插入的行。如果在NOT NULL列上定义了多个UNIQUE约束或索引，哪一个将用于聚类？我不知道！我相信您明白，“第一个”很难确定，即使您知道如何确定！]

我还看到Microsoft建议从优化角度避免使用域聚合函数：

访问数据库中有关查询性能的信息 http://support.microsoft.com/kb/209126 避免使用域聚合函数，例如DLookup函数...Jet数据库引擎无法优化使用域聚合函数的查询。

如果您选择重新编写，可以利用PARAMETERS语法，或者您可能更喜欢Jet 4.0/ACE PROCEDURE语法，例如：

CREATE PROCEDURE GetUniqueIslandName
(
   :village_name VARCHAR(60)
)
AS 
SELECT V1.island_name
  FROM Villages AS V1
 WHERE V1.village_name = :village_name
       AND EXISTS 
       (
        SELECT V2.village_name
          FROM Villages AS V2
         WHERE V2.village_name = V1.village_name
         GROUP 
            BY V2.village_name
        HAVING COUNT(*) = 1
       );

这样，您就可以使用引擎自身的功能 - 或者至少是其数据提供者的功能 - 以必要时转义所有字符（而不仅仅是双引号和单引号）。

- onedaywhen

Answer 4

像Joel Coehoorn建议的那样，参数化查询是正确的方法，而不是在查询字符串中进行连接。首先-避免了某些安全风险，其次-我相当确定它会将转义交给引擎自己处理，您不必担心这个问题。

- Emil

Answer 5

但是，正确的格式应该是这样的（每个双引号后面再加一个双引号）：

sSQL = "SELECT * FROM tblTranslation WHERE fldEnglish=""" & myString & """;"

或者我更喜欢的方法是：

制作一个转义单引号的函数，因为使用“[]”进行“转义”不允许在字符串中包含这些字符...

Public Function fncSQLStr(varStr As Variant) As String

If IsNull(varStr) Then
        fncSQLStr = ""
    Else
        fncSQLStr = Replace(Trim(varStr), "'", "''")
    End If

End Function

我在所有的SQL查询中使用这个函数，如SELECT、INSERT和UPDATE（以及WHERE子句中...）

strSQL = "INSERT INTO tbl" & 
    " (fld1, fld2)" & _
    " VALUES ('" & fncSQLStr(str1) & "', '" & fncSQLStr(Me.tfFld2.Value) & "');"

或者

strSQL = "UPDATE tbl" & _
    " SET fld1='" & fncSQLStr(str1) & "', fld2='" & fncSQLStr(Me.tfFld2.Value) & "'" & _
    " WHERE fld3='" & fncSQLStr(str3) & "';"

- user2497464

---

不，不，不，这完全是错的。 - Joel Coehoorn

Answer 6

我相信Access可以使用Chr$(34)，并且可以快乐地在其中包含单引号/撇号。
例如

DLookup("island", "villages", "village = " & chr$(34) & nonEscapedString & chr$(34))

虽然这样你就必须转义 chr$(34) (")

你可以使用 Replace 函数。

Dim escapedString as String

escapedString = Replace(nonescapedString, "'", "''")

- rob_g

Answer 7

在可能包含撇号的条件周围加上括号。

类似这样：

DLookup("island", "villages", "village = '[" & txtVillage & "]'")

它们可能需要在单引号外面或者只是在txtVillage周围，比如：

DLookup("island", "villages", "village = '" & [txtVillage] & "'")

但是如果你找到了正确的组合，它会处理撇号。

Keith B

- keith b

---

括号定义对象，而不是字符串输入。测试失败了。这不是一个有效的答案。 - June7

Answer 8

对于那些在单引号和替换函数方面遇到麻烦的人，这行代码可能会让你的一天变得更美好 ^o^

Replace(result, "'", "''", , , vbBinaryCompare)

- niceboomer

Answer 9

顺便说一下，这是我的EscapeQuotes函数

Public Function EscapeQuotes(s As String) As String

    If s = "" Then
        EscapeQuotes = ""
    ElseIf Left(s, 1) = "'" Then
        EscapeQuotes = "''" & EscapeQuotes(Mid(s, 2))
    Else
        EscapeQuotes = Left(s, 1) & EscapeQuotes(Mid(s, 2))
    End If

End Function

- inglesp

---

那正是我真正想要的。我不常使用VBA，而且我也不觉得帮助文件有太大帮助！干杯。 - inglesp

Answer 10

我的解决方案更简单。最初，我使用了这个SQL表达式来创建一个ADO记录集：

Dim sSQL as String
sSQL="SELECT * FROM tblTranslation WHERE fldEnglish='" & myString & "';"

当myString中有撇号时，比如Int'l Electrics，我的程序会停止。使用双引号解决了这个问题。

sSQL="SELECT * FROM tblTranslation WHERE fldEnglish="" & myString & "";"

- dubi

---

好的，所以现在你不是遇到了一个省略号问题，而是遇到了一个双引号问题 :-/ - Sascha L.