Pass A Vbscript String List To A Sql "in"operator

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

Solution 1:

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.

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
INNERJOIN studentSelections ss on s.StudentID = ss.StudentID
WHERE ss.SessionKey = ?

Solution 2:

What does your addParameter() function do? I don't see that anywhere in your code.

You should be able to create and add your string param like so:

With dbCommand
    .Parameters.Append .CreateParameter(, vbString, , Len(studentid), studentid)
EndWith

(Small hack here. vbString has the same value as adBSTR. You'll find that the VarType of all VB "types" have matching ADO counterparts.)

TypeVarType (VBScript)  DataTypeEnum (ADO)  Value
---------  ------------------  ------------------  -----
Integer    vbInteger           adSmallInt, 2-byte      2Long       vbLong              adInteger, 4-byte       3Single     vbSingle            adSingle                4Double     vbDouble            adDouble                5Currency   vbCurrency          adCurrency              6Date       vbDate              adDate                  7String     vbString            adBSTR                  8Object     vbObject            adIDispatch             9Error      vbError             adError                10Boolean    vbBoolean           adBoolean              11Variant    vbVariant           adVariant              12Byte       vbByte              adUnsignedTinyInt      17

---

Edit: Looks like Joel has a good solution for you. I didn't realize IN isn't compatible with ADO parameterized queries. I think something like the following would work, but you probably wouldn't want to do it with (potentially) 1000 ID's.

' Create array from student IDs entered...
a = Split(studentid, ",")

' Construct string containing proper number of param placeholders. Remove final comma.
strParams = Replace(String(UBound(a) - 1, "?"), "?", "?,")
strParams = Left(strParams, Len(strParams) - 1)

With dbCommand
    .CommandText = "select * from students where studentID in (" & strParams & ")"Set rsGetData = .Execute(, a)
EndWith

Solution 3:

After reading through the article that was provided by Joel and the answer that All Blond provided this is the solution that ended up working for me.

Dim studentidstudentid= GetRequestParam("studentid")

Dim splitStudentid, xsplitStudentid= Split(studentid,",")

for x=0 to Ubound(splitStudentid)
Dim rsGetData, dbCommand, originSID
SetdbCommand= Server.CreateObject("ADODB.Command")
SetrsGetData= Server.CreateObject("ADODB.Recordset")
dbCommand.CommandType = adCmdText
dbCommand.ActiveConnection = dbConn
dbCommand.CommandText = "SELECT * FROM students WHERE studentID=?"
Call addParameter(dbCommand, "studentID", adVarChar, adParamInput, 35, splitStudentid(x))
SetrsGetData= dbCommand.Execute()

If (NOT rsGetData.EOF) ThenoriginSID= rsGetData.Fields(0)
    //additional code
End If
next

I found that there was no elegant way to use the "in" operator in my code. I also decided against a Stored Procedure as it is a simple query though I agree

ALSO I realize that addParameter is a Function my company uses internally so below is an additional solution that works also works but is not my companies preference.

    Dim studentidstudentid= GetRequestParam("studentid")

Dim splitStudentid, xsplitStudentid= Split(studentid,",")

for x=0 to Ubound(splitStudentid)
Dim rsGetData, dbCommand, originSID
SetdbCommand= Server.CreateObject("ADODB.Command")
SetrsGetData= Server.CreateObject("ADODB.Recordset")
dbCommand.CommandType = adCmdText
dbCommand.ActiveConnection = dbConn
dbCommand.CommandText = "SELECT * FROM students WHERE studentID=?"SetrsGetData= dbCommand.Execute(, Array(splitStudentid(x)))

If (NOT rsGetData.EOF) ThenoriginSID= rsGetData.Fields(0)
    //additional code
End If
next

Solution 4:

Using IN with a Parameterised Query isn't Hard

Posting this here in relation to another question that was marked as a duplicate of this one.

This isn't as difficult as you think, the adCmdText query just needs to the placeholders in the query to match the number of parameters and their ordinal position and it will work with any number of parameters you pass into an IN statement.

Here is a quick example using the AdventureWorks example database in SQL Server. We use an Array to store the id of each Person.Contact record we wish to filter out using IN than build the parameters dynamically based on that array before executing the ADODB.Command.

Note: The source of the array is not important it could be a string list which is Split() into an Array or just an Array() call (like the one used in this example).

<%
Optionexplicit
%>
<!-- #include virtual = "/config/data.asp" -->
<%
Dim cmd, rs, sql, data, rows, row
'Our example parameters as an Array for the IN statement.Dim ids: ids = Array(2, 5, 10)
Dim id

sql = ""
sql = sql & "SELECT [FirstName], [LastName] " & vbCrLf
sql = sql & "FROM Person.Contact " & vbCrLf
sql = sql & "WHERE [ContactId] IN (?, ?, ?) " & vbCrLf
sql = sql & "ORDER BY [LastName], [FirstName]" & vbCrLf

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
  .ActiveConnection = conn_string
  .CommandType = adCmdText
  .CommandText = SQL
  'Loop through the Array and append the required parameters.ForEach id in ids
    Call .Parameters.Append(.CreateParameter("@id" & id, adInteger, adParamInput, 4))
    .Parameters("@id" & id).Value = id
  NextSet rs = .Execute()
  'Output the Recordset to a 2-Dimensional ArrayIfNot rs.EOF Then data = rs.GetRows()
  Call rs.Close()
EndWithSet cmd = NothingIf IsArray(data) Then
  rows = UBound(data, 2)
  For row = 0To rows
    Call Response.Write("<p>" & data(0, row) & " " & data(1, row) & "</p>" & vbCrLf)
  NextEndIf
%>

Output:

<p>Catherine Abel</p><p>Pilar Ackerman</p><p>Ronald Adina</p>

Worth noting this example shows the explicit way of writing the parameter code for an elegant approach to the problem see the second solution in @Bond's answer.

Solution 5:

Try to add to your code following(assuming that StudentID is numeric)

dim outputArray,x,compareArray
 outputArray=split(inputText,",")
 foreach x in outputArray
  If IsNumeric(x) Thenif len(compareArray)>1andcInt(x)>0then
         compareArray=compareArray&"," & cInt(x)
      else
          compareArray=cInt(x)
      endifelse' throw some log entry or do something for you to know that someone tryendifnext

and then do all your Db connection set etc up to this point where you use this new string of integers:

dbCommand.CommandText = "SELECT * FROM students WHERE studentID in (" & compareArray &")"

and this will safeguard you from anyone use your StudentId list for SQL injection. I would rather use Store procedure and user-defined table types but ...

In any case if it is not numeric then it must have some parameter like length or complexity which you can use to verify that value has not been compromised using regular expression for example for limiting what can be in that value; but idea of the looping through and verifying values remain the same.