[sork] passwd mysql problem

[mrwolf at nunux.ca]

Thank you Jan,

the line is perfect and I can now change my password via passwd  
without any problem.

Quoting Jan Schneider <jan at horde.org>:

> Zitat von mrwolf at nunux.ca:
>
>> 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 <jan at horde.org>:
>>
>>> Zitat von mrwolf at nunux.ca:
>>>
>>>> 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: user1 at domain1.ca, user2 at domain3.ca, user3 at domain2.com
>>>>
>>>> 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: sork-unsubscribe at lists.horde.org
>>>>
>>>
>>>
>>>
>>> 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: sork-unsubscribe at lists.horde.org
>>
>
>
>
> Jan.
>
> -- 
> Do you need professional PHP or Horde consulting?
> http://horde.org/consulting/
>