#157 Small SQL error in vacation

closed-fixed
nobody
Vacation (31)
5
2009-10-21
2009-08-11
No

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;

Discussion

  • Stevan Bajic

    Stevan Bajic - 2009-08-12

    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;

     
  • Stevan Bajic

    Stevan Bajic - 2009-08-12

    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;

     
  • GingerDog

    GingerDog - 2009-08-12

    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?

     
  • Stevan Bajic

    Stevan Bajic - 2009-08-12

    Halo gingerdog

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

    Now Postfix.Admin will add this alias if vacation is active:
    user#domain.com@myvacation.local

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

     
  • Stevan Bajic

    Stevan Bajic - 2009-08-12

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

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

    Now Postfix.Admin will add this alias if vacation is active:
    user#domain.com@myvacation.local

    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='user@domain.com' AND (goto LIKE 'user#domain.com@%' OR
    goto LIKE '%,user#domain.com@%' )
    ------

     
  • Christian Boltz

    Christian Boltz - 2009-10-18
    • priority: 5 --> 6
     
  • Stevan Bajic

    Stevan Bajic - 2009-10-18

    Patch against Postfix.Admin 2.3 rc7

     
  • GingerDog

    GingerDog - 2009-10-20

    I don't think this is necessary; before find_receipient() is called, we take the SMTP recipient and convert it from foo#bar.com@autoreply.whatever.com to foo@bar.com

     
  • GingerDog

    GingerDog - 2009-10-20
    • priority: 6 --> 5
    • status: open --> closed-invalid
     
  • GingerDog

    GingerDog - 2009-10-21

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

     
  • GingerDog

    GingerDog - 2009-10-21
    • status: closed-invalid --> closed-fixed
     
  • GingerDog

    GingerDog - 2009-10-21

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

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks