将 vbscript 字符串列表传递给 SQL“in"操作符 [英] Pass a vbscript String list to a SQL "in"operator

问题描述

在 vb 脚本中，我有一个 select 语句，我试图将一个长度不确定的字符串值传递给 SQL in 运算符，下面的代码可以工作，但允许 SQL 注入.

In the vb script I have a select statement I am trying to pass a string value with an undetermined length to a SQL in operator the below code works but allows for SQL injection.

我正在寻找一种使用 ADO createParameter 方法的方法.我相信我尝试过的不同方法都被我的数据类型所困扰(adVarChar、adLongChar、adLongWChar)

I am looking for a way to use the ADO createParameter method. I believe the different ways I have tried are getting caught up in my data type (adVarChar, adLongChar, adLongWChar)

    Dim studentid 
studentid = GetRequestParam("studentid")

Dim rsGetData, dbCommand
    Set dbCommand = Server.CreateObject("ADODB.Command")
    Set rsGetData = Server.CreateObject("ADODB.Recordset")
    dbCommand.CommandType = adCmdText
    dbCommand.ActiveConnection = dbConn
dbCommand.CommandText = "SELECT * FROM students WHERE studentID in (" & studentid & ")"
Set rsGetData = dbCommand.Execute()

我试过了

Call addParameter(dbCommand, "studentID", adVarChar, adParamInput, Nothing, studentid)

这给了我这个错误ADODB.Parameters 错误'800a0e7c'添加参数时出现问题 (studentID)=('SID0001','SID0010'):参数对象定义不正确.提供的信息不一致或不完整.

which gives me this error ADODB.Parameters error '800a0e7c' Problems adding parameter (studentID)=('SID0001','SID0010') :Parameter object is improperly defined. Inconsistent or incomplete information was provided.

我也试过

Call addParameter(dbCommand, "studentID", adLongVarChar, adParamInput, Nothing, studentid)

和

    Dim studentid 
studentid = GetRequestParam("studentid")

Dim slength
slength = Len(studentid)
response.write(slength)

Dim rsGetData, dbCommand
    Set dbCommand = Server.CreateObject("ADODB.Command")
    Set rsGetData = Server.CreateObject("ADODB.Recordset")
    dbCommand.CommandType = adCmdText
    dbCommand.ActiveConnection = dbConn
dbCommand.CommandText = "SELECT * FROM students WHERE studentID in (?)"
    Call addParameter(dbCommand, "studentID", adVarChar, adParamInput, slength, studentid)
Set rsGetData = dbCommand.Execute()

这两个选项都没有做任何事情...没有错误消息并且没有执行 SQL.

both of these options don't do anything... no error message and the SQL is not executed.

附加信息:

正在通过 HTML 表单文本区域输入学生 ID.设计是能够让用户输入学生 ID 列表(最多 1000 行)并对这些学生资料执行操作.在我上一个asp的javascript中，我有一个函数可以获取列表并将其更改为逗号分隔的列表，并在该列表中的每个元素周围使用''.

studentid is being inputted through a HTML form textarea. the design is to be able to have a user input a list of student id's (up to 1000 lines) and perform actions on these student profiles. in my javascript on the previous asp I have a function that takes the list and changes it into a comma delimited list with '' around each element in that list.

推荐答案

Classic ASP 对此没有很好的支持.您需要退回到此处讨论的替代方案之一:

Classic ASP does not have good support for this. You need to fall back to one of the alternatives discussed here:

http://www.sommarskog.se/arrays-in-sql-2005.html

那篇文章有点长，但很好:很多人认为它是这个主题的标准著作.

That article is kind of long, but in a good way: it's considered by many to be the standard work on this subject.

恰好我的首选选项未包含在该文章中.我喜欢做的是为列表中的每个单独项目使用一个保持表，这样每个项目都使用 ajax 请求在用户选择或取消选择它的那一刻从保持表中插入或删除它.然后我加入该表作为我的列表，这样你最终会得到这样的结果:

It also just so happens that my preferred option is not included in that article. What I like to do is use a holding table for each individual item in the list, such that each item uses an ajax request to insert or remove it from the holding table the moment the user selects or de-selects it. Then I join to that table for my list, so that you end up with something like this:

SELECT s.* 
FROM students s
INNER JOIN studentSelections ss on s.StudentID = ss.StudentID
WHERE ss.SessionKey = ?

这篇关于将 vbscript 字符串列表传递给 SQL“in"操作符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！