cfsavecontent display double apostrophe in SQL statement

Question

I have several OR in my SQL statement so I want to save a chuck of it in a cfsavecontent. Here is that part:

<cfsavecontent variable="checkDepartment">
    <cfif #wrkDept# EQ #dept[2][1]#>
        Department = 'Health' AND
    <cfelse>
    Department = '#wrkDept#' AND
    </cfif>
</cfsavecontent>

But the error I get on the page shows 2 sets of apostrophes around the word Health.

SQL   
 SELECT COUNT(*) AS numItems
 FROM   IT_PROJECTS
 WHERE 
 Department = ''Health'' AND
 status = 'Cancelled'

Can anyone help me to only get a single apostrophe? Thanks

Possible duplicate of [ColdFusion adding extra quotes when constructing database queries in strings](https://stackoverflow.com/questions/266586/coldfusion-adding-extra-quotes-when-constructing-database-queries-in-strings) — SOS, Sep 19 '19 at 13:26
This is a common question. Short answer, CF does it deliberately to prevent a common form of [sql injection](https://stackoverflow.com/questions/266586/coldfusion-adding-extra-quotes-when-constructing-database-queries-in-strings). Although you could use PreserveSingleQuotes to avoid it - *don't* - it's a sql injection risk. For modern versions, the best option is to use query parameters. Learn more about [queryExecute and query parameters](https://cfdocs.org/queryexecute). — SOS, Sep 19 '19 at 13:32
What version of CF and what is the context? Also, why `cfsavecontent`? And there might be a better way to build this query. Are you able to share a pseudocode version of what you want to do? — Shawn, Sep 19 '19 at 13:38
Thank you for your time. My WHERE has several status = 'Approved' OR status = 'Not Approved' OR status = 'Cancelled'. But each of those as a cfif about the department. Rather than repeat the cfif multiple times, I wanted to save it to a variable. Then i found the cfsavecontent tag and went with that. Is there another way to repeat the content within my WHERE clause? thanks again — Michael, Sep 19 '19 at 13:43
@Ageax, how can i use PreserveSingleQuotes to prevent it? this is an internal page only (can't access externally) so I dont have to worry about injections. Thanks — Michael, Sep 19 '19 at 13:48
@MichaelDeDonato You _always_ have to worry about injections. Insider threats, both intentional and accidental, are your biggest security threat. And an accidental injection will still ruin your day. Plus there are other benefits to `cfqueryparam`. — Shawn, Sep 19 '19 at 13:50
Give me a minute and I'll throw together an example of a dynamic `WHERE` like this. — Shawn, Sep 19 '19 at 13:52
Hi @Michael I've tried your code with my test page. I could not able to replicate your issue. The savecontent variable give the result like SQL SELECT COUNT(*) AS numItems FROM IT_PROJECTS WHERE Department = 'Health' AND status = 'Cancelled' I can't see any double quotes. So the issue may be differ. If you give details code / screen shot of an issue I will try to help you. — Kannan.P, Sep 19 '19 at 14:13
@Michael - Ignoring the dynamic syntax for a sec - why do you need separate cfif's for the same field? If the variable contains a value like "health", etc... just use it in the sql, i.e. `WHERE ColumnName = ` — SOS, Sep 19 '19 at 15:22
... why the special exception for `#wrkDept# eq #dept[2][1]#` ? Could you elaborate on what you mean by "*each of those as a cfif about the department.*, because there may be better way to write the sql. — SOS, Sep 19 '19 at 15:36
You also say that you have several `OR`s in your SQL, but your example shows `AND`. They do have a significant difference in a query. On a basic level, what is the structure of your query with `OR` in it? Is it `WHERE (this=1 AND that=2) OR (this=1 AND that=3)` or something like that? Notice my inclusion of parenthesis. (There's also a better way to write that statement.) — Shawn, Sep 19 '19 at 16:56
I find it much easier to dynamically build queries in cfscript, and then using queryExecute() to run the query. — Redtopia, Sep 19 '19 at 17:26

score 0 · Answer 1 · answered Sep 20 '19 at 00:06

So this answer seems a lot more complicated than it really is. And without knowing specifically what your query looks like (re:OR conditions), I'm not really sure how to structure it. It can be better. The goal should be to make one single trip to your SQL server with the query that makes the most sense for the data you're trying to get. I'm not sure what you are trying to do with cfsavecontent, but I don't think you need it.

The bulk of my example query (https://trycf.com/gist/4e1f46bfa84a6748aced0f9ee8221c6d/acf2016?theme=monokai) is setup. I chose to go with a cfscript format, because as Redtopia said, I also find it much easier to build a dynamic query in cfscript.

After initial setup, I basically just script out the variables I'll use in my final queryExecute().

// Base query.
qry = "SELECT count(*) AS theCount FROM IT_PROJECTS WHERE 1=1 " ;
// This is our dynamic filter that we build below.
qfilter = {} ;
// Query options. 
opts =  { "dbtype":"query" } ;

After we have our base, I build up the dynamic part of the query. This is the part that will likely change quite a bit depending on your current needs and setup.

For the first part, I basically replaced your cfif with a ternary evaluation. I'm not sure how your data plays into the evaluation of dept or where that array comes from. But from there I build a basic included statement of the query and set up the queryparam values for it. Then I add a second check that will pick a different set of values for the query (currently based on even/odd seconds). Again, I'm not sure of the intent of your query here, so I just made something dynamic.

  //////////// BUILD DYNAMIC FILTER ////////////

  qdept = ( wrkDept == dept[2][1] ) ? 'Health' : wrkDept ;
  /// This one is an included filter:
  qry &= " AND department = :dpt AND status = :sts " ;
  qfilter.dpt = {"value":qdept,"cfsqltype":"CFSQLVARCHAR"} ;
  qfilter.sts = {"value":"Cancelled","cfsqltype":"CFSQLVARCHAR"} ;

  /// Adding Dynamic ORs

  // Dynamically set status based on even/odd seconds.
  qStatus = ( now().second()%2==0) ? "Cancelled" : "Active" ;
  qry &= " OR ( department = :dpt2 AND status = :sts2 ) " ;
  qfilter.dpt2 = {value:"IT",cfsqltype:"CFSQLVARCHAR"} ;
  qfilter.sts2 = {value:qStatus,cfsqltype:"CFSQLVARCHAR"} ;

This gives us a SQL string that looks like:

SELECT count(*) AS theCount 
FROM IT_PROJECTS 
WHERE 1=1 
    AND department = :dpt AND status = :sts 
    OR 
    ( department = :dpt2 AND status = :sts2 )

With a SQL statement, the placement of AND and OR conditions can greatly impact the results. Use parenthesis to group conditions how you need them.

After we've built the query string, we just have to plug it and our queryparams into the queryExecute().

  result = queryExecute( qry , qfilter , opts ) ;

And if we want to output our data, we can go:

  writeOutput("There are " & result.theCount & " records." ) ;

Which gives us:

There are 8 records.

Again, I don't know what your main conditions look like. If you can give me an example of a query with a bunch of ORs and ANDs, I'll try to modify this for you.

cfsavecontent display double apostrophe in SQL statement

1 Answers1