Note this happens with both the Postgres and Microsoft SQL server drivers... so not driver specific.
Further, both work correctly for INSERT - it is only UPDATE that is not working.

Further still, CONNECT appears to be escaping the single quote character with \' rather than doubling the single quote character as is normal in SQL ("Single quotes are escaped by doubling them up").

Olivier... What is the status of this?

Could you publish the query you are using?

You can see below it is doing a \' instead of doubling up on the single quote character.

SELECT `Azure_all_types`.`id`, `Azure_all_types`.`mychar`, `Azure_all_types`.`myvarchar`, `Azure_all_types`.`mytext`, `Azure_all_types`.`mynchar`, `Azure_all_types`.`mynvarchar`, `Azure_all_types`.`myntext`, `Azure_all_types`.`myint`, `Azure_all_types`.`mysmallint`, `Azure_all_types`.`mytinyint`, `Azure_all_types`.`mybit`, `Azure_all_types`.`mynumeric`, `Azure_all_types`.`myfloat`, `Azure_all_types`.`mydate`, `Azure_all_types`.`mydatetime`, `Azure_all_types`.`mydatetime2`, `Azure_all_types`.`mysmalldatetime`, `Azure_all_types`.`mytime`, `Azure_all_types`.`myDECIMAL`, `Azure_all_types`.`myREAL` 

FROM `Azure_all_types`  

WHERE   `Azure_all_types`.`myvarchar` LIKE 'dog\'s%'

Woops. I'm being stupid here.
This looks like my error... I'm generating the \' not you... which looks wrong to me but does work on MySQL/MariaDB – just not other servers which makes sense to me.

I'll try and fix on my end and verify the right behavior.

It should be fixed for UPDATE, depending on the version you're using (see MDEV-13925)

However, some servers, including Oracle, do not accept escaping the quote with a backslash. Just doubling the quote is accepted by all servers.

Checking further, I realize that the problem still exists with select statements and where clauses containing a quote (doubled). Waiting for a fix, the turnaround is to specify it by 4 quotes!

select * from odbc_emp where last_name='O''''Connor';

Maybe doing \'\' also work (I did not try yet)

Yes, I was generating the \' in the where clause NOT connect. I just fixed our code, but unfortunately still not right. This is what our code is now submitting:

SELECT `Azure_all_types`.`id`, `Azure_all_types`.`mychar`, `Azure_all_types`.`myvarchar`, `Azure_all_types`.`mytext`, `Azure_all_types`.`mynchar`, `Azure_all_types`.`mynvarchar`, `Azure_all_types`.`myntext`, `Azure_all_types`.`myint`, `Azure_all_types`.`mysmallint`, `Azure_all_types`.`mytinyint`, `Azure_all_types`.`mybit`, `Azure_all_types`.`mynumeric`, `Azure_all_types`.`myfloat`, `Azure_all_types`.`mydate`, `Azure_all_types`.`mydatetime`, `Azure_all_types`.`mydatetime2`, `Azure_all_types`.`mysmalldatetime`, `Azure_all_types`.`mytime`, `Azure_all_types`.`myDECIMAL`, `Azure_all_types`.`myREAL` FROM `Azure_all_types`  WHERE   `Azure_all_types`.`myvarchar` LIKE 'dog''s%' 

You can see the doubled up single quote in the LIKE expression... Microsoft SQL Server, Postgres, and even a remote MySQL via JDBC connect doesn't like that. Your connection to this Azure test instance should still be valid for testing.

So it must be transforming it somehow?

Indeed, there is no turnaround. When specifying four quotes, the query send to the external server is allright but when MariaDB applies this false where clause, no lines are returned.

BTW, when telling that some servers "don't like that" what do you mean? That the query is not accepted or just that no lines are returned?

I have made a fix locally and tested in on the test_Nvarchar_type table in which I had inserted a row by:

insert into nchar2 values(80,'O''Connors');

and executed successfully with SQL Server the following queries:

select * from nchar2 where name_as_nvarchar = 'O''Connors';

select * from nchar2 where name_as_nvarchar = 'O\'Connors';

select * from nchar2 where name_as_nvarchar = "O'Connors";

My fix doubles the quote in the query that is sent to the server but it remains single in the original MariaDB query.

Actually, there is a turnaround. This is to prevent the where clause to be included in the query sent to the remote server and have it executed locally by MariaDB:

select * from (select * from nchar2) as x where name_as_nvarchar = "O'Connors";

Of course, this is not as good performance wise but the part of the where clause not containing a single quote can still be sent to the remote server:

select * from (select * from nchar2 where id > 10) as x where name_as_nvarchar = "O'Connors";

Your example above won't work with ANSI QUOTES turned on, correct? (this is our default configuration)

I think we can forget about the WHERE clause issues for now... and just get UPDATE statements with single quote characters working (100x more common for us).

In the current version, the following generates an error if the data being SET contains a single quote. This is with bound variables, so we don't control the escaping.

UPDATE `Azure_all_types` SET `myvarchar` = ? WHERE `id` = ?

The error from connect is:

(conn:7796) Got error 122 'ExecuteUpdate: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 's'. n=-1' from CONNECT)

This is the main one to deal with (at least for our use case). Trying to keep it simple to not introduce more bugs.

But it does as well with either:

where name_as_nvarchar = 'O''Connors'

or

where name_as_nvarchar = 'O\'Connors'

About the UPDATE case, could you publish the exact sequence of statements you're using? So I can try to reproduce and debug it.

On MS SQL when I do an update to a varchar column that has a single quote, I get the following error:

(conn:8286) Got error 122 'ExecuteUpdate: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 's'. n=-1' from CONNECT

The SQL shows as the prepared statement like this:

UPDATE `Azure_all_types` SET `myvarchar` = ? WHERE `id` = ? 

The Java code that binds looks like this (using 1.6 series driver if that matters):

this.pstmt = conn.prepareStatement(sqlUpdate); // this is the prepared statement above

this.pstmt.setString(bindIndex, dv.getString()); // this will be the *UNescaped* string in the SET - not sure what the driver does with it

this.pstmt.setString(bindIndex, dv.getPrimaryKeyValue()); // this will be the PK value for the id col

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).