You did not tell me what command you used. Normally CONNECT is not using prepared statements.
How did you make the use of prepared statement?

Very interesting...

When I use the mysql command line tool and issue the following command it WORKS! (and shows the correct result on query)

UPDATE Azure_all_types SET myvarchar = 'Bob''s' WHERE id = 2;

But when I change it to:

UPDATE Azure_all_types SET myvarchar = 'Bob\'s' WHERE id = 2;

it fails with the same error:

ERROR 1296 (HY000): Got error 122 'ExecuteUpdate: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax n

ear 's'. n=-1' from CONNECT

So clearly the MariaDB JDBC driver is escaping single quotes like \' before sending it to the server.... which is valid for MariaDB but not optimal when sending to a remote server via CONNECT.

Maybe the solution is to change our code base so that we don't use prepared statements, or maybe this is or can be fixed in a MariaDB JDBC driver update? Note this also explains some of the other strange behavior such as with double quotes where a \ is added.

For example updating a record (with a prepared statement) that has a " (double quote char) in it causes it to save as \"

From the mysql command line tool, the following works and gives the correct result when you select it:

UPDATE Azure_all_types SET myvarchar = 'Bob"s' WHERE id = 2;

but the prepared statement adds a backslash before the double quote:
UPDATE Azure_all_types SET myvarchar = 'Bob\"s' WHERE id = 2;

which stores it in the remote database as \" !!!

This all makes sense now. Also \n inserts on the remote system as a \ and n character - i.e. not newline.

I don't see the need to escape ANY character in a single quoted string EXCEPT the single quote char itself? Do you agree?

Also, I don't see how CONNECT can tell if \" really means backslash quote or is an escaped double quote.

So two possible fixes I can see:
1) we change our code to eliminate prepared statements and only escape ' char as '' and leave all other characters as is
2) the MariaDB JDBC driver is changed so that it only escapes ' to double single quote if ANSI_QUOTES is turned on (is this a safe change to make??)

I am more and more puzzled by your comments.

First of all CONNECT does not use prepared statement for UPDATE.

When you say: updating a record (with a prepared statement) what do you mean? Show me how you do it.

In Java, via the JDBC driver:

this.pstmt = conn.prepareStatement("UPDATE `Azure_all_types` SET `myvarchar` = ? WHERE `id` = ?"); 

this.pstmt.setString(bindIndex, "Bob's"); // binds to 1st ?

this.pstmt.setString(bindIndex, "2"); // bind to 2nd ?

The MariaDB JDBC driver that the client app uses to connect to MariaDB obviously escapes certain characters in the string. The prepared statement is at the CLIENT level, the MariaDB JDBC driver encodes the data and passes it to the server, then I assume it calls CONNECT. So the problem is WAY upstream from CONNECT.

Instead of using prepared statements, our client application could generate the SQL statements directly and escape the values itself (rather than rely on the client JDBC driver).

Maybe I'm not explaining this well - nothing to do with CONNECT and nothing to do with CONNECT's use of JDBC... its the CLEINT app's use of JDBC is where this is going wrong.

Then again, IF connect has access to the unescaped values, maybe it could use the JDBC prepared statement calls to issue the remote commands.... this way the escaping would be handled by the driver and should take into account database-specific encoding requirements.

If I understand well, your client does not use a MariaDB client and a JDBC CONNECT table, but directly access JDBC from a Java client. Right?

If so, this is not the place for this case here, it should be placed in a MDEV case against the MariaDB JDBC driver.

About the way CONNECT handles UPDATE, it was buggy because the way the original query was retrieved, the quotes were escaped. However, it is now fixed (see MDEV-13621).