Database::_getVersionedKeys() - Code Metrics - Inspection of "[WIP] Webcrypto, v2 paste format, zlib compression" - PrivateBin/PrivateBin - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#431)

by El

created 2019-05-19 06:38 UTC

Database::_getVersionedKeys() A

↳ Parent: Database

Complexity

Conditions	2
Paths	2

Size

Total Lines	6
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	4
CRAP Score	2

Importance

Changes

Metric	Value
cc	2
eloc	3
nc	2
nop	1
dl	0
loc	6
ccs	4
cts	4
cp	1
crap	2
rs	10
c	0
b	0
f	0

<?php
/**
 * PrivateBin
 *
 * a zero-knowledge paste bin
 *
 * @link      https://github.com/PrivateBin/PrivateBin
 * @copyright 2012 Sébastien SAUVAGE (sebsauvage.net)
 * @license   https://www.opensource.org/licenses/zlib-license.php The zlib/libpng License
 * @version   1.2.1
 */

namespace PrivateBin\Data;

use Exception;
use PDO;
use PDOException;
use PrivateBin\Controller;
use PrivateBin\Json;

/**
 * Database
 *
 * Model for database access, implemented as a singleton.
 */
class Database extends AbstractData
{
    /**
     * cache for select queries
     *
     * @var array
     */
    private static $_cache = array();

    /**
     * instance of database connection
     *
     * @access private
     * @static
     * @var PDO
     */
    private static $_db;

    /**
     * table prefix
     *
     * @access private
     * @static
     * @var string
     */
    private static $_prefix = '';

    /**
     * database type
     *
     * @access private
     * @static
     * @var string
     */
    private static $_type = '';

    /**
     * get instance of singleton
     *
     * @access public
     * @static
     * @param  array $options
     * @throws Exception
     * @return Database
     */
    public static function getInstance(array $options)
    {
        // if needed initialize the singleton
        if (!(self::$_instance instanceof self)) {
            self::$_instance = new self;
        }

        if (is_array($options)) {

            // set table prefix if given
            if (array_key_exists('tbl', $options)) {
                self::$_prefix = $options['tbl'];
            }

            // initialize the db connection with new options
            if (
                array_key_exists('dsn', $options) &&
                array_key_exists('usr', $options) &&
                array_key_exists('pwd', $options) &&
                array_key_exists('opt', $options)
            ) {
                // set default options
                $options['opt'][PDO::ATTR_ERRMODE]          = PDO::ERRMODE_EXCEPTION;
                $options['opt'][PDO::ATTR_EMULATE_PREPARES] = false;
                $options['opt'][PDO::ATTR_PERSISTENT]       = true;
                $db_tables_exist                            = true;

                // setup type and dabase connection
                self::$_type = strtolower(
                    substr($options['dsn'], 0, strpos($options['dsn'], ':'))
                );
                $tableQuery = self::_getTableQuery(self::$_type);
                self::$_db  = new PDO(
                    $options['dsn'],
                    $options['usr'],
                    $options['pwd'],
                    $options['opt']
                );

                // check if the database contains the required tables
                $tables = self::$_db->query($tableQuery)->fetchAll(PDO::FETCH_COLUMN, 0);

                // create paste table if necessary
                if (!in_array(self::_sanitizeIdentifier('paste'), $tables)) {
                    self::_createPasteTable();
                    $db_tables_exist = false;
                }

                // create comment table if necessary
                if (!in_array(self::_sanitizeIdentifier('comment'), $tables)) {
                    self::_createCommentTable();
                    $db_tables_exist = false;
                }

                // create config table if necessary
                $db_version = Controller::VERSION;
                if (!in_array(self::_sanitizeIdentifier('config'), $tables)) {
                    self::_createConfigTable();
                    // if we only needed to create the config table, the DB is older then 0.22
                    if ($db_tables_exist) {
                        $db_version = '0.21';
                    }
                } else {
                    $db_version = self::_getConfig('VERSION');
                }

                // update database structure if necessary
                if (version_compare($db_version, Controller::VERSION, '<')) {
                    self::_upgradeDatabase($db_version);
                }
            } else {
                throw new Exception(
                    'Missing configuration for key dsn, usr, pwd or opt in the section model_options, please check your configuration file', 6
                );
            }
        }

        return self::$_instance;
    }

    /**
     * Create a paste.
     *
     * @access public
     * @param  string $pasteid
     * @param  array  $paste
     * @return bool
     */
    public function create($pasteid, array $paste)
    {
        if (
            array_key_exists($pasteid, self::$_cache)
        ) {
            if (false !== self::$_cache[$pasteid]) {
                return false;
            } else {
                unset(self::$_cache[$pasteid]);
            }
        }

        $expire_date      = 0;
        $opendiscussion   = $burnafterreading = false;
        $attachment       = $attachmentname   = null;
        $meta             = $paste['meta'];
        $isVersion1       = array_key_exists('data', $paste);
        list($createdKey) = self::_getVersionedKeys($isVersion1 ? 1 : 2);
        $created          = (int) $meta[$createdKey];
        unset($meta[$createdKey], $paste['meta']);
        if (array_key_exists('expire_date', $meta)) {
            $expire_date = (int) $meta['expire_date'];
            unset($meta['expire_date']);
        }
        if (array_key_exists('opendiscussion', $meta)) {
            $opendiscussion = $meta['opendiscussion'];
            unset($meta['opendiscussion']);
        }
        if (array_key_exists('burnafterreading', $meta)) {
            $burnafterreading = $meta['burnafterreading'];
            unset($meta['burnafterreading']);
        }
        if ($isVersion1) {
            if (array_key_exists('attachment', $meta)) {
                $attachment = $meta['attachment'];
                unset($meta['attachment']);
            }
            if (array_key_exists('attachmentname', $meta)) {
                $attachmentname = $meta['attachmentname'];
                unset($meta['attachmentname']);
            }
        } else {
            $opendiscussion   = $paste['adata'][2];
            $burnafterreading = $paste['adata'][3];
        }
        return self::_exec(
            'INSERT INTO ' . self::_sanitizeIdentifier('paste') .
            ' VALUES(?,?,?,?,?,?,?,?,?)',
            array(
                $pasteid,
                $isVersion1 ? $paste['data'] : Json::encode($paste),
                $created,
                $expire_date,
                (int) $opendiscussion,
                (int) $burnafterreading,
                Json::encode($meta),
                $attachment,
                $attachmentname,
            )
        );
    }

