ADODB Recordset.Open给SQL提供语法错误excel [英] ADODB Recordset.Open giving syntax error excel to SQL

问题描述

我敬酒，无法弄清楚为什么在
rst.Open strSQL

$ b行上出现 SYNTAX 错误
$ b

我已经使用 rst.Open strSQL，cnt，adOpenStatic，adLockReadOnly，adCmdText

尝试过此操作，但是

我有一个偷偷的怀疑，它与strSQL如何获取单元格值并将其附加到字符串的末尾有关。 / p>

任何帮助都受到高度赞赏。

  Public Sub EzPz（）
 
 Dim cnt作为ADODB.Connection 
 Dim rst作为ADODB.Recordset 
 
 
 Set cnt = New ADODB.Connection 
 Set rst = New ADODB.Recordset 
 
 
 Dim strSQL As String 
 
'打开连接
 cnt.ConnectionString = Driver = {SQL Server}; Server = HIDDEN; Database = HIDDENXX; Trusted_Connection =是; UID = HIDDENU; PWD = HIDDENP; 
 cnt.Open 
 
'SQL cmd 
 Dim p1作为范围
 Set p1 = ActiveSheet.Cells（1，4）
 strSQL =从DBNAME.vItem中选择DBNAME.vItem.Upc，其中vItem.ItemDesc =& p1.Value 
 
 
 
 rst.ActiveConnection = cnt 
 rst.Open strSQL 
 
 ActiveSheet.Cells（1，1）.CopyFromRecordset rst 
 
结束子
  

解决方案

您的代码需要进行SQL注入。您可以将字符串文字用单引号引起来如该答案所示，以修复语法错误，但不能解决严重的安全问题。

强制性XKCD

那么您如何安全地参数化查询？使用参数化查询！

  Dim conn作为ADODB.Connection 
设置conn =新ADOBD .Connection 
 .ConnectionString =理想情况下使用Windows身份验证的连接字符串 
 .Open 
  

理想情况下，您的连接字符串不包含任何用户名或密码；您的服务器需要配置为支持 Windows身份验证，此功能才能正常运行-查询随后将以登录的Windows用户的凭据以及该用户的特权执行。

  Dim cmd作为ADODB.Command 
 Set cmd.ActiveConnection = conn 
 cmd.CommandType = adCmdText 
 cmd.CommandText =从DBNAME.vItem中选择DBNAME.vItem.Upc vItem.ItemDesc =？; 
  

设置 Command 对象。 CommandText 将是您的SQL语句，但您无需在其中连接参数，而是使用？问号

  Dim itemDesc作为ADODB.Parameter 
 Set itemDesc = New ADODB.Parameter 
 itemDesc.Type = adVarChar 
 itemDesc.Direction = adParamInput 
 itemDesc.Value = p1.Value 
 
 cmd.Parameters.Append（itemDesc）
  

为每个问题创建一个参数？在SQL语句中标记。您必须为每个？问号提供一个参数。

 昏暗结果如ADODB.Recordset 
设置结果= cmd。执行
  

您获得<$ c $通过调用命令的 Execute 方法c> Recordset ；服务器处理参数。

  ActiveSheet.Cells（1，1）.CopyFromRecordset结果
  

如果一切顺利，记录集将包含您的结果。

始终使用参数化查询：将用户输入串联到SQL语句中是一种困扰。

I'm toast, cannot figure out why I'm getting a SYNTAX error on the line rst.Open strSQL

I've tried it with rst.Open strSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText

But it still gives me an error.

I have a sneaking suspicion it has to do with how strSQL is taking a cell value and appending it to the end of a string.

Any help is highly appreciated.

   Public Sub EzPz()

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset


Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset


Dim strSQL As String

'Open connection
cnt.ConnectionString = "Driver={SQL Server};Server=HIDDEN;Database=HIDDENXX;Trusted_Connection=yes;UID=HIDDENU;PWD=HIDDENP;"
cnt.Open

'String for SQL cmd
Dim p1 As Range
Set p1 = ActiveSheet.Cells(1, 4)
strSQL = "SELECT DBNAME.vItem.Upc FROM DBNAME.vItem WHERE vItem.ItemDesc=" & p1.Value



rst.ActiveConnection = cnt    
rst.Open strSQL

ActiveSheet.Cells(1, 1).CopyFromRecordset rst

End Sub

解决方案

Your code is subject to SQL injection. You could enclose the string literal in single quotes as shown in this answer to fix the syntax error, but that wouldn't fix the serious security issue.

Obligatory XKCD

So how do you securely parameterize a query? With parameterized queries!

Dim conn As ADODB.Connection
Set conn = New ADOBD.Connection
.ConnectionString = "connection string ideally using Windows Authentication"
.Open

Ideally your connection string doesn't contain any username or password; your server needs to be configured to support Windows Authentication for this to work - the query then executes with the credentials of the logged-in Windows user, with the privileges of that user.

Dim cmd As ADODB.Command
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT DBNAME.vItem.Upc FROM DBNAME.vItem WHERE vItem.ItemDesc = ?;"

Set up a Command object. The CommandText will be your SQL statement, but instead of concatenating the parameters into it, you use a ? question mark for each.

Dim itemDesc As ADODB.Parameter
Set itemDesc = New ADODB.Parameter
itemDesc.Type = adVarChar
itemDesc.Direction = adParamInput
itemDesc.Value = p1.Value

cmd.Parameters.Append(itemDesc)

Create a Parameter for each ? question mark in the SQL statement. You must supply a parameter for each ? question mark.

Dim results As ADODB.Recordset
Set results = cmd.Execute

You obtain the Recordset by calling the command's Execute method; the server deals with the parameters.

ActiveSheet.Cells(1, 1).CopyFromRecordset results

If all went well, the Recordset contains your results.

Always use parameterized queries: user input concatenated into SQL statements is a plague.

这篇关于ADODB Recordset.Open给SQL提供语法错误excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！