PLEASE TAKE ME OFF ALL OF YOUR EMAIL LIST. I'm getting information shared by all of you and I should not. THANK YOU
-----Original Message----- From: Kevin Shortt [mailto:[EMAIL PROTECTED] Sent: Friday, June 04, 2004 10:51 AM To: [EMAIL PROTECTED] Subject: insertion problem... Note: cross posted on [EMAIL PROTECTED] Hi everyone, I am having some trouble with an insertion. Specifically an execution call. I have installed MailScanner (a perl Email Defense software). MailScanner is working, but I am setting up Real Time SQL Logging using a customizable feature of this package. I have "SQLRealTimeLogging.pm" placed into a CustomFunctions directory that gets parse and "require"d. Yet I am unable to insert into mysql with my custom package. See my .pm and syslog entry at the bottom of this message. I have marked "IT DIES HERE" where the death occurs. This is what I have. Also a trace is included too. 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 have used a test script in that directory to insert successfully, but not through my custom.pm. (as written below) I have tried everything that I know of. I've changed the database it only include one table and one field. I changed from using execute to using do. (inefficient, I know..but I am desparate. :) It just will not insert into that database. I have scanned this mailling list and MailScanner's lists. If anyone has had this problem before, please let me know. Thanks.. -k ----begin trace level 9 for one transaction DBI::db=HASH(0xae84b98) trace level set to 9 in DBI 1.40-ithread (pid 3251) -> prepare for DBD::mysql::db (DBI::db=HASH(0xae61ae4)~0xae84b98 'INSERT INTO maillog_mail (time, msg_id, size, from_user, from_domain, subject, clientip, archives, isspam, ishighspam, sascore, spamreport) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)') thr#9f9c008 dbih_setup_handle(DBI::st=HASH(0xae84d3c)=>DBI::st=HASH(0xae390bc), DBD::mysql::st, ae84d48, Null!) dbih_make_com(DBI::db=HASH(0xae84b98), ae84f48, DBD::mysql::st, 208, 0) thr#9f9c008 dbih_setup_attrib(DBI::st=HASH(0xae390bc), Err, DBI::db=HASH(0xae84b98)) SCALAR(0xa43f798) (already defined) dbih_setup_attrib(DBI::st=HASH(0xae390bc), State, DBI::db=HASH(0xae84b98)) SCALAR(0xa43f7f8) (already defined) dbih_setup_attrib(DBI::st=HASH(0xae390bc), Errstr, DBI::db=HASH(0xae84b98)) SCALAR(0xa43f7c8) (already defined) dbih_setup_attrib(DBI::st=HASH(0xae390bc), TraceLevel, DBI::db=HASH(0xae84b98)) 9 (already defined) dbih_setup_attrib(DBI::st=HASH(0xae390bc), FetchHashKeyName, DBI::db=HASH(0xae84b98)) 'NAME' (already defined) dbih_setup_attrib(DBI::st=HASH(0xae390bc), HandleError, DBI::db=HASH(0xae84b98)) undef (not defined) Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0xae84d3c) at /usr/lib/MailScanner/MailScanner/CustomFunctions/SQLRealTimeLogging.pm line 48 via /usr/lib/MailScanner/MailScanner/Config.pm line 754 -> prepare for DBD::mysql::db (DBI::db=HASH(0xae61ae4)~0xae84b98 'INSERT INTO maillog_report (msg_id, filename, filereport) VALUES (?,?,?)') thr#9f9c008 dbih_setup_handle(DBI::st=HASH(0xae84dcc)=>DBI::st=HASH(0xae390d4), DBD::mysql::st, ae84dd8, Null!) dbih_make_com(DBI::db=HASH(0xae84b98), ae84f48, DBD::mysql::st, 208, 0) thr#9f9c008 dbih_setup_attrib(DBI::st=HASH(0xae390d4), Err, DBI::db=HASH(0xae84b98)) SCALAR(0xa43f798) (already defined) dbih_setup_attrib(DBI::st=HASH(0xae390d4), State, DBI::db=HASH(0xae84b98)) SCALAR(0xa43f7f8) (already defined) dbih_setup_attrib(DBI::st=HASH(0xae390d4), Errstr, DBI::db=HASH(0xae84b98)) SCALAR(0xa43f7c8) (already defined) dbih_setup_attrib(DBI::st=HASH(0xae390d4), TraceLevel, DBI::db=HASH(0xae84b98)) 9 (already defined) dbih_setup_attrib(DBI::st=HASH(0xae390d4), FetchHashKeyName, DBI::db=HASH(0xae84b98)) 'NAME' (already defined) dbih_setup_attrib(DBI::st=HASH(0xae390d4), HandleError, DBI::db=HASH(0xae84b98)) undef (not defined) Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0xae84dcc) at /usr/lib/MailScanner/MailScanner/CustomFunctions/SQLRealTimeLogging.pm line 50 via /usr/lib/MailScanner/MailScanner/Config.pm line 754 -> prepare for DBD::mysql::db (DBI::db=HASH(0xae61ae4)~0xae84b98 'INSERT INTO maillog_recipient (msg_id, to_user, to_domain) VALUES (?,?,?)') thr#9f9c008 dbih_setup_handle(DBI::st=HASH(0xae89998)=>DBI::st=HASH(0xae84dfc), DBD::mysql::st, ae899a4, Null!) dbih_make_com(DBI::db=HASH(0xae84b98), ae84f48, DBD::mysql::st, 208, 0) thr#9f9c008 dbih_setup_attrib(DBI::st=HASH(0xae84dfc), Err, DBI::db=HASH(0xae84b98)) SCALAR(0xa43f798) (already defined) dbih_setup_attrib(DBI::st=HASH(0xae84dfc), State, DBI::db=HASH(0xae84b98)) SCALAR(0xa43f7f8) (already defined) dbih_setup_attrib(DBI::st=HASH(0xae84dfc), Errstr, DBI::db=HASH(0xae84b98)) SCALAR(0xa43f7c8) (already defined) dbih_setup_attrib(DBI::st=HASH(0xae84dfc), TraceLevel, DBI::db=HASH(0xae84b98)) 9 (already defined) dbih_setup_attrib(DBI::st=HASH(0xae84dfc), FetchHashKeyName, DBI::db=HASH(0xae84b98)) 'NAME' (already defined) dbih_setup_attrib(DBI::st=HASH(0xae84dfc), HandleError, DBI::db=HASH(0xae84b98)) undef (not defined) Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0xae89998) at /usr/lib/MailScanner/MailScanner/CustomFunctions/SQLRealTimeLogging.pm line 52 via /usr/lib/MailScanner/MailScanner/Config.pm line 754 ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390bc)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0xae84b98)~INNER) thr#9f9c008 &imp_dbh->mysql: ae84f9c ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae84dfc)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390d4)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390bc)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0xae84b98)~INNER) thr#9f9c008 &imp_dbh->mysql: ae84f9c ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae84dfc)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390d4)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390bc)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0xae84b98)~INNER) thr#9f9c008 &imp_dbh->mysql: ae84f9c ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae84dfc)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390d4)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390bc)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0xae84b98)~INNER) thr#9f9c008 &imp_dbh->mysql: ae84f9c ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae84dfc)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390d4)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390bc)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0xae84b98)~INNER) thr#9f9c008 &imp_dbh->mysql: ae84f9c ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae84dfc)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390d4)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction -> execute for DBD::mysql::st (DBI::st=HASH(0xae84d3c)~0xae390bc '2004-06-04 09:10:52' 'i54DAm3i003241' 1247 'jspanbauer' 'nfschools.net' 'Golf' '168.169.15.29' '' 0 0 0.159 'not spam, SpamAssassin (score=0.159, required 6, BAYES_44 -0.00, NO_REAL_NAME 0.16)') thr#9f9c008 -> dbd_st_execute for 0ae84db4 Binding parameters: INSERT INTO maillog_mail (time, msg_id, size, from_user, from_domain, subject, clientip, archives, isspam, ishighspam, sascore, spamreport) VALUES ('2004-06-04 09:10:52','i54DAm3i003241','1247','jspanbauer','nfschools.net','Golf','168.169.1 5.29','','0','0','0.159','not spam, SpamAssassin (score=0.159, required 6, BAYES_44 -0.00, NO_REAL_NAME 0.16)') error 0 recorded: <- dbd_st_execute -2 rows <- execute= undef at /usr/lib/MailScanner/MailScanner/CustomFunctions/SQLRealTimeLogging.pm line 105 via /usr/lib/MailScanner/MailScanner/Config.pm line 119 -> $DBI::errstr (&) FETCH from lasth=HASH <- $DBI::errstr= '' -> $DBI::errstr (&) FETCH from lasth=HASH <- $DBI::errstr= '' ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390bc)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0xae84b98)~INNER) thr#9f9c008 &imp_dbh->mysql: ae84f9c ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae84dfc)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0xae390d4)~INNER) thr#9f9c008 ! <- DESTROY= undef during global destruction ---- end trace ---- 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(9,"/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:
