544 lines
18 KiB
PHP
544 lines
18 KiB
PHP
|
<?php
|
||
|
|
|
||
|
|
/**
|
||
|
|
* dmarc-srg - A php parser, viewer and summary report generator for incoming DMARC reports.
|
||
|
|
* Copyright (C) 2020 Aleksey Andreev (liuch)
|
||
|
|
*
|
||
|
|
* Available at:
|
||
|
|
* https://github.com/liuch/dmarc-srg
|
||
|
|
*
|
||
|
|
* This program is free software: you can redistribute it and/or modify it
|
||
|
|
* under the terms of the GNU General Public License as published by the Free
|
||
|
|
* Software Foundation, either version 3 of the License.
|
||
|
|
*
|
||
|
|
* This program is distributed in the hope that it will be useful, but WITHOUT
|
||
|
|
* ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
|
||
|
|
* FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
|
||
|
|
* more details.
|
||
|
|
*
|
||
|
|
* You should have received a copy of the GNU General Public License along with
|
||
|
|
* this program. If not, see <http://www.gnu.org/licenses/>.
|
||
|
|
*/
|
||
|
|
|
||
|
|
namespace Liuch\DmarcSrg\Database;
|
||
|
|
|
||
|
|
use Liuch\DmarcSrg\Settings\SettingString;
|
||
|
|
|
||
|
|
class Database
|
||
|
|
{
|
||
|
|
public const REQUIRED_VERSION = '2.0';
|
||
|
|
|
||
|
|
private $conn;
|
||
|
|
private static $instance = null;
|
||
|
|
|
||
|
|
private function __construct()
|
||
|
|
{
|
||
|
|
$this->conn = null;
|
||
|
|
$this->establishConnection();
|
||
|
|
}
|
||
|
|
|
||
|
|
public static function connection()
|
||
|
|
{
|
||
|
|
if (!self::$instance) {
|
||
|
|
self::$instance = new self();
|
||
|
|
}
|
||
|
|
return self::$instance->conn;
|
||
|
|
}
|
||
|
|
|
||
|
|
public static function type()
|
||
|
|
{
|
||
|
|
global $database;
|
||
|
|
return $database['type'];
|
||
|
|
}
|
||
|
|
|
||
|
|
public static function name()
|
||
|
|
{
|
||
|
|
global $database;
|
||
|
|
return $database['name'];
|
||
|
|
}
|
||
|
|
|
||
|
|
public static function location()
|
||
|
|
{
|
||
|
|
global $database;
|
||
|
|
return $database['host'];
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Returns the prefix for tables of the database
|
||
|
|
*
|
||
|
|
* @param string $postfix String to be concatenated with the prefix.
|
||
|
|
* Usually, this is a table name.
|
||
|
|
*
|
||
|
|
* @return string
|
||
|
|
*/
|
||
|
|
public static function tablePrefix(string $postfix = ''): string
|
||
|
|
{
|
||
|
|
global $database;
|
||
|
|
return ($database['table_prefix'] ?? '') . $postfix;
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Returns information about the database as an array.
|
||
|
|
*
|
||
|
|
* @return array May contain the following fields:
|
||
|
|
* `tables` - an array of tables with their properties;
|
||
|
|
* `needs_upgrade` - true if the database needs upgrading;
|
||
|
|
* `correct` - true if the database is correct;
|
||
|
|
* `type` - the database type;
|
||
|
|
* `name` - the database name;
|
||
|
|
* `location` - the database location;
|
||
|
|
* `version` - the current version of the database structure;
|
||
|
|
* `message` - a state message;
|
||
|
|
* `error_code` - an error code;
|
||
|
|
*/
|
||
|
|
public static function state(): array
|
||
|
|
{
|
||
|
|
$res = [];
|
||
|
|
try {
|
||
|
|
$prefix = self::tablePrefix();
|
||
|
|
$p_len = strlen($prefix);
|
||
|
|
if ($p_len > 0) {
|
||
|
|
$like_str = ' WHERE NAME LIKE "' . str_replace('_', '\\_', $prefix) . '%"';
|
||
|
|
} else {
|
||
|
|
$like_str = '';
|
||
|
|
}
|
||
|
|
$db = self::connection();
|
||
|
|
$tables = [];
|
||
|
|
$st = $db->query(
|
||
|
|
'SHOW TABLE STATUS FROM `' . str_replace('`', '', self::name()) . '`' . $like_str
|
||
|
|
);
|
||
|
|
while ($row = $st->fetch(\PDO::FETCH_ASSOC)) {
|
||
|
|
$tnm = $row['Name'];
|
||
|
|
$st2 = $db->query('SELECT COUNT(*) FROM `' . $tnm . '`');
|
||
|
|
$rows = $st2->fetch(\PDO::FETCH_NUM)[0];
|
||
|
|
$tables[substr($tnm, $p_len)] = [
|
||
|
|
'engine' => $row['Engine'],
|
||
|
|
'rows' => intval($rows),
|
||
|
|
'data_length' => intval($row['Data_length']),
|
||
|
|
'index_length' => intval($row['Index_length']),
|
||
|
|
'create_time' => $row['Create_time'],
|
||
|
|
'update_time' => $row['Update_time']
|
||
|
|
];
|
||
|
|
}
|
||
|
|
foreach (array_keys(self::$schema) as $table) {
|
||
|
|
if (!isset($tables[$table])) {
|
||
|
|
$tables[$table] = false;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
$exist_sys = false;
|
||
|
|
$exist_cnt = 0;
|
||
|
|
$absent_cnt = 0;
|
||
|
|
$tables_res = [];
|
||
|
|
foreach ($tables as $tname => $tval) {
|
||
|
|
$t = null;
|
||
|
|
if ($tval) {
|
||
|
|
$t = $tval;
|
||
|
|
$t['exists'] = true;
|
||
|
|
if (isset(self::$schema[$tname])) {
|
||
|
|
$exist_cnt += 1;
|
||
|
|
$t['message'] = 'Ok';
|
||
|
|
if (!$exist_sys && $tname === 'system') {
|
||
|
|
$exist_sys = true;
|
||
|
|
}
|
||
|
|
} else {
|
||
|
|
$t['message'] = 'Unknown table';
|
||
|
|
}
|
||
|
|
} else {
|
||
|
|
$absent_cnt += 1;
|
||
|
|
$t = [
|
||
|
|
'error_code' => 1,
|
||
|
|
'message' => 'Not exist'
|
||
|
|
];
|
||
|
|
}
|
||
|
|
$t['name'] = $tname;
|
||
|
|
$tables_res[] = $t;
|
||
|
|
}
|
||
|
|
$res['tables'] = $tables_res;
|
||
|
|
$ver = $exist_sys ? (new SettingString('version'))->value() : null;
|
||
|
|
if ($exist_sys && $ver !== self::REQUIRED_VERSION) {
|
||
|
|
self::setDbMessage('The database structure needs upgrading', 0, $res);
|
||
|
|
$res['needs_upgrade'] = true;
|
||
|
|
} elseif ($absent_cnt == 0) {
|
||
|
|
$res['correct'] = true;
|
||
|
|
self::setDbMessage('Ok', 0, $res);
|
||
|
|
} else {
|
||
|
|
if ($exist_cnt == 0) {
|
||
|
|
self::setDbMessage('The database schema is not initiated', -1, $res);
|
||
|
|
} else {
|
||
|
|
self::setDbMessage('Incomplete set of the tables', -1, $res);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
if ($ver) {
|
||
|
|
$res['version'] = $ver;
|
||
|
|
}
|
||
|
|
} catch (\Exception $e) {
|
||
|
|
$res['error_code'] = $e->getCode();
|
||
|
|
$res['message'] = $e->getMessage();
|
||
|
|
}
|
||
|
|
$res['type'] = self::type();
|
||
|
|
$res['name'] = self::name();
|
||
|
|
$res['location'] = self::location();
|
||
|
|
|
||
|
|
return $res;
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Inites the database.
|
||
|
|
*
|
||
|
|
* This method creates needed tables and indexes in the database.
|
||
|
|
* The method will fail if the database already have tables with the table prefix.
|
||
|
|
*
|
||
|
|
* @return array Result array with `error_code` and `message` fields.
|
||
|
|
*/
|
||
|
|
public static function initDb(): array
|
||
|
|
{
|
||
|
|
try {
|
||
|
|
$db = self::connection();
|
||
|
|
$st = $db->query(self::sqlShowTablesQuery());
|
||
|
|
try {
|
||
|
|
if ($st->fetch()) {
|
||
|
|
if (empty(self::tablePrefix())) {
|
||
|
|
throw new \Exception('The database is not empty', -4);
|
||
|
|
} else {
|
||
|
|
throw new \Exception('Database tables already exist with the given prefix', -4);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
foreach (array_keys(self::$schema) as $table) {
|
||
|
|
self::createDbTable(self::tablePrefix($table), self::$schema[$table]);
|
||
|
|
}
|
||
|
|
} finally {
|
||
|
|
$st->closeCursor();
|
||
|
|
}
|
||
|
|
$st = $db->prepare(
|
||
|
|
'INSERT INTO `' . self::tablePrefix('system') . '` (`key`, `value`) VALUES ("version", ?)'
|
||
|
|
);
|
||
|
|
$st->bindValue(1, self::REQUIRED_VERSION, \PDO::PARAM_STR);
|
||
|
|
$st->execute();
|
||
|
|
$st->closeCursor();
|
||
|
|
} catch (\Exception $e) {
|
||
|
|
return [
|
||
|
|
'error_code' => $e->getCode(),
|
||
|
|
'message' => $e->getMessage()
|
||
|
|
];
|
||
|
|
}
|
||
|
|
return [ 'message' => 'The database has been initiated' ];
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Cleans up the database.
|
||
|
|
*
|
||
|
|
* Drops tables with the table prefix in the database or all tables in the database if no table prefix is set.
|
||
|
|
*
|
||
|
|
* @return array Result array with `error_code` and `message` fields.
|
||
|
|
*/
|
||
|
|
public static function dropTables(): array
|
||
|
|
{
|
||
|
|
try {
|
||
|
|
$db = self::connection();
|
||
|
|
$db->query('SET foreign_key_checks = 0');
|
||
|
|
$st = $db->query(self::sqlShowTablesQuery());
|
||
|
|
while ($table = $st->fetchColumn(0)) {
|
||
|
|
$db->query('DROP TABLE `' . $table . '`');
|
||
|
|
}
|
||
|
|
$st->closeCursor();
|
||
|
|
$db->query('SET foreign_key_checks = 1');
|
||
|
|
} catch (\PDOException $e) {
|
||
|
|
return [
|
||
|
|
'error_code' => $e->getCode(),
|
||
|
|
'message' => $e->getMessage()
|
||
|
|
];
|
||
|
|
}
|
||
|
|
return [ 'message' => 'Database tables have been dropped' ];
|
||
|
|
}
|
||
|
|
|
||
|
|
private function establishConnection()
|
||
|
|
{
|
||
|
|
global $database;
|
||
|
|
try {
|
||
|
|
$dsn = "{$database['type']}:host={$database['host']};dbname={$database['name']};charset=utf8";
|
||
|
|
$this->conn = new \PDO(
|
||
|
|
$dsn,
|
||
|
|
$database['user'],
|
||
|
|
$database['password'],
|
||
|
|
[ \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION ]
|
||
|
|
);
|
||
|
|
$this->conn->query('SET time_zone = "+00:00"');
|
||
|
|
} catch (\Exception $e) {
|
||
|
|
throw new \Exception($e->getMessage(), -1);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Return SHOW TABLES SQL query string for tables with the table prefix
|
||
|
|
*
|
||
|
|
* @return string
|
||
|
|
*/
|
||
|
|
private static function sqlShowTablesQuery(): string
|
||
|
|
{
|
||
|
|
$res = 'SHOW TABLES';
|
||
|
|
$prefix = self::tablePrefix();
|
||
|
|
if (strlen($prefix) > 0) {
|
||
|
|
$res .= ' WHERE `tables_in_' . str_replace('`', '', self::name())
|
||
|
|
. '` LIKE "' . str_replace('_', '\\_', $prefix) . '%"';
|
||
|
|
}
|
||
|
|
return $res;
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Creates a table in the database.
|
||
|
|
*
|
||
|
|
* @param string $name Table name
|
||
|
|
* @param array $definitions Table structure
|
||
|
|
*
|
||
|
|
* @return void
|
||
|
|
*/
|
||
|
|
private static function createDbTable(string $name, array $definitions): void
|
||
|
|
{
|
||
|
|
$query = 'CREATE TABLE `' . $name . '` (';
|
||
|
|
$col_num = 0;
|
||
|
|
foreach ($definitions['columns'] as $column) {
|
||
|
|
if ($col_num > 0) {
|
||
|
|
$query .= ', ';
|
||
|
|
}
|
||
|
|
$query .= '`' . $column['name'] . '` ' . $column['definition'];
|
||
|
|
$col_num += 1;
|
||
|
|
}
|
||
|
|
$query .= ', ' . $definitions['additional'] . ') ' . $definitions['table_options'];
|
||
|
|
self::connection()->query($query);
|
||
|
|
}
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Sets the database message and error code for the state array
|
||
|
|
*
|
||
|
|
* @param string $message Message string
|
||
|
|
* @param int $err_code Error code
|
||
|
|
* @param array $state Database state array
|
||
|
|
*
|
||
|
|
* @return void
|
||
|
|
*/
|
||
|
|
private static function setDbMessage(string $message, int $err_code, array &$state): void
|
||
|
|
{
|
||
|
|
$state['message'] = $message;
|
||
|
|
if ($err_code !== 0) {
|
||
|
|
$state['error_code'] = $err_code;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
private static $schema = [
|
||
|
|
'system' => [
|
||
|
|
'columns' => [
|
||
|
|
[
|
||
|
|
'name' => 'key',
|
||
|
|
'definition' => 'varchar(64) NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'value',
|
||
|
|
'definition' => 'varchar(255) DEFAULT NULL'
|
||
|
|
]
|
||
|
|
],
|
||
|
|
'additional' => 'PRIMARY KEY (`key`)',
|
||
|
|
'table_options' => 'ENGINE=InnoDB DEFAULT CHARSET=utf8'
|
||
|
|
],
|
||
|
|
'domains' => [
|
||
|
|
'columns' => [
|
||
|
|
[
|
||
|
|
'name' => 'id',
|
||
|
|
'definition' => 'int(10) unsigned NOT NULL AUTO_INCREMENT'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'fqdn',
|
||
|
|
'definition' => 'varchar(255) NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'active',
|
||
|
|
'definition' => 'boolean NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'description',
|
||
|
|
'definition' => 'TEXT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'created_time',
|
||
|
|
'definition' => 'datetime NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'updated_time',
|
||
|
|
'definition' => 'datetime NOT NULL'
|
||
|
|
]
|
||
|
|
],
|
||
|
|
'additional' => 'PRIMARY KEY (`id`), UNIQUE KEY `fqdn` (`fqdn`)',
|
||
|
|
'table_options' => 'ENGINE=InnoDB DEFAULT CHARSET=utf8'
|
||
|
|
],
|
||
|
|
'reports' => [
|
||
|
|
'columns' => [
|
||
|
|
[
|
||
|
|
'name' => 'id',
|
||
|
|
'definition' => 'int(10) unsigned NOT NULL AUTO_INCREMENT'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'domain_id',
|
||
|
|
'definition' => 'int(10) NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'begin_time',
|
||
|
|
'definition' => 'datetime NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'end_time',
|
||
|
|
'definition' => 'datetime NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'loaded_time',
|
||
|
|
'definition' => 'datetime NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'org',
|
||
|
|
'definition' => 'varchar(255) NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'external_id',
|
||
|
|
'definition' => 'varchar(255) NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'email',
|
||
|
|
'definition' => 'varchar(255) NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'extra_contact_info',
|
||
|
|
'definition' => 'varchar(255) NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'error_string',
|
||
|
|
'definition' => 'text NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'policy_adkim',
|
||
|
|
'definition' => 'varchar(20) NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'policy_aspf',
|
||
|
|
'definition' => 'varchar(20) NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'policy_p',
|
||
|
|
'definition' => 'varchar(20) NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'policy_sp',
|
||
|
|
'definition' => 'varchar(20) NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'policy_pct',
|
||
|
|
'definition' => 'varchar(20) NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'policy_fo',
|
||
|
|
'definition' => 'varchar(20) NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'seen',
|
||
|
|
'definition' => 'boolean NOT NULL'
|
||
|
|
]
|
||
|
|
],
|
||
|
|
'additional' => 'PRIMARY KEY (`id`), UNIQUE KEY `external_id` (`domain_id`, `external_id`), KEY (`begin_time`), KEY (`end_time`), KEY `org` (`org`, `begin_time`)',
|
||
|
|
'table_options' => 'ENGINE=InnoDB DEFAULT CHARSET=utf8'
|
||
|
|
],
|
||
|
|
'rptrecords' => [
|
||
|
|
'columns' => [
|
||
|
|
[
|
||
|
|
'name' => 'id',
|
||
|
|
'definition' => 'int(10) unsigned NOT NULL AUTO_INCREMENT'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'report_id',
|
||
|
|
'definition' => 'int(10) unsigned NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'ip',
|
||
|
|
'definition' => 'varbinary(16) NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'rcount',
|
||
|
|
'definition' => 'int(10) unsigned NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'disposition',
|
||
|
|
'definition' => 'tinyint unsigned NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'reason',
|
||
|
|
'definition' => 'text NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'dkim_auth',
|
||
|
|
'definition' => 'text NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'spf_auth',
|
||
|
|
'definition' => 'text NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'dkim_align',
|
||
|
|
'definition' => 'tinyint unsigned NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'spf_align',
|
||
|
|
'definition' => 'tinyint unsigned NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'envelope_to',
|
||
|
|
'definition' => 'varchar(255) NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'envelope_from',
|
||
|
|
'definition' => 'varchar(255) NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'header_from',
|
||
|
|
'definition' => 'varchar(255) NULL'
|
||
|
|
]
|
||
|
|
],
|
||
|
|
'additional' => 'PRIMARY KEY (`id`), KEY (`report_id`), KEY (`ip`)',
|
||
|
|
'table_options' => 'ENGINE=InnoDB DEFAULT CHARSET=utf8'
|
||
|
|
],
|
||
|
|
'reportlog' => [
|
||
|
|
'columns' => [
|
||
|
|
[
|
||
|
|
'name' => 'id',
|
||
|
|
'definition' => 'int(10) unsigned NOT NULL AUTO_INCREMENT'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'domain',
|
||
|
|
'definition' => 'varchar(255) NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'external_id',
|
||
|
|
'definition' => 'varchar(255) NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'event_time',
|
||
|
|
'definition' => 'datetime NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'filename',
|
||
|
|
'definition' => 'varchar(255) NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'source',
|
||
|
|
'definition' => 'tinyint unsigned NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'success',
|
||
|
|
'definition' => 'boolean NOT NULL'
|
||
|
|
],
|
||
|
|
[
|
||
|
|
'name' => 'message',
|
||
|
|
'definition' => 'text NULL'
|
||
|
|
]
|
||
|
|
],
|
||
|
|
'additional' => 'PRIMARY KEY (`id`), KEY(`event_time`)',
|
||
|
|
'table_options' => 'ENGINE=InnoDB DEFAULT CHARSET=utf8'
|
||
|
|
]
|
||
|
|
];
|
||
|
|
}
|