    /**
     * Read a paste.
     *
     * @access public
     * @param  string $pasteid
     * @return array|false
     */
    public function read($pasteid)
    {
        if (array_key_exists($pasteid, self::$_cache)) {
            return self::$_cache[$pasteid];
        }

        self::$_cache[$pasteid] = false;
        $paste                  = self::_select(
            'SELECT * FROM ' . self::_sanitizeIdentifier('paste') .
            ' WHERE dataid = ?', array($pasteid), true
        );

        if ($paste === false) {

            return false;
        }
        // create array
        $data       = Json::decode($paste['data']);
        $isVersion2 = array_key_exists('v', $data) && $data['v'] >= 2;
        if ($isVersion2) {
            self::$_cache[$pasteid] = $data;
            list($createdKey)       = self::_getVersionedKeys(2);
        } else {
            self::$_cache[$pasteid] = array('data' => $paste['data']);
            list($createdKey)       = self::_getVersionedKeys(1);
        }

        $paste['meta'] = Json::decode($paste['meta']);
        if (!is_array($paste['meta'])) {

            $paste['meta'] = array();
        }
        $paste                                       = self::upgradePreV1Format($paste);
        self::$_cache[$pasteid]['meta']              = $paste['meta'];
        self::$_cache[$pasteid]['meta'][$createdKey] = (int) $paste['postdate'];
        $expire_date                                 = (int) $paste['expiredate'];
        if ($expire_date > 0) {
            self::$_cache[$pasteid]['meta']['expire_date'] = $expire_date;
        }
        if ($isVersion2) {
            return self::$_cache[$pasteid];
        }

        // support v1 attachments
        if (array_key_exists('attachment', $paste) && strlen($paste['attachment'])) {
            self::$_cache[$pasteid]['attachment'] = $paste['attachment'];
            if (array_key_exists('attachmentname', $paste) && strlen($paste['attachmentname'])) {
                self::$_cache[$pasteid]['attachmentname'] = $paste['attachmentname'];
            }
        }
        if ($paste['opendiscussion']) {
            self::$_cache[$pasteid]['meta']['opendiscussion'] = true;
        }
        if ($paste['burnafterreading']) {
            self::$_cache[$pasteid]['meta']['burnafterreading'] = true;
        }

        return self::$_cache[$pasteid];
    }

    /**
     * Delete a paste and its discussion.
     *
     * @access public
     * @param  string $pasteid
     */
    public function delete($pasteid)
    {
        self::_exec(
            'DELETE FROM ' . self::_sanitizeIdentifier('paste') .
            ' WHERE dataid = ?', array($pasteid)
        );
        self::_exec(
            'DELETE FROM ' . self::_sanitizeIdentifier('comment') .
            ' WHERE pasteid = ?', array($pasteid)
        );
        if (
            array_key_exists($pasteid, self::$_cache)
        ) {
            unset(self::$_cache[$pasteid]);
        }
    }

    /**
     * Test if a paste exists.
     *
     * @access public
     * @param  string $pasteid
     * @return bool
     */
    public function exists($pasteid)
    {
        if (
            !array_key_exists($pasteid, self::$_cache)
        ) {
            self::$_cache[$pasteid] = $this->read($pasteid);
        }
        return (bool) self::$_cache[$pasteid];
    }

    /**
     * Create a comment in a paste.
     *
     * @access public
     * @param  string $pasteid
     * @param  string $parentid
     * @param  string $commentid
     * @param  array  $comment
     * @return bool
     */
    public function createComment($pasteid, $parentid, $commentid, array $comment)
    {
        if (array_key_exists('data', $comment)) {
            $version = 1;
            $data    = $comment['data'];
        } else {
            $version = 2;
            $data    = Json::encode($comment);
        }
        list($createdKey, $iconKey) = self::_getVersionedKeys($version);
        $meta                       = $comment['meta'];
        unset($comment['meta']);
        foreach (array('nickname', $iconKey) as $key) {
            if (!array_key_exists($key, $meta)) {
                $meta[$key] = null;
            }
        }
        return self::_exec(
            'INSERT INTO ' . self::_sanitizeIdentifier('comment') .
            ' VALUES(?,?,?,?,?,?,?)',
            array(
                $commentid,
                $pasteid,
                $parentid,
                $data,
                $meta['nickname'],
                $meta[$iconKey],
                $meta[$createdKey],
            )
        );
    }

    /**
     * Read all comments of paste.
     *
     * @access public
     * @param  string $pasteid
     * @return array
     */
    public function readComments($pasteid)
    {
        $rows = self::_select(
            'SELECT * FROM ' . self::_sanitizeIdentifier('comment') .
            ' WHERE pasteid = ?', array($pasteid)
        );

        // create comment list
        $comments = array();
        if (count($rows)) {
            foreach ($rows as $row) {
                $i    = $this->getOpenSlot($comments, (int) $row['postdate']);
                $data = Json::decode($row['data']);
                if (array_key_exists('v', $data) && $data['v'] >= 2) {
                    $version      = 2;
                    $comments[$i] = $data;
                } else {
                    $version      = 1;
                    $comments[$i] = array('data' => $row['data']);
                }
                list($createdKey, $iconKey) = self::_getVersionedKeys($version);
                $comments[$i]['id']         = $row['dataid'];
                $comments[$i]['parentid']   = $row['parentid'];
                $comments[$i]['meta']       = array($createdKey => (int) $row['postdate']);
                foreach (array('nickname' => 'nickname', 'vizhash' => $iconKey) as $rowKey => $commentKey) {
                    if (array_key_exists($rowKey, $row) && !empty($row[$rowKey])) {
                        $comments[$i]['meta'][$commentKey] = $row[$rowKey];
                    }
                }
            }
            ksort($comments);
        }
        return $comments;
    }

