Output variables from database in CFSAVECONTENT

[paul_8809]

I have HTML code, containing dynamic variables, stored in a database in a field named "data',. An example of raw data in that field would be:

<a href="<cfoutput>#application.siteroot#</cfoutput>/?utm_campaign=transactional&utm_source=emails.logo&utm_medium=email"><img src="<cfoutput>#application.siteroot/logo10-180.png"></a>

Within a cfc, I have code that sets this data in a <cfcontent> tag as follows:

<cfsavecontent variable="emailBody">
  <cfoutput>#vData#</cfoutput>
</cfsavecontent>

However, when I tried to insert "emailBody" into an email and send it, the resulting email does not evaluate the variables in the email body and the email ends up looking like this:

What am I doing wrong?

[Charlie Arehart]

Update to my original comment, for future readers of this thread: skip to my new reply thread here a couple days later, where I came to better understand the problem. (I leave the rest here and the back and forth which follows, for the sake of context.) Or of course see other reply threads here, from others. I just am referring to the replies between Paul and me the first day about this first reply of mine.

Focus first on outputting the value right after the cfsavecontent. Is it correct then? If so, it would not become incorrect simply because the same variable result was put in a cfmail. 

(And if you'd contend that you "can't output the variable there", log it instead with cflog.) 

Some may propose also/instead that you put a cfoutput within the cfmail, but you have it already in the cfsavecontent. That's why I assert that if that same variable is passed to the cfmail, the cfml should already be evaluated. But if you try it and it works, something else was at play in your code. 

Finally, some would want to discourage the approach of storing cfml code in a db, to then run dynamically. I'll leave that debate for them to contend with you. (That said, nothing in your code or the remaining discussion conveys where the db involvement is. It may or may not be significant.) 

PS I'll change your title, as you said cfcontent when your clearly meant cfsavecontent. 

[paul_8809]

Hi Charlie, I tried but placing the cfoutput below the cfsavecontent did not work in displaying the variables. I'll keep trying

[Charlie Arehart]

I didn't suggest that. Please re-read carefully. And I offered a couple of suggestions. 

[paul_8809]

Sorry, I don't know what you mean. 

Noted your other suggestions. I don't have a comment on those right now, but considering giving up on the database storage idea. 

Thanks for the guidance!

[Charlie Arehart]

Sorry, I was distracted when I wrote that last reply. So you're saying you output the emailbody variable right after the cfsavecontent, the values within are not resolved?  They should be...and you're finding that they are not resolved? What if you run this simple example, in whatever cf template you want to:

<cfsavecontent variable="emailbody">
  <cfoutput>#now()#</cfoutput>
</cfsavecontent>
<cfoutput>#emailbody#</cfoutput>

That should show the current date and time. Does it? If so, then slip it into that code you started with, replacing your cfsavecontent. Does it show the date/time then?  And if it does, what if you do a cfmail sending that variable?

[paul_8809]

Correct, the variables are not resolved by placing the emailBody output below the cfcsavecontent block. Yes, certainly if I place the date output inside the tag, it outputs correctly. I'll give your next suggestion a shot shortly. Just a little tied up atm. But, I think this issue relates to a similar issue on Stackoverflow here https://stackoverflow.com/questions/4171341/coldfusion-cfsavecontent-html-page-with-dynamic-variable...

I tried one of the suggestions and it didn't work.

[Charlie Arehart]

To be clear, the variables SHOULD be resolved...and that's done WITHIN the cfsavecontent which is saved to the variable. So if you output that variable AFTER the the cfsavecontent and somehow do NOT see the variables having been resolved, there's something very unusual going on.

As for that (2010) SO post you shared, I don't see things happening as they propose. For instance, if you create another file with this code, calling it test.cfm:

<cfparam name="url.test" default="somevar">
<cfsavecontent variable="somevar">
  <cfoutput>#now()# and #url.test#</cfoutput>
</cfsavecontent>
<cfoutput>#somevar#</cfoutput>

 And you call it with test.cfm?test=someothervar, you should see it output the time then and that "othervar" value. It definitely does evaluate the variable at the time the cfsavecontent is run.

Whatever you're experiencing, it's going to have some explanation that we have yet to discern. But yep, keep trying the things I'm proposing as tests, to help us find if the issue is somehow about the setup on your end. Right now, I cna't fahom what it will be.

[kazu98296633]

<cfset application.siteroot = "https://example.com">
<cfset vData = '<a href="<cfoutput>##application.siteroot##</cfoutput>/?utm_campaign=transactional&utm_source=emails.logo&utm_medium=email"><img src="<cfoutput>##application.siteroot##</cfoutput>/logo10-180.png"></a>'>
<cfsavecontent variable="emailBody">
  <cfoutput>#vData.reReplace("<cfoutput>(##[^##]+##)</cfoutput>", function(obj){return Evaluate(obj.match[2]);}, "all")#</cfoutput>
</cfsavecontent>
<cfoutput>#emailBody#</cfoutput>

[paul_8809]

Thanks for replying! This throws an error, as follows:

[kazu98296633]

I ran the test in CF2021, CF2023 and CF2025.

https://cffiddle.org/

It doesn't work well in my CF2018 environment.

[paul_8809]

OK, I'm running an ancient CF2011.

[paul_8809]

It's good to know it actually works though. Once I upload the site to hosting, it should be all good. Thanks my friend! Much appreciated.

[BKBK]

I have HTML code, containing dynamic variables, stored in a database in a field named "data',. An example of raw data in that field would be:

<a href="<cfoutput>#application.siteroot#</cfoutput>/?utm_campaign=transactional&utm_source=emails.logo&utm_medium=email"><img src="<cfoutput>#application.siteroot/logo10-180.png"></a>

By paul_8809

Hi @paul_8809 , the data seems to be incorrect. The second mention of application.siteroot isn't followed by #</cfoutput>

To confirm, you could test with

<cfsavecontent variable="data">
  <cfoutput>
<a href="#application.siteroot#/?utm_campaign=transactional&utm_source=emails.logo&utm_medium=email"><img src="#application.siteroot#/logo10-180.png"></a>
</cfoutput>
</cfsavecontent>

[paul_8809]

Well spotted, but that missing tag isn't in my code.  It occurred while pasting and cleaning up the code for this post. I can't edit my post to correct it.

[BKBK]

Please look again, as there is something else. Your code uses nested <cfoutput> tags, for example, like this

<cfset vData='<a href="<cfoutput>#application.siteroot#</cfoutput>/?utm_campaign=transactional&utm_source=emails.logo&utm_medium=email"><img src="<cfoutput>#application.siteroot#/logo10-180.png"></a>'>

<cfsavecontent variable="emailBody">
  <cfoutput>#vData#</cfoutput>
</cfsavecontent>

Nesting cfoutput tags is not good practice. Besides, if the cfsavecontent contains nothig else, then the above code contains a repetition. That is because vData and emailBody represent the same string.

I would therefore write instead

<cfset vData='<a href="<cfoutput>#application.siteroot#</cfoutput>/?utm_campaign=transactional&utm_source=emails.logo&utm_medium=email"><img src="<cfoutput>#application.siteroot#/logo10-180.png"></a>'>

<cfset emailBody = vData>

[paul_8809]

I have done further testing.

To recap, I obtain the data from a database, using the call 

<cfset vData = qEmailTemplate.data>

If I copy the resulting data from cflog and hardcode it before <cfsavecontent>, as below:

<cfset vData = "<div style=""padding:1.25em 50px;text-align:center""><a href=""<cfoutput>#application.siteroot#</cfoutput>/?utm_campaign=transactional&utm_source=emails.logo&utm_medium=email""><img src=""http://aceoftrades/images/website/logo10-180.png""></a></div>">

<cfsavecontent variable="emailBody">
   <cfoutput>#vData#</cfoutput>
</cfsavecontent>

The variables render in the email.

However, if I do not hardcode it prior to <cfsavecontent>, the variables do not render in the email.

Yet, the output from cflog dumps is exactly the same in both cases.

[BKBK]

Ah. that makes sense. Thanks for clarifying.

As I said, your hardcoding beforehand ensures that you get the email-body directly.

<cfset emailBody = "<div style=""padding:1.25em 50px;text-align:center""><a href=""<cfoutput>#application.siteroot#</cfoutput>/?utm_campaign=transactional&utm_source=emails.logo&utm_medium=email""><img src=""http://aceoftrades/images/website/logo10-180.png""></a></div>">

So there is no need for cfsavecontent.

[paul_8809]

Yes, if I hardcode, it all works. But that means I can't store the data in a database, then retrieve it programatically.

i.e. I can't do:

<cfset emailBody = qEmailTemplate.data>

<cfset resPostmark = oPostmark.sendMail(
   mailTo = qJobDetails.recipient_email
   ,mailFrom = application.fromEmail
   ,mailSubject = vSubject
   ,apiKey = application.postmarkAPIKey
   ,mailHTML = emailBody
)>

I have to do:

<cfset emailBody = "<div style=""padding:1.25em 50px;text-align:center""><a href=""<cfoutput>#application.siteroot#</cfoutput>/?utm_campaign=transactional&utm_source=emails.logo&utm_medium=email""><img src=""http://aceoftrades/images/website/logo10-180.png""></a></div>">

<cfset resPostmark = oPostmark.sendMail(
   mailTo = qJobDetails.recipient_email
   ,mailFrom = application.fromEmail
   ,mailSubject = vSubject
   ,apiKey = application.postmarkAPIKey
   ,mailHTML = emailBody
)>

The whole purpose of this was the get the untidy html code out of my function and maybe have an admin page to update the html as necessary in future.

[BKBK]

Indeed, with no intervening cfsavecontent necessary. 🙂 

If that works for you, then that's fine. Due respect goes to working code; optimization comes later. So hats-off to you.

Nevertheless, I have one advice before this closes. I would avoid storing ColdFusion code in this way in the database. It is not just bad practice. It is up there among the biggest no-nos in software. For good reason.

Here are just 4 of the draw-backs of storing code like that in the database:

1. It violates "separation of concerns", one of the cornerstones of software design.
   Business logic should stay in files on the application server, not in the database. Storing code in the database mixes logic and data. This makes future updates of the code more complex.
   
   
2.  It creates maintainability issues, as you've just discovered.

Debugging database-stored code is usually harder because of the need to convert between two different environments. Code readability is reduced. As a result, understanding and maintaining the code becomes more difficult.

3. It has impact on performance.
Queries are expensive in terms of CPU resources. Fetching code from a database slows execution. In addition to that, parsing and running the dynamic code adds unnecessary overhead.

4. It incurs a security risk. 
Storing executable code in the database can lead to code injection attacks. An attacker could either insert malicious ColdFusion code into the data and compromise the server, or use the data to spoof the server..

There is a ready solution. It is very simple, yet very powerful, and answers each and every one of the 4 points above:

• keep the code logic in a CFM or CFC file. 
  Accessing files is much faster, more efficient and safer than querying a database for code.

[paul_8809]

Makes sense. I wanted it as dynamic as possible in the sense that if anything changes in a particular HTML template (there are several, with variants of each), I can just make a change in the database without having to generate a new HTML page for the different variants. I'll look into a simple way to do that though.

There is logic in the database but it's more "display" logic than  "business" logic, i.e. whether to display a button or subtext in the email.

Maintainability: The end goal is to make the data and template layouts itself more easily maintainable, i.e. much easier to update a database field than generate a new template for every minor change.

Security and performane are the two I'm most concerned about.

[Charlie Arehart · Accepted solution]

Ah, ok. I understand your need now. You want to be able to dynamically execute the CFML which is held in the database column. Sorry, I didn't connect that dot (since your original post referred to cfsavecontent, and it used a "vdata" variable you'd not defined for us). I gather now that's the name of the variable holding the "cfml" that you obtained from the database record. Is that right?

