SQL Redux

[Julian Field]

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