Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
48.15% |
13 / 27 |
CRAP | |
89.46% |
348 / 389 |
| Database | |
0.00% |
0 / 1 |
|
48.15% |
13 / 27 |
139.43 | |
89.46% |
348 / 389 |
| __construct | |
0.00% |
0 / 1 |
13 | |
97.37% |
37 / 38 |
|||
| create | |
100.00% |
1 / 1 |
10 | |
100.00% |
40 / 40 |
|||
| read | |
0.00% |
0 / 1 |
14.04 | |
94.12% |
32 / 34 |
|||
| delete | |
100.00% |
1 / 1 |
1 | |
100.00% |
7 / 7 |
|||
| exists | |
100.00% |
1 / 1 |
2 | |
100.00% |
6 / 6 |
|||
| createComment | |
100.00% |
1 / 1 |
5 | |
100.00% |
23 / 23 |
|||
| readComments | |
100.00% |
1 / 1 |
9 | |
100.00% |
22 / 22 |
|||
| existsComment | |
100.00% |
1 / 1 |
2 | |
100.00% |
6 / 6 |
|||
| setValue | |
0.00% |
0 / 1 |
3.10 | |
77.78% |
7 / 9 |
|||
| getValue | |
0.00% |
0 / 1 |
8.06 | |
90.48% |
19 / 21 |
|||
| _getExpiredPastes | |
100.00% |
1 / 1 |
2 | |
100.00% |
6 / 6 |
|||
| getAllPastes | |
0.00% |
0 / 1 |
2 | |
0.00% |
0 / 3 |
|||
| _exec | |
0.00% |
0 / 1 |
5.02 | |
90.91% |
10 / 11 |
|||
| _select | |
0.00% |
0 / 1 |
10.14 | |
60.00% |
9 / 15 |
|||
| _getVersionedKeys | |
100.00% |
1 / 1 |
2 | |
100.00% |
3 / 3 |
|||
| _getTableQuery | |
100.00% |
1 / 1 |
8 | |
100.00% |
25 / 25 |
|||
| _getConfig | |
0.00% |
0 / 1 |
3.33 | |
66.67% |
4 / 6 |
|||
| _getPrimaryKeyClauses | |
0.00% |
0 / 1 |
3.10 | |
77.78% |
7 / 9 |
|||
| _getDataType | |
0.00% |
0 / 1 |
3.33 | |
66.67% |
4 / 6 |
|||
| _getAttachmentType | |
0.00% |
0 / 1 |
3.33 | |
66.67% |
4 / 6 |
|||
| _getMetaType | |
0.00% |
0 / 1 |
2.06 | |
75.00% |
3 / 4 |
|||
| _createPasteTable | |
100.00% |
1 / 1 |
1 | |
100.00% |
16 / 16 |
|||
| _createCommentTable | |
0.00% |
0 / 1 |
2.01 | |
88.89% |
16 / 18 |
|||
| _createConfigTable | |
100.00% |
1 / 1 |
2 | |
100.00% |
11 / 11 |
|||
| _sanitizeClob | |
100.00% |
1 / 1 |
2 | |
100.00% |
3 / 3 |
|||
| _sanitizeIdentifier | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| _upgradeDatabase | |
0.00% |
0 / 1 |
10.20 | |
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 | |
| ); | |
| } |