[Dovecot] Developing new Dspam Plugin
This email /was/ going to be a bit of a bitch about trying to get mysql compiled in to a dovecot plugin, but I got it all working before I finished the email! :-D
Ok, basically what I am doing (Johannes permission) is modifying the dspam plugin, so that it instead talks to a database (MySQL in this case), and updates a table, so that you can do the actual training at a time of lower load.
It /does/ require MySQL >= 5 cause I am using stored PROCEDURE's, which gives you the advantage, that you can use the same plugin, without changing anything, regardless of what your database looks like, or how you want the database to work! :-D
I have successfully modified Johannes plugin to call SPAM and HAM in the database, with the signatures, and also upgraded it to work with beta9. I also made the small change of a define for the Spam folder, so that if your Spam folder is named differently, you don't have to change the code in 2-3 places as well ;-)
I am yet to write any 'batch' code for processing the database tables, but that is fairly trivial, and I could even write it in php if I wanted ;-) (If you want something to do, go ahead and write me a C client to do this! I'll get to it eventually, and you can reuse most of the code from the plugin!)
See http://members.plug.org.au/~linuxalien/dokuwiki/projects:dovecot-mysql-dspam... for more details, and the code!
Feed back, comments, and criticism are all welcome!
Tim
Linux Counter user #273956
On Fri, 2006-06-23 at 20:27 +0800, Timothy White wrote:
I have successfully modified Johannes plugin to call SPAM and HAM in the database, with the signatures, and also upgraded it to work with beta9. I also made the small change of a define for the Spam folder, so that if your Spam folder is named differently, you don't have to change the code in 2-3 places as well ;-)
Feed back, comments, and criticism are all welcome!
Cool. I'll look at it in a week, preparing for an exam right now.
johannes
On Fri, 2006-06-23 at 20:27 +0800, Timothy White wrote:
I have successfully modified Johannes plugin to call SPAM and HAM in the database, with the signatures,
Your call_dspam routine is a bit bogus, the neither exit(127) nor return -1 can ever execute ;) The original code must have had something like that because it was forking, but your code doesn't.
Same goes for mysqlinit(), you shouldn't exit() from a plugin, just return an error to the imap connection. If you exit(), the imap connection dies.
Also, you should lazily init the mysql connection and then either close it, or keep it open and re-use it, currently you don't close it but re-init the mysql context and reconnect, that's probably going to create a bunch of stale connections to the database.
If you decide to go for the route that keeps the connection open for the next time, then you will need to check for disconnects before using the connection again, the database might have been restarted and you don't want the plugin to fall on its nose over that, it should just reconnect...
johannes
On Jun 26, 2006, at 9:21 AM, Johannes Berg wrote:
Also, you should lazily init the mysql connection and then either
close it, or keep it open and re-use it, currently you don't close it but re-init the mysql context and reconnect, that's probably going to
create a bunch of stale connections to the database.
Or would lib-dict's API be enough? Then you can make the connections
go through dict proxy process which keeps the MySQL connections open
all the time.
On Mon, 2006-06-26 at 11:41 +0300, Timo Sirainen wrote:
Or would lib-dict's API be enough? Then you can make the connections
go through dict proxy process which keeps the MySQL connections open
all the time.
Haven't looked but it might be worth investigating for that so when the user needs a connection anyway no other one is opened. Or something.
johannes
On Mon, 2006-06-26 at 11:41 +0300, Timo Sirainen wrote:
Or would lib-dict's API be enough? Then you can make the connections
go through dict proxy process which keeps the MySQL connections open
all the time.
Yeah, I think lib-dict would be enough. Effectively all we need is add tokens (the dspam signature) with an atomic count associated, exactly what lib-dict does. Does the dict proxy properly reopen sql connections when the database server is restarted?
johannes
On Wed, 2006-07-19 at 12:17 +0200, Johannes Berg wrote:
On Mon, 2006-06-26 at 11:41 +0300, Timo Sirainen wrote:
Or would lib-dict's API be enough? Then you can make the connections
go through dict proxy process which keeps the MySQL connections open
all the time.Yeah, I think lib-dict would be enough. Effectively all we need is add tokens (the dspam signature) with an atomic count associated, exactly what lib-dict does. Does the dict proxy properly reopen sql connections when the database server is restarted?
Yes, it uses lib-sql which handles all this internally. lib-sql is also used by dovecot-auth.
On 6/26/06, Johannes Berg johannes@sipsolutions.net wrote:
On Fri, 2006-06-23 at 20:27 +0800, Timothy White wrote:
I have successfully modified Johannes plugin to call SPAM and HAM in the database, with the signatures,
Your call_dspam routine is a bit bogus, the neither exit(127) nor return -1 can ever execute ;) The original code must have had something like that because it was forking, but your code doesn't.
The original code had those 2 lines. exit(127); /* fall through if dspam can't be found */ return -1; /* never executed */ Now I look at it, I can do without both of them.
Same goes for mysqlinit(), you shouldn't exit() from a plugin, just return an error to the imap connection. If you exit(), the imap connection dies.
Done.
Also, you should lazily init the mysql connection and then either close it, or keep it open and re-use it, currently you don't close it but re-init the mysql context and reconnect, that's probably going to create a bunch of stale connections to the database.
Hmmm, show's that I'm a bad programmer. I can't believe I left connections open, I don't normally do that! This lib-dict stuff sounds good. Timo, do you have an example of how to use it? Otherwise, for now I'll just close the connections.
Tim
Linux Counter user #273956
Also, you should lazily init the mysql connection and then either close it, or keep it open and re-use it, currently you don't close it but re-init the mysql context and reconnect, that's probably going to create a bunch of stale connections to the database.
Hmmm, show's that I'm a bad programmer. I can't believe I left connections open, I don't normally do that! This lib-dict stuff sounds good. Timo, do you have an example of how to use it? Otherwise, for now I'll just close the connections.
Ok, I've now fixed this, by initialising a SQL connection once, and then using SQL ping to check if it's alive, and if it's not the give an error (I'll try and make it disconnect and reconnect later).
I just realised that it may be possible to exploit the snprintf and send strange commands to the server, for this reason, the user that the plugin uses, should only be able to run the 2 procedure's. I have no idea how to make this secure, or if it is secure or not. Any ideas? (e.g. snprintf(query, 20+MAXSIGLEN, "CALL SPAM(\"%s\")", signature); If someone modifies the header, as long as it's within the MAXSIGLEN then they can effect the query?)
Anyway, I'm off to try and work out why my DB is doing strange things, then I'll update my wiki, and check for compat with RC1
Tim
Linux Counter user #273956
Hi,
Ok, I've now fixed this, by initialising a SQL connection once, and then using SQL ping to check if it's alive, and if it's not the give an error (I'll try and make it disconnect and reconnect later).
You really want the latter since imap connections will stay open, and the workaround would require closing the connection which could be quite expensive for the client (evolution for example synchronizes completely then)
I just realised that it may be possible to exploit the snprintf and send strange commands to the server, for this reason, the user that the plugin uses, should only be able to run the 2 procedure's. I have no idea how to make this secure, or if it is secure or not. Any ideas? (e.g. snprintf(query, 20+MAXSIGLEN, "CALL SPAM(\"%s\")", signature); If someone modifies the header, as long as it's within the MAXSIGLEN then they can effect the query?)
Yeah, good point. But luckily dspam signatures are fairly good in that they'll always match the re "^[A-Za-z0-9,]*$" so you can check against that... Indeed, I think it always consists of only numbers and the optional userid separated off with a comma, but above RE should work always. So just check that each character is alphanumeric or a comma and return an error otherwise.
johannes
On 6/28/06, Timothy White weirdit@gmail.com wrote:
I just realised that it may be possible to exploit the snprintf and send strange commands to the server, for this reason, the user that the plugin uses, should only be able to run the 2 procedure's. I have no idea how to make this secure, or if it is secure or not. Any ideas? (e.g. snprintf(query, 20+MAXSIGLEN, "CALL SPAM(\"%s\")", signature); If someone modifies the header, as long as it's within the MAXSIGLEN then they can effect the query?)
Anyone got ideas/comments on this?
Anyway, I'm off to try and work out why my DB is doing strange things, then I'll update my wiki, and check for compat with RC1
Wiki updated, code still untested with RC1. Client Runner written (in php for now). I discovered a bug in my SQL file for setting up the procedures, which was truncating signatures. Also fixed warning, by using count(ID) rather than trying to select ID's when it could/should result in an empty set.
Tim http://members.plug.org.au/~linuxalien/dokuwiki/projects:dovecot-mysql-dspam...
Linux Counter user #273956
On Fri, 30 Jun 2006, Timothy White wrote:
On 6/28/06, Timothy White weirdit@gmail.com wrote:
I just realised that it may be possible to exploit the snprintf and send strange commands to the server, for this reason, the user that the plugin uses, should only be able to run the 2 procedure's. I have no idea how to make this secure, or if it is secure or not. Any ideas? (e.g. snprintf(query, 20+MAXSIGLEN, "CALL SPAM(\"%s\")", signature); If someone modifies the header, as long as it's within the MAXSIGLEN then they can effect the query?)
Anyone got ideas/comments on this?
Dunno what you exactly mean, but when the signature is user-specified, you have to:
a) sanities the contents, so it cannot break out of the quotes, e.g. you have to quote embedded quotes and escape characters, and b) you must ensure that strlen(signatures) < 20+MAXSIGLEN - strlen(pattern)
Bye,
-- Steffen Kaiser
On Fri, 2006-06-30 at 11:08 +0800, Timothy White wrote:
On 6/28/06, Timothy White weirdit@gmail.com wrote:
I just realised that it may be possible to exploit the snprintf and send strange commands to the server, for this reason, the user that the plugin uses, should only be able to run the 2 procedure's. I have no idea how to make this secure, or if it is secure or not. Any ideas? (e.g. snprintf(query, 20+MAXSIGLEN, "CALL SPAM(\"%s\")", signature); If someone modifies the header, as long as it's within the MAXSIGLEN then they can effect the query?)
Anyone got ideas/comments on this?
#include "strescape.h"
query = t_strdup_printf("CALL SPAM(\"%s\")", str_escape(signature));
And maybe put t_push() and t_pop() calls somewhere so that when copying lots of messages it doesn't temporarily use too much memory (the query's memory is valid until t_pop() is called).
Although the str_escape() isn't the right way to do it. mysql_real_escape_string() would be better. But anyway rather than using MySQL directly you could use sql-api.h. Then you could also use sql_escape_string() which is easier. :)
participants (5)
-
Johannes Berg
-
Steffen Kaiser
-
Timo Sirainen
-
Timothy White
-
Timothy White