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 | |
); | |
} | |