Subject: [postgresql-interfaces/psqlodbc] issue #42: Why does the ODBC driver expect the password in a connection string to be URL-encoded?

From: "omeuid (@omeuid)" <[email-masked]>
Date: Mon, 16 Sep 2024 17:11:37 +0000

ODBC driver expects the password in a connection string to be URL-encoded but I don't find any reason to do that.

This requirement could lead to connection issues when client applications (like Microsoft PowerBI) request the credentials from the user and create a connection string in the following way:
* "DSN=myDSN;UID=myUser;PWD=myPass;"

If the password contains characters that need to be encoded and the application does not perform any of the following changes the connection will fail:
* Encode password as the driver requires.
* Send the password in the connection string between brackets.

Currently, to avoid this issue there are two alternatives:
* Make the final user to encode their password. 
* Change the client application to encode the password properly.

The first option does not seem feasible many users are using the application. Also, the second option cannot be achieved by generic ODBC clients (for example, Microsoft PowerBI with the generic ODBC connector), as the client could not know this requirement.

I would suggest removing the `encode` and `decode` methods included in `dlg_specific.c` file.

Notes: 
* The option conn_settings was required to be URL-Encoded in the past, but this requirement was removed in [this ](https://github.com/postgresql-interfaces/psqlodbc/commit/94070db14b11de3cbf1fd3b510023e5057810e1c) commit.
    * Why? and Why not with the password? 
* This problem does not happen if the credentials stored in the DSN are used.
* Microsoft ODBC documentation of [SQLDriverConnect](https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqldriverconnect-function?view=sql-server-ver16) function
    * If this requirement is removed, the client could use the ODBC specification to determine if the password must be sent between brackets.
* Reviewed useful information in [this](https://www.postgresql.org/message-id/5194F426.1020000%40tpf.co.jp) message from the mailing list.

Please, feel free to ask anything which is not clear with my description.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 24 Sep 2024 10:54:44 +0000

Sorry for the late response. 

How else would you put special characters in the password ?

---

From: "omeuid (@omeuid)" <[email-masked]>
Date: Fri, 27 Sep 2024 16:34:39 +0000

You could use passwords with special characters like '+', '%' or '$' without requiring to URL-encode those values.

Why do you assume that using one of those characters in the password is a problem?

A generic ODBC client(for any ODBC driver) does not know this kind of requirements for this specific driver. It will use the ODBC api requirerments (See [comments](https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqldriverconnect-function?view=sql-server-ver16#comments) section).

So, when a client asks for user credentials, the following connection strings could be created.
* If the password contains a special character like '+' --> `DSN=mydsn;UID=myUser;PWD=my+pass`
* If the password contains a special character like ';' or '=' --> `DSN=mydsn;UID=myUser;PWD={my;pass}`
* If the password contains a special character like ';' or '=' and also a bracker '}' --> `DSN=mydsn;UID=myUser;PWD={my;}}pass}`

The first scenario fails with the current implementation of the driver (the '+' symbol should be URL-escaped).

Please, feel free to ask me anything which is not clear enough.