如何处理Word VBA SQL查询中的单引号？

发布于 2024-09-28 02:29:10 字数 394 浏览 0 评论 0原文

我从下拉列表中获取客户名称，并使用该值查询 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

原文

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?

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苯莒 2024-10-05 02:29:10

如果您指定两个单引号 ''，其中一个将转义另一个并导致单引号，请尝试像这样替换它：

customerName = Replace(customerName, "'", "''")

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, "'", "''")

回复收藏 0 原文

梦罢 2024-10-05 02:29:10

这使您很容易受到 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

This leaves you wide open to an SQL injection attack. I would recommend changing this to a parameterised query like this

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

回复收藏 0 原文

~没有更多了~