带有子查询错误的 ADO 参数化查询
我有一个运行 SQL Server 2012(也用 2016 测试)的旧版经典 ASP 应用程序，我正在尝试切换到使用参数化查询.该站点的所有查询都通过一个函数运行，该函数将 sql 语句视为字符串，其中包含由问号表示的参数以及这些参数的数组.该函数目前对参数进行过滤，使它们成为 sql 安全的，并在执行语句之前将它们放入 sql 字符串中.
I have a legacy classic ASP application running with SQL Server 2012 (also tested with 2016) that I am trying to switch over to using parameterized queries. All the site's queries run through a function which expects a sql statement as a string with parameters represented by question marks as well as an array of those parameters. The function currently filters the parameters to make them sql safe and puts them into the sql string before executing the statement.
鉴于此，我认为将其切换为参数化查询会非常简单.初始测试看起来不错，一切似乎都正常工作，直到我在子查询中遇到了带有参数的 sql 语句.
Given this, I thought it would be pretty straightforward to switch this to parameterized queries. Initial testing looked good, and everything appeared to be working properly until I hit a sql statement with parameters in subqueries.
以下是有效的测试示例:
Here's a test sample of what works:
Const connectionString = "Provider=SQLNCLI11; Server=********; Database=********; UID=*******; PWD=*******"
Dim sql, productId, parameters
sql = "SELECT SKU FROM Products WHERE ProductId = ?"
productId = 3
parameters = Array(productId)
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connectionString
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = sql
cmd.Parameters.Refresh
Dim rs
Set rs = cmd.Execute(, parameters)
Response.Write("SKU: " & rs("SKU"))
没问题，这会按预期返回 SKU.但是，如果我使用子查询:
No problem, this returns the SKU as expected. However, if I use a subquery:
Const connectionString = "Provider=SQLNCLI11; Server=********; Database=********; UID=*******; PWD=*******"
Dim sql, productId, parameters
'contrived subquery for demonstration purposes
sql = "SELECT SKU FROM ( SELECT SKU FROM Products WHERE ProductId = ? ) AS P"
productId = 3
parameters = Array(productId)
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connectionString
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = sql
cmd.Parameters.Refresh
Dim rs
Set rs = cmd.Execute(, parameters)
Response.Write("SKU: " & rs("SKU"))
它在 cmd.Parameters.Refresh 行抛出错误:
It throws an error on the cmd.Parameters.Refresh line:
Microsoft VBScript 运行时错误0x80004005"Microsoft SQL Server 本机客户端 11.0语法错误、权限违规或其他非特定错误
Microsoft VBScript runtime error '0x80004005' Microsoft SQL Server Native Client 11.0 Syntax error, permission violation, or other nonspecific error
如果我在第一个样本中检查 cmd.Parameters.Count，我会正确地得到 1.在错误的样本中，它会抛出相同的错误.
If I check cmd.Parameters.Count in the first sample, I correctly get 1. In the bad sample it throws the same error.
是否有任何解释为什么将参数放入子查询会导致参数集合出现问题?我确实尝试将参数手动添加到 Parameters 集合中，效果很好，但这意味着要修改数百个现有的 sql 调用，因此目前 cmd.Parameters.Refresh 往返是值得的.
Is there any explanation as to why putting the parameter into a subquery causes problems with the parameter collection? I did try manually adding the parameter to the Parameters collection, and that works fine, but it means modifying hundreds of existing sql calls, so for the moment the cmd.Parameters.Refresh round-trip was worth the expense.
推荐答案
cmd.execute你想要什么都可以，不过我好久没用了.
You can give cmd.execute what you want, but I haven't used it in a long time.
cmd.execute("SELECT SKU FROM ( SELECT SKU FROM Products WHERE ProductId = ? ) AS P", Array(productId))
cmd.execute("SELECT SKU FROM ( SELECT SKU FROM Products WHERE ProductId = ? ) AS P", Array(productId))
相关文章