Re: passwd mysql problem

Zitat von [email protected]:

> Thanks Jan for your fast answer,
>
> if this line is good in my postfix:
> query = SELECT 1 FROM virtual_users AS U LEFT JOIN virtual_domains  
> AS D ON U.domain_id=D.id WHERE CONCAT(U.user, '@',D.name)='%s'
>
> and those one aren't in passwd:
> 'query_lookup' => 'SELECT password FROM virtual_users AS U LEFT JOIN  
> virtual_domains AS D ON U.domain_id=D.id WHERE U.user = %U AND  
> D.name = %d'

This one is fine.

> 'query_modify' => 'UPDATE virtual_users SET password = %e  
> virtual_users AS U LEFT JOIN virtual_domains AS D ON  
> U.domain_id=D.id WHERE U.user = %U AND D.name = %d'
>
> which part of those lines aren't good? is it the left join part or  
> any others parts?

This is simply not a valid UPDATE syntax.

> can you write me an example of what would be good for my db?

Try something like (works only with sufficiently recent mysql versions):
UPDATE virtual_users SET password = %e WHERE virtual_users.user = %U  
AND virtual_users.domain_id = (SELECT id FROM virtual_domains WHERE  
name = %d)

> Quoting Jan Schneider <[email protected]>:
>
>> Zitat von [email protected]:
>>
>>> Hi everyone,
>>>
>>> I have a particular mysql config that give me some problem with passwd:
>>>
>>> mysql> Select * from virtual_domains;
>>> +----+--------------+
>>> | id | name         |
>>> +----+--------------+
>>> |  2 | domain1.ca   |
>>> |  3 | domain2.com  |
>>> |  1 | domain3.ca   |
>>> +----+--------------+
>>> 3 rows in set (0.00 sec)
>>>
>>>
>>> mysql> Select * from virtual_users;
>>> +----+-----------+--------+------------+
>>> | id | domain_id | user   | password   |
>>> +----+-----------+--------+------------+
>>> |  1 |         2 | user1  | <Password> |
>>> |  2 |         1 | user2  | <Password> |
>>> |  3 |         3 | user3  | <Password> |
>>> +----+-----------+--------+------------+
>>> 3 rows in set (0.00 sec)
>>>
>>> From postfix:
>>> query = SELECT 1 FROM virtual_users AS U LEFT JOIN virtual_domains  
>>>  AS D ON U.domain_id=D.id WHERE CONCAT(U.user, '@',D.name)='%s'
>>>
>>> so I got: [email protected], [email protected], [email protected]
>>>
>>> I changed the backends.php from passwd to this:
>>> $backends['sql'] = array (
>>>   'name' => 'Exampe SQL Server',
>>>   'preferred' => '',
>>>   'password policy' => array(
>>>       'minLength' => 7,
>>>       'maxLength' => 15,
>>>       'maxSpace' => 0,
>>>       'minUpper' => 1,
>>>       'minLower' => 1,
>>>       'minNumeric' => 1,
>>>       'minSymbols' => 0
>>>   ),
>>>   'driver' => 'sql',
>>>   'params' => array(
>>>       'phptype'    => 'mysql',
>>>       'hostspec'   => 'localhost',
>>>       'username'   => '<MySQL_User>',
>>>       'password'   => '<password>',
>>>       'encryption' => 'md5-hex',
>>>       'database'   => 'virtual_email',
>>>       'table'      => 'virtual_users',
>>>       //'user_col'   => 'user',
>>>       //'pass_col'   => 'password',
>>>       'show_encryption' => false,
>>>       // 'query_lookup' => 'SELECT password FROM virtual_users,   
>>> virtual_domains WHERE virtual_users.user = %u',
>>> 	'query_lookup' => 'SELECT password FROM virtual_users AS U LEFT   
>>> JOIN virtual_domains AS D ON U.domain_id=D.id WHERE U.user = %U  
>>> AND  D.name = %d',
>>> 	//'query_modify' => 'UPDATE virtual_users SET password = %e WHERE  
>>>  user = %u',
>>> 	'query_modify' => 'UPDATE virtual_users SET password = %e   
>>> virtual_users AS U LEFT JOIN virtual_domains AS D ON   
>>> U.domain_id=D.id WHERE U.user = %U AND D.name = %d'
>>
>> This is not a valid SQL query.
>>
>>>   )
>>> );
>>>
>>> when I try to change my password passwd tell me that he didn't  
>>> found  any matching user
>>>
>>> can anyone help me fix my passwd backends?
>>>
>>> thank you
>>> --
>>> Sork mailing list - Join the hunt: http://horde.org/bounties/#sork
>>> Frequently Asked Questions: http://horde.org/faq/
>>> To unsubscribe, mail: [email protected]
>>>
>>
>>
>>
>> Jan.
>>
>> -- 
>> Do you need professional PHP or Horde consulting?
>> http://horde.org/consulting/
>>
>
>
> --
> Sork mailing list - Join the hunt: http://horde.org/bounties/#sork
> Frequently Asked Questions: http://horde.org/faq/
> To unsubscribe, mail: [email protected]
>



Jan.

-- 
Do you need professional PHP or Horde consulting?
http://horde.org/consulting/

--
Sork mailing list - Join the hunt: http://horde.org/bounties/#sork
Frequently Asked Questions: http://horde.org/faq/
To unsubscribe, mail: [email protected]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)

iEYEABECAAYFAkoXzHQACgkQeZECfE3luWkkvQCg/LWNut2CP+4ys03nijsFOu5u
qMgAoORVLWupubHP2Yn762DZeMW9x5cI
=Pu4c
-----END PGP SIGNATURE-----