Detecting Duplicate Private Message Rules

phpBB 3.0.5 Detecting Duplicate Private Message Rules:

The current system for detecting duplicate message rules is not very good, nor is it efficient.

Take, for example, a rule matching a sender's exact username (using the 'is user' option).

I can make two rules which are identical except for in one I enter my username in all minuscules, like dog cow, and then the other one with majuscules, such as Dog Cow. The system will accept and save these two rules, even though logically, they are identical. Unfortunately, they are not physically identical since the 'rule_string' stored in the two rows differs. To prevent this, the username returned from the users table should be stored, rather than the exact string which the user had originally entered.

Secondly, the query for detecting duplicate rules is quite ugly. Essentially, it is building a big SELECT statement with a whole menagerie of WHERE and AND clauses on multiple, unindexed columns.

Therefore, I propose a change: instead of searching on all of these columns, why not make a composite of the $rule_ary, such as by using crc32() or md5()? This way, a new, indexed column such as rule_hash may be added, which stores the composite of all of the rule's variables, and a SELECT statement looking for potential duplicates would be more efficient.

Here is my proposed patch:

In file: /includes/ucp/ucp_pm_options.php

CHANGE THESE LINES:

$rule_ary = array(
'user_id' => $user->data['user_id'],
'rule_check' => $check_option,
'rule_connection' => $rule_option,
'rule_string' => $rule_string,
'rule_user_id' => $rule_user_id,
'rule_group_id' => $rule_group_id,
'rule_action' => $action,
'rule_folder_id' => $folder_id
);

$sql = 'SELECT rule_id
FROM ' . PRIVMSGS_RULES_TABLE . '
WHERE ' . $db->sql_build_array('SELECT', $rule_ary);
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);

if ($row)

{ trigger_error('RULE_ALREADY_DEFINED'); }


TO THIS INSTEAD:

$rule_ary = array(
'user_id' => $user->data['user_id'],
'rule_check' => $check_option,
'rule_connection' => $rule_option,
'rule_string' => $rule_string,
'rule_user_id' => $rule_user_id,
'rule_group_id' => $rule_group_id,
'rule_action' => $action,
'rule_folder_id' => $folder_id
);

$rule_hash = md5(serialize($rule_ary));

$sql = 'SELECT rule_id
FROM ' . PRIVMSGS_RULES_TABLE . "
WHERE rule_hash = '" . $db->sql_escape($rule_hash) . "'";
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);

if ($row)
{ trigger_error('RULE_ALREADY_DEFINED'); }

else

{ $rule_ary += array('rule_hash' => $rule_hash); }

SQL schema changes:

ALTER TABLE `phpbb_privmsgs_rules` ADD `rule_hash` VARCHAR( 32 ) NOT NULL;
ALTER TABLE `phpbb_privmsgs_rules` ADD INDEX ( `rule_hash` );

Date found: 02 June, 2009
by Dog Cow