trAvis - MANAGER

Edit File: opendmarc-import

#!/usr/bin/perl
#
# Copyright (c) 2012, 2014, 2018, The Trusted Domain Project.  All rights reserved.
#
# Script to import per-message DMARC data.

###
### Setup
###

use strict;
use warnings;

use Switch;

use DBI;
use File::Basename;
use Fcntl qw(:flock);
use Getopt::Long;
use POSIX;
use JSON;

require DBD::mysql;

# general
my $progname      = basename($0);
my $version       = "1.4.1";
my $verbose       = 0;
my $helponly      = 0;
my $showversion   = 0;

# DB parameters
my $def_dbhost    = "localhost";
my $def_dbname    = "opendmarc";
my $def_dbuser    = "opendmarc";
my $def_dbpasswd  = "opendmarc";
my $def_dbport    = "3306";
my $def_interval  = "86400";
my $def_inputfh   = *STDIN;
my $dbhost;
my $dbname;
my $dbuser;
my $dbpasswd;
my $dbport;
my $inputfile;
my $inputfh;

my $dbscheme     = "mysql";

my $dbi_a;
my $dbi_h;
my $dbi_s;
my $dbi_t;

my $lineno;
my $key;
my $value;
my $data;
my @serialized_data;

my $action;
my $adkim;
my $align_dkim;
my $align_spf;
my $aspf;
my $dkim_align;
my @dkim_data;
my $dkim_domain;
my $dkim_selector;
my $dkim_result;
my $arc;
my $arc_policy;
my @arc_policy_data;
my $envdomain;
my $fdomain;
my $ipaddr;
my $jobid;
my $p;
my $pct;
my $pdomain;
my $policy;
my $received;
my $reporter;
my $repuri;
my $sigcount = 0;
my $sp;
my $spf;
my @rua;

###
### NO user-serviceable parts beyond this point
###

sub get_value
{
	my $table;
	my $column;
	my $id;
	my $out;

($table, $column, $id) = @_;

$dbi_t = $dbi_h->prepare("SELECT $column FROM $table WHERE id = ?");
	if (!$dbi_t->execute($id))
	{
		print STDERR "$progname: failed to $column value for ID $id: " . $dbi_h->errstr . "\n";
		return undef;
	}

while ($dbi_a = $dbi_t->fetchrow_arrayref())
	{
		if (defined($dbi_a->[0]))
		{
			$out = $dbi_a->[0];
		}
	}

return $out;
}

sub get_table_id
{
	my $name;
	my $table;
	my $column;
	my $fkey_column;
	my $fkey_value;
	my $out;
	my $query;
	my @query_params;

($name, $table, $column, $fkey_column, $fkey_value) = @_;

if ((!defined($name) || $name eq '-') || !defined($table))
	{
		return undef;
	}

if (!defined($column) || length($column) == 0)
	{
		$column = "name";
	}

@query_params = ($name);
	$query = "SELECT id FROM $table WHERE $column = ?";
	if (defined($fkey_column) && defined($fkey_value))
	{
		$query = $query . " AND $fkey_column = ?";
		push(@query_params, $fkey_value);
	}

$dbi_t = $dbi_h->prepare($query);
	if (!$dbi_t->execute(@query_params))
	{
		print STDERR "$progname: failed to retrieve table ID: " . $dbi_h->errstr . "\n";
		return undef;
	}

undef $out;
	while ($dbi_a = $dbi_t->fetchrow_arrayref())
	{
		if (defined($dbi_a->[0]))
		{
			$out = $dbi_a->[0];
		}
	}

$dbi_t->finish;

if (!defined($out))
	{
		@query_params = ($name);
		$query = "INSERT INTO $table ($column) VALUES(?)";
		if (defined($fkey_column) && defined($fkey_value))
		{
			$query = "INSERT INTO $table ($column, $fkey_column) VALUES(?, ?)";
			push(@query_params, $fkey_value);
		}

$dbi_t = $dbi_h->prepare($query);
		if (!$dbi_t->execute(@query_params))
		{
			print STDERR "$progname: failed to create table ID: " . $dbi_h->errstr . "\n";
			return undef;
		}

$dbi_t = $dbi_h->prepare("SELECT LAST_INSERT_ID()");
		if (!$dbi_t->execute())
		{
			print STDERR "$progname: failed to retrieve created table ID: " . $dbi_h->errstr . "\n";
			return undef;
		}

while ($dbi_a = $dbi_t->fetchrow_arrayref())
		{
			if (defined($dbi_a->[0]))
			{
				$out = $dbi_a->[0];
			}
		}

$dbi_t->finish;

if (!defined($out))
		{
			print STDERR "$progname: failed to retrieve created table ID: " . $dbi_h->errstr . "\n";
			return undef;
		}
	}

return $out;
}

