将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.

其他信息:

studentid.该设计应能够让用户输入学生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.

推荐答案

经典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屋！