[sork] passwd mysql problem

[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'

'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?

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

Thank you

Frank

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