SV: mysql insert issue with FC2

Fri Jun 4 09:08:13 IST 2004

Hi Kevin,

I havn't used the SQLRealTimeLoggin.pm before, but I have just been setting up MailWatch (http://mailwatch.sourceforge.net) which probably is doing about the same thing (i.e. logging to mysql + generating statistics via apache/php). I had a heck of a time getting the database connection to work until I found out that there was some compatibility issues with DBD::mysql 2.9003-4... I think you are banging your head against the same problem here. Downgrade to DBD::mysql 2.1028 and I think you will be back on the track again...

Let me know if this solves your problem...

brgds,
/Stefan

-----Ursprungligt meddelande-----
Från: MailScanner mailing list [mailto:MAILSCANNER at JISCMAIL.AC.UK] För Shortt, Kevin
Skickat: den 3 juni 2004 22:39
Till: MAILSCANNER at JISCMAIL.AC.UK
Ämne: mysql insert issue with FC2

Hi everyone,

MailScanner is working, but I am setting up Real Time SQL Logging using the "Always Looked Up Last = &SQLRealTimeLogging".
I have "SQLRealTimeLogging.pm" placed into CustomFunctions directory.
I am unable to insert into mysql. See my .pm and log entry at the bottom of this message. I have marked "IT DIES HERE" where the death occurs. This is what I have.

All packages are installed as RPM's.

OS:       Fedora Core 2
MS:       mailscanner-4.30.3-2
PERL:   perl-DBI-1.40-4
        perl-DBD-MySQL-2.9003-4
DB:     mysql-3.23.58-9
        mysql-server-3.23.58-9

I can insert with a test script, but not through my custom.pm.
If I create a test.pl script that inserts and place it into CustomFunctions, then that script will work.
I have tried everything. I've changed the database it only include one table and one field. I changed from using execute to using do. It just will not insert into that database.
I have scanned this mailling list and DBI's lists.

If anyone has had this problem before, please let me know.

Thanks..

-k
---- my custom.pm (SQLRealTimeLogging.pm) - this is placed into CustomFunctions directory.
package MailScanner::CustomConfig;

use DBI;
use strict 'vars';
use strict 'refs';
no  strict 'subs'; # Allow bare words for parameter %'s

my $database = "mailscanner";
my $dbuser = "mailscanner";
my $dbpass = "!mailscanner32!";
my $mysqlsocket = "/db/mysql/mysql.sock";

my $dbhandle;
my $sthMail;
my $sthReport;
my $sthRecipient;

