Re: [log4perl-devel] implementing a DBI appender, RFC

[Kevin G. <ke...@go...>]

Erik W. Selberg wrote:
> (yes, I'm back from the dead :)
> 
> I'll toss in one... it'd be nice if at least the password (if not the 
> username) could be obtained from environment variables. What we do 
> around here is we have a reasonably secure script that sets certain 
> environment variables and give permission to use said script via sudo. 
> This lets the DBAs keep their passwords under control.

Erik, wazzup?

Actually, we do almost the same thing here, but with the addition of a 
module that maps the environment variable switch to dsn/username/pwd 
combos, so I approached it like this,

log4j.appender.DBAppndr             = xx::DBLogger
log4j.appender.DBAppndr.sqlStatement = \
    insert into log4perltest ...etc.\

=============================================
package xx::DBLogger;
use base qw(Log::Log4perl::Appender::DBI);
use xx::DB;

#subclassing the DBI appender to override _init()
sub _init {
     my $self = shift;
     my %params = @_;

	#lookup DSN, username, pwd re: ENV
     $self->{dbh} = xx::DB->new->getConnection
           or die $DBI::errstr;
     $self->{_mine} = 1;
     $self->{sth} = $self->create_statement($params{sqlStatement});
}
========================================================

What do you think of that?  Any other ideas?


> Now, granted, I'm from the school whereby username / password security 
> is just dumb for applications because (a) you gotta bake 'em in 
> somewhere, which means when that hacker roots your box, they can find 
> it,

No doubt, can you say "cat /proc/$$/environ"?




> and (b) you can never change both at the same time and therefore you 
> end up just screwing yourself, but that's just me. :)
> 
> -e
> 
> Kevin Goess wrote:
> 
>> I needed to add logging to a database, here's a proposed solution.
>>
>> Log4j has a JDBCAppender with the warning: "This version of 
>> JDBCAppender is very likely to be completely replaced in the future."  
>> The API is kind of different, you call append() until the buffer is 
>> full, at which point the an sth is created and executed on the 
>> buffer.  So the API isn't interchangeable with other appenders.
>>
>> So I've implemented a DBI appender that's completely controllable from 
>> the config file, looking something like this:
>>
>>     log4j.category = WARN, DBAppndr
>>     log4j.appender.DBAppndr             = Log::Log4perl::Appender::DBI
>>     log4j.appender.DBAppndr.datasource = DBI:CSV:f_dir=t/tmp
>>     log4j.appender.DBAppndr.username  = bobjones
>>     log4j.appender.DBAppndr.password = 12345
>>     log4j.appender.DBAppndr.sqlStatement = \
>>        insert into log4perltest \
>>        (level, message, shortmessage) \
>>        values (?,?,?)
>>     log4j.appender.DBAppndr.params.1 = %p
>>     log4j.appender.DBAppndr.params.2 = %m
>>     log4j.appender.DBAppndr.params.3 = %5.5m
>>     ...
>>
>> The code is based on Log::Dispatch::DBI by Tatsuhiko Miyagawa.  His 
>> module gets behavior changes via subclassing, but by the time I was 
>> done I had overridden everything but DESTROY, so I just dropped the 
>> dependency on his module.
>>
>> Below is the only change to existing code, the module and a unit test 
>> are attached. Any comments or suggestions?
>>
>>
>> ============================================================
>> --- lib/Log/Log4perl/Appender.pm        18 Nov 2002 20:03:39 
>> -0000      1.17
>> +++ lib/Log/Log4perl/Appender.pm        9 Dec 2002 19:51:31 -0000
>> @@ -120,7 +120,10 @@
>>                                              $level,
>>                                              3 + 
>> $Log::Log4perl::caller_depth,
>>                                              );
>> -    $self->{appender}->log(%$p);
>> +    $self->{appender}->log(%$p,
>> +                            #these are used by our Appender::DBI
>> +                            log4p_category => $category,
>> +                            log4p_level  => $level,);
>>
>>      return 1;
>>  }
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>> package Log::Log4perl::Appender::DBI;
>>
>> use Carp;
>>
>> use strict;
>> use Log::Log4perl::Layout::PatternLayout;
>>
>> use base qw(Log::Dispatch::Output);
>>
>>
>> sub new {
>>    my($proto, %params) = @_;
>>    my $class = ref $proto || $proto;
>>
>>    my $self = bless {}, $class;
>>
>>    $self->_basic_init(%params);
>>    $self->_init(%params);
>>
>>    #e.g.
>>    #log4j.appender.DBAppndr.params.1 = %p       
>> #log4j.appender.DBAppndr.params.2 = %5.5m
>>    foreach my $pnum (keys %{$params{params}}){
>>        $self->{bind_value_layouts}{$pnum} =                
>> Log::Log4perl::Layout::PatternLayout->new(
>>                    {ConversionPattern => {value  => 
>> $params{params}->{$pnum}}});
>>
>>    }
>>
>>    return $self;
>> }
>>
>>
>> sub _init {
>>    my $self = shift;
>>    my %params = @_;
>>
>>    # set parameters
>>    if ($params{dbh}) {
>>        $self->{dbh} = $params{dbh};
>>    } else {
>>        $self->{dbh} = DBI->connect(@params{qw(datasource username 
>> password)})
>>            or die $DBI::errstr;
>>        $self->{_mine} = 1;
>>    }
>>    $self->{sth} = $self->create_statement($params{sqlStatement});
>> }
>>
>> sub create_statement {
>>    my ($self, $stmt) = @_;
>>
>>    $stmt || die "sqlStatement not set in Log4perl::Appender::DBI";
>>
>>    return $self->{dbh}->prepare($stmt);
>> }
>>
>>
>> sub log_message {
>>    my $self = shift;
>>    my %p = @_;
>>
>>    #params is
>>    #    { name    => \$appender_name,
>>    #      level   => \$Log::Log4perl::Level::L4P_TO_LD{\$level},      
>> #      message => \$message,
>>    #      log4p_category => $category,
>>    #      log4p_level  => $level,);
>>    #    },
>>
>>    my @qmarks = ();
>>
>>    foreach my $pnum (keys %{$self->{bind_value_layouts}}){
>>        my $msg = $self->{bind_value_layouts}{$pnum}->render(
>>                $p{message},
>>                $p{log4p_category},
>>                $p{log4p_level},
>>                5 + $Log::Log4perl::caller_depth,             );
>>
>>        push @qmarks, $msg;
>>    }
>>
>>    $self->{sth}->execute(@qmarks);
>> }
>>
>> sub DESTROY {
>>    my $self = shift;
>>    if ($self->{_mine} && $self->{dbh}) {
>>        $self->{dbh}->disconnect;
>>    }
>> }
>>
>>
>> 1;
>>
>> __END__
>>
>> =head1 NAME
>>
>> Log::Log4perl::Appender::DBI - implements appending to a DB
>>
>> =head1 SYNOPSIS
>>
>>    $dbh->do('CREATE TABLE log4perltest (level char(9), message 
>> char(128));
>>
>>    my $config = <<'EOT';
>>    log4j.category = WARN, DBAppndr
>>    log4j.appender.DBAppndr             = Log::Log4perl::Appender::DBI
>>    log4j.appender.DBAppndr.datasource = DBI:CSV:f_dir=t/tmp
>>    log4j.appender.DBAppndr.username  = bobjones
>>    log4j.appender.DBAppndr.password = 12345
>>    log4j.appender.DBAppndr.sqlStatement = \
>>       insert into log4perltest \
>>       (level, message, shortmessage, category, millis) \
>>       values (?,?,?,?,?)
>>    log4j.appender.DBAppndr.params.1 = %p       
>> log4j.appender.DBAppndr.params.2 = %m
>>    log4j.appender.DBAppndr.params.3 = %5.5m
>>    log4j.appender.DBAppndr.params.4 = %c
>>    log4j.appender.DBAppndr.params.5 = %C
>>           #want the pattern layout so we can avoid the \n       
>> log4j.appender.DBAppndr.layout    = Log::Log4perl::Layout::PatternLayout
>>    log4j.appender.DBAppndr.layout.ConversionPattern    = %m
>>
>> =head1 DESCRIPTION
>>
>> This is a specialized Log::Dispatch object customized to work with
>> log4perl and its abilities.
>>
>> The code is based on Log::Dispatch::DBI by Tatsuhiko Miyagawa.  I was
>> going to subclass it, but after I was done overriding, there
>> wasn't anything left in the superclass!
>>
>> This is an attempted compromise between what Log::Dispatch::DBI was 
>> doing and what log4j's JDBCAppender does.  Since the JDBCAppender
>> docs say it "is very likely to be completely replaced in the future"
>> and since it looks like it does stuff like prepares the sth each time 
>> around (bad performance?) I didn't feel too bad about straying from 
>> its example.  And the Log::Dispatch::DBI required subclassing
>> to change performance, where log4perl is inspired by the power
>> of the config file!
>>
>> =head1 CHANGING DBH CONNECTIONS (POOLING)
>>
>> If you want to get your dbh from some place in particular, like
>> maybe a pool, subclass and override _init() and/or create_statement(), 
>> q.v.
>>
>> =head1 LIFE OF CONNECTIONS
>>
>> This module creates an sth when it starts and keeps it for the life
>> of the program.  For long-running processes (e.g. mod_perl) this
>> may be a problem, your connections may go stale.
>>
>> It also holds one connection open for every appender, which might
>> be too many.
>>
>>
>> =head1 AUTHOR
>>
>> Kevin Goess <cp...@go...> December, 2002
>>
>> =head1 SEE ALSO
>>
>> L<Log::Dispatch::DBI>
>>
>> L<Log::Log4perl::JavaMap>
>>
>> L<Log::Log4perl::JavaMap::JDBCAppender>.
>>
>> =cut
>>
>>  
>>
> 
> 
> 
> 
> 
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> log4perl-devel mailing list
> log...@li...
> https://lists.sourceforge.net/lists/listinfo/log4perl-devel


-- 
Happy Trails . . .

Kevin M. Goess
(and Anne and Frank)
904 Carmel Ave.
Albany, CA  94706
(510) 525-5217