CFQUERY Inconsistancies?

@fredp60157821 , to specifically answer the 2 questions you raised:

1. No, there is no inconsistency.
2. PreserveSingleQuotes helps.

You could fix your code as follows:

<!--- Convert every ' within form.name into '' --->
<cfset nameEscaped=replace(form.name,"'","''","all")>
<cfset lsSql="INSERT INTO TEST (NAME) VALUES ('" & nameEscaped & "')">

<CFQUERY NAME="A" DATASOURCE="MY_LOCALHOST">
   #preserveSingleQuotes(lsSql)#
</CFQUERY>

First a warning,  you should never do this with FORM elements, and in general should never do it.   In your example you've made it super easy for someone to perform a SQL Injection attack using FORM.NAME.    It should be within a cfqueryparam and then the single quote won't matter.

<CFQUERY NAME="A" DATASOURCE="MY_LOCALHOST">
     INSERT INTO TEST (NAME) VALUES ( <cfqueryparam values='#FORM.NAME#'> )
</CFQUERY>

PreserveSingleQuotes is how you do this, but you have to handle the single quote in the name yourself.   So if you output #PreserveSingleQuotes(lsSql)#  you'll probably see something like this:

INSERT INTO TEST (NAME) VALUES ('O'Niel')

That single quote in between the O and the N needs to be doubled to be proper SQL syntax.    Couple ways you could do it, but this should work:

<CFSET lsSql = "INSERT INTO TEST (NAME) VALUES ('" & Replace(FORM.NAME, "'", "''") & "')">
<CFQUERY NAME="A" DATASOURCE="MY_LOCALHOST">
#lsSql#
</CFQUERY>