MailWatch.pm (was RE: SQL Redux)

[Julian Field]

Probably not a bad idea. Current MailWatch traffic level isn't a great
problem. We've always got the official OT list to go to anyway if needed.

At 16:10 31/07/2003, you wrote:
>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.

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