Thread: Password Plugin

I'm tryng to get this plugin running. But I allways get this error-message:

DB Error: MDB2 Error: syntax error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: see_next_code_block] [Native code: 1064] [Native message: You have an error in your SQL syntax; check the m in /path/to/roundcube/program/include/rcube_mdb2.php on line 644 (POST /?_task=settings&_action=plugin.password-save?_task=&_action=)

Here is my query (which works fine in phpmyadmin):

$rcmail_config['password_query'] = 'CREATE TEMPORARY TABLE userID (SELECT `id` FROM `virtual_users` WHERE `domain_id` =(SELECT `id` FROM `virtual_domains` WHERE `name` = %d) AND `user` = %l AND `password` = MD5( %o )); UPDATE `mail`.`virtual_users` SET `password` = MD5( %p ) WHERE `virtual_users`.`id` =(SELECT `id` FROM userID) LIMIT 1 ;';

I don't understand this error-message. I hope you does. Thanks.

At the end of your query you have:

Code:

;';

the first ; shouldn't be there, it should be:

Code:

$rcmail_config['password_query'] = 'CREATE TEMPORARY TABLE userID (SELECT `id` FROM `virtual_users` WHERE `domain_id` =(SELECT `id` FROM `virtual_domains` WHERE `name` = %d) AND `user` = %l AND `password` = MD5( %o )); UPDATE `mail`.`virtual_users` SET `password` = MD5( %p ) WHERE `virtual_users`.`id` =(SELECT `id` FROM userID) LIMIT 1';

EDIT: Oops I just read your query a little bit closer and noticed that you trying to run 2 query, RoundCube only supports running 1 query.

Originally Posted by skaero

Oops I just read your query a little bit closer and noticed that you trying to run 2 query, RoundCube only supports running 1 query.

Do you have any suggestion how I can change this?

The problem is, that I need the id of the user, so I have to select the same table I want to update. And MySQL doesn't like this in one query.

Can you do:

Code:

$rcmail_config['password_query'] = 'UPDATE `mail`.`virtual_users` SET `password` = MD5( %p ) WHERE `virtual_users`.`id` = (SELECT `id` FROM `virtual_users` WHERE `domain_id` = (SELECT `id` FROM `virtual_domains` WHERE `name` = %d) AND `user` = %l AND `password` = MD5( %o )) LIMIT 1';

Or does that also return a error (and if so what error)

This was the first one I tried. Doesn't work either.

Code:

You can't specify target table 'virtual_users' for update in FROM clause

Thats why I tried it with a temporary table.

Ok I think its just a mysql format problem, try this:

Code:

$rcmail_config['password_query'] = 'UPDATE `mail`.`virtual_users` SET `password` = MD5( %p ) WHERE `virtual_users`.`id` = (SELECT `id` FROM `mail`.`virtual_users` WHERE `domain_id` = (SELECT `id` FROM `mail`.`virtual_users` WHERE `name` = %d) AND `user` = %l AND `password` = MD5( %o )) LIMIT 1';

Ok, I made a view now with the password and the mail-address inside.

Works fine now.

Here is my query:

Code:

UPDATE `mail`.`view_users` SET `password` = MD5( %p ) WHERE `email` = %u AND `password` = MD5( %o ) LIMIT 1

Thanks for your help.