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