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