The attached patch enables dbmail to use a simple procmail-like
mechanism for filtering incoming mail. Rather than always delivering to
INBOX, users can now filter their mail with regular expressions and
direct them to different folders. For example, a pattern like
^List-Id:.*dbmail\.org
can be used to direct all mail from the dbmail lists to a specific
folder.
It should be possible for untrusted users to create these filters, and
it's very fast, so of course it can only do pretty simple matching
(POSIX enhanced regexes) and has no command execution etc. like
procmail.
To make this work, my patch adds the "filter" table. If this table is
not present, everything will work like always, so backward compatibility
shouldn't be an issue.
A method for users to input these regular expressions is not included,
as it will probably be a part of a web interface for user administration
at each site.
--
Jonas Jensen <[EMAIL PROTECTED]>
diff -ur dbmail/db.h dbmail-filters/db.h
--- dbmail/db.h 2003-03-17 17:04:08.000000000 +0100
+++ dbmail-filters/db.h 2003-04-18 17:11:20.000000000 +0200
@@ -120,9 +120,10 @@
int db_get_reply_body(u64_t userid, char **body);
u64_t db_get_mailboxid(u64_t useridnr, const char *mailbox);
+u64_t db_find_destination(u64_t useridnr, const char *header);
u64_t db_get_useridnr(u64_t messageidnr);
u64_t db_get_message_mailboxid(u64_t messageidnr);
-u64_t db_insert_message(u64_t useridnr, const char *deliver_to_mailbox, const char *uniqueid);
+u64_t db_insert_message(u64_t useridnr, u64_t mailbox_idnr, const char *uniqueid);
u64_t db_update_message(u64_t messageidnr, const char *unique_id,
u64_t messagesize, u64_t rfcsize);
int db_update_message_multiple(const char *unique_id, u64_t messagesize, u64_t rfcsize);
diff -ur dbmail/mysql/dbmysql.c dbmail-filters/mysql/dbmysql.c
--- dbmail/mysql/dbmysql.c 2003-04-01 09:47:14.000000000 +0200
+++ dbmail-filters/mysql/dbmysql.c 2003-04-19 21:40:47.000000000 +0200
@@ -519,6 +519,58 @@
return inboxid;
}
+u64_t
+db_find_destination (u64_t useridnr, const char *header)
+{
+ regex_t re;
+
+ snprintf (query, DEF_QUERYSIZE,
+ "SELECT filters.mailbox_idnr, filters.pattern "
+ "FROM filters, mailboxes "
+ "WHERE filters.mailbox_idnr = mailboxes.mailbox_idnr "
+ "AND mailboxes.owner_idnr = %llu "
+ "ORDER BY filters.weight DESC, filters.pattern", useridnr);
+
+ if (db_query (query) == -1)
+ return 0;
+
+ if ((res = mysql_store_result (&conn)) == NULL)
+ {
+ trace (TRACE_ERROR,
+ "db_find_destination(): mysql_store_result failed: %s",
+ mysql_error (&conn));
+
+ return 0;
+ }
+
+ while ((row = mysql_fetch_row (res)))
+ {
+ trace (TRACE_DEBUG, "db_find_destination(): matching pattern: [%s]",
+ row[1]);
+
+ if (row[1] == NULL || regcomp (&re, row[1],
+ REG_EXTENDED | REG_NOSUB | REG_ICASE |
+ REG_NEWLINE) != 0)
+ {
+ trace (TRACE_DEBUG, "db_find_destination(): invalid pattern [%s]");
+ continue;
+ }
+
+ if (regexec (&re, header, 0, NULL, 0) == 0)
+ {
+ trace (TRACE_DEBUG, "db_find_destination(): pattern matched");
+
+ regfree (&re);
+ mysql_free_result (res);
+ return row[0] ? strtoull (row[0], NULL, 10) : 0;
+ }
+
+ regfree (&re);
+ }
+
+ mysql_free_result (res);
+ return 0;
+}
u64_t db_get_message_mailboxid (u64_t message_idnr)
{
@@ -609,10 +661,7 @@
}
-/*
- * inserts into inbox !
- */
-u64_t db_insert_message (u64_t useridnr, const char *deliver_to_mailbox,
+u64_t db_insert_message (u64_t useridnr, u64_t mailbox_idnr,
const char *uniqueid)
{
char timestr[30];
@@ -626,8 +675,7 @@
snprintf (query, DEF_QUERYSIZE,"INSERT INTO messages(mailbox_idnr,messagesize,unique_id,"
"internal_date,recent_flag,status)"
" VALUES (%llu, 0, \"%s\", \"%s\", 1, '005')",
- deliver_to_mailbox ? db_get_mailboxid(useridnr, deliver_to_mailbox) :
- db_get_mailboxid (useridnr, "INBOX"),
+ mailbox_idnr ? mailbox_idnr : db_get_mailboxid (useridnr, "INBOX"),
uniqueid ? uniqueid : "", timestr);
if (db_query (query)==-1)
diff -ur dbmail/pgsql/dbpgsql.c dbmail-filters/pgsql/dbpgsql.c
--- dbmail/pgsql/dbpgsql.c 2003-03-21 14:02:22.000000000 +0100
+++ dbmail-filters/pgsql/dbpgsql.c 2003-04-19 21:40:51.000000000 +0200
@@ -23,7 +23,7 @@
#include <string.h>
#include <stdlib.h>
#include <unistd.h>
-
+#include <regex.h>
PGconn *conn;
PGresult *res;
@@ -491,6 +491,56 @@
return inboxid;
}
+u64_t
+db_find_destination (u64_t useridnr, const char *header)
+{
+ regex_t re;
+ int n, i;
+
+ snprintf (query, DEF_QUERYSIZE,
+ "SELECT filters.mailbox_idnr, filters.pattern "
+ "FROM filters, mailboxes "
+ "WHERE filters.mailbox_idnr = mailboxes.mailbox_idnr "
+ "AND mailboxes.owner_idnr = %llu::bigint "
+ "ORDER BY filters.weight DESC, filters.pattern", useridnr);
+
+ if (db_query (query) == -1)
+ return 0;
+
+ n = PQntuples (res);
+
+ for (i = 0; i < n; i++)
+ {
+ char *mailbox_idnr = PQgetvalue (res, i, 0);
+ char *pattern = PQgetvalue (res, i, 1);
+
+ trace (TRACE_DEBUG, "db_find_destination(): matching pattern: [%s]",
+ pattern);
+
+ if (pattern == NULL || regcomp (&re, pattern,
+ REG_EXTENDED | REG_NOSUB | REG_ICASE |
+ REG_NEWLINE) != 0)
+ {
+ trace (TRACE_DEBUG, "db_find_destination(): invalid pattern [%s]");
+ continue;
+ }
+
+ if (regexec (&re, header, 0, NULL, 0) == 0)
+ {
+ trace (TRACE_DEBUG, "db_find_destination(): pattern matched");
+
+ regfree (&re);
+ PQclear (res);
+ return mailbox_idnr ? strtoull (mailbox_idnr, NULL, 10) : 0;
+ }
+
+ regfree (&re);
+ }
+
+ PQclear (res);
+ return 0;
+}
+
/*
* returns the mailbox id of a message
@@ -561,7 +611,7 @@
* defaultly inserts into inbox, unless deliver_to_mailbox exists
* and is a valid mbox.
*/
-u64_t db_insert_message (u64_t useridnr, const char *deliver_to_mailbox,
+u64_t db_insert_message (u64_t useridnr, u64_t mailbox_idnr,
const char *uniqueid)
{
char timestr[30];
@@ -574,8 +624,7 @@
snprintf (query, DEF_QUERYSIZE,"INSERT INTO messages(mailbox_idnr,messagesize,unique_id,"
"internal_date,recent_flag,status) VALUES (%llu::bigint, 0, '%s', '%s', 1, '005')",
- (deliver_to_mailbox) ? db_get_mailboxid(useridnr, deliver_to_mailbox)
- : db_get_mailboxid(useridnr, "INBOX"),
+ mailbox_idnr ? mailbox_idnr : db_get_mailboxid (useridnr, "INBOX"),
uniqueid ? uniqueid : "", timestr);
if (db_query (query)==-1)
diff -ur dbmail/pipe.c dbmail-filters/pipe.c
--- dbmail/pipe.c 2003-03-17 17:04:08.000000000 +0100
+++ dbmail-filters/pipe.c 2003-04-18 17:00:13.000000000 +0200
@@ -304,14 +304,21 @@
}
else
{
+ u64_t mailbox_idnr;
+
/* make the id numeric */
userid = strtoull((char *)tmp->data, NULL, 10);
+ if (deliver_to_mailbox)
+ mailbox_idnr = db_get_mailboxid(userid, deliver_to_mailbox);
+ else
+ mailbox_idnr = db_find_destination(userid, header);
+
create_unique_id(unique_id,0);
/* create a message record */
temp_message_record_id = db_insert_message ((u64_t)userid,
- deliver_to_mailbox, unique_id);
+ mailbox_idnr, unique_id);
/* message id is an array of returned message id's
* all messageblks are inserted for each message id
diff -ur dbmail/sql/mysql/create_tables_innoDB.mysql dbmail-filters/sql/mysql/create_tables_innoDB.mysql
--- dbmail/sql/mysql/create_tables_innoDB.mysql 2003-03-17 17:04:09.000000000 +0100
+++ dbmail-filters/sql/mysql/create_tables_innoDB.mysql 2003-04-19 21:50:54.000000000 +0200
@@ -106,3 +106,12 @@
KEY msg_index (message_idnr),
UNIQUE messageblk_idnr_2 (messageblk_idnr)
) TYPE = InnoDB;
+
+CREATE TABLE filters (
+ filter_idnr bigint(20) NOT NULL auto_increment,
+ mailbox_idnr bigint(20) NOT NULL default '0',
+ pattern varchar(255) NOT NULL default '',
+ weight int(11) NOT NULL default '0',
+ PRIMARY KEY (filter_idnr),
+ KEY mailbox_idnr (mailbox_idnr)
+) TYPE=InnoDB;
diff -ur dbmail/sql/mysql/create_tables.mysql dbmail-filters/sql/mysql/create_tables.mysql
--- dbmail/sql/mysql/create_tables.mysql 2003-03-17 17:04:09.000000000 +0100
+++ dbmail-filters/sql/mysql/create_tables.mysql 2003-04-19 21:49:58.000000000 +0200
@@ -128,3 +128,13 @@
KEY msg_index (message_idnr),
UNIQUE messageblk_idnr_2 (messageblk_idnr)
) TYPE=MyISAM;
+
+CREATE TABLE filters (
+ filter_idnr bigint(20) NOT NULL auto_increment,
+ mailbox_idnr bigint(20) NOT NULL default '0',
+ pattern varchar(255) NOT NULL default '',
+ weight int(11) NOT NULL default '0',
+ PRIMARY KEY (filter_idnr),
+ KEY mailbox_idnr (mailbox_idnr)
+);
+
diff -ur dbmail/sql/postgresql/create_tables.pgsql dbmail-filters/sql/postgresql/create_tables.pgsql
--- dbmail/sql/postgresql/create_tables.pgsql 2003-03-17 17:04:09.000000000 +0100
+++ dbmail-filters/sql/postgresql/create_tables.pgsql 2003-04-19 17:56:43.000000000 +0200
@@ -4,6 +4,16 @@
- add / remove indexes depending on performance
*/
+CREATE SEQUENCE filter_idnr_seq;
+CREATE TABLE filters (
+ filter_idnr INT8 DEFAULT nextval('filter_idnr_seq'),
+ mailbox_idnr INT8 DEFAULT '0' NOT NULL,
+ pattern VARCHAR(255) NOT NULL,
+ weight INT4 DEFAULT '0' NOT NULL,
+ PRIMARY KEY (filter_idnr)
+);
+CREATE INDEX filters_mailbox_idx ON filters(mailbox_idnr);
+
CREATE SEQUENCE alias_idnr_seq;
CREATE TABLE aliases (
alias_idnr INT8 DEFAULT nextval('alias_idnr_seq'),
