MailWatch.pm (was RE: SQL Redux)

[Steve Freegard]

Thanks Julian,

I'll sort this out for the next version.

As an aside - I'm concerned that MailWatch has been taking up a fair bit of
bandwidth on the MailScanner list at times and don't want to get on anyones
nerves with off-topic posts, so I'll be setting it up as a project on
Sourceforge at the weekend and I'll create the necessary mailing-lists to
move the traffic there.

Cheers,
Steve.

-----Original Message-----
From: Julian Field
To: Steve Freegard
Cc: mailscanner at jiscmail.ac.uk
Sent: 31/07/03 15:45
Subject: RE: SQL Redux

At 15:43 31/07/2003, you wrote:
>Hi Julian,
>
>Thanks very much for this.
>
>I'm 99.9% sure that I kept the code below - the only changes I made to
your
>original code was to log directly to MySQL bypassing the temporary
files.
>
>I'm presuming that the NULL mapping code was added recently (which is
why I
>don't have it in my patches)??

Yes, I added it this morning.

>Do you think it would be easier as there are now two branches of this
code
>for me to distribute a MailWatch.pm instead of patches to
CustomConfig.pm
>and call the MailWatch logging routine something different (something
like
>&MailWatchSQLLogging) - then all that would need to be done is to
include
>the MailWatch.pm from CustomConfig.pm.
>
>That would save on the confusion between the two versions at least -
does
>this sound like a good idea??

This sounds like a great idea. Having 2 branches of the code is a
nightmare
to maintain. This way all your bugs remain yours, and all mine remain
mine :-)
That way you can just distribute your extra file and give a very simple
modification that people need to make to CustomConfig.pm when installing

MailWatch, e.g.
         require MailWatch.pm;


>-----Original Message-----
>From: Julian Field
>To: MAILSCANNER at JISCMAIL.AC.UK
>Sent: 31/07/03 11:47
>Subject: Re: SQL Redux
>
>You also need to make sure he does the
>          @fields = map { s/\'/\\'/g } @fields;
>line as otherwise people could inject SQL code using malicious email
>addresses.
>
>At 11:42 31/07/2003, you wrote:
> >aha
> >
> >I think the
> >
> >@fields = map { ($_ eq '')?'NULL':"$_" } @fields;
> >
> >bit is missing from Steve's patch. I'll try and plop a line or two in
>to
> >check the potential NULL's and mark the value correctly..
> >
> >--
> >Martin
> >
> >Julian Field wrote:
> >
> >>At 11:04 31/07/2003, you wrote:
> >>
> >>>Chris Trudeau wrote:
> >>>
> >>>>>this is exactly the problem I'm seeing with 4.22.5 on FreeBSD.
> >>>>
> >>>>
> >>>>
> >>>>CT:  OK, there seems to be a common thread...I haven't expanded
test
>to
> >>>>anything other than 4.22-5 or 4.22.1, but the older version I have
> >>>>working
> >>>>beatifully, so either the logging function in 4.22-1 doesn't have
>the
> >>>>NULL
> >>>>value problem or its addressed differently.  Perhaps I will try
> >>>>installing
> >>>>4.22-1 or a between version to try and pinpoint where it breaks!
> >>>
> >>>Chris
> >>>
> >>>looking at the 4.22.1 code there's quite a big change on the whole
>SQL
> >>>logging setup. Infact the code is radically different.
> >>>
> >>>in 4.22.1 it reads the logfile to get the info required. Slow...
> >>>
> >>>in 4.22.5 it's using the values from the currently processed email
to
> >>>drive the insert. It's a lot faster, but seems not to translate
NULLs
> >>>etc very well.
> >>
> >>
> >>This goes in CustomConfig.pm around line 373.
> >>It goes in just before the "prepare" statement that inserts @fields
>into
> >>the database table.
> >>Note the additional bugfix when replacing ' with \'
> >>
> >>   while(<$logfile1>) {
> >>     chomp;
> >>     @fields = split(/\t/);
> >>     # Work through each field protecting any special characters
such
>as '
> >>     # The line below replaces ' with \'
> >>     @fields = map { s/\'/\\'/g } @fields;
> >>
> >>     # Set any empty strings to NULL so the SQL insert works
correctly
> >>     @fields = map { ($_ eq '')?'NULL':"$_" } @fields;
> >>
> >>
> >>--
> >>Julian Field
> >>www.MailScanner.info
> >>MailScanner thanks transtec Computers for their support
>
>--
>Julian Field
>www.MailScanner.info
>MailScanner thanks transtec Computers for their support
>
>--
>This email and any files transmitted with it are confidential and
>intended solely for the use of the individual or entity to whom they
>are addressed. If you have received this email in error please notify
>the sender and delete the message from your mailbox.
>
>This footnote also confirms that this email message has been swept by
>MailScanner (www.mailscanner.info) for the presence of computer
viruses.

--
Julian Field
www.MailScanner.info
MailScanner thanks transtec Computers for their support

--
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the sender and delete the message from your mailbox.

This footnote also confirms that this email message has been swept by
MailScanner (www.mailscanner.info) for the presence of computer viruses.