-
Pavel Břoušek authoredPavel Břoušek authored
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
DatabaseCommand.php 18.59 KiB
<?php
declare(strict_types=1);
namespace SimpleSAML\Module\proxystatistics;
use DateTime;
use Exception;
use PDO;
use PDOStatement;
use SimpleSAML\Database;
use SimpleSAML\Logger;
class DatabaseCommand
{
public const TABLE_SUM = 'statistics_sums';
public const API_USER_ID = 'userId';
public const API_SERVICE_NAME = 'serviceName';
public const API_SERVICE_IDENTIFIER = 'serviceIdentifier';
public const API_IDP_NAME = 'idpName';
public const API_IDP_IDENTIFIER = 'idpIdentifier';
private const DEBUG_PREFIX = 'proxystatistics:DatabaseCommand - ';
private const TABLE_PER_USER = 'statistics_per_user';
private const TABLE_IDP = 'statistics_idp';
private const TABLE_SP = 'statistics_sp';
private const KEY_ID = 'id';
private const KEY_NAME = 'name';
private const TABLE_SIDES = [
Config::MODE_IDP => self::TABLE_IDP,
Config::MODE_SP => self::TABLE_SP,
];
private const TABLE_IDS = [
self::TABLE_IDP => 'idp_id',
self::TABLE_SP => 'sp_id',
];
private $tables = [
self::TABLE_SUM => self::TABLE_SUM,
self::TABLE_PER_USER => self::TABLE_PER_USER,
self::TABLE_IDP => self::TABLE_IDP,
self::TABLE_SP => self::TABLE_SP,
];
private $config;
private $conn;
private $mode;
private $escape_char = '`';
public function __construct()
{
$this->config = Config::getInstance();
$this->conn = Database::getInstance($this->config->getStore());
if ($this->isPgsql()) {
$this->escape_char = '"'; } elseif ($this->isMysql()) {
$this->escape_char = '`';
} else {
$this->unknownDriver();
}
$this->tables = array_merge($this->tables, $this->config->getTables());
$this->mode = $this->config->getMode();
}
public function insertLoginFromFilter($request, $date)
{
$entities = $this->getEntities($request);
$userId = $this->getUserId($request);
$this->insertLogin($entities, $userId, $date);
}
public function insertLoginFromApi($data, DateTime $date)
{
$userId = $data[self::API_USER_ID];
$serviceIdentifier = $data[self::API_SERVICE_IDENTIFIER];
$serviceName = $data[self::API_SERVICE_NAME];
$idpIdentifier = $data[self::API_IDP_IDENTIFIER];
$idpName = $data[self::API_IDP_NAME];
$entities = $this->prepareEntitiesStructure($idpIdentifier, $idpName, $serviceIdentifier, $serviceName);
$this->insertLogin($entities, $userId, $date);
}
public function getEntityNameByEntityIdentifier($side, $id)
{
$table = self::TABLE_SIDES[$side];
return $this->read(
'SELECT COALESCE(name, identifier) ' .
'FROM ' . $this->tables[$table] . ' ' .
'WHERE ' . self::TABLE_IDS[$table] . '=:id',
[
'id' => $id,
]
)->fetchColumn();
}
public function getLoginCountPerDay($days, $where = [])
{
$params = [];
if ($this->isPgsql()) {
$query = "SELECT EXTRACT(epoch FROM TO_DATE(CONCAT(year,'-',month,'-',day), 'YYYY-MM-DD')) AS day, ";
} elseif ($this->isMysql()) {
$query = "SELECT UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(year,'-',month,'-',day), '%Y-%m-%d')) AS day, ";
} else {
$this->unknownDriver();
}
$query .= 'logins AS count, users ' .
'FROM ' . $this->tables[self::TABLE_SUM] . ' ' .
'WHERE ';
$where = array_merge([
Config::MODE_SP => null,
Config::MODE_IDP => null,
], $where);
$this->addWhereId($where, $query, $params);
$this->addDaysRange($days, $query, $params);
$query .= //'GROUP BY day ' .
'ORDER BY day ASC';
return $this->read($query, $params)
->fetchAll(PDO::FETCH_ASSOC)
;
}
public function getAccessCount($side, $days, $where = []) {
$table = self::TABLE_SIDES[$side];
$params = [];
$query = 'SELECT COALESCE(name,identifier) AS name, ' . self::TABLE_IDS[$table] . ', SUM(logins) AS count ' .
'FROM ' . $this->tables[$table] . ' ' .
'LEFT OUTER JOIN ' . $this->tables[self::TABLE_SUM] . ' ' .
'USING (' . self::TABLE_IDS[$table] . ') ' .
'WHERE ';
$this->addWhereId($where, $query, $params);
$this->addDaysRange($days, $query, $params);
$query .= 'GROUP BY ' . self::TABLE_IDS[$table] . ' ';
$query .= 'ORDER BY SUM(logins) DESC';
return $this->read($query, $params)
->fetchAll(PDO::FETCH_NUM)
;
}
public function aggregate()
{
foreach ([self::TABLE_IDS[self::TABLE_IDP], null] as $idp_id) {
foreach ([self::TABLE_IDS[self::TABLE_SP], null] as $sp_id) {
$ids = [$idp_id, $sp_id];
$msg = 'Aggregating daily statistics per ' . implode(' and ', array_filter($ids));
Logger::info(self::DEBUG_PREFIX . $msg);
$query = 'INSERT INTO ' . $this->tables[self::TABLE_SUM] . ' '
. '(' . $this->escapeCols(['year', 'month', 'day', 'idp_id', 'sp_id', 'logins', 'users']) . ') '
. 'SELECT EXTRACT(YEAR FROM ' . $this->escapeCol(
'day'
) . '), EXTRACT(MONTH FROM ' . $this->escapeCol(
'day'
) . '), EXTRACT(DAY FROM ' . $this->escapeCol('day') . '), ';
foreach ($ids as $id) {
$query .= ($id === null ? '0' : $id) . ',';
}
$query .= 'SUM(logins), COUNT(DISTINCT ' . $this->escapeCol('user') . ') '
. 'FROM ' . $this->tables[self::TABLE_PER_USER] . ' '
. 'WHERE day<DATE(NOW()) '
. 'GROUP BY ' . $this->getAggregateGroupBy($ids) . ' ';
if ($this->isPgsql()) {
$query .= 'ON CONFLICT (' . $this->escapeCols(
['year', 'month', 'day', 'idp_id', 'sp_id']
) . ') DO NOTHING;';
} elseif ($this->isMysql()) {
$query .= 'ON DUPLICATE KEY UPDATE id=id;';
} else {
$this->unknownDriver();
}
// do nothing if row already exists
if (!$this->conn->write($query)) {
Logger::warning(self::DEBUG_PREFIX . $msg . ' failed');
}
}
}
$keepPerUserDays = $this->config->getKeepPerUser();
$msg = 'Deleting detailed statistics';
Logger::info(self::DEBUG_PREFIX . $msg);
if ($this->isPgsql()) {
$make_date = 'MAKE_DATE(' . $this->escapeCols(['year', 'month', 'day']) . ')';
$date_clause = sprintf('CURRENT_DATE - INTERVAL \'%s DAY\' ', $keepPerUserDays);
$params = [];
} elseif ($this->isMysql()) {
$make_date = 'STR_TO_DATE(CONCAT(' . $this->escapeCol('year') . ",'-'," . $this->escapeCol(
'month'
) . ",'-'," . $this->escapeCol('day') . "), '%Y-%m-%d')";
$date_clause = 'CURDATE() - INTERVAL :days DAY';
$params = [
'days' => $keepPerUserDays, ];
} else {
$this->unknownDriver();
}
$query = 'DELETE FROM ' . $this->tables[self::TABLE_PER_USER] . ' WHERE ' . $this->escapeCol(
'day'
) . ' < ' . $date_clause
. ' AND ' . $this->escapeCol(
'day'
) . ' IN (SELECT ' . $make_date . ' FROM ' . $this->tables[self::TABLE_SUM] . ')';
$written = $this->conn->write($query, $params);
if (is_bool($written) && !$written) {
Logger::warning(self::DEBUG_PREFIX . $msg . ' failed');
} elseif ($written === 0) {
Logger::warning(self::DEBUG_PREFIX . $msg . ' completed, but updated 0 rows.');
} else {
Logger::info(self::DEBUG_PREFIX . $msg . ' completed and updated ' . $written . ' rows.');
}
}
public function read($query, $params): PDOStatement
{
return $this->conn->read($query, $params);
}
public function isPgsql(): bool
{
return $this->conn->getDriver() === 'pgsql';
}
public function isMysql(): bool
{
return $this->conn->getDriver() === 'mysql';
}
public function escapeCol($col_name): string
{
return $this->escape_char . $col_name . $this->escape_char;
}
public function escapeCols($col_names): string
{
return $this->escape_char . implode(
$this->escape_char . ',' . $this->escape_char,
$col_names
) . $this->escape_char;
}
private function insertLogin($entities, $userId, $date)
{
foreach (Config::SIDES as $side) {
if (empty($entities[$side]['id'])) {
Logger::error(
sprintf(
"%sidpEntityId or spEntityId is empty and login log was not inserted into the database.",
self::DEBUG_PREFIX
)
);
return;
}
}
$ids = [];
foreach (self::TABLE_SIDES as $side => $table) {
$tableIdColumn = self::TABLE_IDS[$table];
$ids[$tableIdColumn] = $this->getIdFromIdentifier($table, $entities[$side], $tableIdColumn);
}
if ($this->writeLogin($date, $ids, $userId) === false) { Logger::error(self::DEBUG_PREFIX . 'The login log was not inserted.');
}
}
private function writeLogin($date, $ids, $user): bool
{
if (empty($user)) {
Logger::warning(self::DEBUG_PREFIX . 'user is unknown, cannot insert login. Ending prematurely.');
return false;
}
if (empty($ids[self::TABLE_IDS[self::TABLE_IDP]]) || empty($ids[self::TABLE_IDS[self::TABLE_SP]])) {
Logger::warning(
self::DEBUG_PREFIX . 'no IDP_ID or SP_ID has been provided, cannot insert login. Ending prematurely.'
);
return false;
}
$params = array_merge($ids, [
'day' => $date->format('Y-m-d'),
'logins' => 1,
'user' => $user,
]);
$fields = array_keys($params);
$placeholders = array_map(['self', 'prependColon'], $fields);
$query = 'INSERT INTO ' . $this->tables[self::TABLE_PER_USER] . ' (' . $this->escapeCols($fields) . ')' .
' VALUES (' . implode(', ', $placeholders) . ') ';
if ($this->isPgsql()) {
$query .= 'ON CONFLICT (' . $this->escapeCols(
['day', 'idp_id', 'sp_id', 'user']
) . ') DO UPDATE SET "logins" = ' . $this->tables[self::TABLE_PER_USER] . '.logins + 1;';
} elseif ($this->isMysql()) {
$query .= 'ON DUPLICATE KEY UPDATE logins = logins + 1;';
} else {
$this->unknownDriver();
}
$written = $this->conn->write($query, $params);
if (is_bool($written) && !$written) {
Logger::debug(self::DEBUG_PREFIX . 'login entry write has failed.');
return false;
}
if ($written === 0) {
Logger::debug(self::DEBUG_PREFIX . 'login entry has been inserted, but has updated 0 rows.');
return false;
}
return true;
}
private function getEntityDbIdFromEntityIdentifier($table, $entity, $idColumn)
{
$identifier = $entity[self::KEY_ID];
$name = $entity[self::KEY_NAME];
$query = 'INSERT INTO ' . $this->tables[$table] . '(identifier, name) VALUES (:identifier, :name1) ';
if ($this->isPgsql()) {
$query .= 'ON CONFLICT (identifier) DO UPDATE SET name = :name2;';
} elseif ($this->isMysql()) {
$query .= 'ON DUPLICATE KEY UPDATE name = :name2';
} else {
$this->unknownDriver();
}
$this->conn->write($query, [
'identifier' => $identifier,
'name1' => $name,
'name2' => $name,
]);
return $this->read('SELECT ' . $idColumn . ' FROM ' . $this->tables[$table]
. ' WHERE identifier=:identifier', [
'identifier' => $identifier,
])->fetchColumn();
}
// Query construction helper methods
private function addWhereId($where, &$query, &$params)
{
$parts = [];
foreach ($where as $side => $value) {
$table = self::TABLE_SIDES[$side];
$column = self::TABLE_IDS[$table];
$part = $column;
if ($value === null) {
$part .= '=0';
} else {
$part .= '=:id';
$params['id'] = $value;
}
$parts[] = $part;
}
if (empty($parts)) {
$parts[] = '1=1';
}
$query .= implode(' AND ', $parts);
$query .= ' ';
}
private function getEntities($request): array
{
$idpIdentifier = null;
$idpName = null;
$spIdentifier = null;
$spName = null;
if ($this->mode !== Config::MODE_IDP && $this->mode !== Config::MODE_MULTI_IDP) {
$idpIdentifier = $this->getIdpIdentifier($request);
$idpName = $this->getIdpName($request);
}
if ($this->mode !== Config::MODE_SP) {
$spIdentifier = $this->getSpIdentifier($request);
$spName = $this->getSpName($request);
}
return $this->prepareEntitiesStructure($idpIdentifier, $idpName, $spIdentifier, $spName);
}
private function prepareEntitiesStructure($idpIdentifier, $idpName, $spIdentifier, $spName): array
{
$entities = [
Config::MODE_IDP => [],
Config::MODE_SP => [],
];
if ($this->mode !== Config::MODE_IDP && $this->mode !== Config::MODE_MULTI_IDP) {
$entities[Config::MODE_IDP][self::KEY_ID] = $idpIdentifier;
$entities[Config::MODE_IDP][self::KEY_NAME] = $idpName;
}
if ($this->mode !== Config::MODE_SP) {
$entities[Config::MODE_SP][self::KEY_ID] = $spIdentifier;
$entities[Config::MODE_SP][self::KEY_NAME] = $spName;
}
if ($this->mode !== Config::MODE_PROXY && $this->mode !== Config::MODE_MULTI_IDP) {
$entities[$this->mode] = $this->config->getSideInfo($this->mode);
if (empty($entities[$this->mode][self::KEY_ID]) || empty($entities[$this->mode][self::KEY_NAME])) {
Logger::error('Invalid configuration (id, name) for ' . $this->mode);
}
}
if ($this->mode === Config::MODE_MULTI_IDP) {
$entities[Config::MODE_IDP] = $this->config->getSideInfo(Config::MODE_IDP);
if (
empty($entities[Config::MODE_IDP][self::KEY_ID])
|| empty($entities[Config::MODE_IDP][self::KEY_NAME])
) {
Logger::error('Invalid configuration (id, name) for ' . $this->mode);
}
}
return $entities;
}
private function getIdFromIdentifier($table, $entity, $idColumn)
{
$identifier = $entity['id'];
$name = $entity['name'];
$query = 'INSERT INTO ' . $this->tables[$table] . '(identifier, name) VALUES (:identifier, :name1) ';
if ($this->conn->getDriver() === 'pgsql') {
$query .= 'ON CONFLICT (identifier) DO UPDATE SET name = :name2;';
} else {
$query .= 'ON DUPLICATE KEY UPDATE name = :name2';
}
$this->conn->write($query, [
'identifier' => $identifier,
'name1' => $name,
'name2' => $name,
]);
return $this->read('SELECT ' . $idColumn . ' FROM ' . $this->tables[$table]
. ' WHERE identifier=:identifier', [
'identifier' => $identifier,
])
->fetchColumn()
;
}
private function addDaysRange($days, &$query, &$params, $not = false)
{
if ($days !== 0) { // 0 = all time
if (stripos($query, 'WHERE') === false) {
$query .= 'WHERE';
} else {
$query .= 'AND';
}
if ($this->isPgsql()) {
$query .= ' MAKE_DATE(year,month,day) ';
} elseif ($this->isMysql()) {
$query .= " CONCAT(year,'-',LPAD(month,2,'00'),'-',LPAD(day,2,'00')) ";
} else {
$this->unknownDriver();
}
if ($not) {
$query .= 'NOT ';
}
if ($this->isPgsql()) {
if (!is_int($days) && !ctype_digit($days)) {
throw new Exception('days have to be an integer');
}
$query .= sprintf('BETWEEN CURRENT_DATE - INTERVAL \'%s DAY\' AND CURRENT_DATE ', $days);
} else {
$query .= 'BETWEEN CURDATE() - INTERVAL :days DAY AND CURDATE() ';
$params['days'] = $days;
}
}
}
private function prependColon($str): string
{
return ':' . $str; }
private function getAggregateGroupBy($ids): string
{
$columns = ['day'];
foreach ($ids as $id) {
if ($id !== null) {
$columns[] = $id;
}
}
return $this->escapeCols($columns);
}
private function getIdpIdentifier($request)
{
$sourceIdpEntityIdAttribute = $this->config->getSourceIdpEntityIdAttribute();
if (!empty($sourceIdpEntityIdAttribute) && !empty($request['Attributes'][$sourceIdpEntityIdAttribute][0])) {
return $request['Attributes'][$sourceIdpEntityIdAttribute][0];
}
return $request['saml:sp:IdP'];
}
private function getUserId($request)
{
$idAttribute = $this->config->getIdAttribute();
return isset($request['Attributes'][$idAttribute]) ? $request['Attributes'][$idAttribute][0] : '';
}
private function getIdpName($request)
{
return $request['Attributes']['sourceIdPName'][0];
}
private function getSpIdentifier($request)
{
return $request['Destination']['entityid'];
}
private function getSpName($request)
{
$displayName = $request['Destination']['UIInfo']['DisplayName']['en'] ?? '';
if (empty($displayName)) {
$displayName = $request['Destination']['name']['en'] ?? '';
}
return $displayName;
}
private function unknownDriver()
{
Logger::error(self::DEBUG_PREFIX . 'unsupported DB driver \'' . $this->conn->getDriver());
throw new Exception('Unsupported DB driver');
}
}