在 Access SQL 中转义 '
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/199889/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Escaping ' in Access SQL
提问by inglesp
I'm trying to do a domain lookup in vba with something like this:
我正在尝试使用以下内容在 vba 中进行域查找：
DLookup("island", "villages", "village = '" & txtVillage & "'")
This works fine until txtVillage is something like Dillon's Bay, when the apostrophe is taken to be a single quote, and I get a run-time error.
这工作正常，直到 txtVillage 类似于 Dillon's Bay，当撇号被视为单引号时，我收到运行时错误。
I've written a trivial function that escapes single quotes - it replaces "'" with "''". This seems to be something that comes up fairly often, but I can't find any reference to a built-in function that does the same. Have I missed something?
我写了一个简单的函数来转义单引号——它将“'”替换为“''”。这似乎是经常出现的事情，但我找不到对执行相同操作的内置函数的任何引用。我错过了什么吗？
采纳答案by Matt
The "Replace" function should do the trick. Based on your code above:
“替换”功能应该可以解决问题。根据您上面的代码：
DLookup("island", "villages", "village = '" & Replace(txtVillage, "'", "''") & "'")
回答by Joel Coehoorn
It's worse than you think. Think about what would happen if someone entered a value like this, and you haven't escaped anything:
这比你想象的要糟糕。想想如果有人输入这样的值会发生什么，而你没有逃脱任何东西：
'); DROP TABLE [YourTable]
Not pretty.
不漂亮。
The reason there's no built in function to simply escape an apostrophe is because the correct way to handle this is to use query parameters. For an Ole/Access style query you'd set this as your query string:
没有内置函数来简单地转义撇号的原因是因为处理这个问题的正确方法是使用查询参数。对于 Ole/Access 样式查询，您可以将其设置为查询字符串：
DLookup("island", "village", "village = ? ")
And then set the parameter separately. I don't know how you go about setting the parameter value from vba, though.
然后单独设置参数。不过，我不知道您如何从 vba 设置参数值。
回答by onedaywhen
Though the shorthand domain functions such as DLookup are tempting, they have their disadvantages. The equivalent Jet SQL is something like
尽管 DLookup 等速记域函数很诱人，但它们也有其缺点。等效的 Jet SQL 类似于
SELECT FIRST(island)
FROM villages
WHERE village = ?;
If you have more than one matching candidate it will pick the 'first' one, the definition of 'first' is implementation (SQL engine) dependent and undefined for the Jet/ACE engine IIRC. Do you know which one would be first? If you don't then steer clear of DLookup :)
如果您有多个匹配的候选者，它将选择“第一个”，“第一个”的定义依赖于实现（SQL 引擎），并且对于 Jet/ACE 引擎 IIRC 未定义。你知道哪个会是第一个吗？如果不这样做，请避开 DLookup :)
[For interest, the answer for Jet/ACE will either be the minimum value based on the clusterd index at the time the database file was last compacted or the first (valid time) inserted value if the database has never been compacted. Clustered index is in turn determined by the PRIAMRY KEY if persent otherwise a UNIQUE constraint or index defined on NOT NULL columns, otherwise the first (valid time) inserted row. What if there is more than one UNIQUE constraint or index defined on NOT NULL columns, which one would be used for clustering? I've no idea! I trust you get the idea that 'first' is not easy to determine, even when you know how!]
[出于兴趣，Jet/ACE 的答案将是基于上次压缩数据库文件时的聚集索引的最小值，或者如果数据库从未被压缩，则是第一个（有效时间）插入的值。聚集索引依次由 PRIAMRY KEY 确定，否则为在 NOT NULL 列上定义的 UNIQUE 约束或索引，否则为第一个（有效时间）插入的行。如果在 NOT NULL 列上定义了多个 UNIQUE 约束或索引，哪一个将用于集群呢？我不知道！我相信你知道“第一”不容易确定，即使你知道怎么做！]
I've also seen advice from Microsoft to avoid using domain aggregate functions from an optimization point of view:
我还看到了 Microsoft 从优化的角度避免使用域聚合函数的建议：
Information about query performance in an Access database http://support.microsoft.com/kb/209126
有关 Access 数据库中查询性能的信息 http://support.microsoft.com/kb/209126
"Avoid using domain aggregate functions, such as the DLookup function... the Jet database engine cannot optimize queries that use domain aggregate functions"
“避免使用域聚合函数，例如 DLookup 函数……Jet 数据库引擎无法优化使用域聚合函数的查询”
If you choose to re-write using a query you can then take advantage of the PARAMETERS syntax, or you may prefer the Jet 4.0/ACE PROCEDURE syntax e.g. something like
如果您选择使用查询重写，则可以利用 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
);
This way you can use the engine's own functionality -- or at least that of its data providers -- to escape all characters (not merely double- and single quotes) as necessary.
通过这种方式，您可以根据需要使用引擎自己的功能——或者至少是它的数据提供者的功能——来转义所有字符（不仅仅是双引号和单引号）。
回答by Gnudiff
Parametrized queries such as Joel Coehoorn suggested are the way to go, instead of doing concatenation in query string. First - avoids certain security risks, second - I am reasonably certain it takes escaping into engine's own hands and you don't have to worry about that.
像 Joel Coehoorn 建议的参数化查询是要走的路，而不是在查询字符串中进行连接。首先 - 避免某些安全风险，其次 - 我有理由确定它需要逃到引擎自己的手中，你不必担心。
回答by Rob Gray
I believe access can use Chr$(34) and happily have single quotes/apostrophes inside.
eg
我相信访问可以使用 Chr$(34) 并且很高兴在里面有单引号/撇号。
例如
DLookup("island", "villages", "village = " & chr$(34) & nonEscapedString & chr$(34))
Though then you'd have to escape the chr$(34) (")
虽然那样你就必须逃避 chr$(34) (")
You can use the Replace function.
您可以使用替换功能。
Dim escapedString as String
escapedString = Replace(nonescapedString, "'", "''")
回答by Rob Gray
But then, it should be like this (with one more doublequote each):
但是，它应该是这样的（每个多一个双引号）：
sSQL = "SELECT * FROM tblTranslation WHERE fldEnglish=""" & myString & """;"
Or what I prefer:
或者我更喜欢的：
Make a function to escape single quotes, because "escaping" with "[]" would not allow these characters in your string...
制作一个函数来转义单引号，因为用“[]”“转义”将不允许您的字符串中出现这些字符......
Public Function fncSQLStr(varStr As Variant) As String
If IsNull(varStr) Then
fncSQLStr = ""
Else
fncSQLStr = Replace(Trim(varStr), "'", "''")
End If
End Function
I use this function for all my SQL-queries, like SELECT, INSERT and UPDATE (and in the WHERE clause as well...)
我将这个函数用于我所有的 SQL 查询，比如 SELECT、INSERT 和 UPDATE（以及 WHERE 子句中......）
strSQL = "INSERT INTO tbl" &
" (fld1, fld2)" & _
" VALUES ('" & fncSQLStr(str1) & "', '" & fncSQLStr(Me.tfFld2.Value) & "');"
or
或者
strSQL = "UPDATE tbl" & _
" SET fld1='" & fncSQLStr(str1) & "', fld2='" & fncSQLStr(Me.tfFld2.Value) & "'" & _
" WHERE fld3='" & fncSQLStr(str3) & "';"
回答by inglesp
By the way, here's my EscapeQuotes function
顺便说一句，这是我的 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
回答by niceboomer
For who having trouble with single quotation and Replace function, this line may save your day ^o^
对于那些在单引号和替换功能方面有问题的人，这一行可能会节省您的时间^o^
Replace(result, "'", "''", , , vbBinaryCompare)
回答by keith b
put brackets around the criteria that might have an apostrophe in it.
将括号括起来，其中可能有撇号的标准。
SOmething like:
就像是：
DLookup("island", "villages", "village = '[" & txtVillage & "]'")
They might need to be outside the single quotes or just around txtVillage like:
它们可能需要在单引号之外或在 txtVillage 周围，例如：
DLookup("island", "villages", "village = '" & [txtVillage] & "'")
But if you find the right combination, it will take care of the apostrophe.
但是，如果您找到正确的组合，它将处理撇号。
Keith B
基思
回答by dubi
My solution is much simpler. Originally, I used this SQL expression to create an ADO recordset:
我的解决方案要简单得多。最初，我使用这个 SQL 表达式来创建一个 ADO 记录集：
Dim sSQL as String
sSQL="SELECT * FROM tblTranslation WHERE fldEnglish='" & myString & "';"
When myStringhad an apostrophe in it, like Int'l Electrics, my program would halt. Using double quotes solved the problem.
当其中myString包含撇号时，例如 Int'l Electrics，我的程序将停止。使用双引号解决了这个问题。
sSQL="SELECT * FROM tblTranslation WHERE fldEnglish="" & myString & "";"
