转义不需要的字符，主要是单引号-替换功能和实现 [英] Escaping unwanted characters, mainly single quotes --replace function and implementation
问题描述
我只是在测试数据库，但我意识到，无论数据库中的文本条目包含'字符(单引号)，我都会遇到问题.我现在的解决方案是在对字符串执行任何.execute操作之前，我先调用escape(string, "'", " "'" ").
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, "'", " "'" ").
下面的摘要示例:
qr = "INSERT INTO tblExample VALUES ( " & "'" & me.testparam & "'" & ");"
qr = Replace(qr, "'", " "'" ")
db.execute qr
'also tried qr = "INSERT INTO tblExample VALUES ( " & "'" & replace(me.testparam,"'"," ") & "'" & ");"
这是我认为是防止诸如Tourette's之类的值引起错误的正确解决方法.
This was what I assumed to be the correct workaround to prevent errors from values such as Tourette's.
这有两个问题.首先，它不起作用.其次，我在整个应用程序的代码中有50多个位置，在这里我将语句称为db.execute qr，其中qr是可能包含单引号的字符串.我需要表中的字段包含单引号，所以我不能只用空格或类似内容替换它.
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.
两部分问题:
-
有没有比在要作为查询执行的每个字符串上遍历我所有代码调用
Replace更好的解决方案了?
为什么我当前的实现失败? -即使将单引号转义为空格，查询表达式中仍然会出现语法错误.
Why is my current implementation failing? - I still get syntax error in query expression even when escaping the single quote to a space.
推荐答案
首先检查这两行.
"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.
现在，这是注释告诉您要执行的操作...将源字符串中的每个单引号替换为两个单引号.我添加了Debug.Print，因此您可以在即时"窗口中查看完成的字符串(使用Ctrl + g到那里)...然后您可以查看实际的字符串，而不是想像它的外观.
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.Print so 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.
qr = "INSERT INTO tblExample VALUES ( '" & _
Replace(Me.testparam, "'", "''" & "');"
Debug.Print qr
db.Execute qr, dbFailOnError
由于我假设db是DAO.Database对象变量，所以我包含了dbFailOnError选项.您应该在代码中包含错误处理程序，以处理dbFailOnError暴露的任何问题.
Since I assumed db is a DAO.Database object variable, I included the dbFailOnError option. You should include an error handler in your code to deal with any problems dbFailOnError exposes.
当您在查询中遇到VBA函数的麻烦时，请移至立即"窗口并在此处测试您的函数表达式.这将触发一个编译错误，" Expected:列表分隔符或)":
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 )":
? Replace("Tourette's", "'", " "'" ")
但这一项有效:
? Replace("Tourette's", "'", "''")
Tourette''s
我之所以提到它，是因为它通常很有用，也因为您的标题以"转义不需要的字符，主要是单引号"开头.因此，如果要删除/替换其他字符，而不仅仅是单引号，请在立即"窗口中进行实验，直到找到一个有效的Replace()表达式.然后在查询中使用该表达式.
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.
例如，如果不需要的字符包含换行符...
For example, if unwanted characters include line breaks ...
MyString = "foo" & vbCrlf & "bar" : ? MyString
foo
bar
? Replace(MyString, Chr(13) & Chr(10), " ")
foo bar
注意:我使用Chr(13) & Chr(10)而不是vbCrlf作为查找目标，因为数据库引擎可以使用Chr()函数，但不知道命名常量(vbCrlf).
Note: I used Chr(13) & Chr(10) rather than vbCrlf as the find target because the db engine can use the Chr() function but doesn't know about the named constant (vbCrlf).
这篇关于转义不需要的字符，主要是单引号-替换功能和实现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
