Cannot login with non-Latin characters

Get help with installation and running phpBB 3.1.x here. Please do not post bug reports, feature requests, or extension related questions here.
Suggested Hosts
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: phpBB 3.1.x is at its End of Life stage and support will NOT be provided after July 1st, 2018.
User avatar
david63
Registered User
Posts: 19850
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Contact:

Re: Cannot login with non-Latin characters

Post by david63 »

Just had a thought - rather than trying to work out how/why it has changed would it not be quicker to write a script to loop through the database and re-create them?
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored
User avatar
MarkDHamill
Registered User
Posts: 4554
Joined: Fri Aug 02, 2002 12:36 am
Location: Florence, MA USA
Contact:

Re: Cannot login with non-Latin characters

Post by MarkDHamill »

This was indeed the problem. Since after doing the upgrade the incorrect way and then the correct way, the issue wasn't a matter of the upgrade process. It appears to be some subtle bug in the upgrade program itself. I don't know if at some point it recalculates username_clean, but it didn't do it in this case. I will submit this as a bug. My program with the database fix is below:

Code: Select all

<?php

define('IN_PHPBB', true);
// Specify the path to you phpBB3 installation directory.
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
// The common.php file is required.
include($phpbb_root_path . 'common.' . $phpEx);

// Start session management
$user->session_begin();
$auth->acl($user->data);

$user->setup();

//your PHP and/or HTML code goes here

$sql = "SELECT user_id, username, username_clean FROM phpbb3_users WHERE locate('ø', username_clean) > 0 OR locate('æ', username_clean) > 0 OR 
locate('å', username_clean) > 0";

$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
	echo $row['user_id'] . '~' . $row['username'] . '~' . $row['username_clean'] . '~' . strToHex($row['username_clean']) . '~' . utf8_clean_string($row['username']) . '~' . strToHex(utf8_clean_string($row['username'])) . '<br />';
	$sql2 = "UPDATE phpbb3_users SET username_clean = '" . utf8_clean_string($row['username']) . "' WHERE user_id = " . $row['user_id'];
	$db->sql_query($sql2);
}

function strToHex($string){
    $hex = '';
    for ($i=0; $i<strlen($string); $i++){
        $ord = ord($string[$i]);
        $hexCode = dechex($ord);
        $hex .= substr('0'.$hexCode, -2);
    }
    return strToUpper($hex);
}

?>
Need phpBB services or a phpBB consultant? I offer most phpBB services. Getting lost managing phpBB? Buy my book, Mastering phpBB Administration. eBook and paper versions available.
Paul
Infrastructure Team Leader
Infrastructure Team Leader
Posts: 27491
Joined: Sat Dec 04, 2004 3:44 pm
Location: The netherlands.
Name: Paul Sohier
Contact:

Re: Cannot login with non-Latin characters

Post by Paul »

A better solution would be to use utf8_clean_string() on the username collumn, as that is what login uses as well.
I would also do this for all users, instead just a few while ther emight be more involved.
User avatar
MarkDHamill
Registered User
Posts: 4554
Joined: Fri Aug 02, 2002 12:36 am
Location: Florence, MA USA
Contact:

Re: Cannot login with non-Latin characters

Post by MarkDHamill »

Paul, wouldn't that lowercase the username column?
Need phpBB services or a phpBB consultant? I offer most phpBB services. Getting lost managing phpBB? Buy my book, Mastering phpBB Administration. eBook and paper versions available.
Paul
Infrastructure Team Leader
Infrastructure Team Leader
Posts: 27491
Joined: Sat Dec 04, 2004 3:44 pm
Location: The netherlands.
Name: Paul Sohier
Contact:

Re: Cannot login with non-Latin characters

Post by Paul »

Ow wait, you are actually doing that what I ment already. Only thing different what I would do is just doing it for all users
User avatar
MarkDHamill
Registered User
Posts: 4554
Joined: Fri Aug 02, 2002 12:36 am
Location: Florence, MA USA
Contact:

Re: Cannot login with non-Latin characters

Post by MarkDHamill »

I thought about that, but Danish has only three non-Latin characters and only users with those in username_clean were having issues.
Need phpBB services or a phpBB consultant? I offer most phpBB services. Getting lost managing phpBB? Buy my book, Mastering phpBB Administration. eBook and paper versions available.
User avatar
3Di
Former Team Member
Posts: 16925
Joined: Mon Apr 04, 2005 11:09 pm
Location: Have a guess... 🇮🇹
Name: Marco
Contact:

Re: Cannot login with non-Latin characters

Post by 3Di »

MarkDHamill wrote:According to http://unicodelookup.com/#latin/1 the Unicode for ø is F8 but what is being stored is C3B8

So these non-Latin characters all start with C3 in hex.
Confusing Code Points (Unicode/windows1652 or ISO 8859-1) here with UTF-8?

The Code Point Unicode for ø is U+00F8 (right)
The Code Point Windows1652 or ISO 8859-1 for ø is 0xF8 (right)
The UTF-8 bytes for ø are %C3 %B8 (right)

Since the phpBB upgrader takes care of UTF-8, that by all means is entitled to cover all of the possible characters I guess is not a phpBB bug, just my 2 €cents though. Does your customer's server works under Windows server? Did you upgraded from 3.0.rc3 to 3.0.0 or you missed this step going directly to 3.0.4?
I remember I saw a such issue in the past, unable to find a link though.

http://i18nqa.com/debug/utf8-debug.html

Glad you got a work-around to fix your custom job's issue though. ;)
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
User avatar
MarkDHamill
Registered User
Posts: 4554
Joined: Fri Aug 02, 2002 12:36 am
Location: Florence, MA USA
Contact:

Re: Cannot login with non-Latin characters

Post by MarkDHamill »

The forum was originally on phpBB2. I suspect the original encoding was ISO-8559-1, as I've seen it before as a workaround to deal with non Latin characters. That's probably how the problem started. Most likely the converter from 2 to 3 had issues with these character sets.

There's a lot to this encoding stuff, that's for sure.
Need phpBB services or a phpBB consultant? I offer most phpBB services. Getting lost managing phpBB? Buy my book, Mastering phpBB Administration. eBook and paper versions available.
Locked

Return to “[3.1.x] Support Forum”