Skip to content

Instantly share code, notes, and snippets.

@jaltek jaltek/.a_readme.md
Last active Aug 23, 2018

Embed
What would you like to do?
uberspace, maildrop & MySQL

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 foobar@example.org to the folder Your_IMAP_Folder:

mysql $USER << '__EOF__'
INSERT INTO mailfilter (email, folder) VALUES ('foobar@example.org', '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)

# 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"
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;
INSERT INTO mailfilter (email, folder) VALUES ('foobar@example.org', 'Your_IMAP_Folder');
@bobschi

This comment has been minimized.

Copy link

commented Feb 7, 2018

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: hello@example.com

ADDR: hello@example.com$'
'
RESULT:
MAILDIR: /home/leuser/./users/spam

What I was actually expecting is

ADDRTMP: hello@example.com
ADDR: hello@example.com$''
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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.