SQL.php

Go to the documentation of this file.

<?php

namespace SimpleSAML\Store;

use \SimpleSAML_Configuration as Configuration;
use \SimpleSAML\Logger;
use \SimpleSAML\Store;

class SQL extends Store
{
    public $pdo;


    public $driver;


    public $prefix;


    private $tableVersions;


    public function __construct()
    {
        $config = Configuration::getInstance();

        $dsn = $config->getString('store.sql.dsn');
        $username = $config->getString('store.sql.username', null);
        $password = $config->getString('store.sql.password', null);
        $options = $config->getArray('store.sql.options', null);
        $this->prefix = $config->getString('store.sql.prefix', 'simpleSAMLphp');
        try {
            $this->pdo = new \PDO($dsn, $username, $password, $options);
        } catch (\PDOException $e) {
            throw new \Exception("Database error: " . $e->getMessage());
        }
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

        $this->driver = $this->pdo->getAttribute(\PDO::ATTR_DRIVER_NAME);

        if ($this->driver === 'mysql') {
            $this->pdo->exec('SET time_zone = "+00:00"');
        }

        $this->initTableVersionTable();
        $this->initKVTable();
    }


    private function initTableVersionTable()
    {
        $this->tableVersions = array();

        try {
            $fetchTableVersion = $this->pdo->query('SELECT _name, _version FROM '.$this->prefix.'_tableVersion');
        } catch (\PDOException $e) {
            $this->pdo->exec(
                'CREATE TABLE '.$this->prefix.
                '_tableVersion (_name VARCHAR(30) NOT NULL UNIQUE, _version INTEGER NOT NULL)'
            );
            return;
        }

        while (($row = $fetchTableVersion->fetch(\PDO::FETCH_ASSOC)) !== false) {
            $this->tableVersions[$row['_name']] = (int) $row['_version'];
        }
    }


    private function initKVTable()
    {
        $current_version = $this->getTableVersion('kvstore');

        $text_t = 'TEXT';
        if ($this->driver === 'mysql') {
            // TEXT data type has size constraints that can be hit at some point, so we use LONGTEXT instead
            $text_t = 'LONGTEXT';
        }

        $table_updates = array(
            array(
                'CREATE TABLE '.$this->prefix.
                '_kvstore (_type VARCHAR(30) NOT NULL, _key VARCHAR(50) NOT NULL, _value '.$text_t.
                ' NOT NULL, _expire TIMESTAMP, PRIMARY KEY (_key, _type))',
                'CREATE INDEX '.$this->prefix.'_kvstore_expire ON '.$this->prefix.'_kvstore (_expire)'
            ),
            array(
                'CREATE TABLE '.$this->prefix.
                '_kvstore_new (_type VARCHAR(30) NOT NULL, _key VARCHAR(50) NOT NULL, _value '.$text_t.
                ' NOT NULL, _expire TIMESTAMP NULL, PRIMARY KEY (_key, _type))',
                'INSERT INTO '.$this->prefix.'_kvstore_new SELECT * FROM ' . $this->prefix.'_kvstore',
                'DROP TABLE '.$this->prefix.'_kvstore',
                'ALTER TABLE '.$this->prefix.'_kvstore_new RENAME TO ' . $this->prefix . '_kvstore',
                'CREATE INDEX '.$this->prefix.'_kvstore_expire ON '.$this->prefix.'_kvstore (_expire)'
            )
        );

        $latest_version = count($table_updates);

        if ($current_version == $latest_version) {
            return;
        }

        // Only run queries for after the current version
        $updates_to_run = array_slice($table_updates, $current_version);

        foreach ($updates_to_run as $version_updates) {
            foreach ($version_updates as $query) {
                $this->pdo->exec($query);
            }
        }

        $this->setTableVersion('kvstore', $latest_version);
    }


    public function getTableVersion($name)
    {
        assert(is_string($name));

        if (!isset($this->tableVersions[$name])) {
            return 0;
        }

        return $this->tableVersions[$name];
    }


    public function setTableVersion($name, $version)
    {
        assert(is_string($name));
        assert(is_int($version));

        $this->insertOrUpdate(
            $this->prefix.'_tableVersion',
            array('_name'),
            array('_name' => $name, '_version' => $version)
        );
        $this->tableVersions[$name] = $version;
    }


