Highlighted

Is it possible to copy a cfqueryparam?

Community Beginner ,
Sep 06, 2016

Copy link to clipboard

Copied

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.

Most Valuable Participant
Correct answer by Carl_Von_Stetten | Most Valuable Participant

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).

Views

276

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Is it possible to copy a cfqueryparam?

Community Beginner ,
Sep 06, 2016

Copy link to clipboard

Copied

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.

Most Valuable Participant
Correct answer by Carl_Von_Stetten | Most Valuable Participant

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).

Views

277

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 06, 2016 0
Most Valuable Participant ,
Sep 07, 2016

Copy link to clipboard

Copied

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).

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 07, 2016 1