問題描述
我有一個運行 SQL Server 2012(也用 2016 測試)的舊版經(jīng)典 ASP 應(yīng)用程序，我正在嘗試切換到使用參數(shù)化查詢.該站點的所有查詢都通過一個函數(shù)運行，該函數(shù)將 sql 語句視為字符串，其中包含由問號表示的參數(shù)以及這些參數(shù)的數(shù)組.該函數(shù)目前對參數(shù)進行過濾，使它們成為 sql 安全的，并在執(zhí)行語句之前將它們放入 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.
鑒于此，我認為將其切換為參數(shù)化查詢會非常簡單.初始測試看起來不錯，一切似乎都正常工作，直到我在子查詢中遇到了帶有參數(shù)的 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"))
沒問題，這會按預(yù)期返回 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語法錯誤、權(quán)限違規(guī)或其他非特定錯誤
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.
是否有任何解釋為什么將參數(shù)放入子查詢會導(dǎo)致參數(shù)集合出現(xiàn)問題?我確實嘗試將參數(shù)手動添加到 Parameters 集合中，效果很好，但這意味著要修改數(shù)百個現(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你想要什么都可以，不過我好久沒用了.
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)于帶有子查詢錯誤的 ADO 參數(shù)化查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！