URL: /results.cfm?lname=Abadschieff&fname=Jan (where lname is artist lastname and fname is artist firstname)
This link works fine. However, if I want to add this URL to a Sitemap to be read by Google, it is necessary to escape the & with & which ends up breaking the SQL. So while /results.cfm?lname=Abadschieff&fname=Jan works without issue, /results.cfm?lname=Abadschieff&fname=Jan yields no results.
Here is the CFM code. How can I keep the SQL from breaking once XML escaped?
<cfparam name="URL.lname" default="xxxyyyzzz">
<cfparam name="URL.fname" default="xxxyyyzzz">
<cfparam name="rsArtist2__varArtist2" default="#URL.lname#">
<cfquery name="rsArtist2" datasource=#MM_connArtSql_DSN# username=#MM_connArtSql_USERNAME# password=#MM_connArtSql_PASSWORD#>
Set Rowcount 1000 SELECT distinct code, photo, pid, photonum, fname, lname, title, price, price_USA, pwidth, pheight, catalogue FROM pData WHERE photonum=1 AND lname='#rsArtist2__varArtist2#'
<cfif (url.fname neq "xxxyyyzzz") AND (trim(url.fname) neq "")>
AND fname='#URL.fname#'
<cfelse>
AND (fname IS NULL OR fname ='')
</cfif>
ORDER BY pid desc
</cfquery>
<cfset rsArtist2_NumRows = 0>
<cfset rsArtist2_Index = 1>
<cfscript>
rsArtist2_NumRows = 0;
rsArtist2_Index = 1;
HLooper1_NumRows = 24;
rsArtist2_NumRows = rsArtist2_NumRows + HLooper1_NumRows;
</cfscript>
<cfscript>
// *** Recordset Stats, Move To Record, and Go To Record: set stats variables
// set the record count
rsArtist2_total = rsArtist2.RecordCount;
// set the number of rows displayed on this page
If (rsArtist2_NumRows LT 0 OR rsArtist2_NumRows GTE rsArtist2_total) {
rsArtist2_NumRows = rsArtist2_total;
} Else If (rsArtist2_NumRows EQ 0) {
rsArtist2_NumRows = 1;
}
// set the first and last displayed record
rsArtist2_first = Min(1, rsArtist2_total);
rsArtist2_last = Min(rsArtist2_NumRows, rsArtist2_total);
</cfscript>
<cfscript> MM_paramName = "";
</cfscript>
<cfscript>
// *** Move To Record and Go To Record: declare variables
MM_rs = rsArtist2;
MM_rsCount = rsArtist2_total;
MM_size = rsArtist2_NumRows;
MM_uniqueCol = "lname";
MM_paramName = "lname";
MM_offset = 1;
MM_atTotal = false;
MM_paramIsDefined = false;
If (MM_paramName NEQ "") MM_paramIsDefined = IsDefined(MM_paramName);
</cfscript>
<cfscript>
// *** Move To Record and Go To Record: declare variables
MM_rs = rsArtist2;
MM_rsCount = rsArtist2_total;
MM_size = rsArtist2_NumRows;
MM_uniqueCol = "code";
MM_paramName = "id";
MM_offset = 1;
MM_atTotal = false;
MM_paramIsDefined = false;
If (MM_paramName NEQ "") MM_paramIsDefined = IsDefined(MM_paramName);
</cfscript>
<cfscript>
// *** Move To Specific Record: handle detail parameter
If (MM_paramIsDefined AND MM_rsCount NEQ 0) {
// get the value of the parameter
MM_paramVal = Evaluate(MM_paramName);
// find the record with the unique column value equal to the parameter value
For (MM_offset=1; MM_offset LTE MM_rsCount; MM_offset=MM_offset+1) {
If (MM_paramVal EQ MM_rs[MM_uniqueCol][MM_offset]) {
break;
}
}
// if not found, reset MM_offset
If (MM_offset GT MM_rsCount) {
MM_offset = 1;
}
}
</cfscript>
<cfscript>
// *** Move To Record: handle 'index' or 'offset' parameter
If (NOT MM_paramIsDefined AND MM_rsCount NEQ 0) {
// use index parameter if defined, otherwise use offset parameter
If (IsDefined("index")) {
MM_offset = index;
} Else If (IsDefined("offset")) {
MM_offset = offset;
}
// check if we are past the end of the recordset
If (MM_offset GT MM_rsCount OR MM_offset EQ 0) { // past end or move last
If ((MM_rsCount MOD MM_size) NEQ 0) { // last page not a full repeat region
MM_offset = MM_rsCount - (MM_rsCount MOD MM_size) + 1;
} Else {
MM_offset = MM_rsCount - MM_size + 1;
}
}
}
</cfscript>
<cfscript>
// *** Move To Record: replace the current query, for navigation on a detail page
If (MM_size EQ 1) { // if no repeated regions are defined
// create a new query with one row
MM_newQuery = QueryNew(MM_rs.ColumnList);
QueryAddRow(MM_newQuery, 1);
// copy the data in the row pointed to by MM_offset to the new query
MM_columns = ListToArray(MM_rs.ColumnList,",");
For (i=1; i LTE ArrayLen(MM_columns); i=i+1) {
QuerySetCell(MM_newQuery, MM_columns[i], MM_rs[MM_columns[i]][MM_offset], 1);
}
// change the recordset to be only this one row
rsArtist2 = MM_newQuery;
}
</cfscript>
<cfscript>
// *** Move To Record: update recordset stats
// set the first and last displayed record
rsArtist2_first = Min(MM_offset, MM_rsCount);
rsArtist2_last = Min(MM_offset + MM_size - 1, MM_rsCount);
// set the starting index for repeated regions on this recordset
rsArtist2_Index = MM_offset;
// set the boolean used by hide region to check if we are on the last record
MM_atTotal = (MM_offset + MM_size - 1 GTE MM_rsCount);
</cfscript>
<cfscript>
// *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters
// create the list of parameters which should not be maintained
MM_removeList = "&index=";
If (MM_paramName NEQ "") MM_removeList = MM_removeList & "&" & MM_paramName & "=";
MM_keepURL=""; MM_keepForm=""; MM_keepBoth=""; MM_keepNone="";
// add the existing URL parameters to the MM_keepURL string
MM_params=ListToArray(CGI.QUERY_STRING,"&");
For (i=1; i LTE ArrayLen(MM_params); i=i+1) {
If (FindNoCase("&" & GetToken(MM_params[i],1,"=") & "=",MM_removeList) Is 0)
MM_keepURL = MM_keepURL & "&" & MM_params[i];
}
// add the existing Form variables to the MM_keepForm string
If (IsDefined("FORM.FieldNames")) {
MM_params=ListToArray(FORM.FieldNames,",");
For (i=1; i LTE ArrayLen(MM_params); i=i+1) {
If (FindNoCase("&" & MM_params[i] & "=",MM_removeList) Is 0)
MM_keepForm = MM_keepForm & "&" & LCase(MM_params[i]) & "=" & URLEncodedFormat(Evaluate("FORM." & MM_params[i]));
}
}
// create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm;
If (MM_keepURL NEQ "") MM_keepURL = RemoveChars(MM_keepURL,1,1);
If (MM_keepForm NEQ "") MM_keepForm = RemoveChars(MM_keepForm,1,1);
If (MM_keepBoth NEQ "") MM_keepBoth = RemoveChars(MM_keepBoth,1,1);
</cfscript>
<cfscript>
// *** Move To Record: set the strings for the first, last, next, and previous links
MM_keepMove = MM_keepBoth;
MM_moveParam = "index";
// if the page has a repeated region, remove 'offset' from the maintained parameters
if (MM_size GT 1) {
MM_moveParam = "offset";
MM_params = ListToArray(MM_keepMove,"&");
MM_keepMove = "";
For (i=1; i LTE ArrayLen(MM_params); i=i+1) {
If (CompareNoCase(GetToken(MM_params[i],1,"="),MM_moveParam) IS NOT 0) {
MM_keepMove = MM_keepMove & "&" & MM_params[i];
}
}
If (MM_keepMove NEQ "") {
MM_keepMove = RemoveChars(MM_keepMove,1,1);
}
}
// set the strings for the move to links
if (MM_keepMove NEQ "") MM_keepMove = MM_keepMove & "&";
urlStr = CGI.SCRIPT_NAME & "?" & MM_keepMove & MM_moveParam & "=";
MM_moveFirst = urlStr & "1";
MM_moveLast = urlStr & "0";
MM_moveNext = urlStr & (MM_offset + MM_size);
MM_movePrev = urlStr & Max(MM_offset-MM_size,1);
</cfscript>