asked on

Using PreserveSingleQuotes in a dynamic WHERE Clause

I have a dynamic query that works just fine if the user doesn't put any apostrophes in the input form fields.

I need to be able to allow apostrophe's in the form fields and have SQL Server process them as text instead of identifiying them as a delimiter that separates strings.

Can someone tell me how I can accomplish that?

The query is:
--------------------------------------
<cfset WhereClause = " 1=1 ">
<cfif isDefined("FORM.txtUIDs") AND FORM.txtUIDs NEQ "">
<cfset WhereClause = WhereClause & " AND a.UID IN ("&FORM.txtUIDs&")" >
</cfif>
<cfif isDefined("FORM.txtCenterName") AND FORM.txtCenterName NEQ "">
<cfset WhereClause = WhereClause & " AND a.BldgName LIKE '"&FORM.txtCenterName&"%'" >
</cfif>
<cfif isDefined("FORM.txtStreetNum") AND FORM.txtStreetNum NEQ "">
<cfset WhereClause = WhereClause & " AND a.BldgStNum = '"&FORM.txtStreetNum&"'" >
</cfif>
<cfif isDefined("FORM.txtStreetName") AND FORM.txtStreetName NEQ "">
((( a bunch more of these if statements... you get the idea. ( i hope) )))
</cfif>

<CFQUERY NAME="GetRecords" DATASOURCE="#dsn#">
SELECT a.UID, a.SN, a.Sec, a.[ID], a.ID_numeric, a.Status, a.KM, a.BldgName,
a.BldgStNum, a.BldgStName, a.BldgCity, a.BldgState, a.BldgZip,
a.Contiguous, a.YrRenov, a.Census, a.Anchor, a.[Type], a.preferedListing,
a.rate1, a.rate2, a.avgRent, a.parkingSpaces, a.commission, a.commissionPercent
FROM RET_tblData a
LEFT JOIN RET_tblSector h ON a.Sec = h.Sector
WHERE #PreserveSingleQuotes(WhereClause)#
</CFQUERY>

I cut a bunch of stuff out of the query for brevity but the concept is there.
The problem is with the field for "FORM.txtCenterName". This is just a regular textbox and I must allow the use of single quotes. The sql statement is chocking on it when a single quote is encountered.

Any ideas on how I can fix this?

-Eddie

js_vaughan

It seems like the problem here is that you are using PreserveSingleQuotes() in your WHERE clause and not around the FORM variables themselves. The difference is that while you do what to preserve the single quotes that are coming from the textfield, SQL still needs to properly interpret the single quotes you are putting around the string itself.

So for example, change your code to look more like this:

<cfset WhereClause = WhereClause & " AND a.BldgName LIKE '"&PreserveSingleQuotes(FORM.txtCenterName)&"%'" >

See if that helps.

FastEddie___

ASKER

Thanks for the suggestion. I tried it but it did not work. I'm getting this DB error:

Incorrect syntax near 's'.

The SQL Where cause that is being returned is this:

WHERE 1=1 AND a.BldgName LIKE 'Kathryn's%' order by a.preferedListing desc, a.[ID]

It's the single quote in the string "Kathryn's" that is causing the problem.

Got any other suggestions?

FastEddie___

ASKER

JS, I don't know why preserveSingleQuotes didn't work in tat instance but your suggestion allowed me to come up with a solution.

I just replaced the single quote with two single quotes in the form field prior to setting the where clause.

<cfset WhereClause = WhereClause & " AND a.BldgName LIKE '"&ReplaceNoCase(FORM.txtCenterName,"'","''","All")&"%'" >

That did the trick.

ASKER CERTIFIED SOLUTION

js_vaughan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FastEddie___

ASKER

I tried using queryparam on the form field and the where clause, both tests failed.

Your function solution is great though. It'll let me do it with alot less typing.

Thank you so much for your help.

-Eddie