Mysql password update failed

[rcubeuser]

Hi, I'm trying to update any user password from roundcube and in my logs I have this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%c,modified=NOW() WHERE username='user1@mydomain.com' at line 1 (SQL Query: UPDATE mailbox SET password=%c,modified=NOW() WHERE username='user1@mydomain.com') in /var/www/webmail/program/lib/Roundcube/rcube_db.php on line 567 (POST /?_task=settings&_action=plugin.password-save)

My server:

roundcube: 1.6.1;
password plugin: 5.3;
php:  8.1.2-1ubuntu2.11;
mysql: 8.0.33-0ubuntu0.22.04.2 (Ubuntu)

In february this year I upgraded from 1.4.11 to 1.6.1, but I do not remeber if I tested the password pluging.

[alec]

%c is not supported anymore. You have to replace it with a different one.

[rcubeuser]

Ok, according to config.inc.php of password plugin

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'] = 'UPDATE mailbox SET password=%n,modified=NOW() WHERE username=%u';

// 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;

// 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';

I test %n and %D and of course I have the same error, but with %P I have plaintext password

[alec]

With %P you have to set password_algorithm option. It's 'clear' by default.

[JohnDoh]

That is a old config file. There were quite a few changes in version 1.6 to the password plugin, including replacing `password_hash_algorithm` with with `password_algorithm` and removing `password_crypt_hash`.

Using `password_hash_algorithm` was deprecated back in version 1.2 so I'm guessing you have upgraded a few times. To get similar results I think you want `password_algorithm = 'sha'` or `password_algorithm = 'ssha'`.

You should have a config.inc.php.dist that was shipped with the plugin for all the current options.

[rcubeuser]

Ok, I used config.inc.php.dist with my local configurations and it worked, thank you all!