3 # Copyright (c) 2012, 2014, The Trusted Domain Project. All rights reserved.
5 # Script to import per-message DMARC data.
25 my $progname = basename
($0);
26 my $version = "1.3.1";
32 my $def_dbhost = "localhost";
33 my $def_dbname = "opendmarc";
34 my $def_dbuser = "opendmarc";
35 my $def_dbpasswd = "opendmarc";
36 my $def_dbport = "3306";
37 my $def_interval = "86400";
44 my $dbscheme = "mysql";
83 ### NO user-serviceable parts beyond this point
93 ($table, $column, $id) = @_;
95 $dbi_t = $dbi_h->prepare("SELECT $column FROM $table WHERE id = ?");
96 if (!$dbi_t->execute($id))
98 print STDERR
"$progname: failed to $column value for ID $id: " . $dbi_h->errstr . "\n";
102 while ($dbi_a = $dbi_t->fetchrow_arrayref())
104 if (defined($dbi_a->[0]))
120 ($name, $table, $column) = @_;
122 if (!defined($name) || !defined($table))
127 if (!defined($column))
132 $dbi_t = $dbi_h->prepare("SELECT id FROM $table WHERE $column = ?");
133 if (!$dbi_t->execute($name))
135 print STDERR
"$progname: failed to retrieve table ID: " . $dbi_h->errstr . "\n";
140 while ($dbi_a = $dbi_t->fetchrow_arrayref())
142 if (defined($dbi_a->[0]))
152 $dbi_t = $dbi_h->prepare("INSERT INTO $table ($column) VALUES(?)");
153 if (!$dbi_t->execute($name))
155 print STDERR
"$progname: failed to create table ID: " . $dbi_h->errstr . "\n";
159 $dbi_t = $dbi_h->prepare("SELECT LAST_INSERT_ID()");
160 if (!$dbi_t->execute())
162 print STDERR
"$progname: failed to retrieve created table ID: " . $dbi_h->errstr . "\n";
166 while ($dbi_a = $dbi_t->fetchrow_arrayref())
168 if (defined($dbi_a->[0]))
178 print STDERR
"$progname: failed to retrieve created table ID: " . $dbi_h->errstr . "\n";
199 print STDERR
"$progname: updating at line $lineno\n";
202 $rep_id = get_table_id
($reporter, "reporters");
203 $from_id = get_table_id
($fdomain, "domains");
204 $envfrom_id = get_table_id
($envdomain, "domains");
205 $pdomain_id = get_table_id
($pdomain, "domains");
206 $ipaddr_id = get_table_id
($ipaddr, "ipaddr", "addr");
207 $request_id = get_table_id
($from_id, "requests", "domain");
209 if (!defined($rep_id) ||
210 !defined($from_id) ||
211 !defined($envfrom_id) ||
212 !defined($pdomain_id) ||
213 !defined($ipaddr_id) ||
214 !defined($request_id))
219 $dbi_s = $dbi_h->prepare("INSERT INTO messages (date, jobid, reporter, policy, disp, ip, env_domain, from_domain, spf, align_spf, align_dkim, sigcount) VALUES(FROM_UNIXTIME(?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
220 if (!$dbi_s->execute($received, $jobid, $rep_id, $policy, $action, $ipaddr_id, $envfrom_id, $from_id, $spf, $align_spf, $align_dkim, $sigcount))
222 print STDERR
"$progname: failed to insert message: " . $dbi_h->errstr . "\n";
229 $dbi_s = $dbi_h->prepare("SELECT LAST_INSERT_ID()");
230 if (!$dbi_s->execute())
232 print STDERR
"$progname: failed to retrieve message ID: " . $dbi_h->errstr . "\n";
236 while ($dbi_a = $dbi_s->fetchrow_arrayref())
238 if (defined($dbi_a->[0]))
240 $msg_id = $dbi_a->[0];
246 if (!defined($msg_id))
248 print STDERR
"$progname: failed to retrieve message ID: " . $dbi_h->errstr . "\n";
252 $dbi_s = $dbi_h->prepare("INSERT INTO signatures (message, domain, pass, error) VALUES(?, ?, ?, ?)");
253 foreach $dd (0 .. $#dkim_data)
259 $sdomain = $dkim_data[$dd][0];
260 $pass = $dkim_data[$dd][1];
261 $error = $dkim_data[$dd][2];
263 $sdomain_id = get_table_id
($sdomain, "domains");
264 if (!defined($sdomain_id))
269 if (!$dbi_s->execute($msg_id, $sdomain_id, $pass, $error))
271 print STDERR
"$progname: failed to insert DKIM data: " . $dbi_h->errstr . "\n";
278 if (get_value
("requests", "locked", $request_id) != 1)
282 $repuri = join(",", @rua);
283 $dbi_s = $dbi_h->prepare("UPDATE requests SET repuri = ? WHERE id = ?");
285 if (!$dbi_s->execute($repuri, $request_id))
287 print STDERR
"$progname: failed to update reporting URI for $fdomain: " . $dbi_h->errstr . "\n";
296 $dbi_s = $dbi_h->prepare("UPDATE requests SET repuri = NULL WHERE id = ?");
298 if (!$dbi_s->execute($request_id))
300 print STDERR
"$progname: failed to update reporting URI for $fdomain: " . $dbi_h->errstr . "\n";
308 $dbi_s = $dbi_h->prepare("UPDATE requests SET adkim = ?, aspf = ?, policy = ?, spolicy = ?, pct = ? WHERE id = ?");
310 if (!$dbi_s->execute($adkim, $aspf, $p, $sp, $pct, $request_id))
312 print STDERR
"$progname: failed to update policy data for $fdomain: " . $dbi_h->errstr . "\n";
323 print STDERR
"$progname: usage: $progname [options]\n";
324 print STDERR
"\t--dbhost=host database host [$def_dbhost]\n";
325 print STDERR
"\t--dbname=name database name [$def_dbname]\n";
326 print STDERR
"\t--dbpasswd=passwd database password [$def_dbpasswd]\n";
327 print STDERR
"\t--dbport=port database port [$def_dbport]\n";
328 print STDERR
"\t--dbuser=user database user [$def_dbuser]\n";
329 print STDERR
"\t--help print help and exit\n";
330 print STDERR
"\t--verbose verbose output\n";
331 print STDERR
"\t--version print version and exit\n";
334 # parse command line arguments
335 my $opt_retval = &Getopt
::Long
::GetOptions
('dbhost=s' => \
$dbhost,
336 'dbname=s' => \
$dbname,
337 'dbpasswd=s' => \
$dbpasswd,
338 'dbport=s' => \
$dbport,
339 'dbuser=s' => \
$dbuser,
340 'help!' => \
$helponly,
341 'verbose!' => \
$verbose,
342 'version!' => \
$showversion,
345 if (!$opt_retval || $helponly)
361 print STDOUT
"$progname v$version\n";
366 if (!defined($dbhost))
368 if (defined($ENV{'OPENDMARC_DBHOST'}))
370 $dbhost = $ENV{'OPENDMARC_DBHOST'};
374 $dbhost = $def_dbhost;
378 if (!defined($dbname))
380 if (defined($ENV{'OPENDMARC_DB'}))
382 $dbname = $ENV{'OPENDMARC_DB'};
386 $dbname = $def_dbname;
390 if (!defined($dbpasswd))
392 if (defined($ENV{'OPENDMARC_PASSWORD'}))
394 $dbpasswd = $ENV{'OPENDMARC_PASSWORD'};
398 $dbpasswd = $def_dbpasswd;
402 if (!defined($dbport))
404 if (defined($ENV{'OPENDMARC_PORT'}))
406 $dbport = $ENV{'OPENDMARC_PORT'};
410 $dbport = $def_dbport;
414 if (!defined($dbuser))
416 if (defined($ENV{'OPENDMARC_USER'}))
418 $dbuser = $ENV{'OPENDMARC_USER'};
422 $dbuser = $def_dbuser;
428 print STDERR
"$progname: started at " . localtime() . "\n";
431 my $dbi_dsn = "DBI:" . $dbscheme . ":database=" . $dbname .
432 ";host=" . $dbhost . ";port=" . $dbport;
434 $dbi_h = DBI
->connect($dbi_dsn, $dbuser, $dbpasswd, { PrintError
=> 0 });
435 if (!defined($dbi_h))
437 print STDERR
"$progname: unable to connect to database: $DBI::errstr\n";
443 print STDERR
"$progname: connected to database\n";
447 # Read history file from stdin.
451 if (!flock(STDIN
, LOCK_SH
))
453 print STDERR
"$progname: warning: unable to establish read lock\n";
461 ($key, $value, $dkim_result) = split;
474 $align_dkim = $value;
487 push(@dkim_entry, $value);
488 push(@dkim_entry, $dkim_result);
489 if ($dkim_result eq "4" ||
492 push(@dkim_entry, 1);
496 push(@dkim_entry, 0);
498 push(@dkim_data, [ @dkim_entry ]);
585 print STDERR
"$progname: unknown key '$key' at line $lineno\n";
601 print STDERR
"$progname: terminating at " . localtime() . "\n";