如何处理 Word VBA SQL 查询中的单引号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3975042/
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
How to deal with single quote in Word VBA SQL query?
提问by Morgan
I get a customer name from dropdown and use that value to query an excel spreadsheet, however, the name can contain a single quote (example: Adam's Meat). This breaks my application and how do I make a query with a variable that contains a single quote?
我从下拉列表中获取客户名称并使用该值查询 Excel 电子表格，但是，该名称可以包含单引号（例如：Adam's Meat）。这会破坏我的应用程序，如何使用包含单引号的变量进行查询？
Private Sub cboCompany_Change()
Dim customerName As String
customerName = cboCompany.Value
rsT.Open "SELECT Customer, Postcode, Address1, Address2, State, Country FROM Customers WHERE Customer = '" & customerName & "'", cn, adOpenStatic
回答by Sarfraz
Where you specify two single quotes '', one will escape the other and will result in single, try to replace it like this:
在您指定两个单引号的地方''，一个将转义另一个并导致单引号，尝试像这样替换它：
customerName = Replace(customerName, "'", "''")
回答by Kevin Ross
This leaves you wide open to an SQL injection attack. I would recommend changing this to a parameterised query like this
这让您很容易受到 SQL 注入攻击。我建议将其更改为这样的参数化查询
Dim cmd as NEW ADODB.Command
With cmd
.CommandText=”SELECT foo from tblBar where foo=?”
.Parameters.Append .CreateParameter("@foo", adVarChar, adParamInput, 50, “What ever you want”)
.ActiveConnection=dbCon
.CommandType=adCmdText
End With
Set rst=cmd.execute
