[4764] Uppercase Usernames can't be found (using postgres)

[habakuk]

SMF 2.0rc5
MacOSX 10.6.x
Apache 2.x
PHP 5.3.x
Postgresql 8.3.x
Using English UTF-8 and German UTF-8

PROBLEM:
I do have usernames that start with uppercase and some with lowercase letters. Using the search function (index.php?action=admin;area=viewmembers;sa=search;) to find members does not work, if the username starts with a uppercase letter.

Searching for "Testuser" does not find the user
Searching for "*estuser" does find the user.

I guess, there is a problem with case sensitivity in the search function using postgresql.

cheers

[habakuk]

Update:

In the mean time, I upgraded to the 2.0 final release. Yet, the problem persists.

[habakuk]

Anyone able to confirm this problem?

[emanuele]

Yes, can I confirm it.

With MySQL the search is case-insentive, while with postgreSQL is case-sensitive.

It affects only the search function for members.

Edit: it affects also the normal search if you specify the member name.

[emanuele]

This could be a fix.
In Memberlist.php:

Code (search for) Select Expand

'search' => '%' . strtr($smcFunc['htmlspecialchars']($_POST['search'], ENT_QUOTES), array('_' => '\\_', '%' => '\\%', '*' => '%')) . '%',

Code (replace with) Select Expand

'search' => '%' . strtolower(strtr($smcFunc['htmlspecialchars']($_POST['search'], ENT_QUOTES), array('_' => '\\_', '%' => '\\%', '*' => '%'))) . '%',

Code (search for) Select Expand

$fields = array('member_name', 'real_name');

Code (replace with) Select Expand

$fields = array('lower(member_name)', 'lower(real_name)');

Then in Search.php:

Code (search for) Select Expand

'match_possible_users' => 'real_name LIKE ' . implode(' OR real_name LIKE', $realNameMatches),

Code (replace with) Select Expand

'match_possible_users' => 'lower(real_name) LIKE lower(' . implode(' OR real_name LIKE lower(', $realNameMatches) . ')',

And finally in ManageMembers.php

Code (search for) Select Expand

$_POST[$param_name] = strtotime($_POST[$param_name]);
}

Code (add after) Select Expand


elseif ($param_name == 'membername')
{
$_POST[$param_name] = strtolower($_POST[$param_name]);
}

Code (search for) Select Expand

$query_parts[] = '(' . implode( ' LIKE {string:' . $param_name . '_normal} OR ', $param_info['db_fields']) . ' LIKE {string:' . $param_name . '_normal})';
$where_params[$param_name . '_normal'] = '%' . $parameter . '%';

Code (replace with) Select Expand

if ($param_name == 'membername')
{
$query_parts[] = 'lower(' . implode( ') LIKE {string:' . $param_name . '_normal} OR lower(', $param_info['db_fields']) . ') LIKE {string:' . $param_name . '_normal}';
$where_params[$param_name . '_normal'] = '%' . $parameter . '%';
}
else
{
$query_parts[] = '(' . implode( ' LIKE {string:' . $param_name . '_normal} OR ', $param_info['db_fields']) . ' LIKE {string:' . $param_name . '_normal})';
$where_params[$param_name . '_normal'] = '%' . $parameter . '%';
}

Don't know if I missed anything else.
Tested only partially with MySQL.