vba 转义不需要的字符，主要是单引号 --replace 函数和实现

Escaping unwanted characters, mainly single quotes --replace function and implementation

提问by Scotch

I was just testing my database and I realized that I run into problems wherever a text entry in my database contains a 'character (single quote). My solution for now is that before any .execute operations on a string, I call escape(string, "'", " "'" ").

我只是在测试我的数据库，我意识到我在数据库中的文本条目包含'字符（单引号）的任何地方都会遇到问题。我现在的解决方案是，在对字符串执行任何 .execute 操作之前，我调用escape(string, "'", " "'" ").

Summarized example below:

总结如下：

qr = "INSERT INTO tblExample VALUES ( " & "'" & me.testparam & "'" & ");"
qr = Replace(qr, "'", " "'" ")
db.execute qr
'also tried  qr = "INSERT INTO tblExample VALUES ( " & "'" & replace(me.testparam,"'"," ") & "'" & ");"

This was what I assumed to be the correct workaround to prevent errors from values such as Tourette's.

这是我认为的正确解决方法，以防止Tourette's.

There's two problems with this. First of all, it's not working. Second, I have over 50 locations in code throughout my app where I call the statement db.execute qr where qr is a string that could potentially contain a single quote. I need the field in the table to contain the single quote, so I can't just replace it with a space or something similar.

这有两个问题。首先，它不起作用。其次，我在整个应用程序中的代码中有 50 多个位置，我将语句 db.execute qr 称为其中 qr 是可能包含单引号的字符串。我需要表中的字段包含单引号，所以我不能只用空格或类似的东西替换它。

Two part question:

两部分问题：

1. Is there a better solution than going through all of my code calling Replaceon every string that is to be executed as a query?

2. Why is my current implementation failing? - I still get syntax error in query expression even when escaping the single quote to a space.

1. 有没有比Replace在每个要作为查询执行的字符串上调用所有代码更好的解决方案？

2. 为什么我当前的实施失败？- 即使将单引号转义为空格，我仍然在查询表达式中遇到语法错误。

采纳答案by HansUp

First examine these 2 lines.

首先检查这两行。

"VALUES ( " & "'" & me.testparam & "'" & ");"
"VALUES ( '" & me.testparam & "');"

Both will produce the exact same string. The difference for me is that my brain comprehends the second version faster.

两者都会产生完全相同的字符串。对我来说不同的是我的大脑更快地理解第二个版本。

Now, here is what the comments are telling you to do ... replace each single quote in your source string with two single quotes. I added Debug.Printso you can view the finished string in the Immediate window (go there with Ctrl+g) ... you can then see the actual string rather than trying to imagine what it looks like.

现在，这是注释告诉您要​​做的事情……用两个单引号替换源字符串中的每个单引号。我添加了Debug.Print以便您可以在“立即”窗口中查看完成的字符串（使用 Ctrl+g 转到那里）……然后您可以看到实际的字符串，而不是试图想象它的样子。

qr = "INSERT INTO tblExample VALUES ( '" & _
    Replace(Me.testparam, "'", "''" & "');"
Debug.Print qr
db.Execute qr, dbFailOnError 

Since I assumed dbis a DAO.Databaseobject variable, I included the dbFailOnErroroption. You should include an error handler in your code to deal with any problems dbFailOnErrorexposes.

因为我假设db是一个DAO.Database对象变量，所以我包含了这个dbFailOnError选项。您应该在代码中包含一个错误处理程序来处理dbFailOnError暴露的任何问题。

When you run into trouble with a VBA function in a query, drop to the Immediate window and test your function expression there. This one triggers a compile error, "Expected: list separator or )":

当您在查询中遇到 VBA 函数的问题时，请移至“立即”窗口并在那里测试您的函数表达式。这会触发编译错误，“预期：列表分隔符或）”：

? Replace("Tourette's", "'", " "'" ")

But this one works:

但是这个有效：

? Replace("Tourette's", "'", "''")
Tourette''s

I mentioned that because it's useful in general, and also because your title starts with "Escaping unwanted characters, mainly single quotes". So if you want to remove/replace other characters, not just single quotes, experiment in the Immediate window until you find a Replace()expression which works. Then use that expression in your query.

我提到这一点是因为它通常很有用，还因为您的标题以“转义不需要的字符，主要是单引号”开头。因此，如果您想删除/替换其他字符，而不仅仅是单引号，请在“立即”窗口中进行试验，直到找到有效的Replace()表达式。然后在您的查询中使用该表达式。

For example, if unwanted characters include line breaks ...

例如，如果不需要的字符包括换行符...

MyString = "foo" & vbCrlf & "bar" : ? MyString
foo
bar
? Replace(MyString, Chr(13) & Chr(10), " ")
foo bar

Note: I used Chr(13) & Chr(10)rather than vbCrlfas the find target because the db engine can use the Chr()function but doesn't know about the named constant (vbCrlf).

注意：我使用Chr(13) & Chr(10)而不是vbCrlf作为查找目标，因为数据库引擎可以使用该Chr()函数但不知道命名常量 ( vbCrlf)。

回答by Fionnuala

Your query is failing because you have not said where to insert :

您的查询失败，因为您没有说在哪里插入：

Dim qd As QueryDef
qr = "INSERT INTO tblExample (AText) VALUES ( [avalue] );"

Set qd = CurrentDB.CreateQueryDef("",qr)
qd.Parameters("avalue").Value = me.testparam
qd.Execute dbFailOnError

回答by AndrewM

Another method is to define a quote as constant (Const Quote = """") and use that to build SQL Statements. It is not possible to define a quote as Const Quote = Chr(34) as a constant definition can't be based on a function so one has to use four double quotes in a row. The third quote is what you are saving, the second quote is to excape the third quote and the first and last quote are because the value you are assigning is a string.

另一种方法是将引用定义为常量（Const Quote = """"）并使用它来构建 SQL 语句。不可能将引用定义为 Const Quote = Chr(34)，因为常量定义不能基于函数，因此必须连续使用四个双引号。第三个引号是您要保存的内容，第二个引号是为了排除第三个引号，第一个和最后一个引号是因为您分配的值是一个字符串。

You will then be able to build SQL statements such as:

然后，您将能够构建 SQL 语句，例如：

SQL = SELECT * FROM tblSyndromes
WHERE Syndrome = " & Quote & "Tourette's" & Quote & ";"

It will no longer matter that there are single quotes in your data.

数据中是否有单引号不再重要。

I don't use parameters as if I upscale my database to sql server and convert my queries to pass-through queries, I can't use parameters. I rarely upscale but I write all my code with that assumption. Also if your query is not working as expected, how do find out what went wrong. If I have a variable called SQL, then I can always print the SQL statement and run it in a new query to see what it does.

我不使用参数，就好像我将我的数据库升级到 sql server 并将我的查询转换为传递查询一样，我不能使用参数。我很少高档，但我用这个假设写了我所有的代码。此外，如果您的查询没有按预期工作，如何找出问题所在。如果我有一个名为 SQL 的变量，那么我总是可以打印 SQL 语句并在新查询中运行它以查看它的作用。