Passing SQL variable to ASP

Question

Just a bit of a syntax problem here. I'm using SQL and ASP and have this code thus far:

set rs=Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Questions", conn

sql="INSERT INTO Questions(QuestionText, QuestionType)"
sql=sql & " VALUES "
sql=sql & "('" & qtext & "',"
sql=sql & "'" & "checkbox" & "');" 
sql=sql & "DECLARE @testID int;"
sql=sql & "SET @testID = @@identity;"

on error resume next
conn.Execute sql,recaffected
if err<>0 then
    Response.Write("An Error Has Occured")
else

end if

How would I pass the SQL variable @testID to ASP so that it could be written within the response.write

Example:

Dim, ASPID
ASPID = @testID
Response.write(ASPID)

(obviously this is wrong) Could anyone tell me how to do this? Sorry if this is a bit too simple for some people :P

possible duplicate of [SQL with ASP Syntax](http://stackoverflow.com/questions/21626338/sql-with-asp-syntax) — user692942, Feb 07 '14 at 14:16

user692942 · Answer 1 · 2014-02-07T14:19:48.133

Try this;

' What is this for?
'set rs=Server.CreateObject("ADODB.recordset")
'rs.Open "Select * from Questions", conn

Dim cmd, rs, sql, new_id, yourconnstring

sql = ""
sql = sql & "INSERT INTO Questions(QuestionText, QuestionType) VALUES (?, ?);" & vbCrLf
sql = sql & "SELECT SCOPE_IDENTITY();"

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
  .ActiveConnection = yourconnstring 'No need for separate connection object
  .CommandType = adCmdText
  .CommandText = sql
  .Parameters.Append(.CreateParameter("@questiontext", adVarWChar, adParamInput, 255)
  .Parameters.Append(.CreateParameter("@questiontype", adVarWChar, adParamInput, 255)
  Set rs = .Execute(, Array(qtext, "checkbox"))
  If Not rs.EOF Then new_id = rs(0)
  Call rs.Close()
  Set rs = Nothing
End With
Set cmd = Nothing

If Len(new_id) < 1 Then
  Call Response.Write("An Error Has Occured")
Else
  Call Response.Write("ID is " & new_id)
End If

Not tested but this is how I tend to do this without having to worry about handling apostrophes and malicious code.

Carsten Cumbrowski · Answer 2 · 2015-04-02T09:04:53.673

Alternative without the use of a command object

Set rs=Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Questions", conn
...
rs.Close
sql="INSERT INTO Questions(QuestionText, QuestionType)" & _
    " VALUES " & _
    "('" & qtext & "'," & _
    "'" & "checkbox" & "');" & _
    "SELECT SCOPE_IDENTITY() AS testid;"

On Error Resume Next
rs.Open sql, conn
recaffected = rs("testid")
rs.Close : Set rs = Nothing
If err.Number <> 0 Then
   Response.Write("An Error Has Occured")
Else
End If
On Error Goto 0

That you don't pass unchecked content from user inputs via "qtext" is clear Just a

Replace(qtext,"'","''")

might not be enough. Cleaner would be the use of a function like this:

Function SQLScrub(ByVal val)
   dim HostileAttemptString(5)
       HostileAttemptString(0) = "EXEC(@"
       HostileAttemptString(1) = "exec(@" 
       HostileAttemptString(2) = "and%201=" 
       HostileAttemptString(3) = "and%20char(" 
       HostileAttemptString(4) = "exec%28@" 
       HostileAttemptString(5) = "EXEC%28@"

   dim str,rtnVal
   rtnVal = val
   rtnVal = Replace(val,"'","''")

   For Each str in HostileAttemptString
    rtnVal = Replace(rtnVal,str,"")
   Next

   SQLScrub = rtnVal 
end function

Syntax:

qtext = SQLScrub(original_userinput)

@Lankymart I agree that it is cleaner to use the ADODB.Command object. I am not trusting the build in sanitizing of the object, that's why I was using in my own .ASP code always the ADODB.Command object AND custom "scrubbing", with the only difference that I declared the String array as global variable :) — Carsten Cumbrowski, Apr 02 '15 at 09:31

Passing SQL variable to ASP

2 Answers2

Linked