Re: [Postfixadmin-devel] Regarding Bug #2835877

[Stevan B. <st...@ba...>]

On Tue, 20 Oct 2009 20:27:27 +0100
David Goodwin <da...@co...> wrote:

> > I checked out trunk and see this here:
> > -------------------
> > # Recipient has vacation
> >         if ($rv == 1) {
> >                 $realemail = $email;
> >                 $logger->debug("Found '\$email'\ has vacation active");
> >         } else {
> >                 my $vemail = $email;
> >                 $vemail =~ s/\@/#/g;
> >                 $logger->debug("Looking for alias records that \'$email\' resolves to with vacation turned on");
> >                 $query = qq{SELECT goto FROM alias WHERE address=? AND (goto LIKE ? OR goto LIKE ? OR goto LIKE ?)};
> >                 $stm = $dbh->prepare($query) or panic_prepare($query);
> >                 $stm->execute($email,"$email,%","%,$email","%,$email,%") or panic_execute($query,"address='$email'");
> >                 $rv = $stm->rows;
> > -------------------
> > 
> > This is not going to work. This part here:
> > -------------------
> > $stm->execute($email,"$email,%","%,$email","%,$email,%") or panic_execute($query,"address='$email'");
> > -------------------
> > 
> > Is going to be executed as:
> > -------------------
> > SELECT goto FROM alias WHERE address='da...@co...' AND (goto LIKE 'da...@co...,%' OR goto LIKE '%,da...@co...' OR goto LIKE '%,da...@co...,%')
> > -------------------
> > 
> 
> Hi,
> 
Hallo David,


> The code above lives in a function called find_recipient() (or
> something similar).
>
Correct.


> BEFORE this is called we convert the SMTP
> recipient (e.g. foo#bar@autoreply.whatever) to it's real email addres
> (e.g. foo@bar).
>
Correct.


> Hence I'm confused as to why you still insist it's
> wrong. Can you please read the code which calls that function and
> confirm I'm right - perhaps I've totally misread it?
> 
You miss the point how the content of the alias table is. Lets look at a normal entry (I removed the created and modified column to make it easier for reading):
+------------------+------------------+-------------+
| address          | goto             | domain      |
+------------------+------------------+-------------+
| @example.com     | us...@ex... | example.com |
| us...@ex... | us...@ex... | example.com |
+------------------+------------------+-------------+


Okay. Now "us...@ex..." is going to use Postfix.Admin and add vacation for his account. Beside that the table "vacation" is going to be updated the table "alias" is as well updated and looks like this:
+------------------+--------------------------------------------------+-------------+
| address          | goto                                             | domain      |
+------------------+--------------------------------------------------+-------------+
| @example.com     | us...@ex...                                 | example.com |
| us...@ex... | us...@ex...,user#example.com@vacationdomain | example.com |
+------------------+--------------------------------------------------+-------------+


So lets take a look in the sub called "find_real_address". The purpose of that function is to find users having vacation active. Right? And the purpose of that function is to try hard to find if an alias that got a mail (and aliases do not have "@vacationdomain" in their goto field) in fact has a real address that has vacation active. Right?

So this SQL clause here:
SELECT goto FROM alias WHERE address='us...@ex...' AND (goto LIKE 'us...@ex...,%' OR goto LIKE '%,us...@ex...' OR goto LIKE '%,us...@ex...,%')

Is wrong. It does not honor the vacation goto format. All we want is that the SQL lookup for an alias pointing to an real address (or another alias wich get's then again looked up by the vacation Perl script till it reaches a real address) should only match if that address has in fact vacation active. If goto does not contain the vacation format the the SQL clause should NOT match. That's what I am telling.

Or let's take it the other way around. If you have right then the whole code doing that crazy alias resolving is useless since there is enough to just check for address=$email (wich would be wrong).


The right SQL clause is:
SELECT goto FROM alias WHERE address='us...@ex...' AND (goto LIKE 'user#example.com@%' OR goto LIKE '%,user#example.com@%')


The absolute best (catching all possible cases) SQL clause would be:
SELECT goto FROM alias WHERE address='us...@ex...' AND (goto LIKE 'user#example.com@vacationdomain,%' OR goto LIKE '%,user#example.com@vacationdomain' OR goto LIKE '%,user#example.com@vacationdomain,%' OR goto='user#example.com@vacationdomain')


In Perl code that would be (just the significant lines):
$query = qq{SELECT goto FROM alias WHERE address=? AND (goto LIKE ? OR goto LIKE ? OR goto LIKE ? OR goto=?)};
$stm->execute($email,"$vemail@$vacation_domain,%","%,$vemail@$vacation_domain","%,$vemail@$vacation_domain,%","$vemail@$vacation_domain") or panic_execute($query,"address='$email'");


Do you now understand what I mean?



> Thanks
>
Kind Regards from Switzerland


> David.
> 
Stevan Bajic