Charset issue with Basic authentication

[bnobo]

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 ?

[v-eqin-msft]

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.

[bnobo]

This issue is not related to M language but occurs when using the built-in Basic auth. Please read @Ehren message and following bellow

[Ehren]

Hi @bnobo . Just to confirm: the issue occurs when using the built-in Basic auth, not passing the creds manually via custom M. Is that correct?

[bnobo]

Hi @Ehren , you are right, the issue occurs when using the built-in Basic auth. 

[Ehren]

Ok. Let me see if there's a way to solve this without breaking backwards-compatibility.

[bnobo]

You could handle the "charset" auth-param returned by the server in WWW-Authenticate header when the authentication fail (401 status code) as stated in Basic Authentication RFC  7617: https://datatracker.ietf.org/doc/html/rfc7617#section-2.1

If the WWW-Authenticate header returned by the server contains 'charset="UTF-8"' then re-encode username:password with UTF-8 encoding and re-emit the request.

Doing so, the encoding would change only for servers requesting UTF-8 encoding.

[Ehren]

Because UTF-8 encoding for ASCII characters is identical to ASCII encoding, it should be safe to always encode with UTF-8. The only existing case this would "break" would be non-ASCII characters that today are being mapped to "?", but if anyone is depending on that behavior they can simply use "?" in their username/password instead.

The fix should be available in the December release of PBIDesktop.

[bnobo]

When the fix will be available for Microsoft Excel ?

[Ehren · Accepted solution]

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.

https://social.technet.microsoft.com/Forums/en-US/496325f1-bad0-49fd-a293-be654ccaf67d/power-query-v...