# MySQL change password + Roundcube



## xy16644 (Jan 24, 2014)

I've got Roundcube installed and I use encrypted passwords in a MySQL database with Dovecot and Postfix for authentication. When I added a user account to the database I ran the following SQL statement:

```
INSERT INTO `mailserver`.`virtual_users`
  (`id`, `domain_id`, `password` , `email`)
VALUES
  ('1', '1', ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user@domain.com');
```

This creates a username called "user@domain.com" and encrypts the password using SHA-512. 

I am now trying to get the "change password" plugin to work with Roundcube but in the /usr/local/www/roundcube/plugins/password/config.inc.php it needs a SQL query to change the password. The default is:

```
$rcmail_config['password_query'] = 'SELECT update_passwd(%c, %u)';
```

%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.

%u is replaced with the username (from the session info)

I have changed the password crypt hash to SHA-512.

The question I have is what is the correct SQL query I need to put into the /usr/local/www/roundcube/plugins/password/config.inc.php config file to be able to change the password? 

Also, where do I look to see the failed change password attempts? I've looked in various logfiles but haven't been able to track down an error yet.

I only know the very basics of SQL so I am battling with this!


----------



## SirDice (Jan 24, 2014)

I'm not really a master with MySQL too but I think the query should be something like this:

```
UPDATE `mailserver`.`virtual_users` SET password='%c' WHERE domain_id='%u';
```

That does assume the domain_id column contains the username.


----------



## xy16644 (Jan 24, 2014)

SirDice said:
			
		

> I'm not really a master with MySQL too but I think the query should be something like this:
> 
> ```
> UPDATE `mailserver`.`virtual_users` SET password='%c' WHERE domain_id='%u';
> ...



Thank you @SirDice!

How do I incorparate that query into the Roundcube config file? ie: 


```
$rcmail_config['password_query'] = 'SELECT update_passwd(%c, %u)';
```

Thats the confusing bit.


----------



## SirDice (Jan 24, 2014)

I'm not really sure, I've never configured Roundcube. The default query looks like it's a stored procedure. Isn't there anything in the documentation about it? You probably aren't the only one using MySQL with Roundcube.


----------



## xy16644 (Jan 24, 2014)

I've been going through the README but its not working for me:

http://trac.roundcube.net/browser/github/plugins/password/README#L98

They do have a MySQL section but I'm not sure where the errors are being logged to!


----------



## SirDice (Jan 24, 2014)

Ah, yes. You need to login on MySQL and create the function inside the Roundcube database. There shouldn't be a need to modify anything in the Roundcube code. The code calls this function to update the password. And I wasn't too far off with mine I see. One thing my query doesn't do is verify the old password.


----------



## xy16644 (Jan 24, 2014)

Thats a bit over my head I'm afraid!

I thought I had to query the database I had created (that holds the user accounts and encrypted passwords) and not the Roundcube database?


----------



## SirDice (Jan 24, 2014)

If password_db_dsn is set to "mailserver" then the function should indeed be created there. Errors within the function are probably logged in /var/db/mysql/`hostname`.err (default location for MySQL).


----------



## xy16644 (Jan 24, 2014)

Thats the bit I am battling. How do you create this function, where and with what SQL statement.


----------



## xy16644 (Jan 26, 2014)

A good friend of mine helped me get this working. Without his input it would have taken forever to get working! This is the first time I have gotten the change password plugin to work with Roundcube. Here are the steps I followed from my friend:

Edit /usr/local/www/roundcube/plugins/password/config.inc.php as follows:


```
$rcmail_config['password_driver'] = 'sql';
$rcmail_config['password_db_dsn'] = 'YOUR_DSN_HERE';
$rcmail_config['password_query'] = 'UPDATE mailserverdb.userstable SET password=ENCRYPT(%p,concat(_utf8\'$6$\',right(sha(rand()),8),_utf8\'$\')) WHERE email=%u LIMIT 1';
$rcmail_config['password_crypt_hash'] = 'sha512';
```

Some notes:

The password query is the tricky one. I use SHA-512 encryption so had to change the query from MD5 to SHA and I had to change $1$ to $6$ (for SHA-512). Pay particular attention to the \' options around the $ signs. These caused plenty of issues while testing.

Now i can change the password using Roundcube. Thanks to my friend LK  :e 

I hope this helps someone as trying to find the solution online was tricky.


----------



## matheus (Apr 11, 2020)

Hello, i having the same problems. In my case, still use ECRYPT as a method. So, to resolve changes in this way:

```
$config['password_query'] = 'UPDATE mail.users SET password=ENCRYPT(%p) WHERE email=%u LIMIT 1';
```
 I apologize for the spelling, but I am Brazilian and I need to translate on google translator.


----------