So first, putting that inside a cfsavecontent and using cfoutput around it was not going to execute that CFML. And some people might propose that the CFML "evaluate" function could be used for this, but no: that doesn't evaluate CFML but rather only CFML expressions. It would fail on trying to render the cfoutput within the a href tag, etc. within your opening code fragment.

As i said originally, many will argue against trying to support such dynamic data--partly because it's hard to execute. But it can be done. One way is to write that CFML (that vdata variable of yours) to a file and then CFINCLUDE that file (and then delete it). I offer here an example that demonstrates it, with comments to address some subtleties about it. (and of course it could be done in cfscript for those who prefer that. I stuck with tags as  Paul shows using them):

<!--- 
- First, simulate a query with a column that holds cfml to be executed later.
- Because that column's content is placed here, the pound signs must be escaped.
- That would not be needed for a real db record being read in via cfquery, etc.
--->

<cfset codeQ=querynew([{vdata:"<cfset name='bob'>This is formatted output:
<b><cfoutput>##name##</cfoutput></b>"}])>
<cfdump var="#codeQ#">

<!--- create a temp file name and its path (need them separate, for later)--->

<cfset tempfilename="savedcode#createuuid()#.cfm">
<cfset tempfile=expandpath(tempfilename)>

<!--- write the code from the query column into the temp file, using full path --->

<cffile action="write" output="#codeQ.vdata#" file="#tempfile#" >

<!--- include the file just written, noting that it does not allow full path--->

This shows the resulting evaluated CFML/HTML to be rendered on-screen:<br>
<cfinclude template="#tempfilename#"> 

<!--- this causes the resulting evaluated CFML/HTML to be sent in an email --->

<cfmail to="whoever" from="whoever" subject="whatever" server="wherever">
    <cfinclude  template="#tempfilename#"> 
</cfmail>

<!--- delete the temp file after use, after waiting a second for CF to release its hold--->

<cfset sleep(1000)>
<cffile action="delete" file="#tempfile#" >

Let us know if that might help get you going. Or perhaps my comment may spark ideas from others.

[paul_8809]

OK, I'll give it a shot.  Thanks for that.

[paul_8809]

FYI, the suggestion by kazu98296633 seems to work, unless I'm smoking something.

cffiddle  

[paul_8809]

Disregard that. Surprisingly it works in cffiddle even without using his regex.