<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.E-MailFormatvorlage18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.E-MailFormatvorlage19
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style>
</head>
<body lang="DE" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Hi Timothy and everyone,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">thank you for your help, I think I found the issue.
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">We don’t access a table directly, but a View that is being created with a SELECT statement.
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">The view itself is UTF-8, but the Column “surname” is converted to Binary for some reason:
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> ,cast(`u`.`surname` AS CHAR charset BINARY) AS `surname`<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">This results in it not having a utf8 character set:<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span lang="EN-US" style="mso-fareast-language:EN-US">+-------------+--------------------+<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span lang="EN-US" style="mso-fareast-language:EN-US">| column_name | character_set_name |<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span lang="EN-US" style="mso-fareast-language:EN-US">+-------------+--------------------+<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span lang="EN-US" style="mso-fareast-language:EN-US">| surname | NULL |<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span lang="EN-US" style="mso-fareast-language:EN-US">+-------------+--------------------+<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">As we didn’t create the view/database our self, I’m gonna have to ask why that is done and everything else.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">Thanks again, you all were a huge help!<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">Greetings
<br>
Philip<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><b>Von:</b> Timothy Spear <n614cd@gmail.com> <br>
<b>Gesendet:</b> Donnerstag, 13. Februar 2025 20:15<br>
<b>An:</b> Grüter, Philip <philip.grueter@secunet.com><br>
<b>Betreff:</b> Re: Umlaut (Ü, Ä, Ö) in attribute resolution not UTF-8 encoded<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">Philip,<o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">The fact that forcing a conversion to UTF-8 in the result heavily implies the character encoding is incorrect or lost somewhere further up the chain. I am a few years out of date on using international character encoding with MySQL, so
take what I am going to cover in detail with a grain of salt. And as a developer, one of the things I have learned is to always question my own assumptions and verify. <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">MySQL has six areas of character encoding. <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Database, Table, Column, Client, Connection, Result Set.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">I can never recall the details of who wins except the generic advice of the most specific setting wins. e.g. If you set the character encoding of a column, it will override the character encoding of the connection and the table. But set
the character encoding of the result set, it will win...<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Therefore, I would approach this the following way. Connect to MySQL using the mysql command line interface. The reason is for using the CLI is because the CLI usually has the fewest assumptions and overrides applied.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">I would start by verifying the character encoding is as you expect for the database, table and column. Then the JDBC connection related items.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">(I have to web search these queries, I have not tested them)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">For schema/database<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><br>
SELECT default_character_set_name FROM information_schema.SCHEMATA <br>
WHERE schema_name = "userdb";<br>
<br>
For Tables:<br>
<br>
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,<br>
information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA<br>
WHERE CCSA.collation_name = T.table_collation<br>
AND T.table_schema = "userdb"<br>
AND T.table_name = "moodle2_auth";<br>
<br>
For Columns:<br>
<br>
SELECT character_set_name FROM information_schema.`COLUMNS` <br>
WHERE table_schema = "userdb"<br>
AND table_name = "moodle2_auth"<br>
AND column_name = "surname";<br>
<br>
<br>
"status\G" should show the other three; note: these will be default values which is what you are looking for. This could provide a hint on a specific character set which is not being forced to your standard in the JDBC connection.<br>
<br>
If all of these are correct. Then the next place would be to check the data being inserted. To me, it is a tossup if I would pursue JDBC connection logs or start from the other end and verify the data coming into Shibboleth.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Tim<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">On Thu, Feb 13, 2025 at 1:12 PM Grüter, Philip <<a href="mailto:philip.grueter@secunet.com">philip.grueter@secunet.com</a>> wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<div>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Hi Tim,<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">my JDBC connection string is already enforcing the UTF-8 character set:
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">jdbc:mysql://REDACTED IP:6033/userdb?useUnicode=true&characterEncoding=UTF-8</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">And the database and table is also in UTF-8.
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">That’s why I’m confused about the wrong encoding.</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">Greetings</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">Philip
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><b>Von:</b> users <<a href="mailto:users-bounces@shibboleth.net" target="_blank">users-bounces@shibboleth.net</a>>
<b>Im Auftrag von </b>Timothy Spear via users<br>
<b>Gesendet:</b> Donnerstag, 13. Februar 2025 19:09<br>
<b>An:</b> Shib Users <<a href="mailto:users@shibboleth.net" target="_blank">users@shibboleth.net</a>><br>
<b>Cc:</b> Timothy Spear <<a href="mailto:n614cd@gmail.com" target="_blank">n614cd@gmail.com</a>><br>
<b>Betreff:</b> Re: Umlaut (Ü, Ä, Ö) in attribute resolution not UTF-8 encoded<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Philip,<o:p></o:p></p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">That suggests the JDBC connection is using the default latin1 character set. <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Try forcing the character set assignment on the JDBC URL connection string. <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Tim<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
</div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">On Thu, Feb 13, 2025 at 12:58 PM Grüter, Philip <<a href="mailto:philip.grueter@secunet.com" target="_blank">philip.grueter@secunet.com</a>> wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt">
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Hi Scott and Gianluca,<br>
<br>
I don't really think it has something to do with my terminal or even the output of the aacli.sh tool.<br>
The IDP is connected via SAML2 to a moodle system, which is also getting the wrong umlauts.<br>
Also, the log is also displaying the wrong encoding. When I use the same terminal to connect to the MySQL Database, it displays the Ü correctly.
<br>
<br>
Also, I got it working, but I don't consider my current solution a good one. I changed the SQL Query in my attribute-resolver config to:
<br>
SELECT email,forename,CONVERT(surname USING utf8) AS surname FROM moodle2_auth WHERE username='$resolutionContext.principal'<br>
This outputs the correct result. So in my opinion, the wrong encoding has something to do with the gathering of the data.<br>
<br>
- Philip<br>
<br>
-----Ursprüngliche Nachricht-----<br>
Von: users <<a href="mailto:users-bounces@shibboleth.net" target="_blank">users-bounces@shibboleth.net</a>> Im Auftrag von Cantor, Scott via users<br>
Gesendet: Donnerstag, 13. Februar 2025 17:20<br>
An: Shib Users <<a href="mailto:users@shibboleth.net" target="_blank">users@shibboleth.net</a>><br>
Cc: Cantor, Scott <<a href="mailto:cantor.2@osu.edu" target="_blank">cantor.2@osu.edu</a>><br>
Betreff: Re: Umlaut (Ü, Ä, Ö) in attribute resolution not UTF-8 encoded<br>
<br>
> Are you sure it is not a problem related to your terminal ?<br>
<br>
Without going that far, I will say it's incredibly hard to test and debug this sort of thing because of all the issues with log files, terminal display, etc. It's easy to mix up where the issue is.<br>
<br>
But I wouldn't swear the output logic for the view in that flow isn't wrong somewhere. I would suggest as one thing to try the --saml2 switch and see if the XML seems to be right.<br>
<br>
I'm in the middle of working out some of the Unicode issues with the SP redesign, and I truly believe aliens invading and mandating a new language would be simpler for the world than handling Unicode in C++.<br>
<br>
-- Scott<br>
<br>
<br>
-- <br>
For Consortium Member technical support, see <a href="https://shibboleth.atlassian.net/wiki/x/ZYEpPw" target="_blank">
https://shibboleth.atlassian.net/wiki/x/ZYEpPw</a><br>
To unsubscribe from this list send an email to <a href="mailto:users-unsubscribe@shibboleth.net" target="_blank">
users-unsubscribe@shibboleth.net</a><br>
-- <br>
For Consortium Member technical support, see <a href="https://shibboleth.atlassian.net/wiki/x/ZYEpPw" target="_blank">
https://shibboleth.atlassian.net/wiki/x/ZYEpPw</a><br>
To unsubscribe from this list send an email to <a href="mailto:users-unsubscribe@shibboleth.net" target="_blank">
users-unsubscribe@shibboleth.net</a><o:p></o:p></p>
</blockquote>
</div>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</body>
</html>