You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@spamassassin.apache.org by Dale Morin <da...@MustangInternetServices.com> on 2005/11/20 20:23:00 UTC
sql prefs - results and debugging patch for SQL.pm included - suggestions
please
Hello,
OS: RHES 3.0
SA 3.1.0
MySQL 4.1.12
spamd start (for testing): spamd -q -D -x -p 6666
in another ssh session: echo -e "From: user\nTo:user\nSubject: Test\n\n" | spamc -u 'dale' -p 6666
This way I can test sql userprefs without affecting the running spamd listening on 783.
Here is the output from the spamc side:
From: user
To:user
Subject: Test
Note that there is no indication of any scanning taking place.
Here is the output from the spamd side:
[29347] info: spamd: connection from frodo [127.0.0.1] at port 53771
[29347] dbg: config: Conf::SQL: database connection established
[29347] dbg: config: Conf::SQL: using deliverd query
[29347] dbg: config: Conf::SQL: preparing SQL: select preference, value from userpref where username = 'dale' or
username = '$GLOBAL' order by username asc
[29347] dbg: config: Conf::SQL: executing SQL: select preference, value from userpref where username = 'dale' or
username = '$GLOBAL' order by username asc
[29347] dbg: config: Conf::SQL: retrieving prefs for dale from SQL server
[29347] dbg: config: Conf::SQL: retrieved 12 rows from SQL server
[29347] dbg: config: Conf::SQL: fetched row: score SPF_FAIL 5
[29347] dbg: config: Conf::SQL: fetched row: score UNPARSEABLE_RELAY 3
[29347] dbg: config: Conf::SQL: fetched row: score RCVD_ILLEGAL_IP 4
[29347] dbg: config: Conf::SQL: fetched row: score SPF_HELO_FAIL 5
[29347] dbg: config: Conf::SQL: fetched row: required_score 5
[29347] dbg: config: Conf::SQL: fetched row: rewrite_header Subject *** Potential SPAM (_SCORE_) ***
[29347] dbg: config: Conf::SQL: fetched row: whitelist_from *@aa.com
[29347] dbg: config: Conf::SQL: fetched row: whitelist_from sr@xxxxxxxxxx.com
[29347] dbg: config: Conf::SQL: fetched row: whitelist_from geoff@xxxxxxxxxx.com
[29347] dbg: config: Conf::SQL: fetched row: required_score 6
[29347] dbg: config: Conf::SQL: fetched row: whitelist_from jr@xxxxxxxxxx.com
[29347] dbg: config: Conf::SQL: fetched row: report_safe 1
[29335] dbg: prefork: new lowest idle kid: 29478
[29335] info: spamd: handled cleanup of child pid 29347 due to SIGCHLD
[29335] dbg: prefork: child closed connection
[29335] info: prefork: child states: I
[29616] dbg: prefork: sysread(8) not ready, wait max 300 secs
[29335] info: spamd: server successfully spawned child process, pid 29616
[29335] dbg: prefork: child 29616: entering state 0
[29335] dbg: prefork: new lowest idle kid: 29478
[29335] dbg: prefork: child 29616: entering state 1
[29335] dbg: prefork: new lowest idle kid: 29478
[29335] dbg: prefork: child reports idle
[29335] info: prefork: child states: II
To get this debugging output, I patched SQL.pm as follows:
--- SQL.pm.org 2005-11-20 12:39:22.000000000 -0600
+++ SQL.pm 2005-11-20 12:45:06.000000000 -0600
@@ -125,8 +125,10 @@
my $dbh = DBI->connect($dsn, $dbuser, $dbpass, {'PrintError' => 0});
if ($dbh) {
+ dbg("config: Conf::SQL: database connection established");
my $sql;
if (defined($custom_query)) {
+ dbg("config: Conf::SQL: custom query defined");
$sql = $custom_query;
my $quoted_username = $dbh->quote($username);
my ($mailbox, $domain) = split('@', $username);
@@ -139,19 +141,24 @@
$sql =~ s/_DOMAIN_/$quoted_domain/g;
}
else {
+ dbg("config: Conf::SQL: using deliverd query");
$sql = "select $f_preference, $f_value from $f_table where ".
"$f_username = ".$dbh->quote($username).
- " or $f_username = '\@GLOBAL' order by $f_username asc";
+ " or $f_username = '\$GLOBAL' order by $f_username asc";
}
- dbg("config: Conf::SQL: executing SQL: $sql");
+ dbg("config: Conf::SQL: preparing SQL: $sql");
my $sth = $dbh->prepare($sql);
if ($sth) {
+ dbg("config: Conf::SQL: executing SQL: $sql");
my $rv = $sth->execute();
if ($rv) {
- dbg("config: retrieving prefs for $username from SQL server");
+ dbg("config: Conf::SQL: retrieving prefs for $username from SQL server");
+ my $numrows = $sth->rows;
+ dbg("config: Conf::SQL: retrieved $numrows rows from SQL server");
my @row;
my $text = '';
while (@row = $sth->fetchrow_array()) {
+ dbg("config: Conf::SQL: fetched row: $row[0] $row[1]");
$text .= (defined($row[0]) ? $row[0] : '') . "\t" .
(defined($row[1]) ? $row[1] : '') . "\n";
}
Thanks in advance for any suggestions.
--
Dale Morin, Mustang Internet Services, Inc.
"Support Without Compromise"
main office: 847.541.2811
direct line: 815.496.9853
email: dale@MustangInternetServices.com