uberspace, maildrop & MySQL

.a_readme.md

uberspace, maildrop & MySQL

A simple maildrop sender filter with MySQL backend

This is a simple maildrop filter which filters incoming messages by the sender address and move it to the corresponding folder. E-Mail address and destination folder are both defined in a MySQL table.

This example is used in an uberspace environment.

Step 1: Create MySQL table

For convenience we use the already existing database corresponding to your uberspace account (e.g. your uberspace username is melanie there will be already a database called melanie.

mysql $USER << '__EOF__'
CREATE TABLE `mailfilter` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`folder` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
__EOF__

Step 2: Create some entries

Below you will find a single sample entry which will move e-mails from [email protected] to the folder Your_IMAP_Folder:

mysql $USER << '__EOF__'
INSERT INTO mailfilter (email, folder) VALUES ('[email protected]', 'Your_IMAP_Folder');
__EOF__

Step 3: The maildrop rule

It is important that you have read and understood the basic configuration hints from the uberspace maildrop support wiki.

You can use the below-mentioned script as your maildrop filter rule or enhance your existing script by adding the if ( /^From:\s*(.*)/ ) code block. As mentioned before we are using the database corresponding to your uberspace account. So you have to change the variable DATABASE to your username. Last but not least be sure you set the correct rights to the filter rule (e.g. chmod 600 ~/.myfilter).

(Thx to Jonas from uberspace for his feedback)

.myfilter

# set default Maildir
MAILDIR="$HOME/Maildir"
logfile "$HOME/mailfilter.log"

#user settings
DATABASE=<YOUR_UBERSPACE_USERNAME_HERE>
 
# check if we're called from a .qmail-EXT instead of .qmail
import EXT
if ( $EXT )
{
  # does a vmailmgr user named $EXT exist?
  # if yes, deliver mail to his Maildir instead
  CHECKMAILDIR = `dumpvuser $EXT | grep '^Directory' | awk '{ print $2 }'`
  if ( $CHECKMAILDIR )
  {
    MAILDIR="$HOME/$CHECKMAILDIR"
  }
}
 
# check from address against mysql
if ( /^From:\s*(.*)/ )
{
  #filtering mail
  ADDRTMP=getaddr($MATCH1)
  #escape address for security reasons
  ADDR=`printf '%q' $ADDRTMP | sed 's/^M//'`
 
  #mysql select
  RESULT=`echo -ne "select folder from mailfilter where email = '$ADDR';" | mysql $DATABASE --skip-column-names`
 
  #folder for e-mail address found?
  if ($RESULT ne "")
  {
    MAILDIR="$MAILDIR/.$RESULT"
    `test -d "$MAILDIR"`
    if ( $RETURNCODE == 1 )
    {
      `maildirmake "$MAILDIR"`
    }
    
    to "$MAILDIR";
  }
}
 
# Standardregel:
to "$MAILDIR"

mysql-init.sql

CREATE TABLE `mailfilter` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`folder` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

mysql-sample.sql

INSERT INTO mailfilter (email, folder) VALUES ('[email protected]', 'Your_IMAP_Folder');

Hej,

first off: thanks for making this! :)

I'm having a little problem with wrapping my head around this. First off: basically copy-pasta'ed this onto my uberspace. But it won't work. I am guessing from debugging that it's not finding anything in the database because of formatting issues. I'm getting the following output:

ADDRTMP: [email protected]

ADDR: [email protected]$'
'
RESULT:
MAILDIR: /home/leuser/./users/spam

What I was actually expecting is

ADDRTMP: [email protected]
ADDR: [email protected]$''
RESULT: Newsletters
MAILDIR: /home/leuser/./users/spam/.Newsletters

So I am wondering where that \n in there comes from. I am not sure, as well, what the masked ADDR should look like. Could you provide an example, please? That would help me a lot debugging this.