blib/lib/Mail/DMARC/Report/Store/SQL.pm untested

filter: Statementstmt 0.0% 0 / 581 Branchbran 0.0% 0 / 230 Conditioncond 0.0% 0 / 161 MC/DCmcdc 0.0% 0 / 92 Subroutinesub 0.0% 0 / 64 Podpod 0.0% 0 / 49 totaltotal 0.0% 0 / 1177 CC 116 SCAR 37.0
CC
116
Cov
0%
get_report
182.0
retrieve
132.0
db_connect
72.0
CRAP 40.4
1 package Mail::DMARC::Report::Store::SQL;
2 our $VERSION = '2.20260827';
3 use strict;
4 use warnings;
5 use feature 'signatures';
6  
7 use Carp;
8 use Data::Dumper;
9 use DBIx::Simple;
10 use File::ShareDir;
11  
12 use Mail::DMARC::Report::Store::SQL::Grammars::MySQL;
13 use Mail::DMARC::Report::Store::SQL::Grammars::SQLite;
14 use Mail::DMARC::Report::Store::SQL::Grammars::PostgreSQL;
15  
16 use parent 'Mail::DMARC::Base';
17 use Mail::DMARC::Report::Aggregate;
18 use Mail::DMARC::Report::Aggregate::Record;
19 use Mail::DMARC::Policy;
20  
21 my @AGG_FIELDS = qw/ messages aligned_both aligned_dkim aligned_spf aligned_none
22 disp_none disp_quarantine disp_reject /;
23  
24 my %FORWARDING_REASON
25 = map { $_ => 1 } qw/ forwarded mailing_list trusted_forwarder /;
26  
27 # A source is called aligned while its failures stay under this share of its
28 # own volume. Requiring exactly zero would label almost every real source as
29 # failing: retries, odd forwarders and low volume abuse of your domain put a
30 # few failures behind nearly everyone, and a bucket that catches everything
31 # sorts nothing.
32 my $FAILING_TOLERANCE = 0.02;
33  
34 # Forwarding rarely accounts for every failure at a source that also has other
35 # problems, so the reasons only have to explain the bulk of them.
36 my $FORWARDING_SHARE = 0.5;
37  
38 # ARC exists to carry authentication across a forwarding hop, so a receiver
39 # that overrode its policy on a passing ARC chain is telling you the mail was
40 # legitimately forwarded. The aggregate format has no field for that, so
41 # receivers report it as local_policy with the result in free text.
42 my $ARC_PASS = qr/\barc = pass\b/xi;
43  
44 sub _explains_forwarding($reason) {
45 return 1 if $FORWARDING_REASON{ $reason->{type} // '' };
46 return 1
47 if 'local_policy' eq ( $reason->{type} // '' )
48 && defined $reason->{comment}
49 && $reason->{comment} =~ $ARC_PASS;
50 return 0;
51 }
52  
53 sub save_aggregate( $self, $agg ) {
54  
55 $self->db_connect();
56  
57 croak "policy_published must be a Mail::DMARC::Policy object"
58 if 'Mail::DMARC::Policy' ne ref $agg->policy_published;
59  
60 #warn Dumper($meta); ## no critic (Carp)
61 foreach my $f (qw/ org_name email begin end /) {
62 croak "meta field $f required" if !$agg->metadata->$f;
63 }
64  
65 my $rid = $self->get_report_id($agg)
66 or croak "failed to create report!";
67  
68 # on 6/8/2013, Microsoft spat out a bunch of reports with zero records.
69 if ( !$agg->record ) {
70 warn "\ta report with ZERO records! Illegal.\n"; ## no critic (Carp)
71 return $rid;
72 }
73  
74 foreach my $rec ( @{ $agg->record } ) {
75 $self->insert_agg_record( $rid, $rec );
76 }
77  
78 return $rid;
79 }
80  
81 sub retrieve( $self, @args ) {
82 my %args = @args;
83  
84 my $query = $self->grammar->select_report_query;
85 my @params;
86  
87 if ( $args{rid} ) {
88 $query .= $self->grammar->and_arg('r.id');
89 push @params, $args{rid};
90 }
91 if ( $args{begin} ) {
92 $query .= $self->grammar->and_arg( 'r.begin', '>=' );
93 push @params, $args{begin};
94 }
95 if ( $args{end} ) {
96 $query .= $self->grammar->and_arg( 'r.end', '<=' );
97 push @params, $args{end};
98 }
99 if ( $args{author} ) {
100 my ( $op, $val ) = $self->_negate_arg( $args{author} );
101 $query .= $self->grammar->and_arg( 'a.org_name', $op );
102 push @params, $val;
103 }
104 if ( $args{from_domain} ) {
105 my ( $op, $val ) = $self->_negate_arg( $args{from_domain} );
106 $query .= $self->grammar->and_arg( 'fd.domain', $op );
107 push @params, $val;
108 }
109  
110 my $sort_by = lc( $args{sort_by} || 'rid' );
111 $sort_by = 'rid' if $sort_by eq 'id';
112  
113 my %allowed_sort = map { $_ => 1 } qw( rid author from_domain begin end );
114 $sort_by = 'rid' if !$allowed_sort{$sort_by};
115  
116 my $sort_order = uc( $args{sort_order} || 'DESC' );
117 $sort_order = 'DESC' if $sort_order ne 'ASC' && $sort_order ne 'DESC';
118  
119 $query .= $self->grammar->order_by( $sort_by, $sort_order );
120  
121 if ( defined $args{limit} ) {
122 croak "limit must be a positive integer"
123 if $args{limit} !~ /^\d+$/ || $args{limit} < 1;
124 $query .= $self->grammar->limit( $args{limit} );
125 }
126  
127 my $reports = $self->query( $query, \@params );
128  
129 foreach (@$reports) {
130 $_->{begin} = join( " ", split( /T/, $self->epoch_to_iso( $_->{begin} ) ) );
131 $_->{end} = join( " ", split( /T/, $self->epoch_to_iso( $_->{end} ) ) );
132 }
133 return $reports;
134 }
135  
136 sub _negate_arg( $self, $val ) {
137 if ( substr( $val, 0, 1 ) eq '!' ) {
138 return ( '!=', substr( $val, 1 ) );
139 }
140 return ( '=', $val );
141 }
142  
143 sub next_todo($self) {
144  
145 if ( !exists $self->{_todo_list} ) {
146 $self->{_todo_list}
147 = $self->query( $self->grammar->select_todo_query, [ $self->time ] );
148 return if !$self->{_todo_list};
149 }
150  
151 my $next_todo = shift @{ $self->{_todo_list} };
152 if ( !$next_todo ) {
153 delete $self->{_todo_list};
154 return;
155 }
156  
157 my $agg = Mail::DMARC::Report::Aggregate->new();
158 $self->populate_agg_metadata( \$agg, \$next_todo );
159  
160 my $pp = $self->get_report_policy_published( $next_todo->{rid} );
161 $pp->{domain} = $next_todo->{from_domain};
162 $agg->policy_published( Mail::DMARC::Policy->new(%$pp) );
163  
164 $self->populate_agg_records( \$agg, $next_todo->{rid} );
165 return $agg;
166 }
167  
168 sub retrieve_todo( $self, @args ) {
169  
170 # this method extracts the data from the SQL tables and populates a
171 # list of Aggregate report objects with them.
172 my $reports
173 = $self->query( $self->grammar->select_todo_query, [ $self->time ] );
174 my @reports_todo;
175 return \@reports_todo if !@$reports;
176  
177 foreach my $report ( @{$reports} ) {
178  
179 my $agg = Mail::DMARC::Report::Aggregate->new();
180 $self->populate_agg_metadata( \$agg, \$report );
181  
182 my $pp = $self->get_report_policy_published( $report->{rid} );
183 $pp->{domain} = $report->{from_domain};
184 $agg->policy_published( Mail::DMARC::Policy->new(%$pp) );
185  
186 $self->populate_agg_records( \$agg, $report->{rid} );
187 push @reports_todo, $agg;
188 }
189 return \@reports_todo;
190 }
191  
192 sub delete_report( $self, $report_id = undef ) {
193 $report_id or croak "missing report ID";
194 print "deleting report $report_id\n" if $self->verbose;
195  
196 # deletes with FK don't cascade in SQLite? Clean each table manually
197 my $rows = $self->query( $self->grammar->report_record_id, [$report_id] );
198 my @row_ids = map { $_->{id} } @$rows;
199  
200 if (@row_ids) {
201 foreach my $table (
202 qw/ report_record_spf report_record_dkim report_record_reason /)
203 {
204 print "deleting $table rows " . join( ',', @row_ids ) . "\n"
205 if $self->verbose;
206 eval {
207 $self->query( $self->grammar->delete_from_where_record_in($table),
208 \@row_ids );
209 };
210  
211 # warn $@ if $@;
212 }
213 }
214 foreach my $table (qw/ report_policy_published report_record report_error /) {
215 print "deleting $table rows for report $report_id\n" if $self->verbose;
216 eval {
217 $self->query( $self->grammar->delete_from_where_report($table),
218 [$report_id] );
219 };
220  
221 # warn $@ if $@;
222 }
223  
224 # In MySQL, where FK constraints DO cascade, this is the only query needed
225 $self->query( $self->grammar->delete_report, [$report_id] );
226 return 1;
227 }
228  
229 sub get_domain_id( $self, $domain ) {
230 croak "missing domain calling " . ( caller(0) )[3] if !$domain;
231 my $r = $self->query( $self->grammar->select_domain_id, [$domain] );
232 if ( $r && @$r ) {
233 return $r->[0]{id};
234 }
235 return $self->query( $self->grammar->insert_domain, [$domain] );
236 }
237  
238 sub get_author_id( $self, $meta ) {
239 croak "missing author name" if !$meta->org_name;
240 my $r = $self->query( $self->grammar->select_author_id, [ $meta->org_name ] );
241 if ( $r && @$r ) {
242 return $r->[0]{id};
243 }
244 carp "missing email" if !$meta->email;
245 return $self->query( $self->grammar->insert_author,
246 [ $meta->org_name, $meta->email, $meta->extra_contact_info ] );
247 }
248  
249 sub get_report_id( $self, $aggr ) {
250  
251 my $meta = $aggr->metadata;
252 my $pol = $aggr->policy_published;
253  
254 # check if report exists
255 my $author_id = $self->get_author_id($meta) or croak;
256 my $from_dom_id = $self->get_domain_id( $pol->domain ) or croak;
257  
258 my $ids;
259 if ( $meta->report_id ) {
260  
261 # reports arriving via the wire will have an author ID & report ID
262 $ids = $self->query( $self->grammar->select_report_id,
263 [ $meta->report_id, $author_id ] );
264 }
265 else {
266 # Reports submitted by our local MTA will not have a report ID
267 # They aggregate on the From domain, where the DMARC policy was discovered
268 $ids = $self->query(
269 $self->grammar->select_id_with_end,
270 [ $from_dom_id, $self->time, $author_id ]
271 );
272 }
273  
274 if (@$ids) { # report already exists
275 return $self->{report_id} = $ids->[0]{id};
276 }
277  
278 my $rid = $self->{report_id} = $self->query( $self->grammar->insert_report,
279 [ $from_dom_id, $meta->begin, $meta->end, $author_id, $meta->uuid ] )
280 or return;
281  
282 $self->insert_policy_published( $rid, $pol );
283 return $rid;
284 }
285  
286 sub get_report( $self, @args ) {
287 croak "invalid parameters" if @args % 2;
288 my %args = @args;
289  
290 my @known = qw/ r.id a.org_name fd.domain r.begin r.end /;
291 my %known = map { $_ => 1 } @known;
292  
293 my $where = '';
294 my @where_params;
295  
296 # Optional reporting window, so a caller can scope the list the same way
297 # the aggregate views are scoped. Compared against begin, matching how
298 # those views attribute a report to a day.
299 for my $pair ( [ since => 'r.begin', '>=' ], [ until => 'r.begin', '<=' ] ) {
300 my ( $param, $column, $op ) = @$pair;
301 next if !defined $args{$param};
302 next if $args{$param} !~ /\A-?[0-9]+\z/x;
303 $where .= $self->grammar->and_arg( $column, $op );
304 push @where_params, $args{$param};
305 }
306  
307 my ( $origin, $origin_params )
308 = $self->_origin_filter( $args{reports} // 'all' );
309 $where .= $origin;
310 push @where_params, @$origin_params;
311  
312 # Per-column LIKE searches
313 for my $pair ( [ search_domain => 'fd.domain' ],
314 [ search_author => 'a.org_name' ] )
315 {
316 my ( $param, $col_name ) = @$pair;
317 next unless $args{$param};
318 my $safe = $args{$param};
319 $safe =~ s/([%_!])/!$1/g; # escape LIKE metacharacters using !
320 $where .= " AND $col_name LIKE ? ESCAPE '!'";
321 push @where_params, '%' . $safe . '%';
322 }
323  
324 foreach my $known (@known) {
325 next if !defined $args{$known};
326 $where .= $self->grammar->and_arg($known);
327 push @where_params, $args{$known};
328 }
329  
330 my $total_recs = $self->dbix->query( $self->grammar->count_reports )->list;
331 my $filtered_recs = $total_recs;
332 if ($where) {
333 $filtered_recs
334 = $self->dbix->query(
335 $self->grammar->count_filtered_report_query . $where,
336 @where_params )->list;
337 }
338  
339 my $order = '';
340 if ( $args{sort_col} && $known{ $args{sort_col} } ) {
341 if ( $args{sort_dir} ) {
342 $order = $self->grammar->order_by( $args{sort_col},
343 $args{sort_dir} eq 'desc' ? ' DESC' : ' ASC' );
344 }
345 }
346  
347 my $query = $self->grammar->select_report_query . $where . $order;
348 my @params = @where_params;
349 if ( $args{length} ) {
350 my $start = $args{start} || 0;
351 $start = 0 if $start < 0;
352 $query .= $self->grammar->limit_args(2);
353 push @params, $start, $args{length};
354 }
355  
356 # warn "query: $query\n" . join(", ", @params) . "\n";
357 my $reports = $self->query( $query, \@params );
358 foreach (@$reports) {
359 $_->{begin} = $self->epoch_to_iso( $_->{begin} );
360 $_->{end} = $self->epoch_to_iso( $_->{end} );
361 }
362  
363 $self->attach_report_summaries($reports) if $args{summary};
364  
365 return {
366 recordsTotal => $total_recs,
367 recordsFiltered => $filtered_recs,
368 data => $reports,
369 };
370 }
371  
372 # What each report contains, so a viewer has less reason to expand every row.
373 # Opt in from get_report: the callers that only want the report envelope should
374 # not pay for a second query.
375 sub attach_report_summaries( $self, $reports ) {
376 my @rids = map { $_->{rid} } @$reports;
377 return if !@rids;
378  
379 my $rows = $self->query( $self->grammar->select_report_summary_query, \@rids );
380  
381 my %by_rid;
382 foreach my $row (@$rows) {
383 my $summary = $by_rid{ $row->{rid} } ||= {
384 ( map { $_ => 0 } @AGG_FIELDS ),
385 header_from => {},
386 envelope_to => {},
387 };
388 $summary->{$_} += $row->{$_} || 0 foreach @AGG_FIELDS;
389  
390 # A report names one domain per record; the set is usually a single
391 # entry, occasionally a handful.
392 foreach my $field (qw/ header_from envelope_to /) {
393 next if !defined $row->{$field} || '' eq $row->{$field};
394 $summary->{$field}{ $row->{$field} } = 1;
395 }
396 }
397  
398 foreach my $report (@$reports) {
399 my $found = $by_rid{ $report->{rid} };
400 if ( !$found ) {
401 $report->{summary}
402 = { ( map { $_ => 0 } @AGG_FIELDS ),
403 header_from => [], envelope_to => [] };
404 next;
405 }
406 my %summary = map { $_ => $found->{$_} + 0 } @AGG_FIELDS;
407 $summary{$_} = [ sort keys %{ $found->{$_} } ]
408 foreach qw/ header_from envelope_to /;
409 $report->{summary} = \%summary;
410 }
411 return;
412 }
413  
414 sub get_report_policy_published( $self, $rid ) {
415 my $pp = $self->query( $self->grammar->select_report_policy_published, [$rid] )
416 ->[0];
417 $pp->{p} ||= 'none';
418 $pp = Mail::DMARC::Policy->new( v => 'DMARC1', %$pp );
419 return $pp;
420 }
421  
422 sub get_rr( $self, @args ) {
423 croak "invalid parameters" if @args % 2;
424 my %args = @args;
425  
426 # warn Dumper(\%args);
427 croak "missing report ID (rid)!" if !defined $args{rid};
428  
429 my $rows = $self->query( $self->grammar->select_rr_query, [ $args{rid} ] );
430 foreach (@$rows) {
431 $_->{source_ip} = $self->any_inet_ntop( $_->{source_ip} )
432 if $self->grammar->language ne 'postgresql';
433 $_->{reasons}
434 = $self->query( $self->grammar->select_report_reason, [ $_->{id} ] );
435 }
436 return { data => $rows, };
437 }
438  
439 sub populate_agg_metadata( $self, $agg_ref, $report_ref ) {
440  
441 $$agg_ref->metadata->report_id( $$report_ref->{rid} );
442  
443 foreach my $f (qw/ org_name email extra_contact_info /) {
444 $$agg_ref->metadata->$f( $self->config->{organization}{$f} );
445 }
446 foreach my $f (qw/ begin end /) {
447 $$agg_ref->metadata->$f( $$report_ref->{$f} );
448 }
449  
450 my $errors = $self->query( $self->grammar->select_report_error,
451 [ $$report_ref->{rid} ] );
452 foreach (@$errors) {
453 $$agg_ref->metadata->error( $_->{error} );
454 }
455 return 1;
456 }
457  
458 sub populate_agg_records( $self, $agg_ref, $rid ) {
459  
460 my $recs = $self->query( $self->grammar->select_rr_query, [$rid] );
461  
462 # aggregate the connections per IP-Disposition-DKIM-SPF uniqueness
463 my ( %ips, %uniq, %pe, %auth, %ident, %reasons, %other );
464 foreach my $rec (@$recs) {
465 my $ip = $rec->{source_ip};
466 $ip = $self->any_inet_ntop( $rec->{source_ip} )
467 if $self->grammar->language ne 'postgresql';
468 my $key = join( '-', $ip, @$rec{qw/ disposition dkim spf /} ); # hash slice
469 $uniq{$key}++;
470 $ips{$key} = $rec->{source_ip};
471 $ident{$key}{header_from} ||= $rec->{header_from};
472 $ident{$key}{envelope_from} ||= $rec->{envelope_from};
473 $ident{$key}{envelope_to} ||= $rec->{envelope_to};
474  
475 $pe{$key}{disposition} ||= $rec->{disposition};
476 $pe{$key}{dkim} ||= $rec->{dkim};
477 $pe{$key}{spf} ||= $rec->{spf};
478  
479 $auth{$key}{spf} ||= $self->get_row_spf( $rec->{id} );
480 $auth{$key}{dkim} ||= $self->get_row_dkim( $rec->{id} );
481  
482 my $reasons = $self->get_row_reason( $rec->{id} );
483 foreach my $reason (@$reasons) {
484 my $type = $reason->{type} or next;
485 $reasons{$key}{$type} = $reason->{comment}; # flatten reasons
486 }
487 }
488  
489 foreach my $u ( keys %uniq ) {
490 my $record = Mail::DMARC::Report::Aggregate::Record->new(
491 identifiers => $ident{$u},
492 auth_results => $auth{$u},
493 row => {
494 source_ip => $self->grammar->language eq 'postgresql' ? $ips{$u}
495 : $self->any_inet_ntop( $ips{$u} ),
496 count => $uniq{$u},
497 policy_evaluated => {
498 %{ $pe{$u} },
499 $reasons{$u}
500 ? ( reason => [
501 map { { type => $_, comment => $reasons{$u}{$_} } }
502 sort keys %{ $reasons{$u} }
503 ]
504 )
505 : (),
506 },
507 }
508 );
509 $$agg_ref->record($record);
510 }
511 return $$agg_ref->record;
512 }
513  
514 sub row_exists( $self, $rid, $rec ) {
515  
516 if ( !defined $rec->{row}{count} ) {
517 print "new record\n" if $self->verbose;
518 return;
519 }
520  
521 my $rows = $self->query( $self->grammar->select_report_record,
522 [ $rid, $rec->{row}{source_ip}, $rec->{row}{count}, ] );
523  
524 return 1 if @$rows;
525 return;
526 }
527  
528 sub insert_agg_record( $self, $report_id, $rec ) {
529  
530 return 1 if $self->row_exists( $report_id, $rec );
531  
532 my $row_id = $self->insert_rr( $report_id, $rec )
533 or croak "failed to insert report row";
534  
535 my $reasons = $rec->row->policy_evaluated->reason;
536 if ($reasons) {
537 foreach my $reason (@$reasons) {
538 next if !$reason || !$reason->{type};
539 $self->insert_rr_reason( $row_id, $reason->{type}, $reason->{comment} );
540 }
541 }
542  
543 my $spf_ref = $rec->auth_results->spf;
544 if ($spf_ref) {
545 foreach my $spf (@$spf_ref) {
546 $self->insert_rr_spf( $row_id, $spf );
547 }
548 }
549  
550 my $dkim = $rec->auth_results->dkim;
551 if ($dkim) {
552 foreach my $sig (@$dkim) {
553 next if !$sig || !$sig->{domain};
554 $self->insert_rr_dkim( $row_id, $sig );
555 }
556 }
557 return 1;
558 }
559  
560 sub insert_error( $self, $rid, $error ) {
561  
562 # wait >5m before trying to deliver this report again
563 $self->query( $self->grammar->insert_error(0),
564 [ $self->time + ( 5 * 60 ), $rid ] );
565  
566 return $self->query( $self->grammar->insert_error(1), [ $rid, $error ] );
567 }
568  
569 sub insert_rr_reason( $self, $row_id, $type, $comment ) {
570 return $self->query( $self->grammar->insert_rr_reason,
571 [ $row_id, $type, ( $comment || '' ) ] );
572 }
573  
574 sub insert_rr_dkim( $self, $row_id, $dkim ) {
575 my ( @fields, @values );
576 foreach (qw/ domain selector result human_result /) {
577 next if !defined $dkim->{$_};
578 if ( 'domain' eq $_ ) {
579 push @fields, 'domain_id';
580 push @values, $self->get_domain_id( $dkim->{domain} );
581 next;
582 }
583 push @fields, $_;
584 push @values, $dkim->{$_};
585 }
586 my $query = $self->grammar->insert_rr_dkim( \@fields );
587 $self->query( $query, [ $row_id, @values ] );
588 return 1;
589 }
590  
591 sub insert_rr_spf( $self, $row_id, $spf ) {
592 my ( @fields, @values );
593 for (qw/ domain scope result /) {
594 next if !defined $spf->{$_};
595 if ( 'domain' eq $_ ) {
596 push @fields, 'domain_id';
597 push @values, $self->get_domain_id( $spf->{domain} );
598 next;
599 }
600 push @fields, $_;
601 push @values, $spf->{$_};
602 }
603 my $query = $self->grammar->insert_rr_spf( \@fields );
604 $self->query( $query, [ $row_id, @values ] );
605 return 1;
606 }
607  
608 sub insert_rr( $self, $report_id, $rec ) {
609 $report_id or croak "report ID required?!";
610 my $query = $self->grammar->insert_rr;
611  
612 my $ip = $rec->row->source_ip;
613 $ip = $self->any_inet_pton($ip) if $self->grammar->language ne 'postgresql';
614 my @args = ( $report_id, $ip, $rec->{row}{count}, );
615 foreach my $f (qw/ header_from envelope_to envelope_from /) {
616 push @args,
617 $rec->identifiers->$f
618 ? $self->get_domain_id( $rec->identifiers->$f )
619 : undef;
620 }
621 push @args, map { $rec->row->policy_evaluated->$_ } qw/ disposition dkim spf /;
622 my $rr_id = $self->query( $query, \@args ) or croak;
623 return $self->{report_row_id} = $rr_id;
624 }
625  
626 sub insert_policy_published( $self, $id, $pub ) {
627 my $query = $self->grammar->insert_policy_published;
628 $self->query( $query, [ $id, @$pub{qw/ adkim aspf p sp pct rua /} ] );
629 return 1;
630 }
631  
632 sub db_connect($self) {
633  
634 my $dsn = $self->config->{report_store}{dsn} or croak;
635 my $user = $self->config->{report_store}{user};
636 my $pass = $self->config->{report_store}{pass};
637  
638 # cacheing
639 if ( $self->{grammar} && $self->{dbix} ) {
640 my $cached_grammar_type = $self->{grammar}->dsn;
641 if ( $dsn =~ /$cached_grammar_type/ ) {
642 return $self->{dbix}; # caching
643 }
644 }
645  
646 my $needs_tables;
647  
648 $self->{grammar} = undef;
649 my %opts;
650  
651 if ( $dsn =~ /sqlite/i ) {
652 my ($db) = ( split /=/, $dsn )[-1];
653 if ( !$db || $db eq ':memory:' || !-e $db ) {
654 my $schema = 'mail_dmarc_schema.sqlite';
655 $needs_tables = $self->get_db_schema($schema)
656 or croak
657 "can't locate DB $db AND can't find $schema! Create $db manually.\n";
658 }
659 $self->{grammar} = Mail::DMARC::Report::Store::SQL::Grammars::SQLite->new();
660 }
661 elsif ( $dsn =~ /mysql/i ) {
662 $opts{'mysql_enable_utf8mb4'} = 1;
663 $self->{grammar} = Mail::DMARC::Report::Store::SQL::Grammars::MySQL->new();
664 }
665 elsif ( $dsn =~ /pg/i ) {
666 $self->{grammar}
667 = Mail::DMARC::Report::Store::SQL::Grammars::PostgreSQL->new();
668 }
669 else {
670 croak "can't determine database type, so unable to load grammar.\n";
671 }
672  
673 $self->{dbix} = DBIx::Simple->connect( $dsn, $user, $pass, \%opts )
674 or return $self->error( DBIx::Simple->error );
675  
676 if ($needs_tables) {
677 $self->apply_db_schema($needs_tables);
678 }
679  
680 return $self->{dbix};
681 }
682  
683 sub db_check_err( $self, $err ) {
684 ## no critic (PackageVars)
685 return if !defined $DBI::errstr;
686 return if !$DBI::errstr;
687 return if $DBI::errstr eq 'DBI error: ';
688 croak $err . $DBI::errstr;
689 }
690  
691 sub dbix($self) {
692 return $self->{dbix} if $self->{dbix};
693 return $self->db_connect();
694 }
695  
696 sub apply_db_schema( $self, $file ) {
697 my $setup = $self->slurp($file);
698 foreach ( split /;/, $setup ) {
699  
700 # warn "$_\n";
701 $self->dbix->query($_);
702 }
703 return;
704 }
705  
706 sub get_db_schema( $self, $file ) {
707 return "share/$file" if -f "share/$file"; # when testing
708 return File::ShareDir::dist_file( 'Mail-DMARC', $file ); # when installed
709 }
710  
711 sub query( $self, $query, $params = undef, @extra ) {
712  
713 my @c = caller;
714 my $err = sprintf( "query called by %s, %s\n", $c[0], $c[2] ) . "\t$query\n\t";
715  
716 my @params;
717 if ( defined $params ) {
718 @params = ref $params eq 'ARRAY' ? @$params : $params;
719 no warnings; ## no critic (NoWarnings)
720 $err .= join( ', ', @params );
721 }
722  
723 croak "too many arguments to exec_query!" if @extra;
724  
725 my $dbix = $self->db_connect() or croak DBIx::Simple->error;
726  
727 return $self->query_insert( $query, $err, @params ) if $query =~ /^INSERT/ix;
728 return $self->query_replace( $query, $err, @params )
729 if $query =~ /^(?:REPLACE|UPDATE)/ix;
730 return $self->query_delete( $query, $err, @params )
731 if $query =~ /^(?:DELETE|TRUNCATE)/ix;
732 return $self->query_any( $query, $err, @params );
733 }
734  
735 sub query_any( $self, $query, $err, @params ) {
736  
737 # warn "query: $query\n" . join(", ", @params) . "\n";
738 my $r;
739 eval { $r = $self->dbix->query( $query, @params )->hashes; } or print '';
740 $self->db_check_err($err);
741 die "something went wrong with: $err\n" if !$r; ## no critic (Carp)
742 return $r;
743 }
744  
745 sub query_insert( $self, $query, $err, @params ) {
746 eval { $self->dbix->query( $query, @params ) } or do {
747 warn DBIx::Simple->error . "\n";
748 croak $err;
749 };
750 $self->db_check_err($err);
751  
752 # If the table has no autoincrement field, last_insert_id is zero
753 my ( undef, undef, $table ) = split /\s+/, $query;
754 ($table) = split( /\(/, $table ) if $table =~ /\(/;
755 $table =~ s/^"|"$//g;
756 croak "unable to determine table in query: $query" if !$table;
757 return $self->dbix->last_insert_id( undef, undef, $table, undef );
758 }
759  
760 sub query_replace( $self, $query, $err, @params ) {
761 $self->dbix->query( $query, @params ) or croak $err;
762 $self->db_check_err($err);
763 return 1; # sorry, no indication of success
764 }
765  
766 sub query_delete( $self, $query, $err, @params ) {
767 my $affected = $self->dbix->query( $query, @params )->rows or croak $err;
768 $self->db_check_err($err);
769 return $affected;
770 }
771  
772 sub get_row_spf( $self, $rowid ) {
773 return $self->query( $self->grammar->select_row_spf, [$rowid] );
774 }
775  
776 sub get_row_dkim( $self, $rowid ) {
777 return $self->query( $self->grammar->select_row_dkim, [$rowid] );
778 }
779  
780 sub get_row_reason( $self, $rowid ) {
781 return $self->query( $self->grammar->select_row_reason, [$rowid] );
782 }
783  
784 # A store doubles as the queue for reports this installation is preparing to
785 # send, and those are about other people's domains as seen by us. Reports about
786 # our domains as seen by others are a different population, and blending the
787 # two makes the headline pass rate answer neither question. They are told apart
788 # by who authored the report, which is the only signal the schema carries; an
789 # installation that has changed its org_name will read its older outgoing
790 # reports as received.
791 sub _origin_filter( $self, $kind ) {
792 return ( '', [] ) if 'all' eq ( $kind // '' );
793  
794 my $org = $self->config->{organization}{org_name};
795 return ( '', [] ) if !$org;
796  
797 my $op = 'outgoing' eq ( $kind // '' ) ? '=' : '<>';
798 return ( $self->grammar->and_arg( 'a.org_name', $op ), [$org] );
799 }
800  
801 # Reports are attributed to the day their window begins, matching the day
802 # bucketing in the grammar, so a window filter compares against begin alone.
803 sub _agg_where( $self, $args ) {
804 my ( $where, @params ) = ('');
805  
806 my @filters = (
807 [ since => 'r.begin', '>=' ],
808 [ until => 'r.begin', '<=' ],
809 [ from_domain => 'fd.domain', '=' ],
810 [ author => 'a.org_name', '=' ],
811 );
812  
813 foreach my $filter (@filters) {
814 my ( $arg, $column, $op ) = @$filter;
815 next if !defined $args->{$arg};
816 $where .= $self->grammar->and_arg( $column, $op );
817 push @params, $args->{$arg};
818 }
819  
820 if ( defined $args->{source_ip} ) {
821 $where .= $self->grammar->and_arg('rr.source_ip');
822 push @params,
823 $self->grammar->language eq 'postgresql'
824 ? $args->{source_ip}
825 : $self->any_inet_pton( $args->{source_ip} );
826 }
827  
828 $where .= $self->grammar->and_failing if $args->{failing_only};
829  
830 my ( $origin, $origin_params )
831 = $self->_origin_filter( $args->{reports} // 'received' );
832 $where .= $origin;
833 push @params, @$origin_params;
834  
835 return ( $where, \@params );
836 }
837  
838 # DBI hands back aggregates as strings; the JSON views need numbers.
839 sub _agg_numify($rows) {
840 foreach my $row (@$rows) {
841 foreach my $field ( @AGG_FIELDS,
842 qw/ day reporters first_seen last_seen reports / )
843 {
844 $row->{$field} += 0 if defined $row->{$field};
845 }
846 }
847 return $rows;
848 }
849  
850 sub _agg_ips_to_text( $self, $rows ) {
851 return $rows if $self->grammar->language eq 'postgresql';
852 foreach my $row (@$rows) {
853 $row->{source_ip} = $self->any_inet_ntop( $row->{source_ip} )
854 if $row->{source_ip};
855 }
856 return $rows;
857 }
858  
859 sub get_timeseries( $self, @args ) {
860 croak "invalid parameters" if @args % 2;
861 my %args = @args;
862  
863 my ( $where, $params ) = $self->_agg_where( \%args );
864 my $rows
865 = $self->query( $self->grammar->select_timeseries_query($where), $params );
866  
867 return { data => _agg_numify($rows) };
868 }
869  
870 # Totals are summed from the daily buckets rather than queried separately, so
871 # the tiles and the chart can never disagree.
872 sub _sum_timeseries( $self, %args ) {
873 my $days = $self->get_timeseries(%args)->{data};
874  
875 my %totals = map { $_ => 0 } @AGG_FIELDS;
876 foreach my $day (@$days) {
877 $totals{$_} += $day->{$_} || 0 for @AGG_FIELDS;
878 }
879 $totals{dmarc_pass} = $totals{messages} - $totals{aligned_none};
880 $totals{days} = scalar @$days;
881  
882 return \%totals;
883 }
884  
885 sub get_summary( $self, @args ) {
886 croak "invalid parameters" if @args % 2;
887 my %args = @args;
888  
889 my %summary = ( current => $self->_sum_timeseries(%args) );
890  
891 # An equal-length window immediately before this one, for the deltas.
892 if ( defined $args{since} && defined $args{until} ) {
893 my $span = $args{until} - $args{since};
894 $summary{previous} = $self->_sum_timeseries( %args,
895 since => $args{since} - $span - 1,
896 until => $args{since} - 1,
897 );
898 }
899  
900 return \%summary;
901 }
902  
903 sub get_sources( $self, @args ) {
904 croak "invalid parameters" if @args % 2;
905 my %args = @args;
906  
907 my ( $where, $params ) = $self->_agg_where( \%args );
908 my $sources
909 = $self->query( $self->grammar->select_sources_query($where), $params );
910 my ( $fwhere, $fparams )
911 = $self->_agg_where( { %args, failing_only => 1 } );
912 my $reasons = $self->query(
913 $self->grammar->select_source_reasons_query($fwhere), $fparams );
914  
915 $self->_agg_ips_to_text($sources);
916 $self->_agg_ips_to_text($reasons);
917 _agg_numify($sources);
918  
919 my %by_ip;
920 foreach my $reason (@$reasons) {
921 push @{ $by_ip{ $reason->{source_ip} } },
922 {
923 type => $reason->{type},
924 comment => $reason->{comment},
925 messages => ( $reason->{messages} || 0 ) + 0,
926 };
927 }
928  
929 foreach my $source (@$sources) {
930 $source->{reasons} = $by_ip{ $source->{source_ip} } || [];
931 $source->{bucket} = _classify_source($source);
932 }
933  
934 my $sort = $args{sort_col} && $args{sort_col} eq 'messages'
935 ? sub { $b->{messages} <=> $a->{messages} }
936 : sub { $b->{aligned_none} <=> $a->{aligned_none}
937 || $b->{messages} <=> $a->{messages} };
938 my @sorted = sort $sort @$sources;
939  
940 my $total = scalar @sorted;
941  
942 # Summed before paging: a caller showing each source's share of the window
943 # cannot derive the denominator from one page.
944 my $messages = 0;
945 $messages += $_->{messages} || 0 foreach @sorted;
946  
947 if ( $args{length} ) {
948 my $start = $args{start} || 0;
949 $start = 0 if $start < 0;
950 @sorted = splice @sorted, $start, $args{length};
951 }
952  
953 return {
954 recordsTotal => $total,
955 messagesTotal => $messages,
956 data => \@sorted,
957 };
958 }
959  
960 # Ranks a source by whether it needs attention. Telling a misconfigured sender
961 # apart from an unauthenticated one needs the auth detail tables, which the
962 # list query does not join; get_source_detail makes that distinction.
963 sub _classify_source($source) {
964 my $failing = $source->{aligned_none} || 0;
965 my $total = $source->{messages} || 0;
966  
967 return 'aligned' if !$failing;
968 return 'aligned' if $total && ( $failing / $total ) < $FAILING_TOLERANCE;
969  
970 # The largest single reason, not their sum: one record may carry several,
971 # and each row already counts that record's whole volume.
972 my $forwarded = 0;
973 foreach my $reason ( @{ $source->{reasons} } ) {
974 next if !_explains_forwarding($reason);
975 $forwarded = $reason->{messages} if $reason->{messages} > $forwarded;
976 }
977 return 'forwarded' if $forwarded >= $failing * $FORWARDING_SHARE;
978  
979 return 'failing';
980 }
981  
982 sub get_source_detail( $self, @args ) {
983 croak "invalid parameters" if @args % 2;
984 my %args = @args;
985 croak "missing source_ip" if !defined $args{source_ip};
986  
987 my ( $where, $params ) = $self->_agg_where( \%args );
988  
989 my $records
990 = $self->query( $self->grammar->select_source_detail_query($where), $params );
991 my $dkim
992 = $self->query( $self->grammar->select_source_dkim_query($where), $params );
993 my $spf
994 = $self->query( $self->grammar->select_source_spf_query($where), $params );
995  
996 _agg_numify($_) for $records, $dkim, $spf;
997  
998 my $failing = 0;
999 $failing += $_->{messages}
1000 foreach grep { 'pass' ne ( $_->{dkim} // '' ) && 'pass' ne ( $_->{spf} // '' ) }
1001 @$records;
1002  
1003 my ( $fwhere, $fparams )
1004 = $self->_agg_where( { %args, failing_only => 1 } );
1005 my $failing_auth
1006 = @{ $self->query( $self->grammar->select_source_dkim_query($fwhere),
1007 $fparams ) }
1008 || @{ $self->query( $self->grammar->select_source_spf_query($fwhere),
1009 $fparams ) };
1010  
1011 my $bucket
1012 = !$failing ? 'aligned'
1013 : $failing_auth ? 'broken'
1014 : 'unauthenticated';
1015  
1016 return {
1017 source_ip => $args{source_ip},
1018 bucket => $bucket,
1019 records => $records,
1020 dkim => $dkim,
1021 spf => $spf,
1022 };
1023 }
1024  
1025 sub get_report_domains( $self, @args ) {
1026 croak "invalid parameters" if @args % 2;
1027 my %args = @args;
1028  
1029 my ( $where, $params )
1030 = $self->_origin_filter( $args{reports} // 'received' );
1031 my $rows
1032 = $self->query( $self->grammar->select_report_domains_query($where),
1033 $params );
1034  
1035 return { data => _agg_numify($rows) };
1036 }
1037  
1038 sub grammar($self) {
1039 $self->db_connect();
1040 return $self->{grammar};
1041 }
1042  
1043 1;
1044  
1045 __END__
1046  
1047 =pod
1048  
1049 =head1 NAME
1050  
1051 Mail::DMARC::Report::Store::SQL - store and retrieve reports from a SQL RDBMS
1052  
1053 =head1 VERSION
1054  
1055 version 2.20260827
1056  
1057 =head1 DESCRIPTION
1058  
1059 Uses ANSI SQL syntax, keeping the SQL as portable as possible.
1060  
1061 DB engine specific features are to be avoided.
1062  
1063 =head1 SYPNOSIS
1064  
1065 Store and retrieve DMARC reports from SQL data store.
1066  
1067 Tested with SQLite, MySQL and PostgreSQL.
1068  
1069 =head1 AUTHORS
1070  
1071 =over 4
1072  
1073 =item *
1074  
1075 Matt Simerson <msimerson@cpan.org>
1076  
1077 =item *
1078  
1079 Davide Migliavacca <shari@cpan.org>
1080  
1081 =item *
1082  
1083 Marc Bradshaw <marc@marcbradshaw.net>
1084  
1085 =back
1086  
1087 =head1 COPYRIGHT AND LICENSE
1088  
1089 This software is copyright (c) 2026 by Matt Simerson.
1090  
1091 This is free software; you can redistribute it and/or modify it under
1092 the same terms as the Perl 5 programming language system itself.
1093  
1094 =cut