sub InitSQLRealTimeLogging {

  MailScanner::Log::InfoLog("Initialising SQL Real Time Logging ");

  $dbhandle =
DBI->connect("DBI:mysql:$database:localhost;mysql_socket=$mysqlsocket;",
"$dbuser", $dbpass,
              {'RaiseError' => 1, 'PrintError' => 1}) or MailScanner::Log::DieLog("Cannot connect to the database: %s", $DBI::errstr);
  # $dbhandle->trace(1,"/tmp/mysql.trace2");

  $sthMail = $dbhandle->prepare("INSERT INTO maillog_mail (time, msg_id, size, from_user, from_domain, subject, clientip, archives, isspam, ishighspam, sascore, spamreport) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)") or die("
Prepare did not work: %s", $DBI::errstr);

  $sthReport = $dbhandle->prepare("INSERT INTO maillog_report (msg_id, filename, filereport) VALUES (?,?,?)");

  $sthRecipient = $dbhandle->prepare("INSERT INTO maillog_recipient (msg_id, to_user, to_domain) VALUES (?,?,?)"); }

sub SQLRealTimeLogging {

  use DBI;
  my($message) = @_;

  my $id = $message->{id};
  my $size = $message->{size};
  my $from = $message->{from};
  my ($from_user, $from_domain);

  # split the from address into user and domain bits.
  # This may be unnecessary for you; we use it to more easily determine
  # inbound vs outbound email in a multi-domain environment.
  # HINT: refine queries using SQL 'join' with a table containing local
  # domains.

  ($from_user, $from_domain) = split /\@/, $from;

  my @to   = @{$message->{to}};
  my $subject = $message->{subject};
  my $clientip = $message->{clientip};
  my $archives = join(',', @{$message->{archiveplaces}});
  my $isspam = $message->{isspam};
  my $ishighspam = $message->{ishigh};
  my $sascore = $message->{sascore};
  my $spamreport = $message->{spamreport};

  # Get rid of control chars and tidy-up SpamAssassin report
  $spamreport =~ s/\n/ /g;
  $spamreport =~ s/\t//g;

  # Get timestamp, and format it so it is suitable to use with MySQL
  my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
  my($timestamp) = sprintf("%d-%02d-%02d %02d:%02d:%02d",$year+1900,$mon+1,$mday,$hour,$min,$sec);

  # maillog_mail insert
  my @fields=($timestamp, $id, $size, $from_user, $from_domain,
              $subject, $clientip, $archives, $isspam, $ishighspam,
              $sascore, $spamreport);

  map { s/\'/\\'/g } @fields;
  map { ($_ eq '')?'NULL':"$_" } @fields;

  # Debug statements added for verifying date is populating fields array.
  MailScanner::Log::InfoLog("=========================\nfields array - \n0:[$fields[0]] 1:[$fields[1]] \n2:[$fields[2]] 3:[$fields[3]] \n");
  MailScanner::Log::InfoLog("4:[$fields[4]] 5:[$fields[5]] \n6:[$fields[6]] 7:[$fields[7]] \n");
  MailScanner::Log::InfoLog("8:[$fields[8]] 9:[$fields[9]] \n10:[$fields[10]] 11:[$fields[11]] \n====================\n");

  # Insert @fields into a database table
  $sthMail->execute($timestamp, $id, $size, $from_user, $from_domain, $subject, $clientip, $archives, $isspam, $ishighspam, $sascore,
"$spamreport") or MailScanner::Log::DieLog("Cannot insert into maillog_mail:
%s", $DBI::errstr);
  # IT DIES HERE on the above execute statement.

  my($file, $text);
  while(($file, $text) = each %{$message->{allreports}}) {
    $file = "the entire message" if $file eq "";
    # Use the sanitised filename to avoid problems caused by people forcing
    # logging of attachment filenames which contain nasty SQL instructions.
    $file = $message->{file2safefile}{$file} or $file;
    $text =~ s/\n/ /;  # Make sure text report only contains 1 line
    $text =~ s/\t/ /; # and no tab characters

    my @fields = ($id, $file, $text);
    map { s/\'/\\'/g } @fields;

    $sthReport->execute($fields[0],$fields[1],$fields[2]) or MailScanner::Log::DieLog("Cannot insert row into maillog_report:
[$DBI::errstr]");
  }

  for (@to) {
    # again, split the recipient's email into user and domain halves first.
    # see comment above about splitting the email like this.

    my ($to_user, $to_domain);
    ($to_user, $to_domain) = split /\@/, $_;
    my @fields = ($id, $to_user, $to_domain);
    map { s/\'/\\'/g } @fields;
    $sthRecipient->execute($fields[0],$fields[1],$fields[2]) or MailScanner::Log::DieLog("Cannot insert row into maillog_recipient: [%s]", $DBI::errstr);
  }

}

sub EndSQLRealTimeLogging {
  MailScanner::Log::InfoLog("Ending SQL Real-Time Logging");
  # Close database connection
  $dbhandle->disconnect();
}
1;

---- end custom.pm

----one entry of one message
Jun  3 16:28:03 hostname MailScanner[12208]: MailScanner E-Mail Virus Scanner version 4.30.3 starting...
Jun  3 16:28:03 hostname MailScanner[12208]: Config: calling custom init function SQLRealTimeLogging Jun  3 16:28:03 hostname MailScanner[12208]: Initialising SQL Real Time Logging Jun  3 16:28:03 hostname MailScanner[12208]: Config: calling custom init function MultipleQueueDir Jun  3 16:28:03 hostname MailScanner[12208]: Using locktype = flock Jun  3 16:28:04 hostname MailScanner[12208]: New Batch: Forwarding 1 unscanned messages, 6679 bytes Jun  3 16:28:04 hostname MailScanner[12208]: MCP Checks completed at 6679 bytes per second Jun  3 16:28:04 hostname MailScanner[12208]: Spam Checks: Found 1 spam messages Jun  3 16:28:04 hostname MailScanner[12208]: Spam Checks completed at 6679 bytes per second Jun  3 16:28:04 hostname MailScanner[12208]: Unscanned: Delivered 1 messages

Jun  3 16:28:04 hostname MailScanner[12208]: Virus and Content Scanning:
Starting
Jun  3 16:28:04 hostname MailScanner[12208]: Virus Scanning completed at
6679 bytes per second
Jun  3 16:28:04 hostname MailScanner[12208]: Virus Processing completed at
6679 bytes per second
Jun  3 16:28:04 hostname MailScanner[12208]: Disinfection completed at 6679 bytes per second Jun  3 16:28:04 hostname MailScanner[12208]: Batch completed at 6679 bytes per second (6679 / 0) Jun  3 16:28:04 hostname MailScanner[12208]: ========================= Jun  3 16:28:04 hostname MailScanner[12208]: fields array - Jun  3 16:28:04 hostname MailScanner[12208]: 0:[2004-06-03 16:28:04] 1:[i53KRoO7012126] Jun  3 16:28:04 hostname MailScanner[12208]: 2:[6679] 3:[fakeuser] Jun  3 16:28:04 hostname MailScanner[12208]: 4:[fakedomain.com] 5:[Don, Extend Your Auto Warranty, Extend Your Peace of Mind.] Jun  3 16:28:04 hostname MailScanner[12208]: 6:[192.168.0.1] 7:[] Jun  3 16:28:04 hostname MailScanner[12208]: 8:[1] 9:[0] Jun  3 16:28:04 hostname MailScanner[12208]: 10:[8.528] 11:[spam, SBL+XBL, SpamAssassin (score=8.528, required 6, BAYES_90 2.10, BUY_DIRECT 1.82, HTML_50_60 0.10, HTML_MESSAGE 0.10, HTML_WEB_BUGS 0.34, NO_OBLIGATION 1.46, RCVD_IN_BL_SPAMCOP_NET 1.50, RCVD_IN_SBL 1.11)] Jun  3 16:28:04 hostname MailScanner[12208]: ==================== Jun  3 16:28:04 hostname MailScanner[12208]: Cannot insert into
maillog_mail:

-------------------------- MailScanner list ----------------------
To leave, send    leave mailscanner    to jiscmail at jiscmail.ac.uk
Before posting, please see the Most Asked Questions at
http://www.mailscanner.biz/maq/     and the archives at
http://www.jiscmail.ac.uk/lists/mailscanner.html

-------------------------- MailScanner list ----------------------
To leave, send    leave mailscanner    to jiscmail at jiscmail.ac.uk
Before posting, please see the Most Asked Questions at
http://www.mailscanner.biz/maq/     and the archives at
http://www.jiscmail.ac.uk/lists/mailscanner.html