Notromda has asked for the wisdom of the Perl Monks concerning the following question:

I posted a question earlier, but it would probably be easier if my fellow monks could see the code. This program is monitoring syslog messages - a pipe from syslog-ng sends the data into STDIN. This is never supposed to reach EOF or anything... the syslog process will kill and restart this program as needed (during rotate logs).

It appears to die on a line that contains a single quote:

Sep 9 05:09:53 bouncer postfix/smtpd[6709]: reject: RCPT from 200-158[]: 554 <>: + Recipient address rejected: Service unavailable; [] bl +ocked by See for detai +ls. ]; from=<jh.o'> to=<>

But I use placeholders and such in my sql code.

Can anyone help figure out what is happening?

#!/usr/bin/perl use FileHandle; use strict; use Data::Dumper; use DBI; my $dbh = DBI->connect("DBI:mysql:database=postfix;host=xxxxx", "xxxx", "xxxx") or die; my $debug = 1; use vars qw($line); ################################################################### # Main Script # First, lets collect the data, then we'll process it # open LOGFILE, ">>/var/log/dgmtest"; autoflush LOGFILE 1; while (1) { $_ = <>; $line = $_; if (/postfix\S+ reject: RCPT from (\S+) (530|554|450) (\S+): (.*) +from=<(.*?)> to=<(.*?)>/) { my ($from, $to, $message, $o, $t, $th) = (lc($5),lc($6),$4, $1 +, $2, $3); #print "Blocked message from: $from to: $to because: $message\ +n"; $message = ($message =~ / ? "ORDB" : $message); $message = ($message =~ / ? "SPAMCOP" : $messa +ge); $message = ($message =~ / ? "DUNDNSRBL" : $mess +age); $message = ($message =~ / ? "SPAMDNSRBL" : $me +ssage); $message = ($message =~ /Cannot find your hostname/ ? "RDNS" : + $message); $message = ($message =~ / ? "OSFT" : $mes +sage); $message = ($message =~ /Relay access denied/ ? "RELAYDENIED" +: $message); $message = ($message =~ /Recipient address rejected: Access de +nied/ ? "BLACKLIST" : $message); $message = ($message =~ / ? "CHINA" : $mes +sage); $message = ($message =~ / ? "KOREA" : $mes +sage); $message = ($message =~ / ? "ARGENTINA +" : $message); $message = ($message =~ / ? "BRAZIL" : $m +essage); $message = ($message =~ / ? "EASYNET" : +$message); $message = ($message =~ / ? "BLITZED" : $messa +ge); $message = ($message =~ / ? "TRUSTIC" : $message); # some ID-10T is trying to spam through us as if we were an op +en relay. Let's not count them. if ($message =~ /RELAYDENIED/ and $from =~ /(blvelasq|douglasl +|meinsen|ecr)/) { print LOGFILE "dropping relay from $o, $from to $to\n"; + next; } my $result = Check($to, $from); $result ? Update($result, 1, $message) : Insert($to,$from,$message,1); UpdateStats($to, $message); print LOGFILE $_; } elsif (/bouncer postfix\S+ reject: /) { print LOGFILE $_; } } END { print "dying"; print LOGFILE scalar localtime() ." ending. Last Line: + $line\n"; close LOGFILE; } sub Check($$) { my ($rcpt, $from) = @_; my $id; my $sth = $dbh->prepare("SELECT id from per_user_errors WHERE rcpt +=? AND sender=?") or die $dbh->errstr; #$rcpt = $dbh->quote($rcpt); #$from = $dbh->quote($from); $sth->execute($rcpt, $from); ($id) = $sth->fetchrow_array(); $sth->finish(); return $id; } sub Insert ($$$$) { my ($rcpt, $from, $why, $count) = @_; if ($debug) { print "INSERT INTO per_user_errors VALUES ('','$rcpt','$from', +'$why','$count',CURRENT_DATE)\n"; return; } my $sth = $dbh->prepare("INSERT INTO per_user_errors VALUES ('','$ +rcpt','$from','$why','$count',CURRENT_DATE)"); $sth->execute() or die $dbh->errstr; $sth->finish(); } sub Update ($$$) { my ($id, $count, $why) = @_; if ($debug) { print "UPDATE per_user_errors SET tries=tries+$count, tstamp=C +URRENT_DATE, method='$why' WHERE id=$id\n"; return; } my $sth = $dbh->prepare("UPDATE per_user_errors SET tries=tries+$c +ount, tstamp=CURRENT_DATE WHERE id=$id"); #print ("UPDATE per_user_errors SET tries=tries+$count, tstamp=CUR +RENT_DATE WHERE id=$id"); $sth->execute() or die $dbh->errstr; $sth->finish(); } sub UpdateStats($$) { my ($address, $type) = @_; return if $type !~ /RDNS|SPAMCOP|OSFT|ORDB|BLACKLIST|DUNSDNSRBL|SP +AMDNSRBL/; #Check for entry my $sth = $dbh->prepare("SELECT id FROM control_stats WHERE addres +s=? AND type=?"); my $numrows = $sth->execute($address, $type); $sth->finish(); #insert or update if ($numrows == 0) { #INSERT $sth = $dbh->prepare("INSERT INTO control_stats VALUES ('',?,? +,1)"); } else { #UPDATE $sth = $dbh->prepare("UPDATE control_stats set count=count+1 W +HERE address=? AND type=?"); } $sth->execute($address,$type); $sth->finish(); #update global stats $sth = $dbh->prepare("UPDATE control_stats set count=count+1 WHERE + address='system' AND type=? OR type='total'"); $sth->execute($type); $sth->finish(); } #sample error message #Jul 3 11:19:00 bouncer postfix/smtpd[14071]: reject: RCPT from unkno +wn[]: 530 <>: Recipient address reject +ed: Cannot find your hostname, []. Ask your system mana +ger to fix your reverse domain name registration. If you are sending + spam, go away. ; from=< +m> to=<> #Jan 15 19:52:29 staypuft postfix/smtpd[8530]: reject: RCPT from pp2.d[]: 554 <>: Recipient addres +s rejected: Service unavailable; [] blocked by relays.osir See for details. ]; from=<rrrr> to=<>

Replies are listed 'Best First'.
Re: Why does this script die?
by dws (Chancellor) on Sep 11, 2003 at 16:19 UTC
    It appears to die on a line that contains a single quote:

    You have a variable that holds a string that contains an embedded quote. You then interpolate that variable into another string, which holds the beginning of an SQL query. You end up with extra quotes in the SQL, rendering it syntactically invalid. The execute() fails, which you aren't detected because you're not checking. Since it returns undef, the code is going to blow a few lines later.

    The solution to this is really simple. It's already in your code. Use query parameters, and pass the values you want to bind to the parameters to execute(), which will automagically escape any embedded single quotes (or other characters that need escaping). Check() does this. Why doesn't Insert()?

    Add some error checking while you're at it.

      Doh! I always use placeholders! Why didn't that have place holders? I'm at a loss for words... that's just not my usual programming style.

      Don't I feel stupid. Thanks for pointing that out. Funny how you can stare at something and miss the obvious.