sub update_db
{
	my $rep_id;
	my $from_id;
	my $envfrom_id;
	my $pdomain_id;
	my $ipaddr_id;
	my $msg_id;
	my $sdomain_id;
	my $selector_id;
	my $request_id;

if ($verbose)
	{
		print STDERR "$progname: updating at line $lineno\n";
	}

$rep_id = get_table_id($reporter, "reporters");
	$from_id = get_table_id($fdomain, "domains");
	$envfrom_id = get_table_id($envdomain, "domains");
	$pdomain_id = get_table_id($pdomain, "domains");
	$ipaddr_id = get_table_id($ipaddr, "ipaddr", "addr");
	$request_id = get_table_id($from_id, "requests", "domain");

if (!defined($rep_id) ||
	    !defined($from_id) ||
	    !defined($envfrom_id) ||
	    !defined($pdomain_id) ||
	    !defined($ipaddr_id) ||
	    !defined($request_id))
	{
		return;
	}

$dbi_s = $dbi_h->prepare(q{
	                         INSERT INTO messages(
	                                 date, jobid, reporter, policy, disp, ip, env_domain, from_domain,
	                                 policy_domain, spf, align_spf, align_dkim, sigcount, arc, arc_policy
	                         )
	                         VALUES(
	                                 FROM_UNIXTIME(?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
	                         )
	});

if (!$dbi_s->execute(
			$received, $jobid, $rep_id, $policy, $action, $ipaddr_id, $envfrom_id, $from_id,
			$pdomain_id, $spf, $align_spf, $align_dkim, $sigcount, $arc, $arc_policy
		))
	{
		print STDERR "$progname: failed to insert message: " . $dbi_h->errstr . "\n";
		return;
	}

$dbi_s->finish;

undef $msg_id;
	$dbi_s = $dbi_h->prepare("SELECT LAST_INSERT_ID()");
	if (!$dbi_s->execute())
	{
		print STDERR "$progname: failed to retrieve message ID: " . $dbi_h->errstr . "\n";
		return;
	}

while ($dbi_a = $dbi_s->fetchrow_arrayref())
	{
		if (defined($dbi_a->[0]))
		{
			$msg_id = $dbi_a->[0];
		}
	}

$dbi_s->finish;

if (!defined($msg_id))
	{
		print STDERR "$progname: failed to retrieve message ID: " . $dbi_h->errstr . "\n";
		return;
	}

$dbi_s = $dbi_h->prepare("INSERT INTO signatures (message, domain, selector, pass, error) VALUES(?, ?, ?, ?, ?)");
	foreach my $dd (0 .. $#dkim_data)
	{
		my $sdomain;
		my $sdomain_id;
		my $selector;
		my $selector_id;
		my $pass;
		my $error;

$sdomain = $dkim_data[$dd][0];
		$selector = $dkim_data[$dd][1];
		$pass = $dkim_data[$dd][2];
		$error = $dkim_data[$dd][3];

$sdomain_id = get_table_id($sdomain, "domains");
		if (!defined($sdomain_id))
		{
			next;
		}

# fetch selector_id or insert into table
		$selector_id = get_table_id($selector, "selectors", "", "domain", $sdomain_id) || 0;
		if (!$dbi_s->execute($msg_id, $sdomain_id, $selector_id, $pass, $error))
		{
			print STDERR "$progname: failed to insert DKIM data: " . $dbi_h->errstr . "\n";
			$dbi_s->finish;
			return;
		}
	}
	$dbi_s->finish;

$dbi_s = $dbi_h->prepare("INSERT INTO arcauthresults (message, instance, arc_client_addr) VALUES(?, ?, ?)");
	foreach my $aar (0 .. $#arc_policy_data)
	{
		my $instance;
		my $arc_client_addr;

$instance = $arc_policy_data[$aar]{'i'};
		$arc_client_addr = $arc_policy_data[$aar]{'ip'} || '';

if (!$dbi_s->execute($msg_id, $instance, $arc_client_addr))
		{
			print STDERR "$progname: failed to insert ARC-Authentication-Results data: " . $dbi_h->errstr . "\n";
			$dbi_s->finish;
			return;
		}
	}
	$dbi_s->finish;

$dbi_s = $dbi_h->prepare("INSERT INTO arcseals (message, domain, selector, instance) VALUES(?, ?, ?, ?)");
	foreach my $as (0 .. $#arc_policy_data)
	{
		my $sdomain;
		my $sdomain_id;
		my $selector;
		my $selector_id;
		my $instance;

$instance = $arc_policy_data[$as]{'i'};
		$sdomain = $arc_policy_data[$as]{'d'};
		$selector = $arc_policy_data[$as]{'s'};

$sdomain_id = get_table_id($sdomain, "domains");
		if (!defined($sdomain_id))
		{
			next;
		}

# fetch selector_id or insert into table
		$selector_id = get_table_id($selector, "selectors", "", "domain", $sdomain_id) || 0;
		if (!$dbi_s->execute($msg_id, $sdomain_id, $selector_id, $instance))
		{
			print STDERR "$progname: failed to insert ARC-Seals data: " . $dbi_h->errstr . "\n";
			$dbi_s->finish;
			return;
		}
	}
	$dbi_s->finish;

if (get_value("requests", "locked", $request_id) != 1)
	{
		if (scalar @rua > 0)
		{
			$repuri = join(",", @rua);
			$dbi_s = $dbi_h->prepare("UPDATE requests SET repuri = ? WHERE id = ?");

if (!$dbi_s->execute($repuri, $request_id))
			{
				print STDERR "$progname: failed to update reporting URI for $fdomain: " . $dbi_h->errstr . "\n";
				$dbi_s->finish;
				return;
			}

$dbi_s->finish;
		}
		else
		{
			$dbi_s = $dbi_h->prepare("UPDATE requests SET repuri = '' WHERE id = ?");

if (!$dbi_s->execute($request_id))
			{
				print STDERR "$progname: failed to update reporting URI for $fdomain: " . $dbi_h->errstr . "\n";
				$dbi_s->finish;
				return;
			}

$dbi_s->finish;
		}

$dbi_s = $dbi_h->prepare("UPDATE requests SET adkim = ?, aspf = ?, policy = ?, spolicy = ?, pct = ? WHERE id = ?");

if (!$dbi_s->execute($adkim, $aspf, $p, $sp, $pct, $request_id))
		{
			print STDERR "$progname: failed to update policy data for $fdomain: " . $dbi_h->errstr . "\n";
			$dbi_s->finish;
			return;
		}
	}

$dbi_s->finish;
}

sub usage
{
	print STDERR "$progname: usage: $progname [options]\n";
	print STDERR "\t--dbhost=host      database host [$def_dbhost]\n";
	print STDERR "\t--dbname=name      database name [$def_dbname]\n";
	print STDERR "\t--dbpasswd=passwd  database password [$def_dbpasswd]\n";
	print STDERR "\t--dbport=port      database port [$def_dbport]\n";
	print STDERR "\t--dbuser=user      database user [$def_dbuser]\n";
	print STDERR "\t--input=file       input file [STDIN]\n";
	print STDERR "\t--help             print help and exit\n";
	print STDERR "\t--verbose          verbose output\n";
	print STDERR "\t--version          print version and exit\n";
}

# parse command line arguments
my $opt_retval = &Getopt::Long::GetOptions ('dbhost=s' => \$dbhost,
                                            'dbname=s' => \$dbname,
                                            'dbpasswd=s' => \$dbpasswd,
                                            'dbport=s' => \$dbport,
                                            'dbuser=s' => \$dbuser,
                                            'input=s' => \$inputfile,
                                            'help!' => \$helponly,
                                            'verbose!' => \$verbose,
                                            'version!' => \$showversion,
                                           );

if (!$opt_retval || $helponly)
{
	usage();

if ($helponly)
	{
		exit(0);
	}
	else
	{
		exit(1);
	}
}

if ($showversion)
{
	print STDOUT "$progname v$version\n";
	exit(0);
}

# apply defaults
if (!defined($dbhost))
{
	if (defined($ENV{'OPENDMARC_DBHOST'}))
	{
		$dbhost = $ENV{'OPENDMARC_DBHOST'};
	}
	else
	{
		$dbhost = $def_dbhost;
	}
}

if (!defined($dbname))
{
	if (defined($ENV{'OPENDMARC_DB'}))
	{
		$dbname = $ENV{'OPENDMARC_DB'};
	}
	else
	{
		$dbname = $def_dbname;
	}
}

if (!defined($dbpasswd))
{
	if (defined($ENV{'OPENDMARC_PASSWORD'}))
	{
		$dbpasswd = $ENV{'OPENDMARC_PASSWORD'};
	}
	else
	{
		$dbpasswd = $def_dbpasswd;
	}
}

if (!defined($dbport))
{
	if (defined($ENV{'OPENDMARC_PORT'}))
	{
		$dbport = $ENV{'OPENDMARC_PORT'};
	}
	else
	{
		$dbport = $def_dbport;
	}
}

if (!defined($dbuser))
{
	if (defined($ENV{'OPENDMARC_USER'}))
	{
		$dbuser = $ENV{'OPENDMARC_USER'};
	}
	else
	{
		$dbuser = $def_dbuser;
	}
}

if ($verbose)
{
	print STDERR "$progname: started at " . localtime() . "\n";
}

if (!defined($inputfile))
{
	$inputfh = $def_inputfh;
}
else
{
	open($inputfh, "<", $inputfile) or die "$progname: unable to open $inputfile: $!\n";
	if ($verbose)
	{
		print STDERR "$progname: opened file $inputfile\n"
	}
}
if (!flock($inputfh, LOCK_SH))
{
	print STDERR "$progname: warning: unable to establish read lock\n";
}

my $dbi_dsn = "DBI:" . $dbscheme . ":database=" . $dbname .
              ";host=" . $dbhost . ";port=" . $dbport;

$dbi_h = DBI->connect($dbi_dsn, $dbuser, $dbpasswd, { PrintError => 0 });
if (!defined($dbi_h))
{
	print STDERR "$progname: unable to connect to database: $DBI::errstr\n";
	exit(1);
}

if ($verbose)
{
	print STDERR "$progname: connected to database\n";
}

#
# Read history file from stdin.
#

$lineno = 0;

while (<$inputfh>)
{
	$lineno++;

chomp;
	($key, $value, $data) = split / /, $_, 3;

# skip lines that start with a space
	if (!defined($key))
	{
		next;
	}

if (defined($data) && length($data) > 0)
	{
		if ($data =~ /^json:/)
		{
			@serialized_data = split /:/, $data, 2;
		}
		else
		{
			($dkim_selector, $dkim_result) = split / /, $data
		}
	}

switch ($key)
	{
	  case "action"		{
					$action = $value;
				}

case "adkim"		{
					$adkim = $value;
				}

case "align_dkim"	{
					$align_dkim = $value;
				}

case "align_spf"	{
					$align_spf = $value;
				}

case "arc"		{
		  			$arc = $value;
	  			}

case "arc_policy"	{
					$arc_policy = $value;
					# parse json data
					if (scalar (@serialized_data) == 2)
					{
						@arc_policy_data = @{ decode_json($serialized_data[1]) };
					}
					else
					{
						@arc_policy_data = [];
					}
				}

case "aspf"		{
					$aspf = $value;
				}

case "dkim"		{
					my @dkim_entry;
					push(@dkim_entry, $value);
					push(@dkim_entry, $dkim_selector);
					push(@dkim_entry, $dkim_result);
					if ($dkim_result eq "4" ||
					    $dkim_result eq "5")
					{
						push(@dkim_entry, 1);
					}
					else
					{
						push(@dkim_entry, 0);
					}
					push(@dkim_data, [ @dkim_entry ]);

$sigcount++;
				}

case "from"		{
					$fdomain = $value;
				}

case "job"		{
					if (defined($jobid))
					{
						update_db();

undef $action;
						undef $adkim;
						undef $align_dkim;
						undef $align_spf;
						undef $arc;
						undef $arc_policy;
						undef $aspf;
						undef @serialized_data;
						undef @dkim_data;
						undef @arc_policy_data;
						undef $envdomain;
						undef $fdomain;
						undef $ipaddr;
						undef $jobid;
						undef $p;
						undef $pct;
						undef $pdomain;
						undef $policy;
						undef $received;
						undef $reporter;
						undef @rua;
						$sigcount = 0;
						undef $sp;
						undef $spf;
					}

$jobid = $value;
				}

case "ipaddr"		{
					$ipaddr = $value;
				}

case "mfrom"		{
					$envdomain = $value;
				}

case "p"		{
					$p = $value;
				}

case "pct"		{
					$pct = $value;
				}

case "pdomain"	{
					$pdomain = $value;
				}

case "policy"		{
					$policy = $value;
				}

case "received"	{
					$received = $value;
				}

case "reporter"	{
					$reporter = $value;
				}

case "rua"		{
					if ($value ne "-")
					{
						push(@rua, $value);
					}
				}

case "sp"		{
					$sp = $value;
				}

case "spf"		{
					$spf = $value;
				}

else			{
					print STDERR "$progname: unknown key '$key' at line $lineno\n";
				}
	}
}

if (defined($jobid))
{
	update_db();
}

if (defined($inputfile))
{
	close($inputfh);
}
#
# all done!
#

if ($verbose)
{
	print STDERR "$progname: terminating at " . localtime() . "\n";
}

$dbi_h->disconnect;

exit(0);