3

I am using ColdFusion 10 Update 23 with MySQL database. When I make a change to a script where I am using cfqueryparm the script causes this error message: "The type for attribute value of tag queryparam could not be determined."

This script works perfectly:

<cfquery name="i" datasource="tasktrack">
UPDATE qa_commitments
SET 
    commit_division = <cfqueryparam cfsqltype='cf_sql_integer' value='#trim(commit_division)#'/>,
    commit_source = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(commit_source))#'/>,
    commit_title = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(commit_title))#'/>,
    commit_responsibility = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(commit_responsibility))#'/>,
    <cfif Len(commit_cause)>commit_cause = <cfqueryparam cfsqltype='cf_sql_varchar' value='#commit_cause#'/>
    <cfelse>commit_cause = NULL</cfif>,
    commit_comments = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(PreserveSingleQuotes(commit_comments)))#'/>,
    commit_issue_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_issue_date#'/>,
    commit_response_due_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_response_due_date#'/>,
    commit_response_compl_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_response_compl_date#'/>,
    commit_action_due_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_action_due_date#'/>
    <cfif IsDefined('commit_closeing_date')> ,commit_closeing_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_closeing_date#'/></cfif>
WHERE ID = #id#

If I open the script and add anything (i.e, a return, a tab, a comment), save the script I get the above error. If I restore the script from a older version it works fine again. I open the older one insert a line break w/enter key, save it and then it breaks again. The really weird part is if I remove the cfqueryparam tags completely the script works again. This happens on every script where I am using this tag. The last update to CF was April 2017 and there are several scripts newer that work unless I edit them. I have tried different editors with the same results. I have googled my butt off with no results. Can someone please point me in the right direction?

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
GThurmon
  • 33
  • 4
  • Sounds like you may need to clear your query cache in the CFAdministrator – snackboy Oct 24 '17 at 19:29
  • Add the phrase `id = id` right after the word `set`, and comment out the rest of your fields. Run the page. Then uncomment one field at a time and run the page until you identify the culprit. `commit_division` looks suspicious because you are using trim on something that's supposed to be an integer. – Dan Bracuk Oct 24 '17 at 19:41
  • I cleared the query as suggested but it made no difference. – GThurmon Oct 24 '17 at 20:04
  • the commit_division field was the edit I was trying to make. Changing it from an interger to text. I left the trim in after it error out when I made it varchar. – GThurmon Oct 24 '17 at 20:11
  • As I mentioned in the post this is a system wide issue and it is not just this script. I get the same error when I edit a script that has been running for two years. – GThurmon Oct 24 '17 at 20:13
  • Just to convince myself, I tried the id=#id# by itself and it worked, so I added the nest line in and it error. "commit_division = – GThurmon Oct 24 '17 at 20:17
  • I played musical chairs with each line and for any line that contained a cfqueryparam it error. If id= id worked I thought I would try SET id= but it failed. For some reason my coldfusion no longer works with cfqueryparam. – GThurmon Oct 24 '17 at 20:24
  • Why is id in parens? And I assume it's a typo by cf_sql_integer is spelled incorrectly. – snackboy Oct 24 '17 at 20:25
  • Sorry, yes just a typo integer. I tried with '' and without no difference. Normally I don't '' integers but at this point I am second guessing Everything. I tried it both ways. Same error message. – GThurmon Oct 24 '17 at 20:44
  • The above code works, correct? Can you post code that doesn't work? – snackboy Oct 25 '17 at 00:31
  • If I open the script that works correctly, make a simple edit like add a line return to the bottom of the file, save the file. Run it and it will error out with the above error message. Nothing I do will fix the file except restore it from backup. Once the file is re-saved ti fails. – GThurmon Oct 25 '17 at 01:08
  • Does not matter what text editor I use, if I edit it it fails. Except if I remove all cfqueryparam tags then it works. I am beginning to think that a recent change to McAfee by the IT guys is causing this, but I can't prove. McAfee is the cause of a lot of my issues on this server. – GThurmon Oct 25 '17 at 01:09
  • What text editor are you using? – snackboy Oct 25 '17 at 01:10
  • Eclipse, notpad, notepad plus even had another user edit and save with same results. – GThurmon Oct 25 '17 at 01:16
  • Hmmm...it almost sounds like a special characters are getting inserted. You mentioned McAfee - do you have access to the file directly from the server? – snackboy Oct 25 '17 at 01:21
  • Yes, via share and I even tried with remote desktop directly to the server. Everything but go to the server room and change it directly on the server itself. Do you know if using the cfqueryparam tag calls or uses a java like file that may being accessed at the time the script runs and then McAfee would try and scan 'scriptscan' the file and stop it from being interpreted correctly? – GThurmon Oct 25 '17 at 01:38
  • Considering that all of CF is built on java, then yes. But it wouldn't explain as to why the files work before they are edited unless something is affecting them because of their file date. Do you have a dev/test environment where McAfee could be turned off? – snackboy Oct 25 '17 at 01:57

2 Answers2

1

I can't tell you why it is broke, but I can tell you how I would fix it.

Putting <cfif>s inside of queries makes it so that the DB engine cannot cache the query. So I would move them out, and out the conditional logic within the query.

<cfquery name="i" datasource="tasktrack">
DECLARE @commit_clause varchar(40) = <cfqueryparam cfsqltype='cf_sql_varchar' value='#commit_cause#' null="#IIF(len(commit_clause)1, 0)#"/>
DECLARE @commit_closeing_date date = cfqueryparam cfsqltype='cf_sql_date' value='#commit_closeing_date#' null="#IIF(isDefined(Commit_closeing_date), 0, 1)#"/>.

UPDATE qa_commitments
SET 
    commit_division = <cfqueryparam cfsqltype='cf_sql_integer' value='#trim(commit_division)#'/>,
    commit_source = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(commit_source))#'/>,
    commit_title = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(commit_title))#'/>,
    commit_responsibility = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(commit_responsibility))#'/>,
    commit_cause = @commit_clause,
    commit_comments = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(PreserveSingleQuotes(commit_comments)))#'/>,
    commit_issue_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_issue_date#'/>,
    commit_response_due_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_response_due_date#'/>,
    commit_response_compl_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_response_compl_date#'/>,
    commit_action_due_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_action_due_date#'/>,
    commit_closeing_date = @commit_closeing_date
WHERE ID = #id# /* I would fix this too */
</cfquery>

Off topic

I would consider using Entities. I really don't like writing this stuff over and over.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
0

I found the problem and the problem was McAfee scriptscan. I had IT guys turn it off and now the script works. IT is blaming it on the old version of Windows Server 2008 and it having a problem with McAfee Enterprise. Thanks for all the help. It really does help just to have someone to walk you through things.

GThurmon
  • 33
  • 4