Forum Discussion
Charset issue with Basic authentication
Hi,
I'm using PowerQuery as a datasource for an Excel worksheet. Data are exposed by a REST API supporting Basic Authentication. It is working well except when user name or password contains special characters like µ or é. It seems PowerQuery converts the provided "user:password" to US-ASCII during Basic authentication instead of using UTF-8. Spécial characters like µ and é are replaced with a "?" (char code 0x3F). Below is a Wireshark capture where I tried "µéè" as password and we can see it became "???":
I tried to add a "WWW-Authenticate" header on the 401 response with charset="UTF-8" in value like specified in the RFC : https://www.rfc-editor.org/rfc/rfc7617#section-2.1
WWW-Authenticate: Basic realm="simple", charset="UTF-8"
But it seems PowerQuery simply ignores this header because no more query is emitted after the first 401 Unauthorized.
I can workaround the problem using Anonymous authentication and providing the header manually:
let
Auth = User & ":" & Password,
Bytes = Text.ToBinary(Auth),
Credentials = Binary.ToText(Bytes, BinaryEncoding.Base64),
Source = Web.Contents("http://localhost:53671/7bc0cca0-f3ae-49ce-a229-d9daa0941103/TestBasicAuthentication/Get",
[
Headers = [#"Authorization" = "Basic " & Credentials]
])
in
Source
This solution works well but it is a security issue because the password would be exposed in clear text in the Excel file.
Is it possible to force Basic authentication to use UTF-8 encoding ?
At a high level, the answer is "whenever your Excel channel gets the December release of Power Query". There are more details in the below thread about how to determine which release channel you're on (which determines how quickly you'll receive PQ updates), and how to determine which version of PQ you have.
9 Replies
- AnonymousNot applicable
Hi bnobo ,
According to the syntax of Text.ToBinary():
The second parameter is optional encoding where you may try UTF-8
May help:
https://seddryck.wordpress.com/2017/06/22/encoding-basic-authentication-in-an-url/
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.