SQL Redux

[Steve Freegard]

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)??

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??

Cheers,
Steve.

-----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.