Thank you for your post.
I have forwarded your entire post to our Development and Testing departments for review. I will keep you posted as information becomes available to me.
Thanks for looking into this.
Our Testing department has confirmed this issue, and informed me that I had previously reported this issue last week at:
Embarrassingly, I apologize for not remembering this. In any case, it is known, and I'll keep you updated as more informatioin becomes available.
Thanks for pointing this out. I have just updated the other link.
Is there an update on this?
I have a similar related problem that probably explains the backslash behaviour.
I believe the problem is that FileMaker internally uses the CSV-style double-the-quote text escape technique (which is fine),
BUT the ODBC driver passes the text values directly to FileMaker, without converting \' backslash-quote to doubled-single-quotes (this is a bug).
I connect to FM via an ODBC client.
The quote character for the ODBC connection is " (doublequote), This quote is used for quoting column and table names. So single-quotes would be used for escaping text fields (which is standard SQL I believe).
If I issue an update command like so: UPDATE mytable SET textcol = "hello ' there";
it will respond with an error - because I'm incorrectly using the " quote character which is reserved for quoting column names etc. So that's fine.
If I issue an update command like so: UPDATE mytable SET textcol = 'hello there';
It works fine. Now lets try and add a single-quote into the text.
If I issue an update command like so: UPDATE mytable SET textcol = 'hello ' there';
it will respond with an error - because I'm not escaping the inner ' single quote.
The correct way to escape strings in SQL is with the \ backslash character. Lets try
If I issue an update command like so: UPDATE mytable SET textcol = 'hello \' there';
it will INCORRECTLY respond with an error: #0: 42000:8310:[FileMaker][FileMaker] FQL0001/(1:69): There is an error in the syntax of the query.There is another way of escaping singlequotes that is used in the official CSV format - double the single quote (ie put two single-quotes together). Lets try.
If I issue an update command like so: UPDATE mytable SET textcol = 'hello '' there';
It (incorrectly) SUCCESSFULLY executes, and I end up with the text "hello ' there" in the column - only ONE quote.