tracker issue : CF-3919479

Problem Description:

    Prior to CF11 patch 3, variable names in the dbvarname of the cfprocparam tags were ignored, and parameters were passed in the order defined. As a result of this change, any declarations of dbvarname in the application that does not match the actual name of the parameter in the database procedure fails.

dbvarname should be ignored unless we set the value as indicated in the documentation at https://wikidocs.adobe.com/wiki/display/coldfusionen/cfprocparam 

Steps to Reproduce:

    1. Using an Oracle database connection. 

     2. Create a procedure to be called that has a named parameter.

        PROCEDURE new_session (
            sido OUT VARCHAR2,
            usernamex VARCHAR2,
            passwordx VARCHAR2,
            ip_addressx VARCHAR2,
            rand_intx INTEGER,
            app_idx NUMBER,
            check_ipx NUMBER,
            pwd_requiredx NUMBER DEFAULT 1
            );

2. Use that procedure in a cfprocparam with some parameter name misspelled:

       <cfstoredproc procedure="td_session_pkg.new_session" datasource="#application.datasource#">
           <cfprocparam type="Out" cfsqltype="CF_SQL_CHAR" dbvarname="SID_xo" variable="sid">
           <cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="USERNAMEx" value="#arguments.username#">
          <cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="PASSWORDx" value="#arguments.password#">
          <cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="IP_ADDRESSx" value="#cgi.remote_addr#">
          <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="RAND_INTx" value="#rand_int#">
          <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="APP_IDx" value="#application.app_id#">
          <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="CHECK_IPx" value="1">
          <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="pwd_requiredx" value="#pwdRequired#">
    </cfstoredproc>

Actual Result:

    1. On CF11 prior to patch 3, stored procedure executes.
    2. After patch 3 upgrade, After patch 3, we get an exception:

         Error Executing Database Query.

         ORA-06550: line 1, column 34: PLS-00201: identifier 'SID_XO' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored


Expected Result:

       Query will execute as before, and not produce errors (query parameters will be passed in order instead of by name).

Any Workarounds:

      Removing the dbvarname from the cfprocparam tags, or painstakingly verify the names that have been used over the years to make sure that they match the database parameter names.

      Either of these are extremely risky to a previously well functioning application that has a LOT of cfstoredproc calls.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3919479

External Customer Info:
External Company:  
External Customer Name: TheraRob
External Customer Email:  
External Test Config: My Hardware and Environment details:



    ColdFusion 11 on Windows 2012 R2 server

    Oracle 11 database

    Oracle thin driver.

Attachments:

Comments: