問(wèn)題描述

我有一個(gè)運(yùn)行 SQL Server 2012(也用 2016 測(cè)試)的舊版經(jīng)典 ASP 應(yīng)用程序，我正在嘗試切換到使用參數(shù)化查詢(xún).該站點(diǎn)的所有查詢(xún)都通過(guò)一個(gè)函數(shù)運(yùn)行，該函數(shù)將 sql 語(yǔ)句視為字符串，其中包含由問(wèn)號(hào)表示的參數(shù)以及這些參數(shù)的數(shù)組.該函數(shù)目前對(duì)參數(shù)進(jìn)行過(guò)濾，使它們成為 sql 安全的，并在執(zhí)行語(yǔ)句之前將它們放入 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.

鑒于此，我認(rèn)為將其切換為參數(shù)化查詢(xún)會(huì)非常簡(jiǎn)單.初始測(cè)試看起來(lái)不錯(cuò)，一切似乎都正常工作，直到我在子查詢(xún)中遇到了帶有參數(shù)的 sql 語(yǔ)句.

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.

以下是有效的測(cè)試示例:

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

沒(méi)問(wèn)題，這會(huì)按預(yù)期返回 SKU.但是，如果我使用子查詢(xún):

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 行拋出錯(cuò)誤:

It throws an error on the cmd.Parameters.Refresh line:

Microsoft VBScript 運(yùn)行時(shí)錯(cuò)誤0x80004005"Microsoft SQL Server 本機(jī)客戶(hù)端 11.0語(yǔ)法錯(cuò)誤、權(quán)限違規(guī)或其他非特定錯(cuò)誤

Microsoft VBScript runtime error '0x80004005' Microsoft SQL Server Native Client 11.0 Syntax error, permission violation, or other nonspecific error

如果我在第一個(gè)樣本中檢查 cmd.Parameters.Count，我會(huì)正確地得到 1.在錯(cuò)誤的樣本中，它會(huì)拋出相同的錯(cuò)誤.

If I check cmd.Parameters.Count in the first sample, I correctly get 1. In the bad sample it throws the same error.

是否有任何解釋為什么將參數(shù)放入子查詢(xún)會(huì)導(dǎo)致參數(shù)集合出現(xiàn)問(wèn)題?我確實(shí)嘗試將參數(shù)手動(dòng)添加到 Parameters 集合中，效果很好，但這意味著要修改數(shù)百個(gè)現(xiàn)有的 sql 調(diào)用，因此目前 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你想要什么都可以，不過(guò)我好久沒(méi)用了.

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))

這篇關(guān)于帶有子查詢(xún)錯(cuò)誤的 ADO 參數(shù)化查詢(xún)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！