Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
1.6.3
-
None
-
Centos7
Description
See MDEV-13925 for details.
It appears when using prepared statements and binding a string that contains special characters such as single quote (') or double quote (") and possibly others, the JDBC driver escapes with a \' or \".
While this is apparently valid syntax for MariaDB, when passed to a CONNECT table it is often incompatible with the remote server (Microsoft, Postgres).
Is there any reason not to just place the string in single quotes and escape single quotes the ANSI way (doubling up on single quotes) and not escaping any other characters?
There is 2 different prepared statement in Mariadb jdbc driver :
Server implementation on Connection.prepareStatement(<query>) will send a PREPARE command to server,
prepareStatement.execute() will send parameters to server and execute command.
Client implementation is different : driver will parse query and replace "?" parameters with escaped parameters.
Example :
query "Insert into table1 value ".
parameter is "Bob's".
There is 2 different escapes according to server NO_BACKSLASH_ESCAPES (https://mariadb.com/kb/en/library/sql-mode/ ).
if NO_BACKSLASH_ESCAPES is set, quote will be escaped by quote. if not set, escape character is BACKSLASH.
So depending on SQL_MODE values :
if NO_BACKSLASH_ESCAPES is set: "Insert into table1 value ('Bob''s')"
if NO_BACKSLASH_ESCAPES is NOT set : "Insert into table1 value ('Bob\'s')"
By default NO_BACKSLASH_ESCAPES is not set, so escape will use BACKSLASH, then beeing compatible with other DBMS.
this SQL_MODE can be set on connectionString. Example :
"jdbc:mariadb://host/db?user=xxx&sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES"