Code Coverage
 
Classes and Traits
Functions and Methods
Lines
Total
0.00% covered (danger)
0.00%
0 / 1
48.15% covered (danger)
48.15%
13 / 27
CRAP
89.46% covered (success)
89.46%
348 / 389
Database
0.00% covered (danger)
0.00%
0 / 1
48.15% covered (danger)
48.15%
13 / 27
139.43
89.46% covered (success)
89.46%
348 / 389
 __construct
0.00% covered (danger)
0.00%
0 / 1
13
97.37% covered (success)
97.37%
37 / 38
 create
100.00% covered (success)
100.00%
1 / 1
10
100.00% covered (success)
100.00%
40 / 40
 read
0.00% covered (danger)
0.00%
0 / 1
14.04
94.12% covered (success)
94.12%
32 / 34
 delete
100.00% covered (success)
100.00%
1 / 1
1
100.00% covered (success)
100.00%
7 / 7
 exists
100.00% covered (success)
100.00%
1 / 1
2
100.00% covered (success)
100.00%
6 / 6
 createComment
100.00% covered (success)
100.00%
1 / 1
5
100.00% covered (success)
100.00%
23 / 23
 readComments
100.00% covered (success)
100.00%
1 / 1
9
100.00% covered (success)
100.00%
22 / 22
 existsComment
100.00% covered (success)
100.00%
1 / 1
2
100.00% covered (success)
100.00%
6 / 6
 setValue
0.00% covered (danger)
0.00%
0 / 1
3.10
77.78% covered (warning)
77.78%
7 / 9
 getValue
0.00% covered (danger)
0.00%
0 / 1
8.06
90.48% covered (success)
90.48%
19 / 21
 _getExpiredPastes
100.00% covered (success)
100.00%
1 / 1
2
100.00% covered (success)
100.00%
6 / 6
 getAllPastes
0.00% covered (danger)
0.00%
0 / 1
2
0.00% covered (danger)
0.00%
0 / 3
 _exec
0.00% covered (danger)
0.00%
0 / 1
5.02
90.91% covered (success)
90.91%
10 / 11
 _select
0.00% covered (danger)
0.00%
0 / 1
10.14
60.00% covered (warning)
60.00%
9 / 15
 _getVersionedKeys
100.00% covered (success)
100.00%
1 / 1
2
100.00% covered (success)
100.00%
3 / 3
 _getTableQuery
100.00% covered (success)
100.00%
1 / 1
8
100.00% covered (success)
100.00%
25 / 25
 _getConfig
0.00% covered (danger)
0.00%
0 / 1
3.33
66.67% covered (warning)
66.67%
4 / 6
 _getPrimaryKeyClauses
0.00% covered (danger)
0.00%
0 / 1
3.10
77.78% covered (warning)
77.78%
7 / 9
 _getDataType
0.00% covered (danger)
0.00%
0 / 1
3.33
66.67% covered (warning)
66.67%
4 / 6
 _getAttachmentType
0.00% covered (danger)
0.00%
0 / 1
3.33
66.67% covered (warning)
66.67%
4 / 6
 _getMetaType
0.00% covered (danger)
0.00%
0 / 1
2.06
75.00% covered (warning)
75.00%
3 / 4
 _createPasteTable
100.00% covered (success)
100.00%
1 / 1
1
100.00% covered (success)
100.00%
16 / 16
 _createCommentTable
0.00% covered (danger)
0.00%
0 / 1
2.01
88.89% covered (success)
88.89%
16 / 18
 _createConfigTable
100.00% covered (success)
100.00%
1 / 1
2
100.00% covered (success)
100.00%
11 / 11
 _sanitizeClob
100.00% covered (success)
100.00%
1 / 1
2
100.00% covered (success)
100.00%
3 / 3
 _sanitizeIdentifier
100.00% covered (success)
100.00%
1 / 1
1
100.00% covered (success)
100.00%
1 / 1
 _upgradeDatabase
0.00% covered (danger)
0.00%
0 / 1
10.20
67.50% covered (warning)
67.50%
27 / 40
<?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.5.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
{
    /**
     * instance of database connection
     *
     * @access private
     * @var PDO
     */
    private $_db;
    /**
     * table prefix
     *
     * @access private
     * @var string
     */
    private $_prefix = '';
    /**
     * database type
     *
     * @access private
     * @var string
     */
    private $_type = '';
    /**
     * instantiates a new Database data backend
     *
     * @access public
     * @param  array $options
     * @throws Exception
     * @return
     */
    public function __construct(array $options)
    {
        // set table prefix if given
        if (array_key_exists('tbl', $options)) {
            $this->_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
            $this->_type = strtolower(
                substr($options['dsn'], 0, strpos($options['dsn'], ':'))
            );
            // MySQL uses backticks to quote identifiers by default,
            // tell it to expect ANSI SQL double quotes
            if ($this->_type === 'mysql' && defined('PDO::MYSQL_ATTR_INIT_COMMAND')) {
                $options['opt'][PDO::MYSQL_ATTR_INIT_COMMAND] = "SET SESSION sql_mode='ANSI_QUOTES'";
            }
            $tableQuery = $this->_getTableQuery($this->_type);
            $this->_db  = new PDO(
                $options['dsn'],
                $options['usr'],
                $options['pwd'],
                $options['opt']
            );
            // check if the database contains the required tables
            $tables = $this->_db->query($tableQuery)->fetchAll(PDO::FETCH_COLUMN, 0);
            // create paste table if necessary
            if (!in_array($this->_sanitizeIdentifier('paste'), $tables)) {
                $this->_createPasteTable();
                $db_tables_exist = false;
            }
            // create comment table if necessary
            if (!in_array($this->_sanitizeIdentifier('comment'), $tables)) {
                $this->_createCommentTable();
                $db_tables_exist = false;
            }
            // create config table if necessary
            $db_version = Controller::VERSION;
            if (!in_array($this->_sanitizeIdentifier('config'), $tables)) {
                $this->_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 = $this->_getConfig('VERSION');
            }
            // update database structure if necessary
            if (version_compare($db_version, Controller::VERSION, '<')) {
                $this->_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
            );
        }
    }
    /**
     * Create a paste.
     *
     * @access public
     * @param  string $pasteid
     * @param  array  $paste
     * @return bool
     */
    public function create($pasteid, array $paste)
    {
        $expire_date      = 0;
        $opendiscussion   = $burnafterreading = false;
        $attachment       = $attachmentname   = null;
        $meta             = $paste['meta'];
        $isVersion1       = array_key_exists('data', $paste);
        list($createdKey) = $this->_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];
        }
        try {
            return $this->_exec(
                'INSERT INTO "' . $this->_sanitizeIdentifier('paste') .
                '" VALUES(?,?,?,?,?,?,?,?,?)',
                array(
                    $pasteid,
                    $isVersion1 ? $paste['data'] : Json::encode($paste),
                    $created,
                    $expire_date,
                    (int) $opendiscussion,
                    (int) $burnafterreading,
                    Json::encode($meta),
                    $attachment,
                    $attachmentname,
                )
            );
        } catch (Exception $e) {
            return false;
        }
    }
    /**
     * Read a paste.
     *
     * @access public
     * @param  string $pasteid
     * @return array|false
     */
    public function read($pasteid)
    {
        try {
            $row = $this->_select(
                'SELECT * FROM "' . $this->_sanitizeIdentifier('paste') .
                '" WHERE "dataid" = ?', array($pasteid), true
            );
        } catch (Exception $e) {
            $row = false;
        }
        if ($row === false) {
            return false;
        }
        // create array
        $data       = Json::decode($row['data']);
        $isVersion2 = array_key_exists('v', $data) && $data['v'] >= 2;
        if ($isVersion2) {
            $paste            = $data;
            list($createdKey) = $this->_getVersionedKeys(2);
        } else {
            $paste            = array('data' => $row['data']);
            list($createdKey) = $this->_getVersionedKeys(1);
        }
        try {
            $row['meta'] = Json::decode($row['meta']);
        } catch (Exception $e) {
            $row['meta'] = array();
        }
        $row                        = self::upgradePreV1Format($row);
        $paste['meta']              = $row['meta'];
        $paste['meta'][$createdKey] = (int) $row['postdate'];
        $expire_date                = (int) $row['expiredate'];
        if ($expire_date > 0) {
            $paste['meta']['expire_date'] = $expire_date;
        }
        if ($isVersion2) {
            return $paste;
        }
        // support v1 attachments
        if (array_key_exists('attachment', $row) && !empty($row['attachment'])) {
            $paste['attachment'] = $row['attachment'];
            if (array_key_exists('attachmentname', $row) && !empty($row['attachmentname'])) {
                $paste['attachmentname'] = $row['attachmentname'];
            }
        }
        if ($row['opendiscussion']) {
            $paste['meta']['opendiscussion'] = true;
        }
        if ($row['burnafterreading']) {
            $paste['meta']['burnafterreading'] = true;
        }
        return $paste;
    }
    /**
     * Delete a paste and its discussion.
     *
     * @access public
     * @param  string $pasteid
     */
    public function delete($pasteid)
    {
        $this->_exec(
            'DELETE FROM "' . $this->_sanitizeIdentifier('paste') .
            '" WHERE "dataid" = ?', array($pasteid)
        );
        $this->_exec(
            'DELETE FROM "' . $this->_sanitizeIdentifier('comment') .
            '" WHERE "pasteid" = ?', array($pasteid)
        );
    }
    /**
     * Test if a paste exists.
     *
     * @access public
     * @param  string $pasteid
     * @return bool
     */
    public function exists($pasteid)
    {
        try {
            $row = $this->_select(
                'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('paste') .
                '" WHERE "dataid" = ?', array($pasteid), true
            );
        } catch (Exception $e) {
            return false;
        }
        return (bool) $row;
    }
    /**
     * 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) = $this->_getVersionedKeys($version);
        $meta                       = $comment['meta'];
        unset($comment['meta']);
        foreach (array('nickname', $iconKey) as $key) {
            if (!array_key_exists($key, $meta)) {
                $meta[$key] = null;
            }
        }
        try {
            return $this->_exec(
                'INSERT INTO "' . $this->_sanitizeIdentifier('comment') .
                '" VALUES(?,?,?,?,?,?,?)',
                array(
                    $commentid,
                    $pasteid,
                    $parentid,
                    $data,
                    $meta['nickname'],
                    $meta[$iconKey],
                    $meta[$createdKey],
                )
            );
        } catch (Exception $e) {
            return false;
        }
    }
    /**
     * Read all comments of paste.
     *
     * @access public
     * @param  string $pasteid
     * @return array
     */
    public function readComments($pasteid)
    {
        $rows = $this->_select(
            'SELECT * FROM "' . $this->_sanitizeIdentifier('comment') .
            '" WHERE "pasteid" = ?', array($pasteid)
        );
        // create comment list
        $comments = array();
        if (is_array($rows) && 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) = $this->_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)
    {
        try {
            return (bool) $this->_select(
                'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('comment') .
                '" WHERE "pasteid" = ? AND "parentid" = ? AND "dataid" = ?',
                array($pasteid, $parentid, $commentid), true
            );
        } catch (Exception $e) {
            return false;
        }
    }
    /**
     * Save a value.
     *
     * @access public
     * @param  string $value
     * @param  string $namespace
     * @param  string $key
     * @return bool
     */
    public function setValue($value, $namespace, $key = '')
    {
        if ($namespace === 'traffic_limiter') {
            $this->_last_cache[$key] = $value;
            try {
                $value = Json::encode($this->_last_cache);
            } catch (Exception $e) {
                return false;
            }
        }
        return $this->_exec(
            'UPDATE "' . $this->_sanitizeIdentifier('config') .
            '" SET "value" = ? WHERE "id" = ?',
            array($value, strtoupper($namespace))
        );
    }
    /**
     * Load a value.
     *
     * @access public
     * @param  string $namespace
     * @param  string $key
     * @return string
     */
    public function getValue($namespace, $key = '')
    {
        $configKey = strtoupper($namespace);
        $value     = $this->_getConfig($configKey);
        if ($value === '') {
            // initialize the row, so that setValue can rely on UPDATE queries
            $this->_exec(
                'INSERT INTO "' . $this->_sanitizeIdentifier('config') .
                '" VALUES(?,?)',
                array($configKey, '')
            );
            // migrate filesystem based salt into database
            $file = 'data' . DIRECTORY_SEPARATOR . 'salt.php';
            if ($namespace === 'salt' && is_readable($file)) {
                $fs    = new Filesystem(array('dir' => 'data'));
                $value = $fs->getValue('salt');
                $this->setValue($value, 'salt');
                @unlink($file);
                return $value;
            }
        }
        if ($value && $namespace === 'traffic_limiter') {
            try {
                $this->_last_cache = Json::decode($value);
            } catch (Exception $e) {
                $this->_last_cache = array();
            }
            if (array_key_exists($key, $this->_last_cache)) {
                return $this->_last_cache[$key];
            }
        }
        return (string) $value;
    }
    /**
     * Returns up to batch size number of paste ids that have expired
     *
     * @access private
     * @param  int $batchsize
     * @return array
     */
    protected function _getExpiredPastes($batchsize)
    {
        $statement = $this->_db->prepare(
            'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('paste') .
            '" WHERE "expiredate" < ? AND "expiredate" != ? ' .
            ($this->_type === 'oci' ? 'FETCH NEXT ? ROWS ONLY' : 'LIMIT ?')
        );
        $statement->execute(array(time(), 0, $batchsize));
        return $statement->fetchAll(PDO::FETCH_COLUMN, 0);
    }
    /**
     * @inheritDoc
     */
    public function getAllPastes()
    {
        return $this->_db->query(
            'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('paste') . '"'
        )->fetchAll(PDO::FETCH_COLUMN, 0);
    }
    /**
     * execute a statement
     *
     * @access private
     * @param  string $sql
     * @param  array $params
     * @throws PDOException
     * @return bool
     */
    private function _exec($sql, array $params)
    {
        $statement = $this->_db->prepare($sql);
        foreach ($params as $key => &$parameter) {
            $position = $key + 1;
            if (is_int($parameter)) {
                $statement->bindParam($position, $parameter, PDO::PARAM_INT);
            } elseif (is_string($parameter) && strlen($parameter) >= 4000) {
                $statement->bindParam($position, $parameter, PDO::PARAM_STR, strlen($parameter));
            } else {
                $statement->bindParam($position, $parameter);
            }
        }
        $result = $statement->execute();
        $statement->closeCursor();
        return $result;
    }
    /**
     * run a select statement
     *
     * @access private
     * @param  string $sql
     * @param  array $params
     * @param  bool $firstOnly if only the first row should be returned
     * @throws PDOException
     * @return array|false
     */
    private function _select($sql, array $params, $firstOnly = false)
    {
        $statement = $this->_db->prepare($sql);
        $statement->execute($params);
        if ($firstOnly) {
            $result = $statement->fetch(PDO::FETCH_ASSOC);
        } elseif ($this->_type === 'oci') {
            // workaround for https://bugs.php.net/bug.php?id=46728
            $result = array();
            while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
                $result[] = array_map('PrivateBin\Data\Database::_sanitizeClob', $row);
            }
        } else {
            $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        }
        $statement->closeCursor();
        if ($this->_type === 'oci' && is_array($result)) {
            // returned CLOB values are streams, convert these into strings
            $result = $firstOnly ?
                array_map('PrivateBin\Data\Database::_sanitizeClob', $result) :
                $result;
        }
        return $result;
    }
    /**
     * get version dependent key names
     *
     * @access private
     * @param  int $version
     * @return array
     */
    private 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
     * @param  string $type
     * @throws Exception
     * @return string
     */
    private function _getTableQuery($type)
    {
        switch ($type) {
            case 'ibm':
                $sql = 'SELECT "tabname" FROM "SYSCAT"."TABLES"';
                break;
            case 'informix':
                $sql = 'SELECT "tabname" FROM "systables"';
                break;
            case 'mssql':
                // U: tables created by the user
                $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
                );
        }