    /**
     * Test if a comment exists.
     *
     * @access public
     * @param  string $pasteid
     * @param  string $parentid
     * @param  string $commentid
     * @return bool
     */
    public function existsComment($pasteid, $parentid, $commentid)
    {
        return (bool) self::_select(
            'SELECT dataid FROM ' . self::_sanitizeIdentifier('comment') .
            ' WHERE pasteid = ? AND parentid = ? AND dataid = ?',
            array($pasteid, $parentid, $commentid), true
        );
    }

    /**
     * Returns up to batch size number of paste ids that have expired
     *
     * @access private
     * @param  int $batchsize
     * @return array
     */
    protected function _getExpiredPastes($batchsize)
    {
        $pastes = array();
        $rows   = self::_select(
            'SELECT dataid FROM ' . self::_sanitizeIdentifier('paste') .
            ' WHERE expiredate < ? AND expiredate != ? LIMIT ?',
            array(time(), 0, $batchsize)
        );
        if (count($rows)) {
            foreach ($rows as $row) {
                $pastes[] = $row['dataid'];
            }
        }
        return $pastes;
    }

    /**
     * execute a statement
     *
     * @access private
     * @static
     * @param  string $sql
     * @param  array $params
     * @throws PDOException
     * @return bool
     */
    private static function _exec($sql, array $params)
    {
        $statement = self::$_db->prepare($sql);
        $result    = $statement->execute($params);
        $statement->closeCursor();
        return $result;
    }

    /**
     * run a select statement
     *
     * @access private
     * @static
     * @param  string $sql
     * @param  array $params
     * @param  bool $firstOnly if only the first row should be returned
     * @throws PDOException
     * @return array
     */
    private static function _select($sql, array $params, $firstOnly = false)
    {
        $statement = self::$_db->prepare($sql);
        $statement->execute($params);
        $result = $firstOnly ?
            $statement->fetch(PDO::FETCH_ASSOC) :
            $statement->fetchAll(PDO::FETCH_ASSOC);
        $statement->closeCursor();
        return $result;
    }

    /**
     * get version dependent key names
     *
     * @access private
     * @static
     * @param  int $version
     * @return array
     */
    private static function _getVersionedKeys($version)
    {
        if ($version === 1) {
            return array('postdate', 'vizhash');
        }
        return array('created', 'icon');
    }

    /**
     * get table list query, depending on the database type
     *
     * @access private
     * @static
     * @param  string $type
     * @throws Exception
     * @return string
     */
    private static function _getTableQuery($type)
    {
        switch ($type) {
            case 'ibm':
                $sql = 'SELECT tabname FROM SYSCAT.TABLES ';
                break;
            case 'informix':
                $sql = 'SELECT tabname FROM systables ';
                break;
            case 'mssql':
                $sql = 'SELECT name FROM sysobjects '
                     . "WHERE type = 'U' ORDER BY name";
                break;
            case 'mysql':
                $sql = 'SHOW TABLES';
                break;
            case 'oci':
                $sql = 'SELECT table_name FROM all_tables';
                break;
            case 'pgsql':
                $sql = 'SELECT c.relname AS table_name '
                     . 'FROM pg_class c, pg_user u '
                     . "WHERE c.relowner = u.usesysid AND c.relkind = 'r' "
                     . 'AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) '
                     . "AND c.relname !~ '^(pg_|sql_)' "
                     . 'UNION '
                     . 'SELECT c.relname AS table_name '
                     . 'FROM pg_class c '
                     . "WHERE c.relkind = 'r' "
                     . 'AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) '
                     . 'AND NOT EXISTS (SELECT 1 FROM pg_user WHERE usesysid = c.relowner) '
                     . "AND c.relname !~ '^pg_'";
                break;
            case 'sqlite':
                $sql = "SELECT name FROM sqlite_master WHERE type='table' "
                     . 'UNION ALL SELECT name FROM sqlite_temp_master '
                     . "WHERE type='table' ORDER BY name";
                break;
            default:
                throw new Exception(
                    "PDO type $type is currently not supported.", 5
                );
        }
        return $sql;
    }

    /**
     * get a value by key from the config table
     *
     * @access private
     * @static
     * @param  string $key
     * @throws PDOException
     * @return string
     */
    private static function _getConfig($key)
    {
        $row = self::_select(
            'SELECT value FROM ' . self::_sanitizeIdentifier('config') .
            ' WHERE id = ?', array($key), true
        );
        return $row['value'];
    }

    /**
     * get the primary key clauses, depending on the database driver
     *
     * @access private
     * @static
     * @param  string $key
     * @return array
     */
    private static function _getPrimaryKeyClauses($key = 'dataid')
    {
        $main_key = $after_key = '';
        if (self::$_type === 'mysql') {
            $after_key = ", PRIMARY KEY ($key)";
        } else {
            $main_key = ' PRIMARY KEY';
        }
        return array($main_key, $after_key);
    }

    /**
     * get the data type, depending on the database driver
     *
     * @access private
     * @static
     * @return string
     */
    private static function _getDataType()
    {
        return self::$_type === 'pgsql' ? 'TEXT' : 'BLOB';
    }

    /**
     * get the attachment type, depending on the database driver
     *
     * @access private
     * @static
     * @return string
     */
    private static function _getAttachmentType()
    {
        return self::$_type === 'pgsql' ? 'TEXT' : 'MEDIUMBLOB';
    }

    /**
     * create the paste table
     *
     * @access private
     * @static
     */
    private static function _createPasteTable()
    {
        list($main_key, $after_key) = self::_getPrimaryKeyClauses();
        $dataType                   = self::_getDataType();
        self::$_db->exec(
            'CREATE TABLE ' . self::_sanitizeIdentifier('paste') . ' ( ' .
            "dataid CHAR(16) NOT NULL$main_key, " .
            "data $dataType, " .
            'postdate INT, ' .
            'expiredate INT, ' .
            'opendiscussion INT, ' .
            'burnafterreading INT, ' .
            'meta TEXT, ' .
            'attachment ' . self::_getAttachmentType() . ', ' .
            "attachmentname $dataType$after_key );"
        );
    }

    /**
     * create the paste table
     *
     * @access private
     * @static
     */
    private static function _createCommentTable()
    {
        list($main_key, $after_key) = self::_getPrimaryKeyClauses();
        $dataType                   = self::_getDataType();
        self::$_db->exec(
            'CREATE TABLE ' . self::_sanitizeIdentifier('comment') . ' ( ' .
            "dataid CHAR(16) NOT NULL$main_key, " .
            'pasteid CHAR(16), ' .
            'parentid CHAR(16), ' .
            "data $dataType, " .
            "nickname $dataType, " .
            "vizhash $dataType, " .
            "postdate INT$after_key );"
        );
        self::$_db->exec(
            'CREATE INDEX IF NOT EXISTS comment_parent ON ' .
            self::_sanitizeIdentifier('comment') . '(pasteid);'
        );
    }

    /**
     * create the paste table
     *
     * @access private
     * @static
     */
    private static function _createConfigTable()
    {
        list($main_key, $after_key) = self::_getPrimaryKeyClauses('id');
        self::$_db->exec(
            'CREATE TABLE ' . self::_sanitizeIdentifier('config') .
            " ( id CHAR(16) NOT NULL$main_key, value TEXT$after_key );"
        );
        self::_exec(
            'INSERT INTO ' . self::_sanitizeIdentifier('config') .
            ' VALUES(?,?)',
            array('VERSION', Controller::VERSION)
        );
    }

    /**
     * sanitizes identifiers
     *
     * @access private
     * @static
     * @param  string $identifier
     * @return string
     */
    private static function _sanitizeIdentifier($identifier)
    {
        return preg_replace('/[^A-Za-z0-9_]+/', '', self::$_prefix . $identifier);
    }

    /**
     * upgrade the database schema from an old version
     *
     * @access private
     * @static
     * @param  string $oldversion
     */
    private static function _upgradeDatabase($oldversion)
    {
        $dataType = self::_getDataType();
        switch ($oldversion) {
            case '0.21':
                // create the meta column if necessary (pre 0.21 change)
                try {
                    self::$_db->exec('SELECT meta FROM ' . self::_sanitizeIdentifier('paste') . ' LIMIT 1;');
                } catch (PDOException $e) {
                    self::$_db->exec('ALTER TABLE ' . self::_sanitizeIdentifier('paste') . ' ADD COLUMN meta TEXT;');
                }
                // SQLite only allows one ALTER statement at a time...
                self::$_db->exec(
                    'ALTER TABLE ' . self::_sanitizeIdentifier('paste') .
                    ' ADD COLUMN attachment ' . self::_getAttachmentType() . ';'
                );
                self::$_db->exec(
                    'ALTER TABLE ' . self::_sanitizeIdentifier('paste') . " ADD COLUMN attachmentname $dataType;"
                );
                // SQLite doesn't support MODIFY, but it allows TEXT of similar
                // size as BLOB, so there is no need to change it there
                if (self::$_type !== 'sqlite') {
                    self::$_db->exec(
                        'ALTER TABLE ' . self::_sanitizeIdentifier('paste') .
                        ' ADD PRIMARY KEY (dataid), MODIFY COLUMN data $dataType;'
                    );
                    self::$_db->exec(
                        'ALTER TABLE ' . self::_sanitizeIdentifier('comment') .
                        " ADD PRIMARY KEY (dataid), MODIFY COLUMN data $dataType, " .
                        "MODIFY COLUMN nickname $dataType, MODIFY COLUMN vizhash $dataType;"
                    );
                } else {
                    self::$_db->exec(
                        'CREATE UNIQUE INDEX IF NOT EXISTS paste_dataid ON ' .
                        self::_sanitizeIdentifier('paste') . '(dataid);'
                    );
                    self::$_db->exec(
                        'CREATE UNIQUE INDEX IF NOT EXISTS comment_dataid ON ' .
                        self::_sanitizeIdentifier('comment') . '(dataid);'
                    );
                }
                self::$_db->exec(
                    'CREATE INDEX IF NOT EXISTS comment_parent ON ' .
                    self::_sanitizeIdentifier('comment') . '(pasteid);'
                );
                // no break, continue with updates for 0.22 and later
            default:
                self::_exec(
                    'UPDATE ' . self::_sanitizeIdentifier('config') .
                    ' SET value = ? WHERE id = ?',
                    array(Controller::VERSION, 'VERSION')
                );
        }
    }
}


