none
Multiple parametrized query with same connection and command objects using vbscript RRS feed

  • Question

  • Hi,

      I have two different query sets which needs to be parameterized to prevent from SQLi. I have written the code whereas in the same, the first query executing fine and the second one throws error as "Incorrect syntax near the keyword 'DEFAULT'". Please find the code snippet below and help me to overcome the issue.

     Note: Code is written in vbscript for a classic ASP page and has to execute sequentially.

    /* Code 1 which executes fine*/
    reqQryStr_id= Request.QueryString("ID")
    Set cpRs1= Server.CreateObject("ADODB.Recordset")
    Set cmdObj1= Server.CreateObject("ADODB.Command")
    qryStr = "Select name,id From table_abc Where id= ?" 
       cmdObj1.ActiveConnection=Conn
    cmdObj1.CommandText= qryStr
    cmdObj1.CommandType= adCmdText
    cmdObj1.Parameters.Refresh
    cmdObj1.Parameters.Append cmdObj1.CreateParameter("@id",,,,reqQryStr_id)
    Set cpRs1 = cmdObj1.Execute
    reqid= cprs1("id")
    /*Code 2 where throws error */
    qrystr= "Select name,id from table_abc where name like ?"
    Set cmdObj1= Server.CreateObject("ADODB.Command")
    cmdObj1.ActiveConnection=Conn
    cmdObj1.CommandText= qryStr
    cmdObj1.CommandType= adCmdText
    cmdObj1.Parameters.Refresh
    cmdObj1.Parameters.Append cmdObj1.CreateParameter("@reqid",adVarChar,adParamInput,200,reqid)
    Set cpRs1 = cmdObj.Execute

    I also tried the Parameters.Append similar to that code1, but throwed an error like "necessary parameters are missing".

    Please help to resolve this.


    Go Green.Save Earth.


    • Edited by venturert Thursday, January 14, 2016 11:59 AM
    Thursday, January 14, 2016 11:58 AM

Answers

  • It appear that you problem is one of design and a lack of understanding of how SQL ADO works.

    To give you  full answer is very hard because there are to many missing pieces.

    If you have a parameterized AD>Command object then it is not necessary to recreate it to use it. Just get the parameter and change the value then re-excute the query.

    You need to fin an ASP forum.  There are very few left as ASP is now obsolete and is not used due to security issues.

     


    \_(ツ)_/

    • Marked as answer by venturert Wednesday, January 27, 2016 9:52 AM
    Thursday, January 14, 2016 4:55 PM

