Mailing List Archive

On Fri, Apr 16, 2021 at 02:35:22AM +0200, Torsten M?ller via Exim-users wrote:
> My last attempt was:
>
> server_condition = ${if or{\
> {{${lookup mysql {MYSQL_AUTH_GWUSERPLAIN}}} {no}{yes}}\
> {{${lookup mysql {MYSQL_AUTH_PLAIN}}} {no}{yes}}\
> }}
>
> , but this doens't give the expected result.
> Did i i make an error in combining the conditions ?

Yes, or{..} expects boolean arguments, not strings like "no" or "yes".
I recommend to use "exim -be" to debug such expressions quickly.
--
Eugene Berdnikov

--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/

Torsten Müller via Exim-users <exim-users@exim.org> (Fr 16 Apr 2021 02:35:22 CEST):
> My last attempt was:
>
> server_condition = ${if or{\
> {{${lookup mysql {MYSQL_AUTH_GWUSERPLAIN}}} {no}{yes}}\
> {{${lookup mysql {MYSQL_AUTH_PLAIN}}} {no}{yes}}\
> }}

server_condition = ${if or{\
{bool{{${lookup mysql {MYSQL_AUTH_GWUSERPLAIN}}} {no}{yes}}}\
{bool{{${lookup mysql {MYSQL_AUTH_PLAIN}}} {no}{yes}}}\
}}

Best regards from Dresden/Germany
Viele Grüße aus Dresden
Heiko Schlittermann
--
SCHLITTERMANN.de ---------------------------- internet & unix support -
Heiko Schlittermann, Dipl.-Ing. (TU) - {fon,fax}: +49.351.802998{1,3} -
gnupg encrypted messages are welcome --------------- key ID: F69376CE -

Hello Heiko and Evgeniy,

thanks for your answers.

Heiko, i tried your suggestion

Am 16.04.2021 um 10:04 schrieb Heiko Schlittermann via Exim-users:
> server_condition = ${if or{\
> {bool{{${lookup mysql {MYSQL_AUTH_GWUSERPLAIN}}} {no}{yes}}}\
> {bool{{${lookup mysql {MYSQL_AUTH_PLAIN}}} {no}{yes}}}\
> }}

103670 SMTP<< AUTH PLAIN AHRvcnN0ZW5AZWxiZS1sYW5kLmRlAHF3ZXJ0eg==
103670 auth input decode: '' 'torsten@elbe-land.de' 'qwertz'
103670 auth_plain authenticator server_condition:
103670 $auth1 =
103670 $auth2 = torsten@elbe-land.de
103670 $auth3 = qwertzui
103670 $1 =
103670 $2 = torsten@elbe-land.de
103670 $3 = qwertzui
103670 search_open: mysql "NULL"
103670 search_find: file="NULL"
103670 key="SELECT COUNT(*) from mail where concat(username , '@',
punycode) = 'torsten@elbe-land.de' AND password = 'qwertzui' AND
password != ''" partial=-1 affix=NULL starflags=0 opts=NULL
103670 LRU list:
103670 internal_search_find: file="NULL"
103670 type=mysql key="SELECT COUNT(*) from mail where
concat(username , '@', punycode) = 'torsten@elbe-land.de' AND password =
'qwertzui' AND password != ''" opts=NULL
103670 database lookup required for SELECT COUNT(*) from mail where
concat(username , '@', punycode) = 'torsten@elbe-land.de' AND password =
'qwertzui' AND password != ''
103670 MySQL query: "SELECT COUNT(*) from mail where concat(username ,
'@', punycode) = 'torsten@elbe-land.de' AND password = 'qwertzui' AND
password != ''" opts 'NULL'
103670 MYSQL new connection: host=localhost port=0 socket=NULL
database=popaccounts user=mailadm
103670 lookup yielded: 1
103670 expansion failed: unrecognised boolean value "{1" inside
"or{...}" condition
103670 SMTP>> 435 Unable to authenticate at present