1		<?php
2		/**
3		* PrivateBin
4		*
5		* a zero-knowledge paste bin
6		*
7		* @link https://github.com/PrivateBin/PrivateBin
8		* @copyright 2012 Sébastien SAUVAGE (sebsauvage.net)
9		* @license https://www.opensource.org/licenses/zlib-license.php The zlib/libpng License
10		* @version 1.2.1
11		*/
12
13		namespace PrivateBin\Data;
14
15		use Exception;
16		use PDO;
17		use PDOException;
18		use PrivateBin\Controller;
19		use PrivateBin\Json;
20
21		/**
22		* Database
23		*
24		* Model for database access, implemented as a singleton.
25		*/
26		class Database extends AbstractData
27		{
28		/**
29		* cache for select queries
30		*
31		* @var array
32		*/
33		private static $_cache = array();
34
35		/**
36		* instance of database connection
37		*
38		* @access private
39		* @static
40		* @var PDO
41		*/
42		private static $_db;
43
44		/**
45		* table prefix
46		*
47		* @access private
48		* @static
49		* @var string
50		*/
51		private static $_prefix = '';
52
53		/**
54		* database type
55		*
56		* @access private
57		* @static
58		* @var string
59		*/
60		private static $_type = '';
61
62		/**
63		* get instance of singleton
64		*
65		* @access public
66		* @static
67		* @param array $options
68		* @throws Exception
69		* @return Database
70		*/
71	69	public static function getInstance(array $options)
72		{
73		// if needed initialize the singleton
74	69	if (!(self::$_instance instanceof self)) {
75	38	self::$_instance = new self;
76		}
77
78	69	if (is_array($options)) {
		0 ignored issues – show introduced 2019-05-15 20:01 UTC by Report Bug Copy Issue Report The condition `is_array($options)` is always `true`. Loading history...
79		// set table prefix if given
80	69	if (array_key_exists('tbl', $options)) {
81	48	self::$_prefix = $options['tbl'];
82		}
83
84		// initialize the db connection with new options
85		if (
86	69	array_key_exists('dsn', $options) &&
87	69	array_key_exists('usr', $options) &&
88	69	array_key_exists('pwd', $options) &&
89	69	array_key_exists('opt', $options)
90		) {
91		// set default options
92	69	$options['opt'][PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
93	69	$options['opt'][PDO::ATTR_EMULATE_PREPARES] = false;
94	69	$options['opt'][PDO::ATTR_PERSISTENT] = true;
95	69	$db_tables_exist = true;
96
97		// setup type and dabase connection
98	69	self::$_type = strtolower(
99	69	substr($options['dsn'], 0, strpos($options['dsn'], ':'))
100		);
101	69	$tableQuery = self::_getTableQuery(self::$_type);
102	69	self::$_db = new PDO(
103	69	$options['dsn'],
104	69	$options['usr'],
105	69	$options['pwd'],
106	69	$options['opt']
107		);
108
109		// check if the database contains the required tables
110	69	$tables = self::$_db->query($tableQuery)->fetchAll(PDO::FETCH_COLUMN, 0);
111
112		// create paste table if necessary
113	69	if (!in_array(self::_sanitizeIdentifier('paste'), $tables)) {
114	40	self::_createPasteTable();
115	40	$db_tables_exist = false;
116		}
117
118		// create comment table if necessary
119	69	if (!in_array(self::_sanitizeIdentifier('comment'), $tables)) {
120	40	self::_createCommentTable();
121	40	$db_tables_exist = false;
122		}
123
124		// create config table if necessary
125	69	$db_version = Controller::VERSION;
126	69	if (!in_array(self::_sanitizeIdentifier('config'), $tables)) {
127	40	self::_createConfigTable();
128		// if we only needed to create the config table, the DB is older then 0.22
129	40	if ($db_tables_exist) {
130	40	$db_version = '0.21';
131		}
132		} else {
133	60	$db_version = self::_getConfig('VERSION');
134		}
135
136		// update database structure if necessary
137	69	if (version_compare($db_version, Controller::VERSION, '<')) {
138	69	self::_upgradeDatabase($db_version);
139		}
140		} else {
141	4	throw new Exception(
142	4	'Missing configuration for key dsn, usr, pwd or opt in the section model_options, please check your configuration file', 6
143		);
144		}
145		}
146
147	69	return self::$_instance;
148		}
149
150		/**
151		* Create a paste.
152		*
153		* @access public
154		* @param string $pasteid
155		* @param array $paste
156		* @return bool
157		*/
158	38	public function create($pasteid, array $paste)
159		{
160		if (
161	38	array_key_exists($pasteid, self::$_cache)
162		) {
163	38	if (false !== self::$_cache[$pasteid]) {
164	2	return false;
165		} else {
166	38	unset(self::$_cache[$pasteid]);
167		}
168		}
169
170	38	$expire_date = 0;
171	38	$opendiscussion = $burnafterreading = false;
172	38	$attachment = $attachmentname = null;
173	38	$meta = $paste['meta'];
174	38	$isVersion1 = array_key_exists('data', $paste);
175	38	list($createdKey) = self::_getVersionedKeys($isVersion1 ? 1 : 2);
176	38	$created = (int) $meta[$createdKey];
177	38	unset($meta[$createdKey], $paste['meta']);
178	38	if (array_key_exists('expire_date', $meta)) {
179	23	$expire_date = (int) $meta['expire_date'];
180	23	unset($meta['expire_date']);
181		}
182	38	if (array_key_exists('opendiscussion', $meta)) {
183	2	$opendiscussion = $meta['opendiscussion'];
184	2	unset($meta['opendiscussion']);
185		}
186	38	if (array_key_exists('burnafterreading', $meta)) {
187	1	$burnafterreading = $meta['burnafterreading'];
188	1	unset($meta['burnafterreading']);
189		}
190	38	if ($isVersion1) {
191	2	if (array_key_exists('attachment', $meta)) {
192	1	$attachment = $meta['attachment'];
193	1	unset($meta['attachment']);
194		}
195	2	if (array_key_exists('attachmentname', $meta)) {
196	1	$attachmentname = $meta['attachmentname'];
197	2	unset($meta['attachmentname']);
198		}
199		} else {
200	36	$opendiscussion = $paste['adata'][2];
201	36	$burnafterreading = $paste['adata'][3];
202		}
203	38	return self::_exec(
204	38	'INSERT INTO ' . self::_sanitizeIdentifier('paste') .
205	38	' VALUES(?,?,?,?,?,?,?,?,?)',
206		array(
207	38	$pasteid,
208	38	$isVersion1 ? $paste['data'] : Json::encode($paste),
209	38	$created,
210	38	$expire_date,
211	38	(int) $opendiscussion,
212	38	(int) $burnafterreading,
213	38	Json::encode($meta),
214	38	$attachment,
215	38	$attachmentname,
216		)
217		);
218		}
219
220		/**
221		* Read a paste.
222		*
223		* @access public
224		* @param string $pasteid
225		* @return array\|false
226		*/
227	54	public function read($pasteid)
228		{
229	54	if (array_key_exists($pasteid, self::$_cache)) {
230	26	return self::$_cache[$pasteid];
231		}
232
233	54	self::$_cache[$pasteid] = false;
234	54	$paste = self::_select(
235	54	'SELECT * FROM ' . self::_sanitizeIdentifier('paste') .
236	54	' WHERE dataid = ?', array($pasteid), true
237		);
238
239	54	if ($paste === false) {
		0 ignored issues – show introduced 2019-05-15 20:01 UTC by Report Bug Copy Issue Report The condition `$paste === false` is always `false`. Loading history...
240	52	return false;
241		}
242		// create array
243	37	$data = Json::decode($paste['data']);
244	37	$isVersion2 = array_key_exists('v', $data) && $data['v'] >= 2;
245	37	if ($isVersion2) {
246	34	self::$_cache[$pasteid] = $data;
247	34	list($createdKey) = self::_getVersionedKeys(2);
248		} else {
249	3	self::$_cache[$pasteid] = array('data' => $paste['data']);
250	3	list($createdKey) = self::_getVersionedKeys(1);
251		}
252
253	37	$paste['meta'] = Json::decode($paste['meta']);
254	37	if (!is_array($paste['meta'])) {
		0 ignored issues – show introduced 2019-05-15 20:01 UTC by Report Bug Copy Issue Report The condition `is_array($paste['meta'])` is always `true`. Loading history...
255		$paste['meta'] = array();
256		}
257	37	$paste = self::upgradePreV1Format($paste);
258	37	self::$_cache[$pasteid]['meta'] = $paste['meta'];
259	37	self::$_cache[$pasteid]['meta'][$createdKey] = (int) $paste['postdate'];
260	37	$expire_date = (int) $paste['expiredate'];
261	37	if ($expire_date > 0) {
262	22	self::$_cache[$pasteid]['meta']['expire_date'] = $expire_date;
263		}
264	37	if ($isVersion2) {
265	34	return self::$_cache[$pasteid];
266		}
267
268		// support v1 attachments
269	3	if (array_key_exists('attachment', $paste) && strlen($paste['attachment'])) {
270	2	self::$_cache[$pasteid]['attachment'] = $paste['attachment'];
271	2	if (array_key_exists('attachmentname', $paste) && strlen($paste['attachmentname'])) {
272	2	self::$_cache[$pasteid]['attachmentname'] = $paste['attachmentname'];
273		}
274		}
275	3	if ($paste['opendiscussion']) {
276	2	self::$_cache[$pasteid]['meta']['opendiscussion'] = true;
277		}
278	3	if ($paste['burnafterreading']) {
279	1	self::$_cache[$pasteid]['meta']['burnafterreading'] = true;
280		}
281
282	3	return self::$_cache[$pasteid];
283		}
284
285		/**
286		* Delete a paste and its discussion.
287		*
288		* @access public
289		* @param string $pasteid
290		*/
291	21	public function delete($pasteid)
292		{
293	21	self::_exec(
294	21	'DELETE FROM ' . self::_sanitizeIdentifier('paste') .
295	21	' WHERE dataid = ?', array($pasteid)
296		);
297	21	self::_exec(
298	21	'DELETE FROM ' . self::_sanitizeIdentifier('comment') .
299	21	' WHERE pasteid = ?', array($pasteid)
300		);
301		if (
302	21	array_key_exists($pasteid, self::$_cache)
303		) {
304	19	unset(self::$_cache[$pasteid]);
305		}
306	21	}
307
308		/**
309		* Test if a paste exists.
310		*
311		* @access public
312		* @param string $pasteid
313		* @return bool
314		*/
315	54	public function exists($pasteid)
316		{
317		if (
318	54	!array_key_exists($pasteid, self::$_cache)
319		) {
320	53	self::$_cache[$pasteid] = $this->read($pasteid);
321		}
322	54	return (bool) self::$_cache[$pasteid];
323		}
324
325		/**
326		* Create a comment in a paste.
327		*
328		* @access public
329		* @param string $pasteid
330		* @param string $parentid
331		* @param string $commentid
332		* @param array $comment
333		* @return bool
334		*/
335	8	public function createComment($pasteid, $parentid, $commentid, array $comment)
336		{
337	8	if (array_key_exists('data', $comment)) {
338	1	$version = 1;
339	1	$data = $comment['data'];
340		} else {
341	8	$version = 2;
342	8	$data = Json::encode($comment);
343		}
344	8	list($createdKey, $iconKey) = self::_getVersionedKeys($version);
345	8	$meta = $comment['meta'];
346	8	unset($comment['meta']);
347	8	foreach (array('nickname', $iconKey) as $key) {
348	8	if (!array_key_exists($key, $meta)) {
349	8	$meta[$key] = null;
350		}
351		}
352	8	return self::_exec(
353	8	'INSERT INTO ' . self::_sanitizeIdentifier('comment') .
354	8	' VALUES(?,?,?,?,?,?,?)',
355		array(
356	8	$commentid,
357	8	$pasteid,
358	8	$parentid,
359	8	$data,
360	8	$meta['nickname'],
361	8	$meta[$iconKey],
362	8	$meta[$createdKey],
363		)
364		);
365		}
366
367		/**
368		* Read all comments of paste.
369		*
370		* @access public
371		* @param string $pasteid
372		* @return array
373		*/
374	17	public function readComments($pasteid)
375		{
376	17	$rows = self::_select(
377	17	'SELECT * FROM ' . self::_sanitizeIdentifier('comment') .
378	17	' WHERE pasteid = ?', array($pasteid)
379		);
380
381		// create comment list
382	17	$comments = array();
383	17	if (count($rows)) {
384	6	foreach ($rows as $row) {
385	6	$i = $this->getOpenSlot($comments, (int) $row['postdate']);
386	6	$data = Json::decode($row['data']);
387	6	if (array_key_exists('v', $data) && $data['v'] >= 2) {
388	6	$version = 2;
389	6	$comments[$i] = $data;
390		} else {
391	1	$version = 1;
392	1	$comments[$i] = array('data' => $row['data']);
393		}
394	6	list($createdKey, $iconKey) = self::_getVersionedKeys($version);
395	6	$comments[$i]['id'] = $row['dataid'];
396	6	$comments[$i]['parentid'] = $row['parentid'];
397	6	$comments[$i]['meta'] = array($createdKey => (int) $row['postdate']);
398	6	foreach (array('nickname' => 'nickname', 'vizhash' => $iconKey) as $rowKey => $commentKey) {
399	6	if (array_key_exists($rowKey, $row) && !empty($row[$rowKey])) {
400	6	$comments[$i]['meta'][$commentKey] = $row[$rowKey];
401		}
402		}
403		}
404	6	ksort($comments);
405		}
406	17	return $comments;
407		}
408
409		/**
410		* Test if a comment exists.
411		*
412		* @access public
413		* @param string $pasteid
414		* @param string $parentid
415		* @param string $commentid
416		* @return bool
417		*/
418	11	public function existsComment($pasteid, $parentid, $commentid)
419		{
420	11	return (bool) self::_select(
421	11	'SELECT dataid FROM ' . self::_sanitizeIdentifier('comment') .
422	11	' WHERE pasteid = ? AND parentid = ? AND dataid = ?',
423	11	array($pasteid, $parentid, $commentid), true
424		);
425		}
426
427		/**
428		* Returns up to batch size number of paste ids that have expired
429		*
430		* @access private
431		* @param int $batchsize
432		* @return array
433		*/
434	13	protected function _getExpiredPastes($batchsize)
435		{
436	13	$pastes = array();
437	13	$rows = self::_select(
438	13	'SELECT dataid FROM ' . self::_sanitizeIdentifier('paste') .
439	13	' WHERE expiredate < ? AND expiredate != ? LIMIT ?',
440	13	array(time(), 0, $batchsize)
441		);
442	13	if (count($rows)) {
443	2	foreach ($rows as $row) {
444	2	$pastes[] = $row['dataid'];
445		}
446		}
447	13	return $pastes;
448		}
449
450		/**
451		* execute a statement
452		*
453		* @access private
454		* @static
455		* @param string $sql
456		* @param array $params
457		* @throws PDOException
458		* @return bool
459		*/
460	54	private static function _exec($sql, array $params)
461		{
462	54	$statement = self::$_db->prepare($sql);
463	54	$result = $statement->execute($params);
464	54	$statement->closeCursor();
465	54	return $result;
466		}
467
468		/**
469		* run a select statement
470		*
471		* @access private
472		* @static
473		* @param string $sql
474		* @param array $params
475		* @param bool $firstOnly if only the first row should be returned
476		* @throws PDOException
477		* @return array
478		*/
479	68	private static function _select($sql, array $params, $firstOnly = false)
480		{
481	68	$statement = self::$_db->prepare($sql);
482	68	$statement->execute($params);
483	68	$result = $firstOnly ?
484	68	$statement->fetch(PDO::FETCH_ASSOC) :
485	68	$statement->fetchAll(PDO::FETCH_ASSOC);
486	68	$statement->closeCursor();
487	68	return $result;
488		}
489
490		/**
491		* get version dependent key names
492		*
493		* @access private
494		* @static
495		* @param int $version
496		* @return array
497		*/
498	39	private static function _getVersionedKeys($version)
499		{
500	39	if ($version === 1) {
501	4	return array('postdate', 'vizhash');
502		}
503	36	return array('created', 'icon');
504		}
505
506		/**
507		* get table list query, depending on the database type
508		*
509		* @access private
510		* @static
511		* @param string $type
512		* @throws Exception
513		* @return string
514		*/
515	69	private static function _getTableQuery($type)
516		{
517		switch ($type) {
518	69	case 'ibm':
519	1	$sql = 'SELECT tabname FROM SYSCAT.TABLES ';
520	1	break;
521	69	case 'informix':
522	1	$sql = 'SELECT tabname FROM systables ';
523	1	break;
524	69	case 'mssql':
525		$sql = 'SELECT name FROM sysobjects '
526	1	. "WHERE type = 'U' ORDER BY name";
527	1	break;
528	69	case 'mysql':
529	1	$sql = 'SHOW TABLES';
530	1	break;
531	69	case 'oci':
532	1	$sql = 'SELECT table_name FROM all_tables';
533	1	break;
534	69	case 'pgsql':
535		$sql = 'SELECT c.relname AS table_name '
536		. 'FROM pg_class c, pg_user u '
537		. "WHERE c.relowner = u.usesysid AND c.relkind = 'r' "
538		. 'AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) '
539		. "AND c.relname !~ '^(pg_\|sql_)' "
540		. 'UNION '
541		. 'SELECT c.relname AS table_name '
542		. 'FROM pg_class c '
543		. "WHERE c.relkind = 'r' "
544		. 'AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) '
545		. 'AND NOT EXISTS (SELECT 1 FROM pg_user WHERE usesysid = c.relowner) '
546	1	. "AND c.relname !~ '^pg_'";
547	1	break;
548	69	case 'sqlite':
549		$sql = "SELECT name FROM sqlite_master WHERE type='table' "
550		. 'UNION ALL SELECT name FROM sqlite_temp_master '
551	69	. "WHERE type='table' ORDER BY name";
552	69	break;
553		default:
554	1	throw new Exception(
555	1	"PDO type $type is currently not supported.", 5
556		);
557		}
558	69	return $sql;
559		}
560
561		/**
562		* get a value by key from the config table
563		*
564		* @access private
565		* @static
566		* @param string $key
567		* @throws PDOException
568		* @return string
569		*/
570	60	private static function _getConfig($key)
571		{
572	60	$row = self::_select(
573	60	'SELECT value FROM ' . self::_sanitizeIdentifier('config') .
574	60	' WHERE id = ?', array($key), true
575		);
576	60	return $row['value'];
577		}
578
579		/**
580		* get the primary key clauses, depending on the database driver
581		*
582		* @access private
583		* @static
584		* @param string $key
585		* @return array
586		*/
587	40	private static function _getPrimaryKeyClauses($key = 'dataid')
588		{
589	40	$main_key = $after_key = '';
590	40	if (self::$_type === 'mysql') {
591		$after_key = ", PRIMARY KEY ($key)";
592		} else {
593	40	$main_key = ' PRIMARY KEY';
594		}
595	40	return array($main_key, $after_key);
596		}
597
598		/**
599		* get the data type, depending on the database driver
600		*
601		* @access private
602		* @static
603		* @return string
604		*/
605	40	private static function _getDataType()
606		{
607	40	return self::$_type === 'pgsql' ? 'TEXT' : 'BLOB';
608		}
609
610		/**
611		* get the attachment type, depending on the database driver
612		*
613		* @access private
614		* @static
615		* @return string
616		*/
617	40	private static function _getAttachmentType()
618		{
619	40	return self::$_type === 'pgsql' ? 'TEXT' : 'MEDIUMBLOB';
620		}
621
622		/**
623		* create the paste table
624		*
625		* @access private
626		* @static
627		*/
628	40	private static function _createPasteTable()
629		{
630	40	list($main_key, $after_key) = self::_getPrimaryKeyClauses();
631	40	$dataType = self::_getDataType();
632	40	self::$_db->exec(
633	40	'CREATE TABLE ' . self::_sanitizeIdentifier('paste') . ' ( ' .
634	40	"dataid CHAR(16) NOT NULL$main_key, " .
635	40	"data $dataType, " .
636	40	'postdate INT, ' .
637	40	'expiredate INT, ' .
638	40	'opendiscussion INT, ' .
639	40	'burnafterreading INT, ' .
640	40	'meta TEXT, ' .
641	40	'attachment ' . self::_getAttachmentType() . ', ' .
642	40	"attachmentname $dataType$after_key );"
643		);
644	40	}
645
646		/**
647		* create the paste table
648		*
649		* @access private
650		* @static
651		*/
652	40	private static function _createCommentTable()
653		{
654	40	list($main_key, $after_key) = self::_getPrimaryKeyClauses();
655	40	$dataType = self::_getDataType();
656	40	self::$_db->exec(
657	40	'CREATE TABLE ' . self::_sanitizeIdentifier('comment') . ' ( ' .
658	40	"dataid CHAR(16) NOT NULL$main_key, " .
659	40	'pasteid CHAR(16), ' .
660	40	'parentid CHAR(16), ' .
661	40	"data $dataType, " .
662	40	"nickname $dataType, " .
663	40	"vizhash $dataType, " .
664	40	"postdate INT$after_key );"
665		);
666	40	self::$_db->exec(
667		'CREATE INDEX IF NOT EXISTS comment_parent ON ' .
668	40	self::_sanitizeIdentifier('comment') . '(pasteid);'
669		);
670	40	}
671
672		/**
673		* create the paste table
674		*
675		* @access private
676		* @static
677		*/
678	40	private static function _createConfigTable()
679		{
680	40	list($main_key, $after_key) = self::_getPrimaryKeyClauses('id');
681	40	self::$_db->exec(
682	40	'CREATE TABLE ' . self::_sanitizeIdentifier('config') .
683	40	" ( id CHAR(16) NOT NULL$main_key, value TEXT$after_key );"
684		);
685	40	self::_exec(
686	40	'INSERT INTO ' . self::_sanitizeIdentifier('config') .
687	40	' VALUES(?,?)',
688	40	array('VERSION', Controller::VERSION)
689		);
690	40	}
691
692		/**
693		* sanitizes identifiers
694		*
695		* @access private
696		* @static
697		* @param string $identifier
698		* @return string
699		*/
700	69	private static function _sanitizeIdentifier($identifier)
701		{
702	69	return preg_replace('/[^A-Za-z0-9_]+/', '', self::$_prefix . $identifier);
703		}
704
705		/**
706		* upgrade the database schema from an old version
707		*
708		* @access private
709		* @static
710		* @param string $oldversion
711		*/
712	1	private static function _upgradeDatabase($oldversion)
713		{
714	1	$dataType = self::_getDataType();
715		switch ($oldversion) {
716	1	case '0.21':
717		// create the meta column if necessary (pre 0.21 change)
718		try {
719	1	self::$_db->exec('SELECT meta FROM ' . self::_sanitizeIdentifier('paste') . ' LIMIT 1;');
720	1	} catch (PDOException $e) {
721	1	self::$_db->exec('ALTER TABLE ' . self::_sanitizeIdentifier('paste') . ' ADD COLUMN meta TEXT;');
722		}
723		// SQLite only allows one ALTER statement at a time...
724	1	self::$_db->exec(
725	1	'ALTER TABLE ' . self::_sanitizeIdentifier('paste') .
726	1	' ADD COLUMN attachment ' . self::_getAttachmentType() . ';'
727		);
728	1	self::$_db->exec(
729	1	'ALTER TABLE ' . self::_sanitizeIdentifier('paste') . " ADD COLUMN attachmentname $dataType;"
730		);
731		// SQLite doesn't support MODIFY, but it allows TEXT of similar
732		// size as BLOB, so there is no need to change it there
733	1	if (self::$_type !== 'sqlite') {
734		self::$_db->exec(
735		'ALTER TABLE ' . self::_sanitizeIdentifier('paste') .
736		' ADD PRIMARY KEY (dataid), MODIFY COLUMN data $dataType;'
737		);
738		self::$_db->exec(
739		'ALTER TABLE ' . self::_sanitizeIdentifier('comment') .
740		" ADD PRIMARY KEY (dataid), MODIFY COLUMN data $dataType, " .
741		"MODIFY COLUMN nickname $dataType, MODIFY COLUMN vizhash $dataType;"
742		);
743		} else {
744	1	self::$_db->exec(
745		'CREATE UNIQUE INDEX IF NOT EXISTS paste_dataid ON ' .
746	1	self::_sanitizeIdentifier('paste') . '(dataid);'
747		);
748	1	self::$_db->exec(
749		'CREATE UNIQUE INDEX IF NOT EXISTS comment_dataid ON ' .
750	1	self::_sanitizeIdentifier('comment') . '(dataid);'
751		);
752		}
753	1	self::$_db->exec(
754		'CREATE INDEX IF NOT EXISTS comment_parent ON ' .
755	1	self::_sanitizeIdentifier('comment') . '(pasteid);'
756		);
757		// no break, continue with updates for 0.22 and later
758		default:
759	1	self::_exec(
760	1	'UPDATE ' . self::_sanitizeIdentifier('config') .
761	1	' SET value = ? WHERE id = ?',
762	1	array(Controller::VERSION, 'VERSION')
763		);
764		}
765	1	}
766		}
767

PrivateBin / PrivateBin

Pull Request — master (#431)

Database::_getVersionedKeys() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like