From 90feb3a7b65b840285f0fb7ec46ff47e6480d8ec Mon Sep 17 00:00:00 2001 From: Eduardo M KALINOWSKI Date: Mon, 6 Jun 2022 13:22:15 -0300 Subject: Support for other database types PDO is used instead of mysqli for database connection. A variable $dbtype specifies the driver to be used. If not specified, it defaults to mysql. --- dmarcts-report-viewer-common.php | 17 +++++++++++++++++ dmarcts-report-viewer-config.php.sample | 2 ++ dmarcts-report-viewer-options.php | 20 ++++++-------------- dmarcts-report-viewer-report-data.php | 30 +++++++++++------------------- dmarcts-report-viewer-report-list.php | 22 +++++++--------------- dmarcts-report-viewer.php | 24 ++++++++---------------- 6 files changed, 51 insertions(+), 64 deletions(-) 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
"; + echo "Error: " . $e->getMessage() . " "; + // Debug ONLY. This will expose database credentials when database connection fails + // echo "Database connection information:
dbhost: " . $dbhost . "
dbuser: " . $dbuser . "
dbpass: " . $dbpass . "
dbname: " . $dbname . "
dbport: " . $dbport . "
"; + 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
"; - 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:
dbhost: " . $dbhost . "
dbuser: " . $dbuser . "
dbpass: " . $dbpass . "
dbname: " . $dbname . "
dbport: " . $dbport . "
"; - 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..f60fc6a 100644 --- a/dmarcts-report-viewer-report-data.php +++ b/dmarcts-report-viewer-report-data.php @@ -96,7 +96,7 @@ function tmpl_reportData($reportnumber, $reports, $host_lookup = 1) { $reportdata[] = " "; $reportdata[] = " "; - global $mysqli; + global $dbh; $sql = " SELECT @@ -133,8 +133,8 @@ 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'] ) { @@ -181,7 +181,7 @@ ORDER BY function formatXML($raw_xml, $reportnumber) { - global $mysqli; + global $dbh; $out = ""; $html = ""; @@ -196,9 +196,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 +296,9 @@ if( $dmarc_select == "all" ) { // Debug //echo "
D=$dom_select
O=$org_select
"; -// 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:
dbhost: " . $dbhost . "
dbuser: " . $dbuser . "
dbpass: " . $dbpass . "
dbname: " . $dbname . "
dbport: " . $dbport . "
"; - exit; -} +$dbh = connect_db($dbtype, $dbhost, $dbport, $dbname, $dbuser, $dbpass); // // Get allowed reports and cache them - using serial as key // -------------------------------------------------------------------------- @@ -351,14 +343,14 @@ SELECT FROM report WHERE - serial = " . $mysqli->real_escape_string($reportid) + serial = " . $dbh->quote($reportid) ; // Debug // echo "
Data Report sql: $sql
"; -$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..05e5fdf 100644 --- a/dmarcts-report-viewer-report-list.php +++ b/dmarcts-report-viewer-report-list.php @@ -176,17 +176,9 @@ if(isset($_GET['rptstat'])){ // echo "
D=$dom_select
O=$org_select
"; // echo "
DMARC=$dmarc_select
"; -// 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:
dbhost: " . $dbhost . "
dbuser: " . $dbuser . "
dbpass: " . $dbpass . "
dbname: " . $dbname . "
dbport: " . $dbport . "
"; - 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 @@ -349,8 +341,8 @@ ORDER BY // echo "
Data List sql: $sql
"; // echo "
per_select = " . urlencode($per_select) . "
"; -$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 180fee9..f060ec5 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
"; - 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:
dbhost: " . $dbhost . "
dbuser: " . $dbuser . "
dbpass: " . $dbpass . "
dbname: " . $dbname . "
dbport: " . $dbport . "
"; - 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']; } @@ -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'] ); } -- cgit v1.2.3 From 11c26cd58f2619f02210cb34faceb39012717d5c Mon Sep 17 00:00:00 2001 From: Eduardo M KALINOWSKI Date: Mon, 6 Jun 2022 17:59:18 -0300 Subject: Adjust queries so they work on PostgreSQL --- dmarcts-report-viewer-report-data.php | 4 ++++ dmarcts-report-viewer-report-list.php | 4 +++- dmarcts-report-viewer.php | 8 ++++---- 3 files changed, 11 insertions(+), 5 deletions(-) diff --git a/dmarcts-report-viewer-report-data.php b/dmarcts-report-viewer-report-data.php index f60fc6a..09ea80e 100644 --- a/dmarcts-report-viewer-report-data.php +++ b/dmarcts-report-viewer-report-data.php @@ -96,6 +96,7 @@ function tmpl_reportData($reportnumber, $reports, $host_lookup = 1) { $reportdata[] = " "; $reportdata[] = " "; + global $dbtype; global $dbh; $sql = " @@ -138,6 +139,9 @@ ORDER BY 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 = "-"; diff --git a/dmarcts-report-viewer-report-list.php b/dmarcts-report-viewer-report-list.php index 05e5fdf..806ed8c 100644 --- a/dmarcts-report-viewer-report-list.php +++ b/dmarcts-report-viewer-report-list.php @@ -239,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 diff --git a/dmarcts-report-viewer.php b/dmarcts-report-viewer.php index f060ec5..5730a3d 100644 --- a/dmarcts-report-viewer.php +++ b/dmarcts-report-viewer.php @@ -277,16 +277,16 @@ foreach($query as $row) { $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 ) -- cgit v1.2.3 From a4e2a125ec673ef3c5720d65b2b105f7ee9bc929 Mon Sep 17 00:00:00 2001 From: Eduardo M KALINOWSKI Date: Tue, 7 Jun 2022 10:21:22 -0300 Subject: Document PostgreSQL support --- README.md | 10 ++++++---- 1 file changed, 6 insertions(+), 4 deletions(-) diff --git a/README.md b/README.md index 6665021..cc6b52f 100644 --- a/README.md +++ b/README.md @@ -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-dom` +* Installed `php-mysql` or `php-pgsql` and `php-dom` ### 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=""; $dbuser=""; -- cgit v1.2.3