Hmm, the lookup yielded: 1, but the expansion seems to check
"{1". From where comes this { ?

Thank you
Greetings Torsten



--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/

On Fri, Apr 16, 2021 at 01:39:47PM +0200, Torsten M?ller via Exim-users wrote:
> Am 16.04.2021 um 10:04 schrieb Heiko Schlittermann via Exim-users:
> > server_condition = ${if or{\
> > {bool{{${lookup mysql {MYSQL_AUTH_GWUSERPLAIN}}} {no}{yes}}}\
> > {bool{{${lookup mysql {MYSQL_AUTH_PLAIN}}} {no}{yes}}}\
> > }}
...
> 103670 expansion failed: unrecognised boolean value "{1" inside "or{...}"
> condition
> 103670 SMTP>> 435 Unable to authenticate at present
>
> Hmm, the lookup yielded: 1, but the expansion seems to check
> "{1". From where comes this { ?

IMHO, there was a mistake: one redundant level of {..} iside bool{}
and improper ${lookup..} termination. Try:

server_condition = ${if or{\
{bool{${lookup mysql {MYSQL_AUTH_GWUSERPLAIN}{no}{yes}}}}\
{bool{${lookup mysql {MYSQL_AUTH_PLAI}{no}{yes}}}}\
}}
--
Eugene Berdnikov

--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/

Am 16.04.2021 um 14:07 schrieb Evgeniy Berdnikov via Exim-users:
> IMHO, there was a mistake: one redundant level of {..} iside bool{}
> and improper ${lookup..} termination. Try:
>
> server_condition = ${if or{\
> {bool{${lookup mysql {MYSQL_AUTH_GWUSERPLAIN}{no}{yes}}}}\
> {bool{${lookup mysql {MYSQL_AUTH_PLAI}{no}{yes}}}}\
> }}
>

Thanks Evgeniy,

i tried with the updated server_condition.
Now both MySQL queries work like expected,
the first lookup yielded: 1, the second yielded: 0

Shouldn't be the result : correct authentification data
(because of the or ) ?

Thank you
Torsten

106018 auth_plain authenticator server_condition:
106018 $auth1 =
106018 $auth2 = torsten@elbe-land.de
106018 $auth3 = qwertzui
106018 $1 =
106018 $2 = torsten@elbe-land.de
106018 $3 = qwertzui
106018 search_open: mysql "NULL"
106018 search_find: file="NULL"
106018 key="SELECT COUNT(*) from mail where concat(username , '@',
punycode) = 'torsten@elbe-land.de' AND password = 'qwertzui' AND
password != ''" partial=-1 affix=NULL starflags=0 opts=NULL
106018 LRU list:
106018 internal_search_find: file="NULL"
106018 type=mysql key="SELECT COUNT(*) from mail where
concat(username , '@', punycode) = 'torsten@elbe-land.de' AND password =
'qwertzui' AND password != ''" opts=NULL
106018 database lookup required for SELECT COUNT(*) from mail where
concat(username , '@', punycode) = 'torsten@elbe-land.de' AND password =
'qwertzui' AND password != ''
106018 MySQL query: "SELECT COUNT(*) from mail where concat(username ,
'@', punycode) = 'torsten@elbe-land.de' AND password = 'qwertzui' AND
password != ''" opts 'NULL'
106018 MYSQL new connection: host=localhost port=0 socket=NULL
database=popaccounts user=sogo
106018 lookup yielded: 1
106018 search_open: mysql "NULL"
106018 cached open
106018 search_find: file="NULL"
106018 key="SELECT COUNT(*) from mail where username =
'torsten@elbe-land.de' AND password = 'qwertzui' AND password != ''"
partial=-1 affix=NULL starflags=0 opts=NULL
106018 LRU list:
106018 internal_search_find: file="NULL"
106018 type=mysql key="SELECT COUNT(*) from mail where username =
'torsten@elbe-land.de' AND password = 'qwertzui' AND password != ''"
opts=NULL
106018 database lookup required for SELECT COUNT(*) from mail where
username = 'torsten@elbe-land.de' AND password = 'qwertzui' AND password
!= ''
106018 MySQL query: "SELECT COUNT(*) from mail where username =
'torsten@elbe-land.de' AND password = 'qwertzui' AND password != ''"
opts 'NULL'
106018 MYSQL using cached connection for localhost/popaccounts/mailadm
106018 lookup yielded: 0
106018 expanded string:
106018 SMTP>> 535 Incorrect authentication data



--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/

On 16/04/2021 13:59, Torsten Müller via Exim-users wrote:
> Now both MySQL queries work like expected,
> the first lookup yielded: 1, the second yielded: 0

The way you have coded that ${lookup } you are getting
a "no" string result if the MySQL lookup returns *anything*.

Including "0".
--
Cheers,
Jeremy

--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/

Am 16.04.2021 um 15:16 schrieb Jeremy Harris via Exim-users:
> On 16/04/2021 13:59, Torsten Müller via Exim-users wrote:
>> Now both MySQL queries work like expected,
>> the first lookup yielded: 1, the second yielded: 0
>
> The way you have coded that ${lookup } you are getting
> a "no" string result if the MySQL lookup returns *anything*.
>
> Including "0".

Thanks Jeremey ,
you are absolutely right.

I modified the MySQL queries accordingly and changed yes with no
and no with yes in the server_condition. After some tests it seems
to work now everything right.

Thank you Jeremy, Evgeniy and Heiko.

For other users, searching the archives:

auth_plain:
driver = plaintext
public_name = PLAIN
server_prompts = :
server_condition = ${if or{\
{bool{${lookup mysql {MYSQL_AUTH_GWUSERPLAIN}{yes}{no}}}}\
{bool{${lookup mysql {MYSQL_AUTH_PLAIN}{yes}{no}}}}\
}}
server_set_id = $2

MYSQL_AUTH_PLAIN = SELECT username from MYSQL_SMTPAUTH_TABLE where
MYSQL_SMTPAUTH_USER_FIELD = '${quote_mysql:$2}' AND
MYSQL_SMTPAUTH_PASS_FIELD = '${quote_mysql:$3}' AND
MYSQL_SMTPAUTH_PASS_FIELD != ''

MYSQL_AUTH_GWUSERPLAIN = SELECT username from MYSQL_SMTPAUTH_TABLE where
concat(MYSQL_SMTPAUTH_USER_FIELD , '@', punycode) = '${quote_mysql:$2}'
AND MYSQL_SMTPAUTH_PASS_FIELD = '${quote_mysql:$3}' AND
MYSQL_SMTPAUTH_PASS_FIELD != ''

Greetings from Schönebeck / Elbe
Torsten

--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/