Dynamic Query - Incorrect Syntax 1

[Argus]

If I have a query such as:-

<CFQUERY name = etc etc>
SELECT * From MyTable
WHERE Surname = 'Smith'
</cfquery>

it works fine.

However, if I do the following:-

<CFSET WhereClause = &quot;Surname = 'Smith'&quot;>

<CFQUERY name = etc etc>
SELECT * From MyTable
WHERE #WhereClause#
</cfquery>

When I run the query I get Incorrect Syntax Near '='.

Any ideas?

I've tried using PreserveSingleQuotes but it didn't make any difference.

 

[SidianConsulting]

Maybe you didn't use PreserveSingleQuotes() in the right way. ColdFusion automatically escapes single quotes in queries, so when you need to dynamically build them, you need to use this function, which DOES NOT escape them, hence the &quot;Preserve&quot; part of the function.

<CFSET WhereClause = &quot;Surname = 'Smith'&quot;>

<CFQUERY name = etc etc>
SELECT * From MyTable
WHERE #PreserveSingleQuotes(WhereClause)#
</cfquery>

I know this code works, because I tested it. I hate when people give me examples and they are not sure if they work or not!

I don't mean this toward you, but there are lots of articles and posts on mailing lists out there that confuse this function for a lot of developers.

-Tek

 

[Argus]

Thank you once again. I really should have known that - it's been a long day....