Joined: Oct 2002
the reason you've heard about replacing single quotes is that SQL uses single quotes to delimit a string. So if you wanted to insert the string 'John O'Hara' (delimiting the string with the single quotes), you can see where it would fail, as it would see the apostrophe after O as terminating the string.
You don't really need to jump through hoops to insert/update data safely tho. Just use <cfqueryparam> tags. There's many reasons to use <cfqueryparam>, and none not to (that I can think of). It will provide an extra level of security, it will bind the varibles to the database, making your queries significantly faster, and it will handle 'special' characters.
<cfset myName = "John O'Hara">
<cfquery name="insertRecord" datasource="myDSN">
INSERT into mytable (firstName)
VALUES (<cfqueryparam value="#myName#" cfsqltype="cf_sql_char">
As you can see, no need to manually escape any special characters.