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.

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<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..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[] = "  </thead>";
 	$reportdata[] = "  <tbody>";
 
-	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 "<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 +343,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..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 "<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
@@ -349,8 +341,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 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<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'];
 }
 
@@ -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'] );
 }