[ 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: Open
Resolution: None
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-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