Querying a user the right way? (WebloginPE issue)

There’s [tt]function QueryDbForUser($Username)[/tt] in the webloginpe.class.php file which returns ’false’ if no user found.
The query looks like this:

[tt]$query = "SELECT * FROM ".$web_users.", ".$web_user_attributes." WHERE BINARY LOWER(".$web_users.".username) = ’".strtolower($Username)."’ AND ".$web_user_attributes.".`internalKey` = ".$web_users.".`id`";[/tt]

Unfortunately, when a username contains non-latin utf8 characters like áéó etc. the return value is ’false’. (This crashes the WLPE snippet!)
If I remove BINARY LOWER and strtolower() from the query, it seems to get any user:

[tt]$query = "SELECT * FROM ".$web_users.", ".$web_user_attributes." WHERE (".$web_users.".username) = ’".$Username."’ AND ".$web_user_attributes.".`internalKey` = ".$web_users.".`id`";[/tt]

I’m quite not an expert, I only do exmerimeting with php/mysql in MODx.
So ask you if this workaround is acceptable in the programming scene?
Thanks!

It would be better to declare a locale; Windows users take especial note of the warning, and several of the user comments on the page.

There are quite a number of workaround functions in the user comments on this page http://www.php.net/strtolower

This function is sensible to the current locale, namely the LC_CTYPE category (the default LC_CTYPE category is set from the LANG environment variable or by an explicit LC_CTYPE setting, but it can be overriden by the LC_ALL environment setting). If no locale setting is done in the enironment, the default locale will be C, for which the lowercase/uppercase conversion is based on the default character set of the system: this may convert only ASCII letters, or also ISO-8859-1 letters depending on the system...
add a note

In other words, if no locale has been set, PHP will use the locale of the server itself, and most likely this will not be utf-8.

I’m sorry Susan, it’s over my understanding...
But I gave it a tray on a Linux-Apache server, and I got the same issue and the same workaround.

The issue here is that certain text-handling functions of PHP depend on the locale that has been set. If no locale was set in the php script, then PHP will use the locale of the server operating system (Windows, Linux, etc). The bottom line is that these string-handling functions will most likely not behave as you would expect on all servers, and will most certainly behave differently on different servers.

I can’t get setlocale() to make the output of BINARY LOWER and strtolower() identical in this snippet...

The MODx Manager treats the usernames dragó and dragÓ different, while it treats dragó and Dragó the same.
Here is the query in the save_web_user.processor.php file on line 78, that is for checking if the username already exists:

[tt]$sql = "SELECT id FROM $dbase.`" . $table_prefix . "web_users` WHERE username=’$newusername’";[/tt]

Nor it does switch to lowecase, so that it treats dragó and Dragó identical may may come from MySQL?
In this processor file I can’t find validity check for the username. Is that correct? Are every characters acceptable?

Wonderfully, without case-switching, WebloginPE treats dragó, Dragó, dragÓ and DRAGÓ the same way as the Manager does!

Please someone tell that the case switching is not necessary when querying a user’s data, and then the snippet’s community can start to test this solution with different languages & accented chars to let the next release be more optimal!

Thanks!

Oops, it’s working in lowecase, but without BINARY!!! (As I just saw it in the PhpBB forum’s username validation)

Quote from: vhollo at Aug 04, 2008, 02:06 PM

In this processor file I can’t find validity check for the username. Is that correct? Are every characters acceptable?

Actually the processor file can do it with
if (!$rs = mysql_query($sql)) {
webAlert("An error occurred while attempting to retrieve all users with username $newusername.");
exit;
}
Trying this if (!$rs =...) kinda thing on the web side doesn’t catch and breaks with a MODx error report.
What should I do to check if the username craps without explicitly filter out bad chars?
The original (v1.30) snippet filtered these chars:
$illegals = array(’.’ , ’,’ , ’/’ , ’\\’ , ’`’ , ’;’ , ’[’ ,  ’]’ , ’-’, "’", ’*’, ’&’, ’^’, ’%’, ’$’, ’#’, ’@’, ’!’, ’~’, ’+’, ’(’, ’)’, ’|’, ’{’, ’}’, ’<’, ’>’, ’?’, ’:’, ’"’, ’=’);
I know that not all, but which ones of these are necessary, and what are missing? I actually found only these two characters to be harmful: \ and ’
Or can I catch the error on the web side like the processor does on the backend?