    public function insertOrUpdate($table, array $keys, array $data)
    {
        assert(is_string($table));

        $colNames = '('.implode(', ', array_keys($data)).')';
        $values = 'VALUES(:'.implode(', :', array_keys($data)).')';

        switch ($this->driver) {
            case 'mysql':
                $query = 'REPLACE INTO '.$table.' '.$colNames.' '.$values;
                $query = $this->pdo->prepare($query);
                $query->execute($data);
                return;
            case 'sqlite':
                $query = 'INSERT OR REPLACE INTO '.$table.' '.$colNames.' '.$values;
                $query = $this->pdo->prepare($query);
                $query->execute($data);
                return;
        }

        // default implementation, try INSERT, and UPDATE if that fails.
        $insertQuery = 'INSERT INTO '.$table.' '.$colNames.' '.$values;
        $insertQuery = $this->pdo->prepare($insertQuery);
        try {
            $insertQuery->execute($data);
            return;
        } catch (\PDOException $e) {
            $ecode = (string) $e->getCode();
            switch ($ecode) {
                case '23505': // PostgreSQL
                    break;
                default:
                    Logger::error('Error while saving data: '.$e->getMessage());
                    throw $e;
            }
        }

        $updateCols = array();
        $condCols = array();
        foreach ($data as $col => $value) {
            $tmp = $col.' = :'.$col;

            if (in_array($col, $keys, true)) {
                $condCols[] = $tmp;
            } else {
                $updateCols[] = $tmp;
            }
        }

        $updateQuery = 'UPDATE '.$table.' SET '.implode(',', $updateCols).' WHERE '.implode(' AND ', $condCols);
        $updateQuery = $this->pdo->prepare($updateQuery);
        $updateQuery->execute($data);
    }


    private function cleanKVStore()
    {
        Logger::debug('store.sql: Cleaning key-value store.');

        $query = 'DELETE FROM '.$this->prefix.'_kvstore WHERE _expire < :now';
        $params = array('now' => gmdate('Y-m-d H:i:s'));

        $query = $this->pdo->prepare($query);
        $query->execute($params);
    }


    public function get($type, $key)
    {
        assert(is_string($type));
        assert(is_string($key));

        if (strlen($key) > 50) {
            $key = sha1($key);
        }

        $query = 'SELECT _value FROM '.$this->prefix.
                 '_kvstore WHERE _type = :type AND _key = :key AND (_expire IS NULL OR _expire > :now)';
        $params = array('type' => $type, 'key' => $key, 'now' => gmdate('Y-m-d H:i:s'));

        $query = $this->pdo->prepare($query);
        $query->execute($params);

        $row = $query->fetch(\PDO::FETCH_ASSOC);
        if ($row === false) {
            return null;
        }

        $value = $row['_value'];
        if (is_resource($value)) {
            $value = stream_get_contents($value);
        }
        $value = urldecode($value);
        $value = unserialize($value);

        if ($value === false) {
            return null;
        }
        return $value;
    }


    public function set($type, $key, $value, $expire = null)
    {
        assert(is_string($type));
        assert(is_string($key));
        assert($expire === null || (is_int($expire) && $expire > 2592000));

        if (rand(0, 1000) < 10) {
            $this->cleanKVStore();
        }

        if (strlen($key) > 50) {
            $key = sha1($key);
        }

        if ($expire !== null) {
            $expire = gmdate('Y-m-d H:i:s', $expire);
        }

        $value = serialize($value);
        $value = rawurlencode($value);

        $data = array(
            '_type'   => $type,
            '_key'    => $key,
            '_value'  => $value,
            '_expire' => $expire,
        );

        $this->insertOrUpdate($this->prefix.'_kvstore', array('_type', '_key'), $data);
    }


    public function delete($type, $key)
    {
        assert(is_string($type));
        assert(is_string($key));

        if (strlen($key) > 50) {
            $key = sha1($key);
        }

        $data = array(
            '_type' => $type,
            '_key'  => $key,
        );

        $query = 'DELETE FROM '.$this->prefix.'_kvstore WHERE _type=:_type AND _key=:_key';
        $query = $this->pdo->prepare($query);
        $query->execute($data);
    }
}