I have a tricky single quote problem

I have a form where people can enter multiple names (name1, name2, name3, etc). I then turn their entries into an array, loop through it, and insert it into a table. It works fine UNLESS the name has an apostrophe in it (Jim O'Grady, for example).

First, here's the query:


aCaseNumbers[i][1] and aCaseNumbers[i][3] are not a worry, because they're numbers. aCaseNumbers[i][2] is the name, and if that has the ' in it, the query bombs. Here's the error I get:

Sybase Error Code = 105

Unclosed quote before the character string ')'.

SQL = "INSERT into self_report_multi_file (report_group_key, report_nbr, report_date, case_nbr, loan_officer, loan_nbr, self_sw) values ('040705092830', 3, '04/07/2005', '111-1111', 'Sam O'Jones', '55555', 'U')"

It's easy to see what the problem is...when the query hits that ' in "O'Jones" it reads it as a single-quote in the query.

I've tried using PreserveSingleQuotes a bunch of places, but everything I try doesn't seem to work. Right now I have it looping through and replacing any ' in the name with a space, but that's not exactly the best solution. Any ideas? Is it possible to use the HTML number for the single quote (&#39)?

Thanks.

Hah, nevermind. All I had to do was change '#Trim(aCaseNumbers[i][2])#' in the query to "#Trim(aCaseNumbers[i][2])#" ...sometimes the simplest solutions are the last ones you think of

Bah. Now if, for some reason, the name has a " in it, the query bombs...Normally names don't have " in them, but it's still something I'd like to take care of...Is there a way to deal with this sort of thing? Because something like <CFSET aCaseNumbers[i][2] = #Replace(aCaseNumbers[i][2], """, " ")#> doesn't work because of the quotes... I tried Evaluate, but that didn't work (or my syntax was wrong).

I know I'm talking to myself here, but I thought I'd add some closure. I don't know if the following was the best way to take care of this, but it works!

<cfset double_quote = '"'>
<cfset single_quote = "'">

<CFLOOP INDEX="i" FROM="1" TO="#loop_cnt#">
<CFIF aCaseNumbers[i][2] CONTAINS "#double_quote#">
<CFSET aCaseNumbers[i][2] = #Replace(aCaseNumbers[i][2], "#double_quote#", "#single_quote#", 'ALL')#>
</CFIF>
</CFLOOP>

I think you could just keep the single quotes and wrap the data in the preserveSingleQuotes() function. I also think you could use <cfqueryparam> which I believe handles any character escaping issues.