Be Careful Using "#" In ColdFusion DE() Expressions
Yesterday, while writing data transfer scripts to move data from an MS Access database into a MySQL database, I came across a problem that I had not faced before. In the Access database, all of the phone extensions where broken out into their own fields. In the new database, there were no phone ext fields so as part of the data translation, the phone number and phone extensions needed to be concatenated. This seemed like a simple enough translation and I wrote code like this to take care of it:
<!---
Set phone and ext. In reality, these values would be
coming out of a data base or input form (over which we
do not have control).
--->
<cfset strPhone = "(123) 456-7890" />
<cfset strExt = "128" />
<!---
Create full phone number complete with base number and
line extension.
--->
<cfset strFullPhone = (
strPhone &
IIF(
Len( strExt ),
DE( " x#strExt#" ),
DE( "" )
)
) />
<!--- Output new number. --->
#strFullPhone#
This simply takes the number and extension and if there is a an extension, it appends it to the phone number but prepends " x" to it (the extension). Running the above code, we get the following output:
(123) 456-7890 x128
This worked fine, until I hit a phone extension with a "#" in it:
#128
If the extension contained this hash, ColdFusion would throw the following error:
Invalid CFML construct found on line 1 at column 8. ColdFusion was looking at the following text:<p>\"</p><p> The CFML compiler was processing: <ul><li>An expression that began on line 1, column 4.<br>The expression might be missing an ending #, for example, #expr instead of #expr#.<li> An expression beginning with \", on line 1, column 1. This message is usually caused by a problem in the expressions structure.</ul>
The problem here is that ColdFusion's IIF() doesn't just evaluate the passed in expressions right away - it uses a double (delayed) evaluation. Therefore, when it goes to evaluate the passed in argument for the second time, it appears as if it contains a ColdFusion expression that is malformed (missing the closing hash symbol).
I tried to mess around with the way in which the IIF() statement was executed, but no combination or existence of DE() and quotes would get this error to go away (other than when I produced a different error altogether). The only thing that I could do was to double the hash signs in the phone extension so that they would be escaped in the second evaluation:
<!---
Set phone and ext. In reality, these values would be
coming out of a data base or input form (over which we
do not have control).
--->
<cfset strPhone = "(123) 456-7890" />
<cfset strExt = "##128" />
<!---
Create full phone number complete with base number and
line extension.
--->
<cfset strFullPhone = (
strPhone &
IIF(
Len( strExt ),
DE(
Replace(
" x#strExt#",
"##",
"####",
"all"
)
),
DE( "" )
)
) />
<!--- Output new number. --->
#strFullPhone#
This is not pretty, but it is all I could come up with without actually doing a CFIF / CFELSE statement. If anyone has a better fix, please let me know. Looking at the work-around, it would have been much more simple to just use a CFIF / CFELSE statement, but this was actually part of a CFQueryParam tag value attribute (so, you can't use other tags in there).
Want to use code from this post? Check out the license.
Reader Comments
Is #128 a valid extension? or is it really x128?
@Todd,
I assume it should be just x128. But, I am working on a really old content management system with a lot of "questionable" data. Right now, as the deadline is tight, I am working on translating the best that I can.
Ok, all you can do is escape it as you're doing otherwise remove it if it's not necessary.
Only other way to do this is to replace # with the ASCII value of it (CHR(35)) or the HTML value &;# which has the same hash issue as your original issue.
Ben,
If you're doing your project on ColdFusion 8, you can take advantage of some of the new abilities in CFScript to get the job done, while at the same time, keep things terse and clean.
Is there a ternary operator in the house?
<cfset strPhone = "(123) 456-7890" />
<cfset strExt = "##128" />
<cfscript>
strFullPhone = strPhone;
if (Len(strExt)) {
strFullPhone &= "x#strExt#";
}
</cfscript>
<cfoutput>#strFullPhone#</cfoutput>
@Shayne,
Thanks for the suggestion. The reason I was using the IIF() method was because it was part of a CFQueryParam tag:
<cfqueryparam value="#qSchool.phone##IIF( Len( qSchool.ext), ..... )#" />
Even the new ColdFusion 8 string concatenation would not have helped me here.
@Ben
You can actually do this with some simple math instead of IIF/DE+Replace:
<cfset phone = "123">
<cfset ext = "">
<cfset phone = left("#phone#x#ext#",len(phone)+((1+len(ext))*len(ext)))>
:)
@Elliot
Not sure if I follow your reasoning. Assuming the extension is at least "", you could simply structure the string "#phone#x#ext#" and have the same result.
I don't think Ben needed any help getting the string structured; like his title says "Be Careful Using "#" In ColdFusion DE() Expressions".
I've always thought the IIf()/DE() combo was clunky and now I have a good reason to avoid it or, when necessary implement a check for #s.
It is nice having a concatenation operator in CF8 (&=), keeps things clean. Hopefully CFScript will be fully ECMAScript compliant in the not so near future.
Shayne
@Shayne
Ben said himself:
"This is not pretty, but it is all I could come up with without actually doing a CFIF / CFELSE statement. If anyone has a better fix, please let me know."
He made it clear he wasn't looking for an if/else, which of course could solve this problem, but rather was looking for a single expression he could put in cfqueryparam.
That's what my code does.
@Elliott,
Pretty slick. I had to do the math in my head a few times to see if it worked.
@Elliott - Nice solution. I'll have to keep that one in mind.
@Shayne - You may want to try Elliott's code.
@Elliott
I apologize and didn't mean for anything to be offensive and certainly didn't want to make things hostile. I did run your code however - jumped to a hasty conclusion which resulted in me overlooking the ability to add/remove the "x" from the string when appropriate. Clever. Note to self: Caffeine before code. :)
@Nathan
Thanks for your comment which prompted me to revisit it.
@Ben
Comment delete ability? hahah
@Shayne,
Don't worry about it man, we are all friends here. Besides, the DBA in me screams out for "referential integrity" at the though of deleting a comment :)
#reReplace(strExt,"^(.)"," x\1")#
@Dan,
Good thinking. I didn't put much (any) thought into replacement. That probably would have worked nicely.
@Shayne
No worries. And no offense taken. Sorry if I came across hostile. :)
@Dan
Now that's a cool way to do it! Wow. And much clearer than what I had thought of.
At some point I stopped using de() in favor of a combination of double- and single-quotes when using iif():
strPhone & iif( len(strExt) , "'x' & strExt" ,"''" )
Not sure if this is an official feature, but it has served me well :)
What an unusual find! It's very quirky - but seems to work, hopefully somebody can explain a little better.
The following code works:
<cfset strPhone = "(555) 123-4567" />
<cfset strExt = "##123" />
<cfset strFullPhone = strPhone & IIf(Len(strExt), "'x' & strExt", "") />
<cfoutput>#strFullPhone#</cfoutput>
The following code results in the same error that Ben blogged about:
<cfset strPhone = "(555) 123-4567" />
<cfset strExt = "##123" />
<cfset strFullPhone = strPhone & IIf(Len(strExt), "'x#strExt#'", "") />
<cfoutput>#strFullPhone#</cfoutput>
Changing the quotes out doesn't seem to make a difference in either case:
<cfset strPhone = "(555) 123-4567" />
<cfset strExt = "##123" />
<cfset strFullPhone = strPhone & IIf(Len(strExt), '"x" & strExt', "") />
<cfoutput>#strFullPhone#</cfoutput>
vs.:
<cfset strPhone = "(555) 123-4567" />
<cfset strExt = "##123" />
<cfset strFullPhone = strPhone & IIf(Len(strExt), '"x#strExt#"', "") />
<cfoutput>#strFullPhone#</cfoutput>
Anybody? - anybody?
Very cool that it solved the IIf() problem Ben had!
@Ben
I am sure this has been asked, but what about code blocks in comments?
@Shayne,
Code blocks in the comments are on my list of things to do :)
Thanks Ben, this helped me.
Tks a lot dude!