|
Let's say that we have the following user accounts:
CREATE USER 'no_curly_brace_user'@'%' IDENTIFIED BY 'semi;colon';
|
GRANT ALL PRIVILEGES ON *.* TO 'no_curly_brace_user'@'%';
|
|
CREATE USER 'open_curly_brace_user'@'%' IDENTIFIED BY 'curly;braces{';
|
GRANT ALL PRIVILEGES ON *.* TO 'open_curly_brace_user'@'%';
|
|
CREATE USER 'close_curly_brace_user'@'%' IDENTIFIED BY 'curly;braces}';
|
GRANT ALL PRIVILEGES ON *.* TO 'close_curly_brace_user'@'%';
|
These passwords contain semi-colons (;) and curly braces ({ and }). The ODBC standard says that when these values are used to set the PASSWORD connection keyword in a connection string, the values must be completely quoted in curly braces:
Because of connection string and initialization file grammar, keywords and attribute values that contain the characters []{}(),;?*=!@ not enclosed with braces should be avoided. The value of the DSN keyword cannot consist only of blanks and should not contain leading blanks. Because of the grammar of the system information, keywords and data source names cannot contain the backslash () character.
...
A DSN or connection string value enclosed with braces ({}) containing any of the characters []{}(),;?*=!@ is passed intact to the driver. However, when using these characters in a keyword, the Driver Manager returns an error when working with file DSNs but passes the connection string to the driver for regular connection strings. Avoid using embedded braces in a keyword value.
https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqldriverconnect-function?redirectedfrom=MSDN&view=sql-server-ver15
Unfortunately, MariaDB Connector/ODBC does not seem to properly handle closing curly braces in keyword values, so the 'close_curly_brace_user'@'%' user account defined above will fail to connect.
For example, the 'no_curly_brace_user'@'%' user account succeeds:
$ isql -v -k 'Driver={/usr/lib/libmaodbc.so};SERVER={127.0.0.1};PORT={3306};User={no_curly_brace_user};PASSWORD={semi;colon};'
|
+---------------------------------------+
|
| Connected! |
|
| |
|
| sql-statement |
|
| help [tablename] |
|
| quit |
|
| |
|
+---------------------------------------+
|
SQL> SELECT * FROM spider_sharded_sales.invoices;
|
+------------+------------+------------+---------------------------+----------------+---------------+
|
| branch_id | invoice_id | customer_id| invoice_date | invoice_total | payment_method|
|
+------------+------------+------------+---------------------------+----------------+---------------+
|
| 1 | 4 | 1 | 2020-05-10 12:35:10.000000| 1087.23 | CREDIT_CARD |
|
| 1 | 5 | 2 | 2020-05-10 14:17:32.000000| 1508.57 | WIRE_TRANSFER |
|
| 1 | 6 | 3 | 2020-05-10 14:25:16.000000| 227.15 | CASH |
|
| 2 | 1 | 2 | 2020-05-10 12:31:00.000000| 1351.04 | CREDIT_CARD |
|
| 2 | 2 | 2 | 2020-05-10 12:45:27.000000| 162.11 | WIRE_TRANSFER |
|
| 2 | 3 | 4 | 2020-05-10 13:11:23.000000| 350.00 | CASH |
|
| 3 | 1 | 5 | 2020-05-10 12:31:00.000000| 111.50 | CREDIT_CARD |
|
| 3 | 2 | 8 | 2020-05-10 12:45:27.000000| 1509.23 | WIRE_TRANSFER |
|
| 3 | 3 | 3 | 2020-05-10 13:11:23.000000| 3301.66 | CASH |
|
+------------+------------+------------+---------------------------+----------------+---------------+
|
SQLRowCount returns 9
|
9 rows fetched
|
And the 'open_curly_brace_user'@'%' user account succeeds:
$ isql -v -k 'Driver={/usr/lib/libmaodbc.so};SERVER={127.0.0.1};PORT={3306};User={open_curly_brace_user};PASSWORD={curly;braces{};'
|
+---------------------------------------+
|
| Connected! |
|
| |
|
| sql-statement |
|
| help [tablename] |
|
| quit |
|
| |
|
+---------------------------------------+
|
SQL> SELECT * FROM spider_sharded_sales.invoices;
|
+------------+------------+------------+---------------------------+----------------+---------------+
|
| branch_id | invoice_id | customer_id| invoice_date | invoice_total | payment_method|
|
+------------+------------+------------+---------------------------+----------------+---------------+
|
| 1 | 4 | 1 | 2020-05-10 12:35:10.000000| 1087.23 | CREDIT_CARD |
|
| 1 | 5 | 2 | 2020-05-10 14:17:32.000000| 1508.57 | WIRE_TRANSFER |
|
| 1 | 6 | 3 | 2020-05-10 14:25:16.000000| 227.15 | CASH |
|
| 2 | 1 | 2 | 2020-05-10 12:31:00.000000| 1351.04 | CREDIT_CARD |
|
| 2 | 2 | 2 | 2020-05-10 12:45:27.000000| 162.11 | WIRE_TRANSFER |
|
| 2 | 3 | 4 | 2020-05-10 13:11:23.000000| 350.00 | CASH |
|
| 3 | 1 | 5 | 2020-05-10 12:31:00.000000| 111.50 | CREDIT_CARD |
|
| 3 | 2 | 8 | 2020-05-10 12:45:27.000000| 1509.23 | WIRE_TRANSFER |
|
| 3 | 3 | 3 | 2020-05-10 13:11:23.000000| 3301.66 | CASH |
|
+------------+------------+------------+---------------------------+----------------+---------------+
|
SQLRowCount returns 9
|
9 rows fetched
|
But the 'close_curly_brace_user'@'%' user account fails:
$ isql -v -k 'Driver={/usr/lib/libmaodbc.so};SERVER={127.0.0.1};PORT={3306};User={close_curly_brace_user};PASSWORD={curly;braces}};'
|
[28000][unixODBC][ma-3.1.7]Access denied for user 'close_curly_brace_user'@'127.0.0.1' (using password: YES)
|
[ISQL]ERROR: Could not SQLDriverConnect
|
|