Is there a logical reason to use CFQUERYPARAM in a query request?

Question

First of all, I use CFQUERYPARAM to prevent SQL injection. Since Query-of-Queries (QoQ) does not apply to the database, is there a logical reason to use CFQUERYPARAM in them? I know that values ​​that do not match cfsqltype and maxlength an exception, but these values ​​should already be checked before that and display friendly messages (from a UX point of view).

Answer 1

Since Query-of-Queries (QoQ) does not apply to the database, is there a logical reason for using CFQUERYPARAM in them? In fact, this applies to the database, the database that you currently store in memory. The data in this database can still theoretically be faked using some kind of injection from the user. It affects your physical database - no. It affects the use of data in your application - yes.

You did not specify any specific details, but I would be mistaken on the side of caution. If ANY data that you use to create the query comes from the client, then use the cfqueryparam in it. If you can guarantee that none of the elements of your request comes from the client, I think that it would not be to use cfqueryparam .

As an aside, using cfqueryparam also helps optimize the query for the database, although I'm not sure if this is true for querying queries. It also eludes characters for you, like apostrophes.

Answer 2

Here is a situation where it is easier, in my opinion.

 <cfquery name="NoVisit" dbtype="query"> select chart_no, patient_name, treatment_date, pr, BillingCompareField from BillingData where BillingCompareField not in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#ValueList(FinalData.FinalCompareField)#" list="yes">) </cfquery> 

An alternative would be to use a QuotedValueList. However, if anything in this list of values ​​contains an apostrophe, cfqueryparam will escape it. Otherwise, I have to.

Editing starts here.

Here is another example where unused query parameters cause an error.

 QueryAddRow(x,2); QuerySetCell(x,"dt",CreateDate(2001,1,1),1); QuerySetCell(x,"dt",CreateDate(2001,1,11),2); </cfscript> <cfquery name="y" dbtype="query"> select * from x <!--- where dt in (<cfqueryparam cfsqltype="cf_sql_date" value="#ValueList(x.dt)#" list="yes">) ---> where dt in (#ValueList(x.dt)#) </cfquery> 

The written code causes this error:

 Query Of Queries runtime error. Comparison exception while executing IN. Unsupported Type Comparison Exception: The IN operator does not support comparison between the following types: Left hand side expression type = "DATE". Right hand side expression type = "LONG". 

With the request parameter commented above, the code succeeds.