diff options
author | TechSneeze <dave@techsneeze.com> | 2022-07-21 21:02:26 +0300 |
---|---|---|
committer | GitHub <noreply@github.com> | 2022-07-21 21:02:26 +0300 |
commit | 80b1574d9c7a7362141cfa24e436a699ff1c800e (patch) | |
tree | f77d59811ebdb367811ca5cc826de918e4d42031 | |
parent | 3f175c55c870758ff1bff4abbc83c30bd049a0de (diff) | |
parent | 19c1ee3f55c19e8e25abadb790a4e9f70f92adc2 (diff) |
Merge pull request #80 from ekalin/pgsql
Support for PostgreSQL
-rw-r--r-- | README.md | 10 | ||||
-rw-r--r-- | dmarcts-report-viewer-common.php | 17 | ||||
-rw-r--r-- | dmarcts-report-viewer-config.php.sample | 2 | ||||
-rw-r--r-- | dmarcts-report-viewer-options.php | 20 | ||||
-rw-r--r-- | dmarcts-report-viewer-report-data.php | 34 | ||||
-rw-r--r-- | dmarcts-report-viewer-report-list.php | 26 | ||||
-rw-r--r-- | dmarcts-report-viewer.php | 32 |
7 files changed, 68 insertions, 73 deletions
@@ -1,5 +1,5 @@ # dmarcts-report-viewer -A PHP viewer for DMARC records that have been parsed by [John Levine's rddmarc script](http://www.taugh.com/rddmarc/) or the [dmarcts-report-parser.pl](https://github.com/techsneeze/dmarcts-report-parser) into a MySQL database. +A PHP viewer for DMARC records that have been parsed by [John Levine's rddmarc script](http://www.taugh.com/rddmarc/) or the [dmarcts-report-parser.pl](https://github.com/techsneeze/dmarcts-report-parser) into a MySQL or PostgreSQL database. ### Features * View a table of parsed reports @@ -9,7 +9,7 @@ A PHP viewer for DMARC records that have been parsed by [John Levine's rddmarc s * View DKIM/SPF details for each report in a table, with the same red/orange/yellow/green colour-coding * Sort report detail table by any column * View the raw XML of the report beside the report detail table -* Uses AJAX calls to the MySQL database; no external Javascript libraries are needed +* Uses AJAX calls to the database; no external Javascript libraries are needed ## Screenshots ### Screenshot: Initial Report Listing @@ -31,11 +31,11 @@ A PHP viewer for DMARC records that have been parsed by [John Levine's rddmarc s ### Requirements -* A MySQL database populated with data from [techsneeze.com's dmarcts-report-parser.pl](https://github.com/techsneeze/dmarcts-report-parser) script or [John Levine's rddmarc script](http://www.taugh.com/rddmarc/). +* A MySQL or PostgreSQL database populated with data from [techsneeze.com's dmarcts-report-parser.pl](https://github.com/techsneeze/dmarcts-report-parser) script or [John Levine's rddmarc script](http://www.taugh.com/rddmarc/). * A working webserver (apache, nginx, ...) with PHP -* Installed `php-mysql` and `php-xml` +* Installed `php-mysql` or `php-pgsql` and `php-xml` ### Download dmarcts-report-viewer: ``` @@ -57,6 +57,8 @@ cp dmarcts-report-viewer-config.php.sample dmarcts-report-viewer-config.php Next, edit these basic configuration options near the top of the `dmarcts-report-viewer-config.php` file with your specific information: ``` +// Supported types: mysql, pgsql. If unset, defaults to mysql +//$dbtype="mysql"; $dbhost="localhost"; $dbname="<dmarc-database-name>"; $dbuser="<dmarc-database-username>"; diff --git a/dmarcts-report-viewer-common.php b/dmarcts-report-viewer-common.php index 7fdc542..57811a1 100644 --- a/dmarcts-report-viewer-common.php +++ b/dmarcts-report-viewer-common.php @@ -415,3 +415,20 @@ function test_input($data) { return $data; } + +// This functions opens a connection to the database using PDO +function connect_db($dbtype, $dbhost, $dbport, $dbname, $dbuser, $dbpass) { + $dbtype = $dbtype ?: 'mysql'; + try { + $dbh = new PDO("$dbtype:host=$dbhost;port=$dbport;dbname=$dbname", $dbuser, $dbpass); + $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + $dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); + return $dbh; + } catch (PDOException $e) { + echo "Error: Failed to make a database connection<br />"; + echo "Error: " . $e->getMessage() . " "; + // Debug ONLY. This will expose database credentials when database connection fails + // echo "Database connection information: <br />dbhost: " . $dbhost . "<br />dbuser: " . $dbuser . "<br />dbpass: " . $dbpass . "<br />dbname: " . $dbname . "<br />dbport: " . $dbport . "<br />"; + exit; + } +} diff --git a/dmarcts-report-viewer-config.php.sample b/dmarcts-report-viewer-config.php.sample index c0e0771..340f602 100644 --- a/dmarcts-report-viewer-config.php.sample +++ b/dmarcts-report-viewer-config.php.sample @@ -4,6 +4,8 @@ // ### configuration ################################################## // #################################################################### +// Supported types: mysql, pgsql. If unset, defaults to mysql +//$dbtype="mysql"; $dbhost="localhost"; $dbname="dmarc"; $dbuser="dmarc"; diff --git a/dmarcts-report-viewer-options.php b/dmarcts-report-viewer-options.php index eb5a9aa..084477c 100644 --- a/dmarcts-report-viewer-options.php +++ b/dmarcts-report-viewer-options.php @@ -263,17 +263,9 @@ include "dmarcts-report-viewer-common.php"; configure(); -// Make a MySQL Connection using mysqli +// Make a DB Connection // -------------------------------------------------------------------------- -$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname, $dbport); -if ($mysqli->connect_errno) { - echo "Error: Failed to make a MySQL connection<br />"; - echo "Errno: " . $mysqli->connect_errno . " "; - echo "Error: " . $mysqli->connect_error . " "; -// Debug ONLY. This will expose database credentials when database connection fails -// echo "Database connection information: <br />dbhost: " . $dbhost . "<br />dbuser: " . $dbuser . "<br />dbpass: " . $dbpass . "<br />dbname: " . $dbname . "<br />dbport: " . $dbport . "<br />"; - exit; -} +$dbh = connect_db($dbtype, $dbhost, $dbport, $dbname, $dbuser, $dbpass); // Get all css files in dmartcts directory @@ -300,10 +292,10 @@ FROM report ORDER BY domain"; -$query = $mysqli->query($sql) or die("Query failed: ".$mysqli->error." (Error #" .$mysqli->errno.")"); +$query = $dbh->query($sql); $domains['all'] = "[all]"; -while($row = $query->fetch_assoc()) { +foreach($query as $row) { $domains[$row['domain']] = $row['domain']; } @@ -333,9 +325,9 @@ foreach($dmarc_result as $key => $value) { } -$query = $mysqli->query($sql) or die("Query failed: ".$mysqli->error." (Error #" .$mysqli->errno.")"); +$query = $dbh->query($sql); $orgs['all'] = "[all]"; -while($row = $query->fetch_assoc()) { +foreach($query as $row) { $orgs[$row['org']] = $row['org']; } diff --git a/dmarcts-report-viewer-report-data.php b/dmarcts-report-viewer-report-data.php index 5aae830..09ea80e 100644 --- a/dmarcts-report-viewer-report-data.php +++ b/dmarcts-report-viewer-report-data.php @@ -96,7 +96,8 @@ function tmpl_reportData($reportnumber, $reports, $host_lookup = 1) { $reportdata[] = " </thead>"; $reportdata[] = " <tbody>"; - global $mysqli; + global $dbtype; + global $dbh; $sql = " SELECT @@ -133,11 +134,14 @@ ORDER BY ip ASC "; - $query = $mysqli->query($sql) or die("Query failed: ".$mysqli->error." (Error #" .$mysqli->errno.")"); - while($row = $query->fetch_assoc()) { + $query = $dbh->query($sql); + foreach($query as $row) { if ( $row['ip'] ) { $ip = long2ip($row['ip']); } elseif ( $row['ip6'] ) { + if ( $dbtype == 'pgsql') { + $row['ip6'] = stream_get_contents($row['ip6']); + } $ip = inet_ntop($row['ip6']); } else { $ip = "-"; @@ -181,7 +185,7 @@ ORDER BY function formatXML($raw_xml, $reportnumber) { - global $mysqli; + global $dbh; $out = ""; $html = ""; @@ -196,9 +200,9 @@ function formatXML($raw_xml, $reportnumber) { serial = $reportnumber; "; - $query = $mysqli->query($sql) or die("Query failed: ".$mysqli->error." (Error #" .$mysqli->errno.")"); + $query = $dbh->query($sql); - while($row = $query->fetch_assoc()) { + foreach($query as $row) { $id_min = $row['id_min']; $id_max = $row['id_max']; } @@ -296,17 +300,9 @@ if( $dmarc_select == "all" ) { // Debug //echo "<br />D=$dom_select <br /> O=$org_select <br />"; -// Make a MySQL Connection using mysqli +// Make a DB Connection // -------------------------------------------------------------------------- -$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname, $dbport); -if ($mysqli->connect_errno) { - echo "Error: Failed to make a MySQL connection, here is why: \n"; - echo "Errno: " . $mysqli->connect_errno . "\n"; - echo "Error: " . $mysqli->connect_error . "\n"; -// Debug ONLY. This will expose database credentials when database connection fails -// echo "Database connection information: <br />dbhost: " . $dbhost . "<br />dbuser: " . $dbuser . "<br />dbpass: " . $dbpass . "<br />dbname: " . $dbname . "<br />dbport: " . $dbport . "<br />"; - exit; -} +$dbh = connect_db($dbtype, $dbhost, $dbport, $dbname, $dbuser, $dbpass); // // Get allowed reports and cache them - using serial as key // -------------------------------------------------------------------------- @@ -351,14 +347,14 @@ SELECT FROM report WHERE - serial = " . $mysqli->real_escape_string($reportid) + serial = " . $dbh->quote($reportid) ; // Debug // echo "<br /><b>Data Report sql:</b> $sql<br />"; -$query = $mysqli->query($sql) or die("Query failed: ".$mysqli->error." (Error #" .$mysqli->errno.")"); -while($row = $query->fetch_assoc()) { +$query = $dbh->query($sql); +foreach($query as $row) { if (true) { //add data by serial $reports[$row['serial']] = $row; diff --git a/dmarcts-report-viewer-report-list.php b/dmarcts-report-viewer-report-list.php index d07b107..806ed8c 100644 --- a/dmarcts-report-viewer-report-list.php +++ b/dmarcts-report-viewer-report-list.php @@ -176,17 +176,9 @@ if(isset($_GET['rptstat'])){ // echo "<br />D=$dom_select <br /> O=$org_select <br />"; // echo "<br />DMARC=$dmarc_select<br />"; -// Make a MySQL Connection using mysqli +// Make a DB Connection // -------------------------------------------------------------------------- -$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname, $dbport); - -if ($mysqli->connect_errno) { - echo "Errno: " . $mysqli->connect_errno . " "; - echo "Error: " . $mysqli->connect_error . " "; -// Debug ONLY. This will expose database credentials when database connection fails -// echo "Database connection information: <br />dbhost: " . $dbhost . "<br />dbuser: " . $dbuser . "<br />dbpass: " . $dbpass . "<br />dbname: " . $dbname . "<br />dbport: " . $dbport . "<br />"; - exit; -} +$dbh = connect_db($dbtype, $dbhost, $dbport, $dbname, $dbuser, $dbpass); // Get allowed reports and cache them - using serial as key // -------------------------------------------------------------------------- @@ -227,19 +219,19 @@ switch ($dmarc_select) { // Report Status // -------------------------------------------------------------------------- if ( $report_status != "all" && $report_status != "" ) { - $where .= ( $where <> '' ? " AND" : " WHERE" ) . " " . $mysqli->real_escape_string($dmarc_result[$report_status]['status_sql_where']); + $where .= ( $where <> '' ? " AND" : " WHERE" ) . " " . $dmarc_result[$report_status]['status_sql_where']; } // Domains // -------------------------------------------------------------------------- if( $dom_select <> '' ) { - $where .= ( $where <> '' ? " AND" : " WHERE" ) . " domain='" . $mysqli->real_escape_string($dom_select) . "'"; + $where .= ( $where <> '' ? " AND" : " WHERE" ) . " domain=" . $dbh->quote($dom_select); } // Organisations // -------------------------------------------------------------------------- if( $org_select <> '' ) { - $where .= ( $where <> '' ? " AND" : " WHERE" ) . " org='" . $mysqli->real_escape_string($org_select) . "'"; + $where .= ( $where <> '' ? " AND" : " WHERE" ) . " org=" . $dbh->quote($org_select); } // Periods @@ -247,7 +239,9 @@ if( $org_select <> '' ) { if( $per_select <> '' ) { $ye = substr( $per_select, 0, 4) + 0; $mo = substr( $per_select, 5, 2) + 0; - $where .= ( $where <> '' ? " AND" : " WHERE" ) . " ((year(mindate) = $ye AND month(mindate) = $mo) OR (year(maxdate) = $ye AND month(maxdate) = $mo)) "; + $where .= ( $where <> '' ? " AND" : " WHERE" ) + . " ((extract(year from mindate) = $ye AND extract(month from mindate) = $mo) " + . " OR (extract(year from maxdate) = $ye AND extract(month from maxdate) = $mo)) "; } // Include the rcount via left join, so we do not have to make an sql query @@ -349,8 +343,8 @@ ORDER BY // echo "<br /><b>Data List sql:</b> $sql<br />"; // echo "<br />per_select = " . urlencode($per_select) . "<br />"; -$query = $mysqli->query($sql) or die("Query failed: ".$mysqli->error." (Error #" .$mysqli->errno.")"); -while($row = $query->fetch_assoc()) { +$query = $dbh->query($sql); +foreach($query as $row) { if (true) { //add data by serial $reports[$row['serial']] = $row; diff --git a/dmarcts-report-viewer.php b/dmarcts-report-viewer.php index e6e32e4..7b42cc6 100644 --- a/dmarcts-report-viewer.php +++ b/dmarcts-report-viewer.php @@ -233,17 +233,9 @@ configure(); setcookie("dmarcts-options-tmp", "", time() - 3600, "/"); -// Make a MySQL Connection using mysqli +// Make a DB Connection // -------------------------------------------------------------------------- -$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname, $dbport); -if ($mysqli->connect_errno) { - echo "Error: Failed to make a MySQL connection<br />"; - echo "Errno: " . $mysqli->connect_errno . " "; - echo "Error: " . $mysqli->connect_error . " "; -// Debug ONLY. This will expose database credentials when database connection fails -// echo "Database connection information: <br />dbhost: " . $dbhost . "<br />dbuser: " . $dbuser . "<br />dbpass: " . $dbpass . "<br />dbname: " . $dbname . "<br />dbport: " . $dbport . "<br />"; - exit; -} +$dbh = connect_db($dbtype, $dbhost, $dbport, $dbname, $dbuser, $dbpass); // Get all domains reported @@ -257,9 +249,9 @@ ORDER BY domain "; -$query = $mysqli->query($sql) or die("Query failed: ".$mysqli->error." (Error #" .$mysqli->errno.")"); +$query = $dbh->query($sql); -while($row = $query->fetch_assoc()) { +foreach($query as $row) { $domains[] = $row['domain']; } @@ -274,9 +266,9 @@ ORDER BY org "; -$query = $mysqli->query($sql) or die("Query failed: ".$mysqli->error." (Error #" .$mysqli->errno.")"); +$query = $dbh->query($sql); -while($row = $query->fetch_assoc()) { +foreach($query as $row) { $orgs[] = $row['org']; } @@ -285,16 +277,16 @@ while($row = $query->fetch_assoc()) { $sql = " ( SELECT - YEAR(mindate) AS year, - MONTH(mindate) AS month + EXTRACT(YEAR FROM mindate) AS year, + EXTRACT(MONTH FROM mindate) AS month FROM report ) UNION ( SELECT - YEAR(maxdate) AS year, - MONTH(maxdate) AS month + EXTRACT(YEAR FROM mindate) AS year, + EXTRACT(MONTH FROM mindate) AS month FROM report ) @@ -303,9 +295,9 @@ ORDER BY month DESC "; -$query = $mysqli->query($sql) or die("Query failed: ".$mysqli->error." (Error #" .$mysqli->errno.")"); +$query = $dbh->query($sql); -while($row = $query->fetch_assoc()) { +foreach($query as $row) { $periods[] = sprintf( "%'.04d-%'.02d", $row['year'], $row['month'] ); } |