Hi Gang, Can anyone see what I am doing wrong here, please? I am using Lucee 5.3.9.166 MariaDB using the MySQL JDBC I have a string that is SQL. It has a WHERE statement (Taken from a dump of the string)

WHERE wsq.internalName = 'NPS_response'

When I pass the string into a cfquery tag

<cfquery name="theQuery" datasource = "#customer.getDataSource()#" >
    #theLastSQL#
</cfquery>

I get SQL errors - where my "where" clause above is "double quoted"

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NPS_response''

Any and ALL quoted strings - end up with an extra set of single quotes. It isn't just this one instance - it is simply the first one - that the error gets caught up on. Thanks.

Even when building dynamic SQL use CFQuery Param to pass-in parameters to avoid issues like this.

to answer the question at the heart of this issue, cf will auto-escape single quotes in a VARIABLE passed into a cfquery tag UNLESS you tell it not to by wrapping it in

#preserveSingleQuotes(theLastSQL)#

I take onboard the advice about using cfqueryparams. It is existing code that I am trying not to edit any more than I have to. Though I suppose using queryParams could be seen as a "have to"/// Anyway just as a follow-up - if I use the following instead of the cfquery tag - I do NOT have the doubled-up quotes issue.

<cfset variables.theQuery = new query(
					name = "qry",
					datasource = customer.getDataSource(),
					sql = "#theLastSQL#").execute().getResult()  />

Looks like bad implementation of that query CFC; it's a bug that it is not escaping the single quotes, as that operation is by design. As it's a security thing, someone might want to look @ it, @Mark Takata (Adobe)? Although obvs(?) there's always been a recommendation to not use those service CFCs that Adobe came up with way back then as they're not the best implementation one could hope for. What version of CF are you on and depending on the answer, why are you not using

queryExecute

?