All replies

  • This isn't a SQL or ASP support forum unfortunately.

    Try searching for "asp forum".


    -- Bill Stewart [Bill_Stewart]

    Thursday, January 14, 2016 2:21 PM
    Moderator
  • You can just reuse the code.

    Function ExecuteQuery(id)
    	Set cmdObj1= Server.CreateObject("ADODB.Command")
    	qryStr = "Select name,id From table_abc Where id= ?" 
    	cmdObj1.ActiveConnection=Conn
    	cmdObj1.CommandText= qryStr
    	cmdObj1.Parameters.Append cmdObj1.CreateParameter("@id",,,,reqQryStr_id)
    	Set cpRs1 = cmdObj1.Execute
    	ExecuteQuery= cprs1("id")
    End Function
    
    result = ExecuteQuery Request.QueryString("ID")
    
    result - ExecuteQuery(reqid)

    You did a bad job of copying the original code.


    \_(ツ)_/



    • Edited by jrv Thursday, January 14, 2016 4:51 PM
    Thursday, January 14, 2016 4:48 PM
  • It appear that you problem is one of design and a lack of understanding of how SQL ADO works.

    To give you  full answer is very hard because there are to many missing pieces.

    If you have a parameterized AD>Command object then it is not necessary to recreate it to use it. Just get the parameter and change the value then re-excute the query.

    You need to fin an ASP forum.  There are very few left as ASP is now obsolete and is not used due to security issues.

     


    \_(ツ)_/

    • Marked as answer by venturert Wednesday, January 27, 2016 9:52 AM
    Thursday, January 14, 2016 4:55 PM
  • Thanks for your reply. But i cannot use functionas the query will vary for 2 codes. The query posted here is a sample one.

    Go Green.Save Earth.

    Saturday, January 16, 2016 1:32 PM
  • Thanks for your reply. yes, I am working in classic ASP for the first time. I saw in some other forum where they suggested on recreation of the command object. It worked for them. But not for me, unfortunately :(

    Go Green.Save Earth.

    Saturday, January 16, 2016 1:35 PM
  • I think you do not understand how ADO work and how it needs t be used in an ASPsession.

    The connection object should be stored in a session variable.  THe command object needs to be created from the connection object.  If yu need to reuse the command object then it, too, needs to be stored as a session variable. To access the parameter we would reference the Parameters collection of the command.

    Set cmd = Session("MyCommand")
    cmd.Parameters(0).Value = mynew value
    Set rs = cmd.ExecuteQuery()

    http://www.w3schools.com/asp/asp_sessions.asp


    \_(ツ)_/

    Saturday, January 16, 2016 1:54 PM
  • Here.  I dug around in my old code pile to get an example of how t share Command object in an ASP session.

    Here is an example of how to set up the connection and commands in the Global.asa file for the ASP application.

    <script language="vbscript" runat="server">
    
    Sub Application_OnStart
    'some code
    end Sub
    
    sub Application_OnEnd
    'some code
    end sub
    
    sub Session_OnStart
    	Const adInteger = 3
    	Const adParamInput = 1
    	
    	' build connectionn and save as session variable
    	Set conn = Server.CreateObject("ADODB.Connection")
    	Set Session("AdoConnection") = conn
    	conn.ConnectionString="Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Issue;Data Source=OMEGA\SQLEXPRESS"
    	conn.Open
    	
    	'Build all commands that you want to reuse here and save in Session variable
    	
    	' Select by ID
    	Set cmd = Server.CreateObject("ADODB.Command")
    	Set Session("AdoCommand") =  cmd
    	Set cmd.ActiveConnection = conn
    	cmd.CommandText=Select name,id From table_abc Where id= ?" 
    	Set p1 = cmd.CreateParameter ("ID", adInteger, adParamInput)
    	cmd.Parameters.Append p1
    
    	' Select by Name
    	Set cmd = Server.CreateObject("ADODB.Command")
    	Set Session("AdoSelectName") =  cmd
    	Set cmd.ActiveConnection = conn
    	cmd.CommandText="Select name,id from table_abc where name like ?"
    	Set p2 = cmd.CreateParameter ("Name", adInteger, adParamInput)
    	cmd.Parameters.Append p2
    	
    end Sub
    
    sub Session_OnEnd
    	Session("AdoConnection").Close
    end sub
    
    </script>

    Here is a good reference for ASP: http://www.w3schools.com/asp/asp_globalasa.asp

    They also have some info on using ADODB with ASP.


    \_(ツ)_/




    • Edited by jrv Saturday, January 16, 2016 9:20 PM
    Saturday, January 16, 2016 9:01 PM
  • Here is an example of the ASP that uses the saved command object:

    <%@ language="VBSCRIPT" %>
    <html>
    <head>
    <meta name="GENERATOR" content="SAPIEN Technologies PrimalScript 2015">
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <title>Document Title</title>
    </head>
    <body>
    <table>
    <tbody>
    <%
    Set cmd = Session("AdoSelectID")
    cmd.Parameters("ID").Value = Request.QueryString("ID")
    Set rs = cmd.Execute()
    While Not rs.EOF
    	Response.Write "<tr>"
    	For Each f In rs.Fields
    		Response.Write "<td>" & f.Value & "</td>"
    	Next
    	Response.Write "<tr>"
    	rs.MoveNext
    Wend
    %>
    </tbody>
    </table>
    </body>
    </html>
    

    The issue you have is that you must have two command objects because you have two different queries.  One asks for name and the other by ID.  It makes sense to create to command objects.  They cost nothing until they are executed.  If you want name just get the command object that has the query by name.


    \_(ツ)_/

    Saturday, January 16, 2016 9:14 PM