ADODB Recordset.Open给SQL提供语法错误excel [英] ADODB Recordset.Open giving syntax error excel to SQL
问题描述
我敬酒,无法弄清楚为什么在
rst.Open strSQL
$ 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注入。您可以将字符串文字用单引号引起来如该答案所示,以修复语法错误,但不能解决严重的安全问题。
那么您如何安全地参数化查询?使用参数化查询!
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.
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屋!