Re: Secure remote syslogging?

[Lars Ellenberg <l.g.e@web.de>]

On Thu, Apr 24, 2003 at 08:52:10PM +0200, Jose Luis Domingo Lopez wrote:

8< syslog-ng --> named pipe --> perl script --> ssh tunnel --> SQL DB

> destination d_logpipe { pipe("/tmp/pipe" owner("someone") template("\(
>   '$HOST', '$ISODATE', '$FACILITY', +'$PRIORITY', '$MESSAGE' \)\n") ); };

you need syslog-ng >= 1.5.3 for the template to work, iirc.
and if you go thus far, why not
template("INSERT INTO logs VALUES ... \( ... \);\n"), and then simply
mysql -h 127.0.0.1 -... -D logs < /tmp/pipe ?
what about forged messages containing queries themselves?
logger -p kern.err "'); DELETE *.* FROM logs;#"  =]
you need to quote the input somehow.

so here my suggestion (despite the fact that you hit plenty stuff when
googling on that matter):
since the content of the other macros is well defined, 
and you use a perl script anyways, why not use
template("$HOST $ISODATE $FACILITY $PRIORITY $MESSAGE\n"), which can
be split on the spaces into its parts, and let perl do the quoting?

and, btw, why not just use the syslog format as is?
works with syslog (old generation), too.
(ok, with -og, and the default format, to preserve facility/priority
 you'd need to setup one pipe for each class you want to distinguish...)

and, if you don't mind, please use DBI/DBD::mysql
(or whatever DBD submodule you like).

old syslog.conf:
*.* |/some/fifo
-ng:
destination d_logpipe { pipe("/some/fifo"); } ...
  with $syslog_ng_template=0 below!
or 
destination d_logpipe { pipe("/some/fifo"); 
  template("$HOST $ISODATE $FACILITY $PRIORITY $MESSAGE\n"); }
  and set $syslog_ng_template=1 below.

use strict;
use DBI ();

my $syslog_ng_template=1;

my $driver="mysql";
my $sqlhost="127.0.0.1"; # possibly (ssh) tunnel to somewhere else
my $port=3306;
my $database="logs";
my $user="joseluis";
my $password="joseluis";
my ($sth,$dbh, $host,$time,$facility,$priority,$msg);

# RaiseError will raise an exception, i.e. die(), on errors.
# ->prepare("... ?,?,? ") and execute(arg1,arg2,...) will do the
# necessary quoting and interpolation for you, and even perform better.

sub try_connect() {
  # you may want to ping the db here
  # or eval { $dbh->disconnect } if $dbh;
  $dbh = DBI->connect("DBI:$driver:database=$database;host=$sqlhost;port=$port;",
                      $user,$password,
                      { RaiseError => 1 , AutoCommit => 1 });
  if ($syslog_ng_template) {
    $sth = $dbh->prepare("INSERT INTO logs.testbox "
                        ."(host,time,facility,priority,message) "
                        ."VALUES (?,?,?,?,?)");
  } else {
    $sth = $dbh->prepare("INSERT INTO logs.testbox "
                        ."(host,time,message) "
                        ."VALUES (?,?,?)");
  }
}

# you might want to move this into the eval below
open ( LOGPIPE, "< /tmp/pipe" ) or die "open LOGPIP: $!\n";

while(1) { # forever
  eval { # catch db errors
    try_connect;
    while ( my $log = <LOGPIPE> ) {
      if ($syslog_ng_template) {
        ($host,$time,$facility,$priority,$msg) =
	  $log =~ /^(\S+) (\S+) (\S+) (\S+) (.*)$/;
        $sth->execute($host,$time,$facility,$priority,$msg);
      } else {
	($time,$host,$msg) = $log =~ /^(\S+ \S+ \S+) (\S+) (.*)$/;
        $sth->execute($host,$time,$msg);
      }
    };
  };
  warn($@) if $@;
}

> Hope it helps.
dito :)

		Lars