[ postfixadmin-Bugs-2835877 ] Small SQL error in vacation

[SourceForge.net <no...@so...>]

Bugs item #2835877, was opened at 2009-08-11 22:23
Message generated for change (Comment added) made by gingerdog
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=937964&aid=2835877&group_id=191583

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: Vacation
Group: None
Status: Closed
>Resolution: Fixed
Priority: 5
Private: No
Submitted By: Stevan Bajic (sbajic)
Assigned to: Nobody/Anonymous (nobody)
Summary: Small SQL error in vacation

Initial Comment:
Hello all

there is a small error in one of the SQL clauses for vacation. The problem is in this part here:
# Recipient has vacation
  if ($rv == 1) {
    $realemail = $email;
    $logger->debug("Found '\$email'\ has vacation active");
  } else {
    $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;


That should be:
# 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,$vemail.",%","%,".$vemail,"%,".$vemail.",%") or panic_execute($query,"address='$email'");
    $rv = $stm->rows;


----------------------------------------------------------------------

>Comment By: GingerDog (gingerdog)
Date: 2009-10-21 08:00

Message:
Hi, merged in changeset [734] - thanks for taking the time to explain it to
me :-)

----------------------------------------------------------------------

Comment By: GingerDog (gingerdog)
Date: 2009-10-21 08:00

Message:
Thanks for the bug report; we believe this has been fixed in subversion.

----------------------------------------------------------------------

Comment By: GingerDog (gingerdog)
Date: 2009-10-20 10:50

Message:
I don't think this is necessary; before find_receipient() is called, we
take the SMTP recipient and convert it from
foo#ba...@au... to fo...@ba...


----------------------------------------------------------------------

Comment By: Stevan Bajic (sbajic)
Date: 2009-08-12 06:39

Message:
It's morning and I make to much errors. The message should be:
------
Hallo gingerdog

The $vemail is needed. Let me explain:
Normal email: us...@do...
Vacation domain: myvacation.local

Now Postfix.Admin will add this alias if vacation is active:
user#dom...@my...

So we need to replace in the original email the @ with an # in order to be
able to match against it.

And then the SQL clause checking that should be:
SELECT goto FROM alias WHERE address='us...@do...' AND (goto LIKE
'user#domain.com@%' OR
goto LIKE '%,user#domain.com@%' )
------

----------------------------------------------------------------------

Comment By: Stevan Bajic (sbajic)
Date: 2009-08-12 06:36

Message:
Halo gingerdog

The $vemail is needed. Let me explain:
Normal email: us...@do...
Vacation domain: myvacation.local

Now Postfix.Admin will add this alias if vacation is active:
user#dom...@my...

And then the SQL clause checking that should be:
SELECT goto FROM alias WHERE address='us...@do...' AND (goto LIKE
'user#domain.com@%' OR
goto LIKE '%,us...@do...@%' )

----------------------------------------------------------------------

Comment By: GingerDog (gingerdog)
Date: 2009-08-12 06:21

Message:
Err..


I can see the initial problem with the comma's being placed outside of the
bound parameters... and this should be fixed. What are you doing with the
$vemail parameter though?

----------------------------------------------------------------------

Comment By: Stevan Bajic (sbajic)
Date: 2009-08-12 05:59

Message:
Aggrr! Again wrong. This one is the right one:
# 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 ?)};
    $stm = $dbh->prepare($query) or panic_prepare($query);
    $stm->execute($email,$vemail."@%","%,".$vemail."@%") or
panic_execute($query,"address='$email'");
    $rv = $stm->rows;

----------------------------------------------------------------------

Comment By: Stevan Bajic (sbajic)
Date: 2009-08-12 05:58

Message:
I looked again at the SQL clause and my above posted code is wrong. The
fixed code should be:
# 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 ??)};
    $stm = $dbh->prepare($query) or panic_prepare($query);
    $stm->execute($email,$vemail."@%","%,".$vemail."@%") or
panic_execute($query,"address='$email'");
    $rv = $stm->rows;

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=937964&aid=2835877&group_id=191583