Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | |
89.27% |
391 / 438 |
|
48.15% |
13 / 27 |
CRAP | |
0.00% |
0 / 1 |
Database | |
89.27% |
391 / 438 |
|
48.15% |
13 / 27 |
148.24 | |
0.00% |
0 / 1 |
__construct | |
97.50% |
39 / 40 |
|
0.00% |
0 / 1 |
13 | |||
create | |
100.00% |
43 / 43 |
|
100.00% |
1 / 1 |
10 | |||
read | |
94.29% |
33 / 35 |
|
0.00% |
0 / 1 |
14.04 | |||
delete | |
100.00% |
8 / 8 |
|
100.00% |
1 / 1 |
1 | |||
exists | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
2 | |||
createComment | |
100.00% |
26 / 26 |
|
100.00% |
1 / 1 |
5 | |||
readComments | |
100.00% |
23 / 23 |
|
100.00% |
1 / 1 |
9 | |||
existsComment | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
2 | |||
setValue | |
80.00% |
8 / 10 |
|
0.00% |
0 / 1 |
3.07 | |||
getValue | |
90.91% |
20 / 22 |
|
0.00% |
0 / 1 |
8.05 | |||
_getExpiredPastes | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
2 | |||
getAllPastes | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
2 | |||
_exec | |
90.91% |
10 / 11 |
|
0.00% |
0 / 1 |
5.02 | |||
_select | |
60.00% |
9 / 15 |
|
0.00% |
0 / 1 |
10.14 | |||
_getVersionedKeys | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
_getTableQuery | |
100.00% |
39 / 39 |
|
100.00% |
1 / 1 |
9 | |||
_getConfig | |
71.43% |
5 / 7 |
|
0.00% |
0 / 1 |
3.21 | |||
_getPrimaryKeyClauses | |
77.78% |
7 / 9 |
|
0.00% |
0 / 1 |
4.18 | |||
_getDataType | |
66.67% |
4 / 6 |
|
0.00% |
0 / 1 |
4.59 | |||
_getAttachmentType | |
66.67% |
4 / 6 |
|
0.00% |
0 / 1 |
4.59 | |||
_getMetaType | |
75.00% |
3 / 4 |
|
0.00% |
0 / 1 |
3.14 | |||
_createPasteTable | |
100.00% |
16 / 16 |
|
100.00% |
1 / 1 |
1 | |||
_createCommentTable | |
78.26% |
18 / 23 |
|
0.00% |
0 / 1 |
2.04 | |||
_createConfigTable | |
100.00% |
12 / 12 |
|
100.00% |
1 / 1 |
2 | |||
_sanitizeClob | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
_sanitizeIdentifier | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
_upgradeDatabase | |
69.23% |
36 / 52 |
|
0.00% |
0 / 1 |
11.36 |
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.7.0 |
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 | * instance of database connection |
30 | * |
31 | * @access private |
32 | * @var PDO |
33 | */ |
34 | private $_db; |
35 | |
36 | /** |
37 | * table prefix |
38 | * |
39 | * @access private |
40 | * @var string |
41 | */ |
42 | private $_prefix = ''; |
43 | |
44 | /** |
45 | * database type |
46 | * |
47 | * @access private |
48 | * @var string |
49 | */ |
50 | private $_type = ''; |
51 | |
52 | /** |
53 | * instantiates a new Database data backend |
54 | * |
55 | * @access public |
56 | * @param array $options |
57 | * @throws Exception |
58 | * @return |
59 | */ |
60 | public function __construct(array $options) |
61 | { |
62 | // set table prefix if given |
63 | if (array_key_exists('tbl', $options)) { |
64 | $this->_prefix = $options['tbl']; |
65 | } |
66 | |
67 | // initialize the db connection with new options |
68 | if ( |
69 | array_key_exists('dsn', $options) && |
70 | array_key_exists('usr', $options) && |
71 | array_key_exists('pwd', $options) && |
72 | array_key_exists('opt', $options) |
73 | ) { |
74 | // set default options |
75 | $options['opt'][PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION; |
76 | $options['opt'][PDO::ATTR_EMULATE_PREPARES] = false; |
77 | $options['opt'][PDO::ATTR_PERSISTENT] = true; |
78 | $db_tables_exist = true; |
79 | |
80 | // setup type and dabase connection |
81 | $this->_type = strtolower( |
82 | substr($options['dsn'], 0, strpos($options['dsn'], ':')) |
83 | ); |
84 | // MySQL uses backticks to quote identifiers by default, |
85 | // tell it to expect ANSI SQL double quotes |
86 | if ($this->_type === 'mysql' && defined('PDO::MYSQL_ATTR_INIT_COMMAND')) { |
87 | $options['opt'][PDO::MYSQL_ATTR_INIT_COMMAND] = "SET SESSION sql_mode='ANSI_QUOTES'"; |
88 | } |
89 | $tableQuery = $this->_getTableQuery($this->_type); |
90 | $this->_db = new PDO( |
91 | $options['dsn'], |
92 | $options['usr'], |
93 | $options['pwd'], |
94 | $options['opt'] |
95 | ); |
96 | |
97 | // check if the database contains the required tables |
98 | $tables = $this->_db->query($tableQuery)->fetchAll(PDO::FETCH_COLUMN, 0); |
99 | |
100 | // create paste table if necessary |
101 | if (!in_array($this->_sanitizeIdentifier('paste'), $tables)) { |
102 | $this->_createPasteTable(); |
103 | $db_tables_exist = false; |
104 | } |
105 | |
106 | // create comment table if necessary |
107 | if (!in_array($this->_sanitizeIdentifier('comment'), $tables)) { |
108 | $this->_createCommentTable(); |
109 | $db_tables_exist = false; |
110 | } |
111 | |
112 | // create config table if necessary |
113 | $db_version = Controller::VERSION; |
114 | if (!in_array($this->_sanitizeIdentifier('config'), $tables)) { |
115 | $this->_createConfigTable(); |
116 | // if we only needed to create the config table, the DB is older then 0.22 |
117 | if ($db_tables_exist) { |
118 | $db_version = '0.21'; |
119 | } |
120 | } else { |
121 | $db_version = $this->_getConfig('VERSION'); |
122 | } |
123 | |
124 | // update database structure if necessary |
125 | if (version_compare($db_version, Controller::VERSION, '<')) { |
126 | $this->_upgradeDatabase($db_version); |
127 | } |
128 | } else { |
129 | throw new Exception( |
130 | 'Missing configuration for key dsn, usr, pwd or opt in the section model_options, please check your configuration file', 6 |
131 | ); |
132 | } |
133 | } |
134 | |
135 | /** |
136 | * Create a paste. |
137 | * |
138 | * @access public |
139 | * @param string $pasteid |
140 | * @param array $paste |
141 | * @return bool |
142 | */ |
143 | public function create($pasteid, array $paste) |
144 | { |
145 | $expire_date = 0; |
146 | $opendiscussion = $burnafterreading = false; |
147 | $attachment = $attachmentname = null; |
148 | $meta = $paste['meta']; |
149 | $isVersion1 = array_key_exists('data', $paste); |
150 | list($createdKey) = $this->_getVersionedKeys($isVersion1 ? 1 : 2); |
151 | $created = (int) $meta[$createdKey]; |
152 | unset($meta[$createdKey], $paste['meta']); |
153 | if (array_key_exists('expire_date', $meta)) { |
154 | $expire_date = (int) $meta['expire_date']; |
155 | unset($meta['expire_date']); |
156 | } |
157 | if (array_key_exists('opendiscussion', $meta)) { |
158 | $opendiscussion = $meta['opendiscussion']; |
159 | unset($meta['opendiscussion']); |
160 | } |
161 | if (array_key_exists('burnafterreading', $meta)) { |
162 | $burnafterreading = $meta['burnafterreading']; |
163 | unset($meta['burnafterreading']); |
164 | } |
165 | if ($isVersion1) { |
166 | if (array_key_exists('attachment', $meta)) { |
167 | $attachment = $meta['attachment']; |
168 | unset($meta['attachment']); |
169 | } |
170 | if (array_key_exists('attachmentname', $meta)) { |
171 | $attachmentname = $meta['attachmentname']; |
172 | unset($meta['attachmentname']); |
173 | } |
174 | } else { |
175 | $opendiscussion = $paste['adata'][2]; |
176 | $burnafterreading = $paste['adata'][3]; |
177 | } |
178 | try { |
179 | return $this->_exec( |
180 | 'INSERT INTO "' . $this->_sanitizeIdentifier('paste') . |
181 | '" VALUES(?,?,?,?,?,?,?,?,?)', |
182 | array( |
183 | $pasteid, |
184 | $isVersion1 ? $paste['data'] : Json::encode($paste), |
185 | $created, |
186 | $expire_date, |
187 | (int) $opendiscussion, |
188 | (int) $burnafterreading, |
189 | Json::encode($meta), |
190 | $attachment, |
191 | $attachmentname, |
192 | ) |
193 | ); |
194 | } catch (Exception $e) { |
195 | return false; |
196 | } |
197 | } |
198 | |
199 | /** |
200 | * Read a paste. |
201 | * |
202 | * @access public |
203 | * @param string $pasteid |
204 | * @return array|false |
205 | */ |
206 | public function read($pasteid) |
207 | { |
208 | try { |
209 | $row = $this->_select( |
210 | 'SELECT * FROM "' . $this->_sanitizeIdentifier('paste') . |
211 | '" WHERE "dataid" = ?', array($pasteid), true |
212 | ); |
213 | } catch (Exception $e) { |
214 | $row = false; |
215 | } |
216 | if ($row === false) { |
217 | return false; |
218 | } |
219 | // create array |
220 | $data = Json::decode($row['data']); |
221 | $isVersion2 = array_key_exists('v', $data) && $data['v'] >= 2; |
222 | if ($isVersion2) { |
223 | $paste = $data; |
224 | list($createdKey) = $this->_getVersionedKeys(2); |
225 | } else { |
226 | $paste = array('data' => $row['data']); |
227 | list($createdKey) = $this->_getVersionedKeys(1); |
228 | } |
229 | |
230 | try { |
231 | $row['meta'] = Json::decode($row['meta']); |
232 | } catch (Exception $e) { |
233 | $row['meta'] = array(); |
234 | } |
235 | $row = self::upgradePreV1Format($row); |
236 | $paste['meta'] = $row['meta']; |
237 | $paste['meta'][$createdKey] = (int) $row['postdate']; |
238 | $expire_date = (int) $row['expiredate']; |
239 | if ($expire_date > 0) { |
240 | $paste['meta']['expire_date'] = $expire_date; |
241 | } |
242 | if ($isVersion2) { |
243 | return $paste; |
244 | } |
245 | |
246 | // support v1 attachments |
247 | if (array_key_exists('attachment', $row) && !empty($row['attachment'])) { |
248 | $paste['attachment'] = $row['attachment']; |
249 | if (array_key_exists('attachmentname', $row) && !empty($row['attachmentname'])) { |
250 | $paste['attachmentname'] = $row['attachmentname']; |
251 | } |
252 | } |
253 | if ($row['opendiscussion']) { |
254 | $paste['meta']['opendiscussion'] = true; |
255 | } |
256 | if ($row['burnafterreading']) { |
257 | $paste['meta']['burnafterreading'] = true; |
258 | } |
259 | |
260 | return $paste; |
261 | } |
262 | |
263 | /** |
264 | * Delete a paste and its discussion. |
265 | * |
266 | * @access public |
267 | * @param string $pasteid |
268 | */ |
269 | public function delete($pasteid) |
270 | { |
271 | $this->_exec( |
272 | 'DELETE FROM "' . $this->_sanitizeIdentifier('paste') . |
273 | '" WHERE "dataid" = ?', array($pasteid) |
274 | ); |
275 | $this->_exec( |
276 | 'DELETE FROM "' . $this->_sanitizeIdentifier('comment') . |
277 | '" WHERE "pasteid" = ?', array($pasteid) |
278 | ); |
279 | } |
280 | |
281 | /** |
282 | * Test if a paste exists. |
283 | * |
284 | * @access public |
285 | * @param string $pasteid |
286 | * @return bool |
287 | */ |
288 | public function exists($pasteid) |
289 | { |
290 | try { |
291 | $row = $this->_select( |
292 | 'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('paste') . |
293 | '" WHERE "dataid" = ?', array($pasteid), true |
294 | ); |
295 | } catch (Exception $e) { |
296 | return false; |
297 | } |
298 | return (bool) $row; |
299 | } |
300 | |
301 | /** |
302 | * Create a comment in a paste. |
303 | * |
304 | * @access public |
305 | * @param string $pasteid |
306 | * @param string $parentid |
307 | * @param string $commentid |
308 | * @param array $comment |
309 | * @return bool |
310 | */ |
311 | public function createComment($pasteid, $parentid, $commentid, array $comment) |
312 | { |
313 | if (array_key_exists('data', $comment)) { |
314 | $version = 1; |
315 | $data = $comment['data']; |
316 | } else { |
317 | $version = 2; |
318 | $data = Json::encode($comment); |
319 | } |
320 | list($createdKey, $iconKey) = $this->_getVersionedKeys($version); |
321 | $meta = $comment['meta']; |
322 | unset($comment['meta']); |
323 | foreach (array('nickname', $iconKey) as $key) { |
324 | if (!array_key_exists($key, $meta)) { |
325 | $meta[$key] = null; |
326 | } |
327 | } |
328 | try { |
329 | return $this->_exec( |
330 | 'INSERT INTO "' . $this->_sanitizeIdentifier('comment') . |
331 | '" VALUES(?,?,?,?,?,?,?)', |
332 | array( |
333 | $commentid, |
334 | $pasteid, |
335 | $parentid, |
336 | $data, |
337 | $meta['nickname'], |
338 | $meta[$iconKey], |
339 | $meta[$createdKey], |
340 | ) |
341 | ); |
342 | } catch (Exception $e) { |
343 | return false; |
344 | } |
345 | } |
346 | |
347 | /** |
348 | * Read all comments of paste. |
349 | * |
350 | * @access public |
351 | * @param string $pasteid |
352 | * @return array |
353 | */ |
354 | public function readComments($pasteid) |
355 | { |
356 | $rows = $this->_select( |
357 | 'SELECT * FROM "' . $this->_sanitizeIdentifier('comment') . |
358 | '" WHERE "pasteid" = ?', array($pasteid) |
359 | ); |
360 | |
361 | // create comment list |
362 | $comments = array(); |
363 | if (is_array($rows) && count($rows)) { |
364 | foreach ($rows as $row) { |
365 | $i = $this->getOpenSlot($comments, (int) $row['postdate']); |
366 | $data = Json::decode($row['data']); |
367 | if (array_key_exists('v', $data) && $data['v'] >= 2) { |
368 | $version = 2; |
369 | $comments[$i] = $data; |
370 | } else { |
371 | $version = 1; |
372 | $comments[$i] = array('data' => $row['data']); |
373 | } |
374 | list($createdKey, $iconKey) = $this->_getVersionedKeys($version); |
375 | $comments[$i]['id'] = $row['dataid']; |
376 | $comments[$i]['parentid'] = $row['parentid']; |
377 | $comments[$i]['meta'] = array($createdKey => (int) $row['postdate']); |
378 | foreach (array('nickname' => 'nickname', 'vizhash' => $iconKey) as $rowKey => $commentKey) { |
379 | if (array_key_exists($rowKey, $row) && !empty($row[$rowKey])) { |
380 | $comments[$i]['meta'][$commentKey] = $row[$rowKey]; |
381 | } |
382 | } |
383 | } |
384 | ksort($comments); |
385 | } |
386 | return $comments; |
387 | } |
388 | |
389 | /** |
390 | * Test if a comment exists. |
391 | * |
392 | * @access public |
393 | * @param string $pasteid |
394 | * @param string $parentid |
395 | * @param string $commentid |
396 | * @return bool |
397 | */ |
398 | public function existsComment($pasteid, $parentid, $commentid) |
399 | { |
400 | try { |
401 | return (bool) $this->_select( |
402 | 'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('comment') . |
403 | '" WHERE "pasteid" = ? AND "parentid" = ? AND "dataid" = ?', |
404 | array($pasteid, $parentid, $commentid), true |
405 | ); |
406 | } catch (Exception $e) { |
407 | return false; |
408 | } |
409 | } |
410 | |
411 | /** |
412 | * Save a value. |
413 | * |
414 | * @access public |
415 | * @param string $value |
416 | * @param string $namespace |
417 | * @param string $key |
418 | * @return bool |
419 | */ |
420 | public function setValue($value, $namespace, $key = '') |
421 | { |
422 | if ($namespace === 'traffic_limiter') { |
423 | $this->_last_cache[$key] = $value; |
424 | try { |
425 | $value = Json::encode($this->_last_cache); |
426 | } catch (Exception $e) { |
427 | return false; |
428 | } |
429 | } |
430 | return $this->_exec( |
431 | 'UPDATE "' . $this->_sanitizeIdentifier('config') . |
432 | '" SET "value" = ? WHERE "id" = ?', |
433 | array($value, strtoupper($namespace)) |
434 | ); |
435 | } |
436 | |
437 | /** |
438 | * Load a value. |
439 | * |
440 | * @access public |
441 | * @param string $namespace |
442 | * @param string $key |
443 | * @return string |
444 | */ |
445 | public function getValue($namespace, $key = '') |
446 | { |
447 | $configKey = strtoupper($namespace); |
448 | $value = $this->_getConfig($configKey); |
449 | if ($value === '') { |
450 | // initialize the row, so that setValue can rely on UPDATE queries |
451 | $this->_exec( |
452 | 'INSERT INTO "' . $this->_sanitizeIdentifier('config') . |
453 | '" VALUES(?,?)', |
454 | array($configKey, '') |
455 | ); |
456 | |
457 | // migrate filesystem based salt into database |
458 | $file = 'data' . DIRECTORY_SEPARATOR . 'salt.php'; |
459 | if ($namespace === 'salt' && is_readable($file)) { |
460 | $fs = new Filesystem(array('dir' => 'data')); |
461 | $value = $fs->getValue('salt'); |
462 | $this->setValue($value, 'salt'); |
463 | @unlink($file); |
464 | return $value; |
465 | } |
466 | } |
467 | if ($value && $namespace === 'traffic_limiter') { |
468 | try { |
469 | $this->_last_cache = Json::decode($value); |
470 | } catch (Exception $e) { |
471 | $this->_last_cache = array(); |
472 | } |
473 | if (array_key_exists($key, $this->_last_cache)) { |
474 | return $this->_last_cache[$key]; |
475 | } |
476 | } |
477 | return (string) $value; |
478 | } |
479 | |
480 | /** |
481 | * Returns up to batch size number of paste ids that have expired |
482 | * |
483 | * @access private |
484 | * @param int $batchsize |
485 | * @return array |
486 | */ |
487 | protected function _getExpiredPastes($batchsize) |
488 | { |
489 | $statement = $this->_db->prepare( |
490 | 'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('paste') . |
491 | '" WHERE "expiredate" < ? AND "expiredate" != ? ' . |
492 | ($this->_type === 'oci' ? 'FETCH NEXT ? ROWS ONLY' : 'LIMIT ?') |
493 | ); |
494 | $statement->execute(array(time(), 0, $batchsize)); |
495 | return $statement->fetchAll(PDO::FETCH_COLUMN, 0); |
496 | } |
497 | |
498 | /** |
499 | * @inheritDoc |
500 | */ |
501 | public function getAllPastes() |
502 | { |
503 | return $this->_db->query( |
504 | 'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('paste') . '"' |
505 | )->fetchAll(PDO::FETCH_COLUMN, 0); |
506 | } |
507 | |
508 | /** |
509 | * execute a statement |
510 | * |
511 | * @access private |
512 | * @param string $sql |
513 | * @param array $params |
514 | * @throws PDOException |
515 | * @return bool |
516 | */ |
517 | private function _exec($sql, array $params) |
518 | { |
519 | $statement = $this->_db->prepare($sql); |
520 | foreach ($params as $key => &$parameter) { |
521 | $position = $key + 1; |
522 | if (is_int($parameter)) { |
523 | $statement->bindParam($position, $parameter, PDO::PARAM_INT); |
524 | } elseif (is_string($parameter) && strlen($parameter) >= 4000) { |
525 | $statement->bindParam($position, $parameter, PDO::PARAM_STR, strlen($parameter)); |
526 | } else { |
527 | $statement->bindParam($position, $parameter); |
528 | } |
529 | } |
530 | $result = $statement->execute(); |
531 | $statement->closeCursor(); |
532 | return $result; |
533 | } |
534 | |
535 | /** |
536 | * run a select statement |
537 | * |
538 | * @access private |
539 | * @param string $sql |
540 | * @param array $params |
541 | * @param bool $firstOnly if only the first row should be returned |
542 | * @throws PDOException |
543 | * @return array|false |
544 | */ |
545 | private function _select($sql, array $params, $firstOnly = false) |
546 | { |
547 | $statement = $this->_db->prepare($sql); |
548 | $statement->execute($params); |
549 | if ($firstOnly) { |
550 | $result = $statement->fetch(PDO::FETCH_ASSOC); |
551 | } elseif ($this->_type === 'oci') { |
552 | // workaround for https://bugs.php.net/bug.php?id=46728 |
553 | $result = array(); |
554 | while ($row = $statement->fetch(PDO::FETCH_ASSOC)) { |
555 | $result[] = array_map('PrivateBin\Data\Database::_sanitizeClob', $row); |
556 | } |
557 | } else { |
558 | $result = $statement->fetchAll(PDO::FETCH_ASSOC); |
559 | } |
560 | $statement->closeCursor(); |
561 | if ($this->_type === 'oci' && is_array($result)) { |
562 | // returned CLOB values are streams, convert these into strings |
563 | $result = $firstOnly ? |
564 | array_map('PrivateBin\Data\Database::_sanitizeClob', $result) : |
565 | $result; |
566 | } |
567 | return $result; |
568 | } |
569 | |
570 | /** |
571 | * get version dependent key names |
572 | * |
573 | * @access private |
574 | * @param int $version |
575 | * @return array |
576 | */ |
577 | private function _getVersionedKeys($version) |
578 | { |
579 | if ($version === 1) { |
580 | return array('postdate', 'vizhash'); |
581 | } |
582 | return array('created', 'icon'); |
583 | } |
584 | |
585 | /** |
586 | * get table list query, depending on the database type |
587 | * |
588 | * @access private |
589 | * @param string $type |
590 | * @throws Exception |
591 | * @return string |
592 | */ |
593 | private function _getTableQuery($type) |
594 | { |
595 | switch ($type) { |
596 | case 'ibm': |
597 | $sql = 'SELECT "tabname" FROM "SYSCAT"."TABLES"'; |
598 | break; |
599 | case 'informix': |
600 | $sql = 'SELECT "tabname" FROM "systables"'; |
601 | break; |
602 | case 'mssql': |
603 | // U: tables created by the user |
604 | $sql = 'SELECT "name" FROM "sysobjects" ' |
605 | . 'WHERE "type" = \'U\' ORDER BY "name"'; |
606 | break; |
607 | case 'mysql': |
608 | $sql = 'SHOW TABLES'; |
609 | break; |
610 | case 'oci': |
611 | $sql = 'SELECT table_name FROM all_tables'; |
612 | break; |
613 | case 'pgsql': |
614 | $sql = 'SELECT c."relname" AS "table_name" ' |
615 | . 'FROM "pg_class" c, "pg_user" u ' |
616 | . 'WHERE c."relowner" = u."usesysid" AND c."relkind" = \'r\' ' |
617 | . 'AND NOT EXISTS (SELECT 1 FROM "pg_views" WHERE "viewname" = c."relname") ' |
618 | . "AND c.\"relname\" !~ '^(pg_|sql_)' " |
619 | . 'UNION ' |
620 | . 'SELECT c."relname" AS "table_name" ' |
621 | . 'FROM "pg_class" c ' |
622 | . "WHERE c.\"relkind\" = 'r' " |
623 | . 'AND NOT EXISTS (SELECT 1 FROM "pg_views" WHERE "viewname" = c."relname") ' |
624 | . 'AND NOT EXISTS (SELECT 1 FROM "pg_user" WHERE "usesysid" = c."relowner") ' |
625 | . "AND c.\"relname\" !~ '^pg_'"; |
626 | break; |
627 | case 'sqlite': |
628 | $sql = 'SELECT "name" FROM "sqlite_master" WHERE "type"=\'table\' ' |
629 | . 'UNION ALL SELECT "name" FROM "sqlite_temp_master" ' |
630 | . 'WHERE "type"=\'table\' ORDER BY "name"'; |
631 | break; |
632 | default: |
633 | throw new Exception( |
634 | "PDO type $type is currently not supported.", 5 |
635 | ); |
636 | } |
637 | return $sql; |
638 | } |
639 | |
640 | /** |
641 | * get a value by key from the config table |
642 | * |
643 | * @access private |
644 | * @param string $key |
645 | * @return string |
646 | */ |
647 | private function _getConfig($key) |
648 | { |
649 | try { |
650 | $row = $this->_select( |
651 | 'SELECT "value" FROM "' . $this->_sanitizeIdentifier('config') . |
652 | '" WHERE "id" = ?', array($key), true |
653 | ); |
654 | } catch (PDOException $e) { |
655 | return ''; |
656 | } |
657 | return $row ? $row['value'] : ''; |
658 | } |
659 | |
660 | /** |
661 | * get the primary key clauses, depending on the database driver |
662 | * |
663 | * @access private |
664 | * @param string $key |
665 | * @return array |
666 | */ |
667 | private function _getPrimaryKeyClauses($key = 'dataid') |
668 | { |
669 | $main_key = $after_key = ''; |
670 | switch ($this->_type) { |
671 | case 'mysql': |
672 | case 'oci': |
673 | $after_key = ", PRIMARY KEY (\"$key\")"; |
674 | break; |
675 | default: |
676 | $main_key = ' PRIMARY KEY'; |
677 | break; |
678 | } |
679 | return array($main_key, $after_key); |
680 | } |
681 | |
682 | /** |
683 | * get the data type, depending on the database driver |
684 | * |
685 | * PostgreSQL and OCI uses a different API for BLOBs then SQL, hence we use TEXT and CLOB |
686 | * |
687 | * @access private |
688 | * @return string |
689 | */ |
690 | private function _getDataType() |
691 | { |
692 | switch ($this->_type) { |
693 | case 'oci': |
694 | return 'CLOB'; |
695 | case 'pgsql': |
696 | return 'TEXT'; |
697 | default: |
698 | return 'BLOB'; |
699 | } |
700 | } |
701 | |
702 | /** |
703 | * get the attachment type, depending on the database driver |
704 | * |
705 | * PostgreSQL and OCI use different APIs for BLOBs then SQL, hence we use TEXT and CLOB |
706 | * |
707 | * @access private |
708 | * @return string |
709 | */ |
710 | private function _getAttachmentType() |
711 | { |
712 | switch ($this->_type) { |
713 | case 'oci': |
714 | return 'CLOB'; |
715 | case 'pgsql': |
716 | return 'TEXT'; |
717 | default: |
718 | return 'MEDIUMBLOB'; |
719 | } |
720 | } |
721 | |
722 | /** |
723 | * get the meta type, depending on the database driver |
724 | * |
725 | * OCI doesn't accept TEXT so it has to be VARCHAR2(4000) |
726 | * |
727 | * @access private |
728 | * @return string |
729 | */ |
730 | private function _getMetaType() |
731 | { |
732 | switch ($this->_type) { |
733 | case 'oci': |
734 | return 'VARCHAR2(4000)'; |
735 | default: |
736 | return 'TEXT'; |
737 | } |
738 | } |
739 | |
740 | /** |
741 | * create the paste table |
742 | * |
743 | * @access private |
744 | */ |
745 | private function _createPasteTable() |
746 | { |
747 | list($main_key, $after_key) = $this->_getPrimaryKeyClauses(); |
748 | $dataType = $this->_getDataType(); |
749 | $attachmentType = $this->_getAttachmentType(); |
750 | $metaType = $this->_getMetaType(); |
751 | $this->_db->exec( |
752 | 'CREATE TABLE "' . $this->_sanitizeIdentifier('paste') . '" ( ' . |
753 | "\"dataid\" CHAR(16) NOT NULL$main_key, " . |
754 | "\"data\" $attachmentType, " . |
755 | '"postdate" INT, ' . |
756 | '"expiredate" INT, ' . |
757 | '"opendiscussion" INT, ' . |
758 | '"burnafterreading" INT, ' . |
759 | "\"meta\" $metaType, " . |
760 | "\"attachment\" $attachmentType, " . |
761 | "\"attachmentname\" $dataType$after_key )" |
762 | ); |
763 | } |
764 | |
765 | /** |
766 | * create the paste table |
767 | * |
768 | * @access private |
769 | */ |
770 | private function _createCommentTable() |
771 | { |
772 | list($main_key, $after_key) = $this->_getPrimaryKeyClauses(); |
773 | $dataType = $this->_getDataType(); |
774 | $this->_db->exec( |
775 | 'CREATE TABLE "' . $this->_sanitizeIdentifier('comment') . '" ( ' . |
776 | "\"dataid\" CHAR(16) NOT NULL$main_key, " . |
777 | '"pasteid" CHAR(16), ' . |
778 | '"parentid" CHAR(16), ' . |
779 | "\"data\" $dataType, " . |
780 | "\"nickname\" $dataType, " . |
781 | "\"vizhash\" $dataType, " . |
782 | "\"postdate\" INT$after_key )" |
783 | ); |
784 | if ($this->_type === 'oci') { |
785 | $this->_db->exec( |
786 | 'declare |
787 | already_exists exception; |
788 | columns_indexed exception; |
789 | pragma exception_init( already_exists, -955 ); |
790 | pragma exception_init(columns_indexed, -1408); |
791 | begin |
792 | execute immediate \'create index "comment_parent" on "' . $this->_sanitizeIdentifier('comment') . '" ("pasteid")\'; |
793 | exception |
794 | when already_exists or columns_indexed then |
795 | NULL; |
796 | end;' |
797 | ); |
798 | } else { |
799 | // CREATE INDEX IF NOT EXISTS not supported as of Oracle MySQL <= 8.0 |
800 | $this->_db->exec( |
801 | 'CREATE INDEX "' . |
802 | $this->_sanitizeIdentifier('comment_parent') . '" ON "' . |
803 | $this->_sanitizeIdentifier('comment') . '" ("pasteid")' |
804 | ); |
805 | } |
806 | } |
807 | |
808 | /** |
809 | * create the paste table |
810 | * |
811 | * @access private |
812 | */ |
813 | private function _createConfigTable() |
814 | { |
815 | list($main_key, $after_key) = $this->_getPrimaryKeyClauses('id'); |
816 | $charType = $this->_type === 'oci' ? 'VARCHAR2(16)' : 'CHAR(16)'; |
817 | $textType = $this->_getMetaType(); |
818 | $this->_db->exec( |
819 | 'CREATE TABLE "' . $this->_sanitizeIdentifier('config') . |
820 | "\" ( \"id\" $charType NOT NULL$main_key, \"value\" $textType$after_key )" |
821 | ); |
822 | $this->_exec( |
823 | 'INSERT INTO "' . $this->_sanitizeIdentifier('config') . |
824 | '" VALUES(?,?)', |
825 | array('VERSION', Controller::VERSION) |
826 | ); |
827 | } |
828 | |
829 | /** |
830 | * sanitizes CLOB values used with OCI |
831 | * |
832 | * From: https://stackoverflow.com/questions/36200534/pdo-oci-into-a-clob-field |
833 | * |
834 | * @access public |
835 | * @static |
836 | * @param int|string|resource $value |
837 | * @return int|string |
838 | */ |
839 | public static function _sanitizeClob($value) |
840 | { |
841 | if (is_resource($value)) { |
842 | $value = stream_get_contents($value); |
843 | } |
844 | return $value; |
845 | } |
846 | |
847 | /** |
848 | * sanitizes identifiers |
849 | * |
850 | * @access private |
851 | * @param string $identifier |
852 | * @return string |
853 | */ |
854 | private function _sanitizeIdentifier($identifier) |
855 | { |
856 | return preg_replace('/[^A-Za-z0-9_]+/', '', $this->_prefix . $identifier); |
857 | } |
858 | |
859 | /** |
860 | * upgrade the database schema from an old version |
861 | * |
862 | * @access private |
863 | * @param string $oldversion |
864 | */ |
865 | private function _upgradeDatabase($oldversion) |
866 | { |
867 | $dataType = $this->_getDataType(); |
868 | $attachmentType = $this->_getAttachmentType(); |
869 | switch ($oldversion) { |
870 | case '0.21': |
871 | // create the meta column if necessary (pre 0.21 change) |
872 | try { |
873 | $this->_db->exec( |
874 | 'SELECT "meta" FROM "' . $this->_sanitizeIdentifier('paste') . '" ' . |
875 | ($this->_type === 'oci' ? 'FETCH NEXT 1 ROWS ONLY' : 'LIMIT 1') |
876 | ); |
877 | } catch (PDOException $e) { |
878 | $this->_db->exec('ALTER TABLE "' . $this->_sanitizeIdentifier('paste') . '" ADD COLUMN "meta" TEXT'); |
879 | } |
880 | // SQLite only allows one ALTER statement at a time... |
881 | $this->_db->exec( |
882 | 'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') . |
883 | "\" ADD COLUMN \"attachment\" $attachmentType" |
884 | ); |
885 | $this->_db->exec( |
886 | 'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') . "\" ADD COLUMN \"attachmentname\" $dataType" |
887 | ); |
888 | // SQLite doesn't support MODIFY, but it allows TEXT of similar |
889 | // size as BLOB, so there is no need to change it there |
890 | if ($this->_type !== 'sqlite') { |
891 | $this->_db->exec( |
892 | 'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') . |
893 | "\" ADD PRIMARY KEY (\"dataid\"), MODIFY COLUMN \"data\" $dataType" |
894 | ); |
895 | $this->_db->exec( |
896 | 'ALTER TABLE "' . $this->_sanitizeIdentifier('comment') . |
897 | "\" ADD PRIMARY KEY (\"dataid\"), MODIFY COLUMN \"data\" $dataType, " . |
898 | "MODIFY COLUMN \"nickname\" $dataType, MODIFY COLUMN \"vizhash\" $dataType" |
899 | ); |
900 | } else { |
901 | $this->_db->exec( |
902 | 'CREATE UNIQUE INDEX IF NOT EXISTS "' . |
903 | $this->_sanitizeIdentifier('paste_dataid') . '" ON "' . |
904 | $this->_sanitizeIdentifier('paste') . '" ("dataid")' |
905 | ); |
906 | $this->_db->exec( |
907 | 'CREATE UNIQUE INDEX IF NOT EXISTS "' . |
908 | $this->_sanitizeIdentifier('comment_dataid') . '" ON "' . |
909 | $this->_sanitizeIdentifier('comment') . '" ("dataid")' |
910 | ); |
911 | } |
912 | // CREATE INDEX IF NOT EXISTS not supported as of Oracle MySQL <= 8.0 |
913 | $this->_db->exec( |
914 | 'CREATE INDEX "' . |
915 | $this->_sanitizeIdentifier('comment_parent') . '" ON "' . |
916 | $this->_sanitizeIdentifier('comment') . '" ("pasteid")' |
917 | ); |
918 | // no break, continue with updates for 0.22 and later |
919 | case '1.3': |
920 | // SQLite doesn't support MODIFY, but it allows TEXT of similar |
921 | // size as BLOB and PostgreSQL uses TEXT, so there is no need |
922 | // to change it there |
923 | if ($this->_type !== 'sqlite' && $this->_type !== 'pgsql') { |
924 | $this->_db->exec( |
925 | 'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') . |
926 | "\" MODIFY COLUMN \"data\" $attachmentType" |
927 | ); |
928 | } |
929 | // no break, continue with updates for all newer versions |
930 | default: |
931 | $this->_exec( |
932 | 'UPDATE "' . $this->_sanitizeIdentifier('config') . |
933 | '" SET "value" = ? WHERE "id" = ?', |
934 | array(Controller::VERSION, 'VERSION') |
935 | ); |
936 | } |
937 | } |
938 | } |