From a91453b19c462929b3ab77927b0d0a6807558b92 Mon Sep 17 00:00:00 2001 From: Israel Madueme Date: Mon, 10 Sep 2018 12:34:56 -0400 Subject: Bug 1479466 - Add Security Bugs Report Adds the security bugs report with open count and median age open of sec-critical and sec-high bugs. --- Bugzilla/Config/Reports.pm | 37 +++ Bugzilla/Report/SecurityRisk.pm | 318 +++++++++++++++++++++ scripts/secbugsreport.pl | 83 ++++++ t/security-risk.t | 156 ++++++++++ template/en/default/admin/params/reports.html.tmpl | 20 ++ .../default/reports/email/security-risk.html.tmpl | 95 ++++++ 6 files changed, 709 insertions(+) create mode 100644 Bugzilla/Config/Reports.pm create mode 100644 Bugzilla/Report/SecurityRisk.pm create mode 100644 scripts/secbugsreport.pl create mode 100644 t/security-risk.t create mode 100644 template/en/default/admin/params/reports.html.tmpl create mode 100644 template/en/default/reports/email/security-risk.html.tmpl diff --git a/Bugzilla/Config/Reports.pm b/Bugzilla/Config/Reports.pm new file mode 100644 index 000000000..26c5aad57 --- /dev/null +++ b/Bugzilla/Config/Reports.pm @@ -0,0 +1,37 @@ +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# This Source Code Form is "Incompatible With Secondary Licenses", as +# defined by the Mozilla Public License, v. 2.0. + +package Bugzilla::Config::Reports; + +use 5.10.1; +use strict; +use warnings; + +use Bugzilla::Config::Common; + +our $sortkey = 1100; + +sub get_param_list { + my $class = shift; + my @param_list = ( + { + name => 'report_secbugs_active', + type => 'b', + default => 1, + }, + { + name => 'report_secbugs_emails', + type => 't', + default => 'bugzilla-admin@mozilla.org' + }, + { + name => 'report_secbugs_products', + type => 'l', + default => '[]' + }, + ); +} diff --git a/Bugzilla/Report/SecurityRisk.pm b/Bugzilla/Report/SecurityRisk.pm new file mode 100644 index 000000000..1b62d476c --- /dev/null +++ b/Bugzilla/Report/SecurityRisk.pm @@ -0,0 +1,318 @@ +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# This Source Code Form is "Incompatible With Secondary Licenses", as +# defined by the Mozilla Public License, v. 2.0. + +package Bugzilla::Report::SecurityRisk; + +use 5.10.1; + +use Bugzilla; +use Bugzilla::Error; +use Bugzilla::Status qw(is_open_state); +use Bugzilla::Util qw(datetime_from); + +use DateTime; +use List::Util qw(any first sum); +use Moo; +use MooX::StrictConstructor; +use POSIX qw(ceil); +use Types::Standard qw(Num Int Bool Str HashRef ArrayRef CodeRef Map Dict Enum); +use Type::Utils; + +my $DateTime = class_type { class => 'DateTime' }; + +has 'start_date' => ( + is => 'ro', + required => 1, + isa => $DateTime, +); + +has 'end_date' => ( + is => 'ro', + required => 1, + isa => $DateTime, +); + +has 'products' => ( + is => 'ro', + required => 1, + isa => ArrayRef [Str], +); + +has 'sec_keywords' => ( + is => 'ro', + required => 1, + isa => ArrayRef [Str], +); + +has 'initial_bug_ids' => ( + is => 'lazy', + isa => ArrayRef [Int], +); + +has 'initial_bugs' => ( + is => 'lazy', + isa => HashRef [ + Dict [ + id => Int, + product => Str, + sec_level => Str, + is_open => Bool, + created_at => $DateTime, + ], + ], +); + +has 'check_open_state' => ( + is => 'ro', + isa => CodeRef, + default => sub { return \&is_open_state; }, +); + +has 'events' => ( + is => 'lazy', + isa => ArrayRef [ + Dict [ + bug_id => Int, + bug_when => $DateTime, + field_name => Enum [qw(bug_status keywords)], + removed => Str, + added => Str, + ], + ], +); + +has 'results' => ( + is => 'lazy', + isa => ArrayRef [ + Dict [ + date => $DateTime, + bugs_by_product => HashRef [ + Dict [ + open => ArrayRef [Int], + closed => ArrayRef [Int], + median_age_open => Num + ] + ], + bugs_by_sec_keyword => HashRef [ + Dict [ + open => ArrayRef [Int], + closed => ArrayRef [Int], + median_age_open => Num + ] + ], + ], + ], +); + +sub _build_initial_bug_ids { + # TODO: Handle changes in product (e.g. gravyarding) by searching the events table + # for changes to the 'product' field where one of $self->products is found in + # the 'removed' field, add the related bug id to the list of initial bugs. + my ($self) = @_; + my $dbh = Bugzilla->dbh; + my $products = join ', ', map { $dbh->quote($_) } @{ $self->products }; + my $sec_keywords = join ', ', map { $dbh->quote($_) } @{ $self->sec_keywords }; + my $query = qq{ + SELECT + bug_id + FROM + bugs AS bug + JOIN products AS product ON bug.product_id = product.id + JOIN components AS component ON bug.component_id = component.id + JOIN keywords USING (bug_id) + JOIN keyworddefs AS keyword ON keyword.id = keywords.keywordid + WHERE + keyword.name IN ($sec_keywords) + AND product.name IN ($products) + }; + return Bugzilla->dbh->selectcol_arrayref($query); +} + +sub _build_initial_bugs { + my ($self) = @_; + my $bugs = {}; + my $bugs_list = Bugzilla::Bug->new_from_list( $self->initial_bug_ids ); + for my $bug (@$bugs_list) { + $bugs->{ $bug->id } = { + id => $bug->id, + product => $bug->product, + sec_level => ( + # Select the first keyword matching one of the target keywords + # (of which there _should_ only be one found anyway). + first { + my $x = $_; + grep { lc($_) eq lc( $x->name ) } @{ $self->sec_keywords } + } + @{ $bug->keyword_objects } + )->name, + is_open => $self->check_open_state->( $bug->status->name ), + created_at => datetime_from( $bug->creation_ts ), + }; + } + return $bugs; +} + +sub _build_events { + my ($self) = @_; + return [] if !(@{$self->initial_bug_ids}); + my $bug_ids = join ', ', @{ $self->initial_bug_ids }; + my $start_date = $self->start_date->ymd('-'); + my $query = qq{ + SELECT + bug_id, + bug_when, + field.name AS field_name, + CONCAT(removed) AS removed, + CONCAT(added) AS added + FROM + bugs_activity + JOIN fielddefs AS field ON fieldid = field.id + JOIN bugs AS bug USING (bug_id) + WHERE + bug_id IN ($bug_ids) + AND field.name IN ('keywords' , 'bug_status') + AND bug_when >= '$start_date 00:00:00' + GROUP BY bug_id , bug_when , field.name + }; + my $result = Bugzilla->dbh->selectall_hashref( $query, 'bug_id' ); + my @events = values %$result; + foreach my $event (@events) { + $event->{bug_when} = datetime_from( $event->{bug_when} ); + } + + # We sort by reverse chronological order instead of ORDER BY + # since values %hash doesn't guareentee any order. + @events = sort { $b->{bug_when} cmp $a->{bug_when} } @events; + return \@events; +} + +sub _build_results { + my ($self) = @_; + my $e = 0; + my $bugs = $self->initial_bugs; + my @results = (); + + # We must generate a report for each week in the target time interval, regardless of + # whether anything changed. The for loop here ensures that we do so. + for ( my $report_date = $self->end_date; $report_date >= $self->start_date; $report_date->subtract( weeks => 1 ) ) { + # We rewind events while there are still events existing which occured after the start + # of the report week. The bugs will reflect a snapshot of how they were at the start of the week. + # $self->events is ordered reverse chronologically, so the end of the array is the earliest event. + while ( $e < scalar @{ $self->events } + && ( @{ $self->events }[$e] )->{bug_when} > $report_date ) + { + my $event = @{ $self->events }[$e]; + my $bug = $bugs->{ $event->{bug_id} }; + + # Undo bug status changes + if ( $event->{field_name} eq 'bug_status' ) { + $bug->{is_open} = $self->check_open_state->( $event->{removed} ); + } + + # Undo keyword changes + if ( $event->{field_name} eq 'keywords' ) { + my $bug_sec_level = $bug->{sec_level}; + if ( $event->{added} =~ /\b\Q$bug_sec_level\E\b/ ) { + # If the currently set sec level was added in this event, remove it. + $bug->{sec_level} = undef; + } + if ( $event->{removed} ) { + # If a target sec keyword was removed, add the first one back. + my $removed_sec = first { + $event->{removed} =~ /\b\Q$_\E\b/ + } + @{ $self->sec_keywords }; + $bug->{sec_level} = $removed_sec if ($removed_sec); + } + } + + $e++; + } + + # Remove uncreated bugs + foreach my $bug_key ( keys %$bugs ) { + if ( $bugs->{$bug_key}->{created_at} > $report_date ) { + delete $bugs->{$bug_key}; + } + } + + # Report! + my $date_snapshot = $report_date->clone(); + my @bugs_snapshot = values %$bugs; + unshift @results, + { + date => $date_snapshot, + bugs_by_product => $self->_bugs_by_product( $date_snapshot, @bugs_snapshot ), + bugs_by_sec_keyword => $self->_bugs_by_sec_keyword( $date_snapshot, @bugs_snapshot ), + }; + } + + return \@results; +} + +sub _bugs_by_product { + my ( $self, $report_date, @bugs ) = @_; + my $result = {}; + my $groups = {}; + foreach my $product ( @{ $self->products } ) { + $groups->{$product} = []; + } + foreach my $bug (@bugs) { + # We skip over bugs with no sec level which can happen during event rewinding. + if ( $bug->{sec_level} ) { + push @{ $groups->{ $bug->{product} } }, $bug; + } + } + foreach my $product ( @{ $self->products } ) { + my @open = map { $_->{id} } grep { ( $_->{is_open} ) } @{ $groups->{$product} }; + my @closed = map { $_->{id} } grep { !( $_->{is_open} ) } @{ $groups->{$product} }; + my @ages = map { $_->{created_at}->subtract_datetime_absolute($report_date)->seconds / 86_400; } + grep { ( $_->{is_open} ) } @{ $groups->{$product} }; + $result->{$product} = { + open => \@open, + closed => \@closed, + median_age_open => @ages ? _median(@ages) : 0, + }; + } + + return $result; +} + +sub _bugs_by_sec_keyword { + my ( $self, $report_date, @bugs ) = @_; + my $result = {}; + my $groups = {}; + foreach my $sec_keyword ( @{ $self->sec_keywords } ) { + $groups->{$sec_keyword} = []; + } + foreach my $bug (@bugs) { + # We skip over bugs with no sec level which can happen during event rewinding. + if ( $bug->{sec_level} ) { + push @{ $groups->{ $bug->{sec_level} } }, $bug; + } + } + foreach my $sec_keyword ( @{ $self->sec_keywords } ) { + my @open = map { $_->{id} } grep { ( $_->{is_open} ) } @{ $groups->{$sec_keyword} }; + my @closed = map { $_->{id} } grep { !( $_->{is_open} ) } @{ $groups->{$sec_keyword} }; + my @ages = map { $_->{created_at}->subtract_datetime_absolute($report_date)->seconds / 86_400 } + grep { ( $_->{is_open} ) } @{ $groups->{$sec_keyword} }; + $result->{$sec_keyword} = { + open => \@open, + closed => \@closed, + median_age_open => @ages ? _median(@ages) : 0, + }; + } + + return $result; +} + +sub _median { + # From tlm @ https://www.perlmonks.org/?node_id=474564. Jul 14, 2005 + return sum( ( sort { $a <=> $b } @_ )[ int( $#_ / 2 ), ceil( $#_ / 2 ) ] ) / 2; +} + +1; diff --git a/scripts/secbugsreport.pl b/scripts/secbugsreport.pl new file mode 100644 index 000000000..ae0639e20 --- /dev/null +++ b/scripts/secbugsreport.pl @@ -0,0 +1,83 @@ +#!/usr/bin/perl + +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# This Source Code Form is "Incompatible With Secondary Licenses", as +# defined by the Mozilla Public License, v. 2.0. +# +# Usage secbugsreport.pl YYYY MM DD, e.g. secbugsreport.pl $(date +'%Y %m %d') + +use 5.10.1; +use strict; +use warnings; + +use lib qw(. lib local/lib/perl5); + +use Bugzilla; +use Bugzilla::Component; +use Bugzilla::Constants; +use Bugzilla::Error; +use Bugzilla::Mailer; +use Bugzilla::Report::SecurityRisk; + +use DateTime; +use URI; +use JSON::MaybeXS; + +BEGIN { Bugzilla->extensions } +Bugzilla->usage_mode(USAGE_MODE_CMDLINE); + +exit 0 unless Bugzilla->params->{report_secbugs_active}; +exit 0 unless defined $ARGV[0] && defined $ARGV[1] && defined $ARGV[2]; + +my $html; +my $template = Bugzilla->template(); +my $end_date = DateTime->new( year => $ARGV[0], month => $ARGV[1], day => $ARGV[2] ); +my $start_date = $end_date->clone()->subtract( months => 6 ); +my $report_week = $end_date->ymd('-'); +my $products = decode_json( Bugzilla->params->{report_secbugs_products} ); +my $sec_keywords = [ 'sec-critical', 'sec-high' ]; +my $report = Bugzilla::Report::SecurityRisk->new( + start_date => $start_date, + end_date => $end_date, + products => $products, + sec_keywords => $sec_keywords +); +my $vars = { + urlbase => Bugzilla->localconfig->{urlbase}, + report_week => $report_week, + products => $products, + sec_keywords => $sec_keywords, + results => $report->results, + build_bugs_link => \&build_bugs_link, +}; + +$template->process( 'reports/email/security-risk.html.tmpl', $vars, \$html ) + or ThrowTemplateError( $template->error() ); + +# For now, only send HTML email. +my $email = Email::MIME->create( + header_str => [ + From => Bugzilla->params->{'mailfrom'}, + To => Bugzilla->params->{report_secbugs_emails}, + Subject => "Security Bugs Report for $report_week" + ], + attributes => { + content_type => 'text/html', + charset => 'UTF-8', + encoding => 'quoted-printable', + }, + body_str => $html, +); + +MessageToMTA($email); + +sub build_bugs_link { + my ( $arr, $product ) = @_; + my $uri = URI->new( Bugzilla->localconfig->{urlbase} . 'buglist.cgi' ); + $uri->query_param( bug_id => ( join ',', @$arr ) ); + $uri->query_param( product => $product ) if $product; + return $uri->as_string; +} diff --git a/t/security-risk.t b/t/security-risk.t new file mode 100644 index 000000000..520953bc0 --- /dev/null +++ b/t/security-risk.t @@ -0,0 +1,156 @@ +#!/usr/bin/perl +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# This Source Code Form is "Incompatible With Secondary Licenses", as +# defined by the Mozilla Public License, v. 2.0. +use strict; +use warnings; +use 5.10.1; +use lib qw( . lib local/lib/perl5 ); +use Bugzilla; + +BEGIN { Bugzilla->extensions }; + +use Test::More; +use Test2::Tools::Mock; +use Try::Tiny; + +use ok 'Bugzilla::Report::SecurityRisk'; +can_ok('Bugzilla::Report::SecurityRisk', qw(new results)); + +sub check_open_state_mock { + my ($state) = @_; + return grep { /^$state$/ } qw(UNCOMFIRMED NEW ASSIGNED REOPENED); +} + +try { + use Bugzilla::Report::SecurityRisk; + my $report = Bugzilla::Report::SecurityRisk->new( + start_date => DateTime->new( year => 2000, month => 1, day => 9 ), + end_date => DateTime->new( year => 2000, month => 1, day => 16 ), + products => [ 'Firefox', 'Core' ], + sec_keywords => [ 'sec-critical', 'sec-high' ], + check_open_state => \&check_open_state_mock, + initial_bug_ids => [ 1, 2, 3, 4 ], + initial_bugs => { + 1 => { + id => 1, + product => 'Firefox', + sec_level => 'sec-high', + is_open => 0, + created_at => DateTime->new( year => 2000, month => 1, day => 1 ), + }, + 2 => { + id => 2, + product => 'Core', + sec_level => 'sec-critical', + is_open => 0, + created_at => DateTime->new( year => 2000, month => 1, day => 1 ), + }, + 3 => { + id => 3, + product => 'Core', + sec_level => 'sec-high', + is_open => 1, + created_at => DateTime->new( year => 2000, month => 1, day => 5 ), + }, + 4 => { + id => 4, + product => 'Firefox', + sec_level => 'sec-critical', + is_open => 1, + created_at => DateTime->new( year => 2000, month => 1, day => 10 ), + }, + }, + events => [ + # Canned event's should be in reverse chronological order. + { + bug_id => 2, + bug_when => DateTime->new( year => 2000, month => 1, day => 14 ), + field_name => 'keywords', + removed => '', + added => 'sec-critical', + + }, + { + bug_id => 1, + bug_when => DateTime->new( year => 2000, month => 1, day => 12 ), + field_name => 'bug_status', + removed => 'ASSIGNED', + added => 'RESOLVED', + }, + ], + ); + my $actual_results = $report->results; + my $expected_results = [ + { + date => DateTime->new( year => 2000, month => 1, day => 9 ), + bugs_by_product => { + 'Firefox' => { + # Rewind the event that caused 1 to close. + open => [1], + closed => [], + median_age_open => 8 + }, + 'Core' => { + # 2 wasn't a sec-critical bug on the report date. + open => [3], + closed => [], + median_age_open => 4 + } + }, + bugs_by_sec_keyword => { + 'sec-critical' => { + # 2 wasn't a sec-crtical bug and 4 wasn't created yet on the report date. + open => [], + closed => [], + median_age_open => 0 + }, + 'sec-high' => { + # Rewind the event that caused 1 to close. + open => [ 1, 3 ], + closed => [], + median_age_open => 6 + } + }, + }, + { # The report on 2000-01-16 matches the state of initial_bugs. + date => DateTime->new( year => 2000, month => 1, day => 16 ), + bugs_by_product => { + 'Firefox' => { + open => [4], + closed => [1], + median_age_open => 6 + }, + 'Core' => { + open => [3], + closed => [2], + median_age_open => 11 + } + }, + bugs_by_sec_keyword => { + 'sec-critical' => { + open => [4], + closed => [2], + median_age_open => 6 + }, + 'sec-high' => { + open => [3], + closed => [1], + median_age_open => 11 + } + }, + }, + ]; + + is_deeply($actual_results, $expected_results, 'Report results are accurate'); + +} +catch { + fail('got an exception during main part of test'); + diag($_); +}; + +done_testing; diff --git a/template/en/default/admin/params/reports.html.tmpl b/template/en/default/admin/params/reports.html.tmpl new file mode 100644 index 000000000..79b6af35d --- /dev/null +++ b/template/en/default/admin/params/reports.html.tmpl @@ -0,0 +1,20 @@ +[%# This Source Code Form is subject to the terms of the Mozilla Public + # License, v. 2.0. If a copy of the MPL was not distributed with this + # file, You can obtain one at http://mozilla.org/MPL/2.0/. + # + # This Source Code Form is "Incompatible With Secondary Licenses", as + # defined by the Mozilla Public License, v. 2.0. + #%] +[% + title = "Reports" + desc = "Configure reporting parameters" +%] + +[% param_descs = { + report_secbugs_active => "Enable or disable the security $terms.bugs report feature." + report_secbugs_emails => + "Comma delimited list of the email addresses that the security $terms.bugs report will be sent to.", + report_secbugs_products => + "JSON array of the products the security $terms.bugs report will report on. e.g [\"Prod1\", \"Prod2\"]", + } +%] diff --git a/template/en/default/reports/email/security-risk.html.tmpl b/template/en/default/reports/email/security-risk.html.tmpl new file mode 100644 index 000000000..0fca42e05 --- /dev/null +++ b/template/en/default/reports/email/security-risk.html.tmpl @@ -0,0 +1,95 @@ +[%# This Source Code Form is subject to the terms of the Mozilla Public + # License, v. 2.0. If a copy of the MPL was not distributed with this + # file, You can obtain one at http://mozilla.org/MPL/2.0/. + # + # This Source Code Form is "Incompatible With Secondary Licenses", as + # defined by the Mozilla Public License, v. 2.0. + #%] + +[% PROCESS global/variables.none.tmpl %] + + + + + Security [% terms.Bugs %] Report for the week of [% report_week FILTER html %] + + + +

