ColdFusion 11 Requires Accuracy When Naming Stored Procedure Parameters

CF 11 database activity log

In the process of finally banishing Adobe ColdFusion 8 from my application servers and completing the transition to CF 11, I came across what is apparently a little-known change in the operation of the CFPROCPARAM tag inside a CFSTOREDPROC tag.

After moving the website from CF 8 to CF 11, there were no major changes that had to be made. However, one particular action kept causing a dialog box to pop up:
generic error

I was able to trace the source of the dialog box to an error handler of sorts, but in this case, the error was quite nebulous. The error code “HANDLEDATABASEERROR” didn’t really tell me much. It appears that it may have come from Java itself.

After tracing back which function call seemed to be causing the problem, I discovered that the call to the SQL stored procedure was not using the correct parameter names – that is, names that matched the ones in the stored proc itself.

To determine if the database activity was actually the source of the error, I turned on Log Activity in the Data Source in the ColdFusion Administrator: Data Sources –> (specific Data Source Name) –> Show Advanced Settings –> Log Activity checkbox and text box showing where the log file will go. Example:

CF Log Activity info

After reloading the web page and doing the same action, the an error like the one below showed up in the dblogs.txt file:

spy(ajp-bio-8014-exec-6)(2018/09/26 12:42:30.326)>> java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Procedure or function 'sp_getNameSuggestion' expects parameter '@GoodParamName', which was not supplied. ErrorCode=201 SQLState=HY000
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Procedure or function 'sp_storedProcName' expects parameter '@GoodParamName', which was not supplied.
	at macromedia.jdbc.sqlserverbase.ddcw.b(Unknown Source)
	at macromedia.jdbc.sqlserverbase.ddcw.a(Unknown Source)
	at macromedia.jdbc.sqlserverbase.ddcv.b(Unknown Source)
	at macromedia.jdbc.sqlserverbase.ddcv.a(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddr.v(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddq.a(Unknown Source)
	at macromedia.jdbc.sqlserver.tds.ddr.c(Unknown Source)
	at macromedia.jdbc.sqlserver.dda3.m(Unknown Source)
	at macromedia.jdbc.sqlserverbase.dde7.e(Unknown Source)
	at macromedia.jdbc.sqlserverbase.dde7.a(Unknown Source)
	at macromedia.jdbc.sqlserverbase.ddd2.a(Unknown Source)
	at macromedia.jdbc.sqlserverbase.dde7.x(Unknown Source)
	at macromedia.jdbc.sqlserverbase.dde7.t(Unknown Source)
	at macromedia.jdbc.sqlserverbase.ddd2.execute(Unknown Source)
	at macromedia.jdbc.sqlserverbase.ddd6.execute(Unknown Source)
	at macromedia.jdbcspysqlserver.SpyPreparedStatement.execute(Unknown Source)
	at com.intergral.fusionreactor.jdbc.jdbc42.PreparedStatementSurrogate2.execute(PreparedStatementSurrogate2.java:200)
	at coldfusion.server.j2ee.sql.JRunPreparedStatement.execute(JRunPreparedStatement.java:101)
	at coldfusion.sql.Executive.executeCall(Executive.java:1087)
	at coldfusion.sql.Executive.executeCall(Executive.java:960)
	at coldfusion.sql.Executive.executeCall(Executive.java:910)
	at coldfusion.sql.SqlImpl.executeCall(SqlImpl.java:528)
	at coldfusion.tagext.sql.StoredProcTag.executeQuery(StoredProcTag.java:341)
	at coldfusion.tagext.sql.StoredProcTag.doEndTag(StoredProcTag.java:287)
...

The CFML source code calling this stored procedure was revealing:

<cfstoredproc procedure="dbo.sp_storedProcName" datasource="#getDSN()#">
	<cfprocparam cfsqltype="cf_sql_varchar" type="in" dbvarname="@BadParamName" value="#arguments.GoodParamData#" null="no" />
	<cfprocparam cfsqltype="cf_sql_integer" type="in" dbvarname="@OtherParam" value="#arguments.OtherParamData#" null="no" />
	<cfprocresult name="qResultSet" />
</cfstoredproc>

In some older versions of ColdFusion (at least as recently as version 8, perhaps all the way through 10), if the name of the parameter stored in the dbvarname attribute of the cfprocparam tag wasn’t correct, no error was thrown as long as the data types of the parameters matched in the correct order for both the cfstoredproc tag and the database stored procedure. This dbvarname attribute was essentially ignored in those versions. Version 11 changed all that.

By changing the source code to reflect the correct name, the bug was fixed.

<cfstoredproc procedure="dbo.sp_storedProcName" datasource="#getDSN()#">
	<cfprocparam cfsqltype="cf_sql_varchar" type="in" dbvarname="@GoodParamName" value="#arguments.GoodParamData#" null="no" />
	<cfprocparam cfsqltype="cf_sql_integer" type="in" dbvarname="@OtherParam" value="#arguments.OtherParamData#" null="no" />
	<cfprocresult name="qResultSet" />
</cfstoredproc>

Copying Windows Registry Keys from One User to Another

Windows Registry - regedit.exe

After switching the logged on user on a Windows Service, I found out that the original user had some printers set up that were not present in the new user’s profile.

While I could have manually set up the necessary printers on the new user, it seemed that there must be a way to do this that would ensure that all of the printers were set up correctly on the new user.

Printer information for each user is stored in the HKEY_CURRENT_USER\Printers registry key.

However, the HKEY_CURRENT_USER hive only shows registry information for whatever user you’re logged on as. What if you wanted to copy from one user (other than you), to some other user?

The HKEY_CURRENT_USER key for each user is found in the registry under the HKEY_USERS hive, under each user’s SID. If you don’t have an easy way to find out what a user’s SID is, what then?

There is a solution! The information that is displayed in the HKEY_CURRENT_USER hive is stored in the NTUSER.DAT file in each user’s profile.

Each user’s hive can be loaded either from the Registry Editor or the command line. Both of these must be started as an Administrator to have the privileges to do this.

The example below shows how to do this from a command line. If the user whose hive to be copied is called OLDUSER, the following command (reg load) should attach the OLDUSER hive as HKEY_USERS\OLDUSER. This can be run from Command Prompt or PowerShell.

reg load C:\Users\OLDUSER\NTUSER.DAT

Do the same thing with the new user (NEWUSER):

reg load C:\Users\NEWUSER\NTUSER.DAT

Note: If either of these users is currently logged on or running a service, they must first be logged off or the service stopped.

In the Registry Editor, export (in this case) the Printers key from HKEY_USERS\OLDUSER into a .reg file.

Open the .reg file with an editor and replace all instances of OLDUSER with NEWUSER. Save the file. Execute the file by double-clicking on it. Confirm the dialog box to add the information to NEWUSER’s hive.

confirm registry change

To unload both users’ hives, execute the following commands:

reg unload OLDUSER
reg unload NEWUSER

(Remember to restart any Services that were stopped before loading!)

To verify that the information was copied correctly, you can log on as the new user and examine its HKEY_CURRENT_USER hive.

Overriding the USER_TABLES and USER_VIEWS Public Synonyms

Oracle Database logo

To increase the level of security on one of the applications I support, I had to create a new user (we’ll call this one APPUSERREAD) in our Oracle Database that had read-only access to the previous application user schema (and this one will be APPUSER).

One of the challenges at this point was to avoid changing the queries that were hard-coded into the application. Unfortunately, these queries referenced tables without the schema name, assuming the default schema.

So, the first step was to alter the default schema of the APPUSERREAD account each time a session was created. Aside from adding the required code into the application, it seemed that a trigger would be the best option to do this.

The code below creates a trigger for the new schema that resets the default schema at each logon. This would have to be run while logged on as APPUSERREAD.

CREATE OR REPLACE TRIGGER LOGON_TRG 
  AFTER LOGON ON SCHEMA
BEGIN
     EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = APPUSER';
EXCEPTION 
  WHEN OTHERS 
    THEN NULL; -- prevent a login failure due to an exception
END;

Though this fixes the problem of omitting references to the APPUSER schema in the queries, we weren’t done yet.

As it turns out, the code also runs SELECT statements against two public synonyms: USER_TABLES and USER_VIEWS.

Even changing the default schema will not change the results of a query against these views.

Selecting USER_TABLES returns all tables owned by the logged on user (not schema), and USER_VIEWS similarly returns all views owned by the user.

To get something similar to the contents of the USER_TABLES for the APPUSER account when logged on as APPUSERREAD (or any other account), this query will give you what you need:

SELECT * FROM ALL_TABLES WHERE OWNER = 'APPUSER';

For USER_VIEWS, the query is similar:

SELECT * FROM ALL_VIEWS WHERE OWNER = 'APPUSER';

The only problem is that there is an OWNER column present in both of these result sets, whereas the OWNER is known as the current user for USER_TABLES and USER_VIEWS.

The solution was to create views in the original schema that only had the needed columns – and in the correct order – such that the new views mirrored the public synonyms.

To get the list of columns and their order, I used the LISTAGG command to return a comma-delimited list that would become the column list for each new view.

For USER_TABLES:

SELECT LISTAGG(COLUMN_NAME,',')
WITHIN GROUP (ORDER BY COLUMN_ID)
FROM ALL_TAB_COLUMNS 
WHERE TABLE_NAME='USER_TABLES'
ORDER BY COLUMN_ID;

For USER_VIEWS:

SELECT LISTAGG(COLUMN_NAME,',')
WITHIN GROUP (ORDER BY COLUMN_ID)
FROM ALL_TAB_COLUMNS 
WHERE TABLE_NAME='USER_VIEWS'
ORDER BY COLUMN_ID;

Using the results from these queries, I built new views:

APPUSER.USER_TABLES:

CREATE VIEW APPUSER.USER_TABLES AS
SELECT TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, IOT_NAME, STATUS, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BACKED_UP, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS, DEGREE, INSTANCES, CACHE, TABLE_LOCK, SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED, IOT_TYPE, TEMPORARY, SECONDARY, NESTED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, ROW_MOVEMENT, GLOBAL_STATS, USER_STATS, DURATION, SKIP_CORRUPT, MONITORING, CLUSTER_OWNER, DEPENDENCIES, COMPRESSION, COMPRESS_FOR, DROPPED, READ_ONLY, SEGMENT_CREATED, RESULT_CACHE, CLUSTERING, ACTIVITY_TRACKING, DML_TIMESTAMP, HAS_IDENTITY, CONTAINER_DATA, INMEMORY, INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION, INMEMORY_DUPLICATE
FROM ALL_TABLES 
WHERE OWNER = 'APPUSER';

APPUSER.USER_VIEWS:

CREATE VIEW APPUSER.USER_VIEWS AS
SELECT VIEW_NAME, TEXT_LENGTH, TEXT, TEXT_VC, TYPE_TEXT_LENGTH, TYPE_TEXT, OID_TEXT_LENGTH, OID_TEXT, VIEW_TYPE_OWNER, VIEW_TYPE, SUPERVIEW_NAME, EDITIONING_VIEW, READ_ONLY, CONTAINER_DATA, BEQUEATH, ORIGIN_CON_ID
FROM ALL_VIEWS 
WHERE OWNER = 'APPUSER';

Since the default schema of APPUSERREAD is now APPUSER, a call to USER_TABLES or USER_VIEWS without specifying the schema, will retrieve these instead of the public synonyms.

Problem solved!

Error when Migrating SOAP Web Services from Adobe ColdFusion 8 to CF 11

ColdFusion logo

After moving a CF 8 app to CF 11, I ran into a difficulty in trying to set up the Web Service in the CF 11 Administrator.

CF Administrator error message

Since I couldn’t get the CF 11 server to accept a new entry in the Web Services list, I had to do a little more troubleshooting using what was already there. I changed the HOSTS file on the CF 11 server such that the name old server running the Web Service successfully on CF 8 would temporarily point at the new CF 11 server. This caused an interesting error!

The code that came back is below – slightly edited for readability and also to make if render in a browser correctly:

<!-- AxisFault faultCode: {http://xml.apache.org/axis/}HTTP faultSubcode: faultString: (401)Unauthorized faultActor: faultNode: faultDetail: {}:return code: 401 -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>IIS 8.5 Detailed Error - 401.2 - Unauthorized</title> <style type="text/css"> <!-- body{margin:0;font-size:.7em;font-family:Verdana,Arial,Helvetica,sans-serif;} code{margin:0;color:#006600;font-size:1.1em;font-weight:bold;} .config_source code{font-size:.8em;color:#000000;} pre{margin:0;font-size:1.4em;word-wrap:break-word;} ul,ol{margin:10px 0 10px 5px;} ul.first,ol.first{margin-top:5px;} fieldset{padding:0 15px 10px 15px;word-break:break-all;} .summary-container fieldset{padding-bottom:5px;margin-top:4px;} legend.no-expand-all{padding:2px 15px 4px 10px;margin:0 0 0 -12px;} legend{color:#333333;;margin:4px 0 8px -12px;_margin-top:0px; font-weight:bold;font-size:1em;} a:link,a:visited{color:#007EFF;font-weight:bold;} a:hover{text-decoration:none;} h1{font-size:2.4em;margin:0;color:#FFF;} h2{font-size:1.7em;margin:0;color:#CC0000;} h3{font-size:1.4em;margin:10px 0 0 0;color:#CC0000;} h4{font-size:1.2em;margin:10px 0 5px 0; }#header{width:96%;margin:0 0 0 0;padding:6px 2% 6px 2%;font-family:"trebuchet MS",Verdana,sans-serif; color:#FFF;background-color:#5C87B2; }#content{margin:0 0 0 2%;position:relative;} .summary-container,.content-container{background:#FFF;width:96%;margin-top:8px;padding:10px;position:relative;} .content-container p{margin:0 0 10px 0; }#details-left{width:35%;float:left;margin-right:2%; }#details-right{width:63%;float:left;overflow:hidden; }#server_version{width:96%;_height:1px;min-height:1px;margin:0 0 5px 0;padding:11px 2% 8px 2%;color:#FFFFFF; background-color:#5A7FA5;border-bottom:1px solid #C1CFDD;border-top:1px solid #4A6C8E;font-weight:normal; font-size:1em;color:#FFF;text-align:right; }#server_version p{margin:5px 0;} table{margin:4px 0 4px 0;width:100%;border:none;} td,th{vertical-align:top;padding:3px 0;text-align:left;font-weight:normal;border:none;} th{width:30%;text-align:right;padding-right:2%;font-weight:bold;} thead th{background-color:#ebebeb;width:25%; }#details-right th{width:20%;} table tr.alt td,table tr.alt th{} .highlight-code{color:#CC0000;font-weight:bold;font-style:italic;} .clear{clear:both;} .preferred{padding:0 5px 2px 5px;font-weight:normal;background:#006633;color:#FFF;font-size:.8em;} --> </style> </head> <body> <div id="content"> <div class="content-container"> <h3>HTTP Error 401.2 - Unauthorized</h3> <h4>You are not authorized to view this page due to invalid authentication headers.</h4> </div> <div class="content-container"> <fieldset><h4>Most likely causes:</h4> <ul> <li>No authentication protocol (including anonymous) is selected in IIS.</li> <li>Only integrated authentication is enabled, and a client browser was used that does not support integrated authentication.</li> <li>Integrated authentication is enabled and the request was sent through a proxy that changed the authentication headers before they reach the Web server.</li> <li>The Web server is not configured for anonymous access and a required authorization header was not received.</li> <li>The "configuration/system.webServer/authorization" configuration section may be explicitly denying the user access.</li> </ul> </fieldset> </div> <div class="content-container"> <fieldset><h4>Things you can try:</h4> <ul> <li>Verify the authentication setting for the resource and then try requesting the resource using that authentication method.</li> <li>Verify that the client browser supports Integrated authentication.</li> <li>Verify that the request is not going through a proxy when Integrated authentication is used.</li> <li>Verify that the user is not explicitly denied access in the "configuration/system.webServer/authorization" configuration section.</li> <li>Create a tracing rule to track failed requests for this HTTP status code. For more information about creating a tracing rule for failed requests, click <a href="http://go.microsoft.com/fwlink/?LinkID=66439">here</a>. </li> </ul> </fieldset> </div> <div class="content-container"> <fieldset><h4>Detailed Error Information:</h4> <div id="details-left"> <table border="0" cellpadding="0" cellspacing="0"> <tr class="alt"><th>Module</th><td>&nbsp;&nbsp;&nbsp;IIS Web Core</td></tr> <tr><th>Notification</th><td>&nbsp;&nbsp;&nbsp;AuthenticateRequest</td></tr> <tr class="alt"><th>Handler</th><td>&nbsp;&nbsp;&nbsp;ISAPI-dll</td></tr> <tr><th>Error Code</th><td>&nbsp;&nbsp;&nbsp;0x80070005</td></tr> </table> </div> <div id="details-right"> <table border="0" cellpadding="0" cellspacing="0"> <tr class="alt"><th>Requested URL</th><td>&nbsp;&nbsp;&nbsp;http://mywebsite:80/jakarta/isapi_redirect.dll</td></tr> <tr><th>Physical Path</th><td>&nbsp;&nbsp;&nbsp;D:\ColdFusion11\config\wsconfig\1\isapi_redirect.dll</td></tr> <tr class="alt"><th>Logon Method</th><td>&nbsp;&nbsp;&nbsp;Not yet determined</td></tr> <tr><th>Logon User</th><td>&nbsp;&nbsp;&nbsp;Not yet determined</td></tr> </table> <div class="clear"></div> </div> </fieldset> </div> <div class="content-container"> <fieldset><h4>More Information:</h4> This error occurs when the WWW-Authenticate header sent to the Web server is not supported by the server configuration. Check the authentication method for the resource, and verify which authentication method the client used. The error occurs when the authentication methods are different. To determine which type of authentication the client is using, check the authentication settings for the client. <p><a href="http://go.microsoft.com/fwlink/?LinkID=62293&amp;amp;IIS70Error=401,2,0x80070005,9600">View more information &amp;raquo;</a></p> <p>Microsoft Knowledge Base Articles:</p> <ul><li>907273</li><li>253667</li></ul> </fieldset> </div> </div> </body> </html> {http://xml.apache.org/axis/}HttpErrorCode:401 

When rendered, this code would look like this:

HTTP 401.2 error from IIS

This reminded me of a problem I’d had before!

Sure enough, Anonymous Access was not enabled on the jakarta virtual directory. After enabling that and disabling Windows Authentication on that folder, everything worked properly!