Can i use cfif in cfquery?

Question

Is it possiblt to Use <cfif> inside <cfquery>?

I have a <cfquery> in a Function like below

<cfquery name = "UpdateRecord"   
   dataSource = #arguments.dbSource#   
   username = #arguments.dbUser#   
   password = #arguments.dbPass# 
   result="updateResult" >  
 <cfoutput>#preserveSingleQuotes(arguments.updateQuery)#/cfoutput>
 </cfquery>

I want to check for TimeoutArgument and make it like below

<cfquery name = "UpdateRecord"   
       dataSource = #arguments.dbSource#   
       username = #arguments.dbUser#   
       password = #arguments.dbPass# 
                            timeout = #arguments.Timeout#>
       result="updateResult" >  
     <cfoutput>#preserveSingleQuotes(arguments.updateQuery)#/cfoutput>
 </cfquery>

Timeout is Optional argument, I wan to make 1st query like second query if only Timeout is passed. How can I do that?

Thanks,

Answer 1

No, you can't use a cfif in the attribute section of cfquery. You can, however, set a default value:

<cfargument name="arguments.timeout" default="60" />

Then just use the query with arguments.timeout as specified. It will be 60 seconds by default (or some sane value). If the user specifies a value, then that will be used instead. In my opinion this is better than having two separate cfquery statements.

The livedocs for cfargument.

Answer 2

By the way - what's the point of such a thing:

<cfquery 
  name       = "UpdateRecord"   
  dataSource = "#arguments.dbSource#"   
  username   = "#arguments.dbUser#"   
  password   = "#arguments.dbPass#" 
  timeout    = "#arguments.Timeout#"
  result     = "updateResult"
>
  <cfoutput>#preserveSingleQuotes(arguments.updateQuery)#</cfoutput>
</cfquery>

You are passing in every single bit of info that makes a cfquery, including the actual SQL code. This makes no sense whatsoever, you could just as well use cfquery then and there, instead of invoking such an over-generalized function. It would even be less code.

Apart from that,

• PreserveSingleQuotes(completeSqlString) disables any SQL injection checks ColdFusion has, leaving this function wide open for abuse and SQL syntax errors.
• <cfoutput> is on by default within CF tag bodies. The <cfoutput> is therefore redundant in the above.

Abstraction is good and everything, but the above is completely pointless, and dangerous at that.

Answer 3

NOTE: As other have pointed out: you're almost certainly doing things wrong, and are most likely are creating an insecure and inefficient application that will be a nightmare to maintain.

However, in response to your specific question, yes, you can (indirectly) use cfif to control cfquery attributes - by passing a struct to the magic attribute AttributeCollection, like so:

<cfset var QueryAttributes = StructNew() />

<cfset QueryAttributes.Datasource  = Arguments.dbSource />
<cfset QueryAttributes.Username    = Arguments.dbUser />
<cfset QueryAttributes.Password    = Arguments.dbPass />
<cfif StructKeyExists(Arguments,'Timeout')>
 <cfset QueryAttributes.Timeout = Arguments.Timeout />
</cfif>

<cfquery name="UpdateRecord" result="updateResult"
 AttributeCollection="#QueryAttributes#"
    >
 ...
</cfquery>

(This functionality was introduced with CF8.)