Security [% terms.Bugs %] Report for the week of [% report_week FILTER html %]

+

To narrow down open [% terms.bugs %] click on the link and at the bottom of the search results use the 'Edit Search' functionality to filter by component and so on. +This will filter only the open [% terms.bugs %] counted in the report (as long as you do not modify the '[% terms.Bugs %] numbered' section of the search). +

+ +

[% terms.Bugs %] By Severity

+ + + + [% FOREACH keyword IN sec_keywords %] + + [% END %] + + + + [% FOREACH keyword IN sec_keywords %] + + + [% END %] + + [% FOREACH result IN results.reverse %] + + + [% FOREACH keyword IN sec_keywords %] + + + [% END %] + + [% END %] +
[% keyword FILTER html %]
Open CountMedian Days Open
[% result.date.ymd('-') FILTER html %] + [% IF result.bugs_by_sec_keyword.$keyword.open.size %] + + [% result.bugs_by_sec_keyword.$keyword.open.size FILTER html %] + + [% ELSE %] + [% result.bugs_by_sec_keyword.$keyword.open.size FILTER html %] + [% END %] + + [% result.bugs_by_sec_keyword.$keyword.median_age_open FILTER format("%.2f") FILTER html %] +
+ +

Sec-Critical + Sec-High [% terms.Bugs %] by Product

+ + + + [% FOREACH product IN products %] + + [% END %] + + + + [% FOREACH product IN products %] + + + [% END %] + + [% FOREACH result IN results.reverse %] + + + [% FOREACH product IN products %] + + + [% END %] + + [% END %] +
[% product FILTER html %]
Open CountMedian Days Open
[% result.date.ymd('-') FILTER html %] + [% IF result.bugs_by_product.$product.open.size %] + + [% result.bugs_by_product.$product.open.size FILTER html %] + + [% ELSE %] + [% result.bugs_by_product.$product.open.size FILTER html %] + [% END %] + + [% result.bugs_by_product.$product.median_age_open FILTER format("%.2f") FILTER html %] +
+ + -- cgit v1.2.3-24-g4f1b