From 30d411c5782453456a5818f04fad113305300443 Mon Sep 17 00:00:00 2001
From: Byron Jones <bjones@mozilla.com>
Date: Mon, 10 Dec 2012 16:57:41 -0800
Subject: Bug 786777: add facility for direct read-only database access

---
 extensions/BMO/Extension.pm                        | 61 ++++++++++++++++++++++
 .../en/default/pages/query_database.html.tmpl      | 47 +++++++++++++++++
 2 files changed, 108 insertions(+)
 create mode 100644 extensions/BMO/template/en/default/pages/query_database.html.tmpl

diff --git a/extensions/BMO/Extension.pm b/extensions/BMO/Extension.pm
index 037cc6689..f3940cb26 100644
--- a/extensions/BMO/Extension.pm
+++ b/extensions/BMO/Extension.pm
@@ -40,6 +40,7 @@ use Scalar::Util qw(blessed);
 use Date::Parse;
 use DateTime;
 use Encode qw(find_encoding);
+use Sys::Syslog qw(:DEFAULT setlogsock);
 
 use Bugzilla::Extension::BMO::Constants;
 use Bugzilla::Extension::BMO::FakeBug;
@@ -185,6 +186,9 @@ sub page_before_template {
     elsif ($page eq 'release_tracking_report.html') {
         release_tracking_report($vars);
     }
+    elsif ($page eq 'query_database.html') {
+        query_database($vars);
+    }
 }
 
 sub _get_field_values_sort_key {
@@ -1012,4 +1016,61 @@ sub buglist_columns {
     };
 }
 
+sub query_database {
+    my ($vars) = @_;
+
+    # validate group membership
+    my $user = Bugzilla->user;
+    $user->in_group('query_database')
+        || ThrowUserError('auth_failure', { group  => 'query_database',
+                                            action => 'access',
+                                            object => 'query_database' });
+
+    # read query
+    my $input = Bugzilla->input_params;
+    my $query = $input->{query};
+    $vars->{query} = $query;
+
+    if ($query) {
+        trick_taint($query);
+        $vars->{executed} = 1;
+
+        # add limit if missing
+        if ($query !~ /\sLIMIT\s+\d+\s*$/si) {
+            $query .= ' LIMIT 1000';
+            $vars->{query} = $query;
+        }
+
+        # log query
+        setlogsock('unix');
+        openlog('apache', 'cons', 'pid', 'local4');
+        syslog('notice', sprintf("[db_query] %s %s", $user->login, $query));
+        closelog();
+
+        # connect to database and execute
+        # switching to the shadow db gives us a read-only connection
+        my $dbh = Bugzilla->switch_to_shadow_db();
+        my $sth;
+        eval {
+            $sth = $dbh->prepare($query);
+            $sth->execute();
+        };
+        if ($@) {
+            $vars->{sql_error} = $@;
+            return;
+        }
+
+        # build result
+        my $columns = $sth->{NAME};
+        my $rows;
+        while (my @row = $sth->fetchrow_array) {
+            push @$rows, \@row;
+        }
+
+        # return results
+        $vars->{columns} = $columns;
+        $vars->{rows} = $rows;
+    }
+}
+
 __PACKAGE__->NAME;
diff --git a/extensions/BMO/template/en/default/pages/query_database.html.tmpl b/extensions/BMO/template/en/default/pages/query_database.html.tmpl
new file mode 100644
index 000000000..97f5c0a25
--- /dev/null
+++ b/extensions/BMO/template/en/default/pages/query_database.html.tmpl
@@ -0,0 +1,47 @@
+[%# 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.
+  #%]
+
+[% INCLUDE global/header.html.tmpl
+  title = "Query Database"
+  style_urls = [ "extensions/BMO/web/styles/reports.css" ]
+%]
+
+<form method="post" action="page.cgi">
+<input type="hidden" name="id" value="query_database.html">
+<textarea cols="80" rows="10" name="query">[% query FILTER html %]</textarea><br>
+<input type="submit" value="Execute">
+</form>
+
+[% IF executed %]
+  <hr>
+
+  [% IF sql_error %]
+    <b>[% sql_error FILTER html %]</b>
+  [% ELSIF rows.size %]
+    <table border="0" cellspacing="0" id="report">
+      <tr>
+        [% FOREACH column = columns %]
+          <th>[% column FILTER html %]</th>
+        [% END %]
+      </tr>
+      [% FOREACH row = rows %]
+        [% tr_class = loop.count % 2 ? 'report_row_even' : 'report_row_odd' %]
+        <tr class="[% tr_class FILTER html %]">
+          [% FOREACH field = row %]
+            <td>[% field FILTER html %]</td>
+          [% END %]
+        </tr>
+      [% END %]
+    </table>
+  [% ELSE %]
+    <i>no results</i>
+  [% END %]
+
+[% END %]
+
+[% INCLUDE global/footer.html.tmpl %]
-- 
cgit v1.2.3-24-g4f1b