Single quotes doubled when saving Javascript to MSSQL database

dev
1

Hi, when testing an ACF webapp in Lucee 5.2.x I get a strange occurrence where Javascript that is saved to the database gets all single quotes doubled up. So whereas this works nicely in ACF (as I want it, just write the Javascript as-is to the database-field) in Lucee everything seems to be escaped or so? Using double quotes in the Javascripts does not trigger this behaviour. So this:

<script type="text/javascript">
	var hcOption_#randomCode# = '';
	hcOption_#randomCode# = jQuery.extend(true,{},highChartDefaultOptions);
	hcOption_#randomCode#.chart.height = 400;
	hcOption_#randomCode#.chart.renderTo = 'highChartContainer_#randomCode#';
	hcOption_#randomCode#.legend.enabled = #IIf(object.get("showLegend") eq 0 or object.get("showLegend") eq "",false,true)#;
	hcOption_#randomCode#.xAxis.title.text = '#object.get("xAxisLabel")#';
</script>

turns into this:

<script type="text/javascript">
	var hcOption_#randomCode# = '''';
	hcOption_#randomCode# = jQuery.extend(true,{},highChartDefaultOptions);
	hcOption_#randomCode#.chart.height = 400;
	hcOption_#randomCode#.chart.renderTo = ''highChartContainer_#randomCode#'';
	hcOption_#randomCode#.legend.enabled = #IIf(object.get("showLegend") eq 0 or object.get("showLegend") eq "",false,true)#;
	hcOption_#randomCode#.xAxis.title.text = ''#object.get("xAxisLabel")#'';
</script>

Notice the double single quotes? This breaks my HighCharts rendering as the Javascript is broken. Any ideas why this happens and where I can “untrigger” this behaviour? I cannot find any settings on this in the datasource or anywhere else in Lucee Admin. All content-settings are set to UTF-8, also the template-setting.

The SQL is pretty straightforward:

UPDATE table
SET tableColumn1 = <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#object.get('graphhtmlandjavascript')#">
, tableColumn2 = <cfqueryparam cfsqltype="cf_sql_float" value="#now()#">
WHERE tableID = <cfqueryparam cfsqltype="cf_sql_char" value="#object.get('objectID')#">
2

Hi there fellow Lucee’ers, anyone got an idea on this? Seems weird that this is happening…

3

OK, as no-one has chimed in here, I surveyed my network of CF-developers and came up with the following idea: PRESERVESINGLEQUOTES(). It seems that ColdFusion does this Out-of-the-Box, whilest Lucee does not when inserting data containing single quotes into a (MSSQL) database. When I added the PreserveSingleQuotes functioncall to the queryparam:

<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#PreserveSingleQuotes(object.get('graphhtmlandjavascript'))#">

the complete string is saved to the database as expected. The single quotes in the Javascript are NOT escaped and my HighCharts render as expected when pulled from the database.

@modius is this a Lucee vs ACF bug as it is a difference in how this works between the engines?

4

You could raise it as an ACF compatibility issue. We aim to continuously improve ACF compatibility with rare exceptions.