Skip to main content
P
pbk-nFjYI4
Participant
July 12, 2007
Answered

Dynamic sql execution with quote marks

  • July 12, 2007
  • 1 reply
  • 564 views
I have an app that lets users submit an arbitrary sql statement (keywords like DELETE and INSERT are prohibited, in case you are wondering). I send the string to theSQL Server stored procedure below. The problem is that I get an error when the sql statement contains quote marks like this:

select top 10 companyName
from customers
where country = 'USA'

Any solutions? Thanks.

Pete
This topic has been closed for replies.
Correct answer Newsgroup_User
By default, CF escapes any single quotes found in string in a variable
passed to a <cfquery...> tag. This is to allow use cases like this to
work correctly.

<cfset aVar = "O'Connor said to him, 'I'll Be Back!'">

<cfquery....>
INSERT INTO aTable (aField)
(#aVar#)
</cfquery>

ColdFusion will escape the single quotes in the string by doubling them
so that this works properly. In your case you do not want them escaped,
and the function to tell CF to not do this is preserveSingleQuotes().

> <cfquery name="myQuery" datasource="#application.mainDS#">
> #preserveSingleQuotes(form.queryString)#
></cfquery>

1 reply

D
Dan_Bracuk
Inspiring
July 12, 2007
My suggestion is to simply run the sql instead of using a stored procedure.
P
pbk-nFjYI4Author
Participant
July 12, 2007
But I get the same error if I use this instead of a stored procedure:

<cfquery name="myQuery" datasource="#application.mainDS#">
#form.queryString#
</cfquery>
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices