phpBB :: Knowledge base - Viewing article Fixing PostgreSQL Case Sensitivity

Support Home | Knowledge Base Home | Submit Article | Search Articles | Browse Articles

Fixing PostgreSQL Case Sensitivity

Description: This article describes how to remove the annoyance of case sensitivity in certain areas of phpBB for PostgreSQL users

Author: R45

Date: Thu May 13, 2004 5:45 am

Type: Fix

Keywords: postgresql, case, sensitivity, sensttive, insensitive, insensitivity

Category: Improvements

Fixing PostgreSQL Case Sensitivity

phpBB users who use PostgreSQL as their database backend would encounter a very annoying issue whereby several portions of phpBB are case sensitive. Notable areas would be Logging in (Your username must be in the same case as your registered), Private Messaging and Username searching. This is due to the fact that unlike MySQL, PostgreSQL datatypes are NOT case sensitive by default. Perhaps an oversight by phpBB the phpBB developers, you need to modify your SQL statements to gain case insensitivity in PostgreSQL.

To go about changing the behaviour of queries, I'd like to run through the different ways of achieving it. Basically, there are three options for us. There is a PostgreSQL operator called ILIKE, which does a case insensitive match. It performs just like the regular LIKE operator, except for the case insensitivity. I choose not to use this for two reasons.

The second option is using Regular Expressions, the ~ operator. I also choose not to use this option because Regular Expressions are very slow and point b as per above applies.

The method I prefer to use is lowering the case of the fields we are comparing while we execute the query. We achieve this using the LOWER() SQL function (which is ANSI/SQL compatible) and the PHP function strtolower().

Fixing Logging In

For the login process, we will need to alter how the username inputted in the login box is compared to the username in the database. On line 64 (note the line number is as of phpBB 2.0.8a. Future and previous releases may alter the line number) of login.php (which should be in your forum root directory, find

Code:

WHERE username = '" . str_replace("\\'", "''", $username) . "'";

We will alter this portion of the query to lower the case on both sides, achieving case insensitivity.

Code:

WHERE LOWER(username) = '" . str_replace("\\'", "''", strtolower($username)) . "'";

Note: We put the SQL function LOWER() in upper case only by a de facto standard whereby SQL statements remain upper case. Lower() would also work. However, PHP functions are case sensitive.

Save, close and upload the file and your logging in is fixed. Below is a diff of the above change, for users who would like to use the unix patch utility

Code:

***************
*** 61,67 ****

$sql = "SELECT user_id, username, user_password, user_active, user_level
FROM " . USERS_TABLE . "
! WHERE username = '" . str_replace("\\'", "''", $username) . "'";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Error in obtaining userdata', '', __LINE__, __FILE__, $sql);
--- 61,67 ----

$sql = "SELECT user_id, username, user_password, user_active, user_level
FROM " . USERS_TABLE . "
! WHERE LOWER(username) = '" . str_replace("\\'", "''", strtolower($username)) . "'";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Error in obtaining userdata', '', __LINE__, __FILE__, $sql);

Fixing Searching

Adjusting the search queries require more edits, but it is rather simple (thanks to the fact that we don't need to fix keyword search due to the fact that phpBB's search system stores words in lowercase in the database and changes inputted keywords to lowercase at search time). First, we start by fixing the username searching, which applies to various Admin Panel screens. In /includes/functions.php, on line 98, find

Code:

$sql .= ( ( is_integer($user) ) ? "user_id = $user" : "username = '" . $user . "'" ) . " AND user_id <> " . ANONYMOUS;

replace with

Code:

$sql .= ( ( is_integer($user) ) ? "user_id = $user" : "LOWER(username) = '" . strtolower($user) . "'" ) . " AND user_id <> " . ANONYMOUS;

Save and upload. A diff of that change would be:

Code:

***************
*** 95,101 ****
$sql = "SELECT *
FROM " . USERS_TABLE . "
WHERE ";
! $sql .= ( ( is_integer($user) ) ? "user_id = $user" : "username = '" . $user . "'" ) . " AND user_id <> " . ANONYMOUS;
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Tried obtaining data for a non-existent user', '', __LINE__, __FILE__, $sql);
--- 95,101 ----
$sql = "SELECT *
FROM " . USERS_TABLE . "
WHERE ";
! $sql .= ( ( is_integer($user) ) ? "user_id = $user" : "LOWER(username) = '" . strtolower($user) . "'" ) . " AND user_id <> " . ANONYMOUS;
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Tried obtaining data for a non-existent user', '', __LINE__, __FILE__, $sql);

Now, we need to do an update to search.php to fix Author searching. On line 201 find

Code:

WHERE username LIKE '" . str_replace("\'", "''", $search_author) . "'";

replace with

Code:

WHERE LOWER(username) LIKE '" . str_replace("\'", "''", strtolower($search_author)) . "'";

Save and upload. Diff:

Code:

***************
*** 198,204 ****

$sql = "SELECT user_id
FROM " . USERS_TABLE . "
! WHERE username LIKE '" . str_replace("\'", "''", $search_author) . "'";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, "Couldn't obtain list of matching users (searching for: $search_author)", "", __LINE__, __FILE__, $sql);
--- 198,204 ----

$sql = "SELECT user_id
FROM " . USERS_TABLE . "
! WHERE LOWER(username) LIKE '" . str_replace("\'", "''", strtolower($search_author)) . "'";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, "Couldn't obtain list of matching users (searching for: $search_author)", "", __LINE__, __FILE__, $sql);

Lastly, we need to fix the username searching (The little popup username searching box). Open /includes/functions_search.php, on line 442 find:

Code:

WHERE username LIKE '" . str_replace("\'", "''", $username_search) . "' AND user_id <> " . ANONYMOUS . "

replace with

Code:

WHERE LOWER(username) LIKE '" . str_replace("\'", "''", strtolower($username_search)) . "' AND user_id <> " . ANONYMOUS . "

Save and upload. Diff:

Code:

***************
*** 439,445 ****

$sql = "SELECT username
FROM " . USERS_TABLE . "
! WHERE username LIKE '" . str_replace("\'", "''", $username_search) . "' AND user_id <> " . ANONYMOUS . "
ORDER BY username";
if ( !($result = $db->sql_query($sql)) )
{
--- 439,445 ----

$sql = "SELECT username
FROM " . USERS_TABLE . "
! WHERE LOWER(username) LIKE '" . str_replace("\'", "''", strtolower($username_search)) . "' AND user_id <> " . ANONYMOUS . "
ORDER BY username";
if ( !($result = $db->sql_query($sql)) )
{

Fixing Private Messaging

Only one edit to fix this one, open privmsg.php, on line 1139 find:

Code:

WHERE username = '" . str_replace("\'", "''", $to_username) . "'

replace with

Code:

WHERE LOWER(username) = '" . str_replace("\'", "''", strtolower($to_username)) . "'

Save and Upload. Diff:

Code:

***************
*** 1136,1142 ****

$sql = "SELECT user_id, user_notify_pm, user_email, user_lang, user_active
FROM " . USERS_TABLE . "
! WHERE username = '" . str_replace("\'", "''", $to_username) . "'
AND user_id <> " . ANONYMOUS;
if ( !($result = $db->sql_query($sql)) )
{
--- 1136,1142 ----

$sql = "SELECT user_id, user_notify_pm, user_email, user_lang, user_active
FROM " . USERS_TABLE . "
! WHERE LOWER(username) = '" . str_replace("\'", "''", strtolower($to_username)) . "'
AND user_id <> " . ANONYMOUS;
if ( !($result = $db->sql_query($sql)) )
{

Fixing the Group CP

Lastly, the adding of members to groups suffers the same flaw. To fix, open groupcp.php, on line 479 find:

Code:

WHERE username = '" . str_replace("\'", "''", $username) . "'";

replace with

Code:

WHERE LOWER(username) = '" . str_replace("\'", "''", strtolower($username)) . "'";

Save and Upload. Diff:

Code:

***************
*** 476,482 ****

$sql = "SELECT user_id, user_email, user_lang, user_level
FROM " . USERS_TABLE . "
! WHERE username = '" . str_replace("\'", "''", $username) . "'";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, "Could not get user information", $lang['Error'], __LINE__, __FILE__, $sql);
--- 476,482 ----

$sql = "SELECT user_id, user_email, user_lang, user_level
FROM " . USERS_TABLE . "
! WHERE LOWER(username) = '" . str_replace("\'", "''", strtolower($username)) . "'";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, "Could not get user information", $lang['Error'], __LINE__, __FILE__, $sql);

Fixing Email Banning

Email bans may not be effective due to case sensitivity (however, note well that according to the RFC standards on email addresses, only the hostname portion if case insensitive. In practice however, Mail Servers have fully case sensitive addresses, so we will treat an email address as a whole string), so we need to fix this in two spots, first session starting then validation (used for registration or changing of addresses). For sessions, open includes/sessions.php, on lines 120 - 121, find:

Code:

$sql .= " OR ban_email LIKE '" . str_replace("\'", "''", $userdata['user_email']) . "'
OR ban_email LIKE '" . substr(str_replace("\'", "''", $userdata['user_email']), strpos(str_replace("\'", "''", $userdata['user_email']), "@")) . "'";

replace with

Code:

$sql .= " OR LOWER(ban_email) LIKE '" . str_replace("\'", "''", strtolower($userdata['user_email'])) . "'
OR LOWER(ban_email) LIKE '" . substr(str_replace("\'", "''", strtolower($userdata['user_email'])), strpos(str_replace("\'", "''", strtolower($userdata['user_email'])), "@")) . "'";

Save and upload. Diff:

Code:

***************
*** 117,124 ****
OR ban_userid = $user_id";
if ( $user_id != ANONYMOUS )
{
! $sql .= " OR ban_email LIKE '" . str_replace("\'", "''", $userdata['user_email']) . "'
! OR ban_email LIKE '" . substr(str_replace("\'", "''", $userdata['user_email']), strpos(str_replace("\'", "''", $userdata['user_email']), "@")) . "'";
}
if ( !($result = $db->sql_query($sql)) )
{
--- 117,124 ----
OR ban_userid = $user_id";
if ( $user_id != ANONYMOUS )
{
! $sql .= " OR LOWER(ban_email) LIKE '" . str_replace("\'", "''", strtolower($userdata['user_email'])) . "'
! OR LOWER(ban_email) LIKE '" . substr(str_replace("\'", "''", strtolower($userdata['user_email'])), strpos(str_replace("\'", "''", strtolower($userdata['user_email'])), "@")) . "'";
}
if ( !($result = $db->sql_query($sql)) )
{

Now, we apply the fix to username validation. Open includes/functions_validate.php, on line 148 find:

Code:

WHERE user_email = '" . str_replace("\'", "''", $email) . "'";

replace with

Code:

WHERE LOWER(user_email) = '" . str_replace("\'", "''", strtolower($email)) . "'";

Save and upload. Diff:

Code:

***************
*** 145,151 ****

$sql = "SELECT user_email
FROM " . USERS_TABLE . "
! WHERE user_email = '" . str_replace("\'", "''", $email) . "'";
if (!($result = $db->sql_query($sql)))
{
message_die(GENERAL_ERROR, "Couldn't obtain user email information.", "", __LINE__, __FILE__, $sql);
--- 145,151 ----

$sql = "SELECT user_email
FROM " . USERS_TABLE . "
! WHERE LOWER(user_email) = '" . str_replace("\'", "''", strtolower($email)) . "'";
if (!($result = $db->sql_query($sql)))
{
message_die(GENERAL_ERROR, "Couldn't obtain user email information.", "", __LINE__, __FILE__, $sql);

Index Consideration

Because of the way indexes work in PostgreSQL, we need to create a lowered index on the username column to make the database planner execute searches on the username column efficiently. We will make this index unique as well, to prevent any possible username duplications (which won't happen if you use a vanilla phpBB, but for data integrity reasons, we will do so). Execute the following query on your database, either via the psql command line application or web based database interface like phpPgAdmin:

Code:

CREATE UNIQUE INDEX username_phpbb_users_lowered_index ON phpbb_users (LOWER(username));

Note: if you table prefix (set during installation) is different from the default phpbb_, alter the phpbb_users line to yourprefix_users if your prefix is "your_prefix".

2nd Note: You could do the same for the user_email column, however most likely it won't be necessary unless you have many users. But for reference sake:

Code:

CREATE UNIQUE INDEX user_email_phpbb_users_lowered_index ON phpbb_users (LOWER(user_email));

(if you altered phpBB to allow more than one email address per account, remove the UNIQUE clause from the query)

Finished!

That's it. Good luck with your forums Smile