Howto password change in webmailThis howto describes how to enable a password change for mail accounts directly in the ispcp webmail (squirrel mail)Version from 2007-10-15 - better sql formula... Step 1 - the plugin itself
We need the plugin change_sqlpass for squirrelmail - here Requires: SquirrelMail 1.2.8, Compatibility 2.0.4, Pear DB library SquirrelMail and Compatibility is ok by ispcp - PEAR DB should be separately installed... We unpack the file in /var/www/ispcp/gui/tools/webmail/plugins/ so that the files come into /var/www/ispcp/gui/tools/webmail/plugins/change_sqlpass. Set ownership and permissions like the other plugins... (vu2000:www-data, -r--r--r--, dr-xr-xr-x). Then we have to activate the plugin by adding a line to /var/www/ispcp/gui/tools/webmail/config/config_local.php: $plugins[26] = 'change_sqlpass'; (the index number should be higher than the ones from the already activated plugins...) Step 2, a user for the database
We need to create a user in mysql which is used for the password change. Use a sql console (in the shell, in pma, whatever - as mysql-root). GRANT USAGE ON ispcp.* TO 'ispcp-mail-user'@'localhost' IDENTIFIED BY '###greatpassword###' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0; GRANT SELECT (`domain_id`, `domain_name` ) ON `ispcp`.`domain` TO 'ispcp-mail-user'@'localhost'; GRANT SELECT (`domain_id`, `alias_id`, `alias_name` ) ON `ispcp`.`domain_aliasses` TO 'ispcp-mail-user'@'localhost'; GRANT SELECT (`domain_id`, `subdomain_id`, `subdomain_name` ) ON `ispcp`.`subdomain` TO 'ispcp-mail-user'@'localhost'; GRANT SELECT ( `mail_id`, `mail_acc`, `domain_id`, `sub_id`, `mail_type`, `status`, `mail_pass`), UPDATE (`mail_pass` , `status`) ON `ispcp`.`mail_users` TO 'ispcp-mail-user'@'localhost'; Step 3, configuration of the plugin
Go into the plugin folder /var/www/ispcp/gui/tools/webmail/plugins/change_sqlpass, make a copy of the sample config: cp config.php.sample config.php and open config.php for editing, we need to changes some lines: make the beginning of the file (global ...) looks like this (add two globals and set one with null): global $csp_dsn, $password_update_queries, $lookup_password_query,
$force_change_password_check_query, $password_encryption,
$csp_salt_query, $csp_salt_static, $csp_secure_port,
$csp_non_standard_http_port, $csp_delimiter, $csp_debug,
$min_password_length, $max_password_length, $include_digit_in_password,
$include_uppercase_letter_in_password, $include_lowercase_letter_in_password,
$include_nonalphanumeric_in_password,
$lookup_password_query_get_id, $mail_id;
$mail_id = null;
now we set 4 variables: $csp_dsn = 'mysql://ispcp-mail-user:###greatpassword###@localhost/ispcp';
$lookup_password_query = "SELECT COUNT(DISTINCT t1.`mail_id`) = 1
FROM `mail_users` t1
LEFT JOIN `domain` t2 USING ( `domain_id` )
LEFT JOIN `domain_aliasses` t3 USING ( `domain_id` )
LEFT JOIN `subdomain` t4 USING ( `domain_id` )
WHERE t1.`mail_acc` = '%2' AND t1.`mail_pass` = '%5'
AND (
( t1.`mail_type` = 'normal_mail' AND t2.`domain_name` = '%3' )
OR ( t1.`mail_type` = 'alias_mail' AND t3.`alias_name` = '%3' AND t3.`alias_id` = t1.`sub_id` )
OR ( t1.`mail_type` = 'subdom_mail'AND CONCAT( t4.`subdomain_name` , '.', t2.`domain_name` ) = '%3' AND t4.`subdomain_id` = t1.`sub_id` )
)";
$lookup_password_query_get_id = "SELECT DISTINCT t1.`mail_id`
FROM `mail_users` t1
LEFT JOIN `domain` t2 USING ( `domain_id` )
LEFT JOIN `domain_aliasses` t3 USING ( `domain_id` )
LEFT JOIN `subdomain` t4 USING ( `domain_id` )
WHERE t1.`mail_acc` = '%2' AND t1.`mail_pass` = '%5'
AND (
( t1.`mail_type` = 'normal_mail' AND t2.`domain_name` = '%3' )
OR ( t1.`mail_type` = 'alias_mail' AND t3.`alias_name` = '%3' AND t3.`alias_id` = t1.`sub_id` )
OR ( t1.`mail_type` = 'subdom_mail' AND CONCAT( t4.`subdomain_name`, '.', t2.`domain_name` ) = '%3' AND t4.`subdomain_id` = t1.`sub_id` )
)";
$password_update_queries = array(
"UPDATE `mail_users` SET `mail_pass` = '%5', status = 'change' WHERE mail_id = %6",
);
$csp_salt_query = '';
I needed to add the $mail_id variable, otherwise it'd be to tricky with the sql statements - therefore we need some changes also in functions.php (same folder). The diff -u looks like this: --- functions.php_org 2005-11-12 07:48:24.000000000 +0100
+++ functions.php 2007-09-30 01:46:15.000000000 +0200
@@ -694,7 +694,7 @@
global $lookup_password_query, $csp_debug, $min_password_length,
$max_password_length, $include_digit_in_password,
$include_uppercase_letter_in_password, $include_lowercase_letter_in_password,
- $include_nonalphanumeric_in_password;
+ $include_nonalphanumeric_in_password, $lookup_password_query_get_id, $mail_id;
load_config('change_sqlpass', array('config.php'));
@@ -742,6 +742,14 @@
$sql);
$db_password = $db->getAll($sql);
+ $sql = $lookup_password_query_get_id;
+ $sql = str_replace(array('%1', '%2', '%3', '%4', '%5'),
+ array($full_username, $user, $dom, $encrypted_pwd, $db->escapeSimple($cp_oldpass)),
+ $sql);
+ $mail_id = $db->getAll($sql); // we need the id of the mail account, otherwise it's to hard to create a good pw update sql statement
+ if (count($mail_id) != 1) {
+ array_push($messages, _("Problem detected: ".count($mail_id)." accounts found"));
+ }
// check for database errors
//
@@ -826,7 +834,7 @@
function change_password($password)
{
- global $password_update_queries, $csp_debug, $base_uri;
+ global $password_update_queries, $csp_debug, $base_uri, $mail_id;
load_config('change_sqlpass', array('config.php'));
@@ -846,8 +854,8 @@
{
$sql = $query;
- $sql = str_replace(array('%1', '%2', '%3', '%4', '%5'),
- array($full_username, $user, $dom, $encrypted_pwd, $db->escapeSimple($password)),
+ $sql = str_replace(array('%1', '%2', '%3', '%4', '%5', '%6'),
+ array($full_username, $user, $dom, $encrypted_pwd, $db->escapeSimple($password), $mail_id[0][0]),
$sql);
@@ -872,6 +880,9 @@
}
+ // send a signal to the ispcp request manager:
+ require_once('../../../../include/ispcp-functions.php');
+ send_request();
// Write new cookies for the password
//
That's it.
I think it should be safe (not setting the password to the wrong account) but maybe we need some testing... [feedback is welcome]. Maybe we get this into ispcp - after 1.0 :-) A small drawback: users have to logoff and login again after password change - maybe we can fix that later... |
