Is it possible to copy a cfqueryparam?

[Perkley1]

I am using CF11 and I am trying to achieve the following:

<cfset request.myValue = 1>

<cfset request.dataType = "cf_sql_integer">

<cfquery>

      <cfsavecontent variable="scSQL">

          SELECT * from table

          WHERE Field1 > <cfqueryparam value="#request.myValue#" cfsqlType="#request.dataType#">

     </cfsavecontent>

     <cfset scSQL &= " AND Field2 > @Param1">

     #scSQL#

</cfquery>

To explain in words, I want to set the values with cfqueryparam for SQL Injection purposes, but I also want to add some additional criteria using the same cfqueryparam.  So the @Param1 is just my way of saying I want to use that same value.

This is simplified of what I am really doing, so no I can't just add another cfqueryparam line, the SQL is built in chunks by a script. It would be quite a task to know which value I needed.  Right now I have regular expressions getting the section and copying it multiple times and changing the fieldname.  If I don't have a cfqueryparam then it works fine.  If I do have a cfqueryparam then it gives me this error: "Invalid parameter binding(s)." SQLState:07009, even though it shows the query with (param1) (param2), etc.  It looks like it copied it fine, but somehow in the internals it doesn't know what it is.

Any ideas on how to reference or populate directly into the parameter bindings or to make this work?

One thought I have had is to make all the values become some string like "@VALUE_1_INT" and then build out my entire query and then very last convert all of these variables into cfqueryparams, but I will see if other ideas exist first.

[Carl Von Stetten · Accepted solution]

I don't think that will work.  You'll have to use two cfqueryparam tags in your where statement.  However, if you write the query in script using queryExecute, then you can use a named parameter and define it only once (I believe).