Hi,
I am trying to configure password plugin to give users password change ability. However I need help with MySQL query.
Database name is system, and password (SHA1) are stored in table user:
MariaDB [system]> SELECT * FROM user;
+-----------+----------------------+------------------------------+--------------+------------------+--------------+----------------+----------+------------------------------+---------------------+
| username | domain | password | SMTP_allowed | SMTPAUTH_allowed | IMAP_allowed | spam_threshold | spam_tag | Full Name | last_modified |
+-----------+----------------------+------------------------------+--------------+------------------+--------------+----------------+----------+------------------------------+---------------------+
| roundcube | 123.com | QL0AFWMIX8NRZTKeof9cXsvbvu8= | YES | YES | YES | 5 | {SPAM?} | RoundCubeMail | 2015-03-23 20:25:07 |
to change password in SQL syntax I need run following command:
MariaDB [system]> UPDATE user SET password='2jmj7l5rSw0yVb/vlWAYkK/YBwk=' WHERE username='webmaster';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Now I need a proper MySQL query in config.inc.php:
$config['password_query'] = 'UPDATE user SET password='%p' WHERE username='%u';
Unfortunately when I set this as my MySQL query, then I am not able to display RoundCube login page (Error500):
--736e7b34-A--
[05/Aug/2016:13:59:31 +0100] V6SNs7IhNKMAAD8-aZYAAABC 1.2.3.4 54086 5.6.7.8 443
--736e7b34-B--
GET /poczta/ HTTP/1.1
Host: domain
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:48.0) Gecko/20100101 Firefox/48.0
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate, br
DNT: 1
Cookie: roundcube_sessid=4dmornn5tdqlga1rdsbmrfkv94; PHPSESSID=6em7fqrk2er34hc8l89i2h2fj4
Connection: keep-alive
Upgrade-Insecure-Requests: 1
--736e7b34-F--
HTTP/1.0 500 Internal Server Error
Strict-Transport-Security: max-age=15768000
X-Powered-By: PHP/5.6.24
Expires: Thu, 19 Nov 1981 08:52:00 GMT
Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0
Pragma: no-cache
Content-Length: 0
Connection: close
Content-Type: text/html; charset=UTF-8
--736e7b34-H--
Apache-Error: [file "ssl_engine_kernel.c"] [line 366] [level 7] AH02034: %s HTTPS request received for child %ld (server %s)
Apache-Error: [file "mod_authz_core.c"] [line 809] [level 7] AH01626: authorization result of %s: %s
Apache-Error: [file "mod_authz_core.c"] [line 809] [level 7] AH01626: authorization result of %s: %s
Apache-Error: [file "mod_authz_core.c"] [line 809] [level 7] AH01626: authorization result of %s: %s
Apache-Error: [file "mod_authz_core.c"] [line 809] [level 7] AH01626: authorization result of %s: %s
Apache-Handler: application/x-httpd-php
Stopwatch: 1470401971244933 14127 (- - -)
Stopwatch2: 1470401971244933 14127; combined=302, p1=292, p2=0, p3=0, p4=0, p5=9, sr=43, sw=1, l=0, gc=0
Producer: ModSecurity for Apache/2.9.1 (http://www.modsecurity.org/); OWASP_CRS/2.2.9.
Server: Apache
--736e7b34-Z--
It doesn't matter ModSecurity engine is enabled or not.
In meat time I found that Roundcube is complaining about PHP parse error:
[05-Aug-2016 16:51:16 Europe/London] PHP Parse error: syntax error, unexpected 'password_crypt_hash' (T_STRING) in /var/www/htdocs/poczta/plugins/password/config.inc.php on line 115
[05-Aug-2016 16:52:42 Europe/London] PHP Parse error: syntax error, unexpected 'password_crypt_hash' (T_STRING) in /var/www/htdocs/poczta/plugins/password/config.inc.php on line 115
[05-Aug-2016 16:52:43 Europe/London] PHP Parse error: syntax error, unexpected 'password_crypt_hash' (T_STRING) in /var/www/htdocs/poczta/plugins/password/config.inc.php on line 115
[05-Aug-2016 16:53:06 Europe/London] PHP Parse error: syntax error, unexpected 'password_crypt_hash' (T_STRING) in /var/www/htdocs/poczta/plugins/password/config.inc.php on line 115
[05-Aug-2016 16:53:30 Europe/London] PHP Parse error: syntax error, unexpected 'password_crypt_hash' (T_STRING) in /var/www/htdocs/poczta/plugins/password/config.inc.php on line 115
[05-Aug-2016 16:53:54 Europe/London] PHP Parse error: syntax error, unexpected 'password_crypt_hash' (T_STRING) in /var/www/htdocs/poczta/plugins/password/config.inc.php on line 115
Even if I comment it out that line in the config file:
<?php
// Password Plugin options
// -----------------------
// A driver to use for password change. Default: "sql".
// See README file for list of supported driver names.
$config['password_driver'] = 'sql';
// Determine whether current password is required to change password.
// Default: false.
$config['password_confirm_current'] = true;
// Require the new password to be a certain length.
// set to blank to allow passwords of any length
$config['password_minimum_length'] = 12;
// Require the new password to contain a letter and punctuation character
// Change to false to remove this check.
$config['password_require_nonalpha'] = true;
// Enables logging of password changes into logs/password
$config['password_log'] = true;
// Comma-separated list of login exceptions for which password change
// will be not available (no Password tab in Settings)
$config['password_login_exceptions'] = null;
// Array of hosts that support password changing. Default is NULL.
// Listed hosts will feature a Password option in Settings; others will not.
// Example: array('mail.example.com', 'mail2.example.org');
$config['password_hosts'] = array('localhost');
// Enables saving the new password even if it matches the old password. Useful
// for upgrading the stored passwords after the encryption scheme has changed.
$config['password_force_save'] = false;
// Enables forcing new users to change their password at their first login.
$config['password_force_new_user'] = true;
// Default password hashing/crypting algorithm.
// Possible options: des-crypt, ext-des-crypt, md5-crypt, blowfish-crypt,
// sha256-crypt, sha512-crypt, md5, sha, smd5, ssha, samba, ad, dovecot, clear.
// For details see password::hash_password() method.
$config['password_algorithm'] = 'sha';
// Password prefix (e.g. {CRYPT}, {SHA}) for passwords generated
// using password_algorithm above. Default: empty.
$config['password_algorithm_prefix'] = '{SHA}';
// Path for dovecotpw/doveadm-pw (if not in the $PATH).
// Used for password_algorithm = 'dovecot'.
// $config['password_dovecotpw'] = '/usr/locadb_dsnwl/sbin/doveadm pw'; // for dovecot-2.x
$config['password_dovecotpw'] = '/usr/bin/doveadm pw'; // for dovecot-1.x
// Dovecot password scheme.
// Used for password_algorithm = 'dovecot'.
$config['password_dovecotpw_method'] = 'SHA1';
// Iteration count parameter for Blowfish-based hashing algo.
// It must be between 4 and 31. Default: 12.
// Be aware, the higher the value, the longer it takes to generate the password hashes.
$config['password_blowfish_cost'] = 12;
// Number of rounds for the sha256 and sha512 crypt hashing algorithms.
// Must be at least 1000. If not set, then the number of rounds is left up
// to the crypt() implementation. On glibc this defaults to 5000.
// Be aware, the higher the value, the longer it takes to generate the password hashes.
//$config['password_crypt_rounds'] = 50000;
// This option temporarily disables the password change functionality.
// Use it when the users database server is in maintenance mode or sth like that.
// You can set it to TRUE/FALSE or a text describing the reason
// which will replace the default.
$config['password_disabled'] = false;
// SQL Driver options
// ------------------
// PEAR database DSN for performing the query. By default
// Roundcube DB settings are used.
$config['password_db_dsn'] = 'mysql://***:***]c@localhost/system';
// The SQL query used to change the password.
// The query can contain the following macros that will be expanded as follows:
// %p is replaced with the plaintext new password
// %P is replaced with the crypted/hashed new password
// according to configured password_method
// %o is replaced with the old (current) password
// %O is replaced with the crypted/hashed old (current) password
// according to configured password_method
// %h is replaced with the imap host (from the session info)
// %u is replaced with the username (from the session info)
// %l is replaced with the local part of the username
// (in case the username is an email address)
// %d is replaced with the domain part of the username
// (in case the username is an email address)
// Deprecated macros:
// %c is replaced with the crypt version of the new password, MD5 if available
// otherwise DES. More hash function can be enabled using the password_crypt_hash
// configuration parameter.
// %D is replaced with the dovecotpw-crypted version of the new password
// %n is replaced with the hashed version of the new password
// %q is replaced with the hashed password before the change
// Escaping of macros is handled by this module.
// Default: "SELECT update_passwd(%c, %u)"
//$config['password_query'] = 'SELECT update_passwd(%c, %u)';
$config['password_query'] = 'UPDATE user SET password=%p WHERE username=%u LIMIT 1;
// By default the crypt() function which is used to create the %c
// parameter uses the md5 algorithm (deprecated, use %P).
// You can choose between: des, md5, blowfish, sha256, sha512.
// $config['password_crypt_hash'] = 'md5';
// By default domains in variables are using unicode.
// Enable this option to use punycoded names
$config['password_idn_ascii'] = false;
// Enables use of password with crypt method prefix in %D, e.g. {MD5}$1$LUiMYWqx$fEkg/ggr/L6Mb2X7be4i1/
// when using the %D macro (deprecated, use %P)
$config['password_dovecotpw_with_method'] = false;
// Using a password hash for %n and %q variables (deprecated, use %P).
// Determine which hashing algorithm should be used to generate
// the hashed new and current password for using them within the
// SQL query. Requires PHP's 'hash' extension.
$config['password_hash_algorithm'] = 'sha1';
// You can also decide whether the hash should be provided
// as hex string or in base64 encoded format.
$config['password_hash_base64'] = false;