Database.php 37.9 KB
Newer Older
1
2
3
4
5
6
7
8
<?php
/**
 * Created by PhpStorm.
 * User: crose
 * Date: 1/4/16
 * Time: 7:14 PM
 */

Marc Egger's avatar
Marc Egger committed
9
namespace IMATHUZH\Qfq\Core\Database;
10

11
use IMATHUZH\Qfq\Core\Exception\Thrower;
Marc Egger's avatar
Marc Egger committed
12
use IMATHUZH\Qfq\Core\Helper\BindParam;
13
use IMATHUZH\Qfq\Core\Helper\HelperFile;
Marc Egger's avatar
Marc Egger committed
14
use IMATHUZH\Qfq\Core\Helper\HelperFormElement;
15
use IMATHUZH\Qfq\Core\Helper\Logger;
Marc Egger's avatar
Marc Egger committed
16
use IMATHUZH\Qfq\Core\Helper\OnArray;
17
use IMATHUZH\Qfq\Core\Helper\Path;
18
use IMATHUZH\Qfq\Core\Helper\SqlQuery;
19
use IMATHUZH\Qfq\Core\Store\Store;
20

Carsten  Rose's avatar
Carsten Rose committed
21
22
23
24
/**
 * Class Database
 * @package qfq
 */
25
26
27
class Database {

    /**
28
     * @var Store
29
30
     */
    private $store = null;
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

    /**
     * @var \mysqli
     */
    private $mysqli = null;

    /**
     * @var \mysqli_stmt
     */
    private $mysqli_stmt = null;

    /**
     * @var \mysqli_result
     */
    private $mysqli_result = null;

47
48
49
    /**
     * @var string
     */
50
    private $sqlLogAbsolute = '';
51

52
53
54
    /**
     * @var array
     */
Carsten  Rose's avatar
Carsten Rose committed
55
    private $sqlLogModePrio = [SQL_LOG_MODE_NONE => 1, SQL_LOG_MODE_ERROR => 2, SQL_LOG_MODE_MODIFY => 3, SQL_LOG_MODE_ALL => 4];
56

57
58
    private $dbName = null;
    private $dbIndex = null;
59

60
61
62
63
    /**
     * Returns current data base handle from Store[System][SYSTEM_DBH].
     * If not exists: open database and store the new dbh in Store[System][SYSTEM_DBH]
     *
64
     * @param string $dbIndex Typically '1' for Data, optional 2 for external Form/FormElement
65
     *
Marc Egger's avatar
Marc Egger committed
66
67
68
69
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
70
     */
71
    public function __construct($dbIndex = DB_INDEX_DEFAULT) {
Carsten  Rose's avatar
Carsten Rose committed
72

73
74
75
        if (empty($dbIndex)) {
            $dbIndex = DB_INDEX_DEFAULT;
        }
76
        $this->dbIndex = $dbIndex;
77

78
        $this->store = Store::getInstance();
79
        $storeSystem = $this->store->getStore(STORE_SYSTEM);
80

81
        $this->sqlLogAbsolute = Path::absoluteSqlLogFile();
82
        $dbInit = $storeSystem[SYSTEM_DB_INIT];
83

84
85
        $config = $this->getConnectionDetails($dbIndex, $storeSystem);
        $this->dbName = $config[SYSTEM_DB_NAME];
86

87
        if ($this->mysqli === null) {
88
            $this->mysqli = $this->dbConnect($config);
89
        }
90
91
92

        // DB Init
        if ($dbInit !== false && $dbInit != '') {
93
            $arr = explode(';', $dbInit);
94
            foreach ($arr as $sql) {
95
96
97
98
99
                $sql = trim($sql);
                if ('' != $sql) {
                    $this->sql($sql);
                }
            }
100
        }
101
102
    }

103
104
105
106
107
108
109
    /**
     * @return mixed|string
     */
    public function getDbIndex() {
        return $this->dbIndex;
    }

110
111
112
    /**
     * @return mixed|string
     */
113
114
115
116
    public function getDbName() {
        return $this->dbName;
    }

117
118
119
120
121
122
    /**
     * Depending on $dbIndex, read DB_?_SERVER ... crendentials.
     * If $dbIndex==1 but no DB_1_xxx specified, take the default DB_xxxx - old config.qfq.ini style
     *
     * @param $dbIndex 1,2,...
     *
123
     * @param array $config
124
     * @return array
Marc Egger's avatar
Marc Egger committed
125
     * @throws \UserFormException
126
     */
127
    private function getConnectionDetails($dbIndex, array $config) {
128
129
130
131
132
133
134

        if (isset($config["DB_" . $dbIndex . "_SERVER"])) {
            $config[SYSTEM_DB_SERVER] = $config["DB_" . $dbIndex . "_SERVER"];
            $config[SYSTEM_DB_USER] = $config["DB_" . $dbIndex . "_USER"];
            $config[SYSTEM_DB_PASSWORD] = $config["DB_" . $dbIndex . "_PASSWORD"];
            $config[SYSTEM_DB_NAME] = $config["DB_" . $dbIndex . "_NAME"];
        } elseif ($dbIndex != 1) {
135
            // Backward compatibility: old configs use SYSTEM_DB_SERVER (without index) and index=1 might mean 'legacy config'.
Marc Egger's avatar
Marc Egger committed
136
            throw new \UserFormException("DB Handle not found in config: $dbIndex", ERROR_INVALID_VALUE);
137
138
139
140
        }

        return $config;
    }
141

142
    /**
143
144
     * Open mysqli database connection if not already done.
     *
145
     * @param $config
146
     * @return \mysqli
Marc Egger's avatar
Marc Egger committed
147
     * @throws \UserFormException
148
     */
149
    private function dbConnect($config) {
150
151
        $mysqli = null;

152
        $mysqli = new \mysqli($config[SYSTEM_DB_SERVER], $config[SYSTEM_DB_USER], $config[SYSTEM_DB_PASSWORD], $config[SYSTEM_DB_NAME]);
153
154

        if ($mysqli->connect_error) {
Marc Egger's avatar
Marc Egger committed
155
            throw new \UserFormException (
156
                json_encode([ERROR_MESSAGE_TO_USER => 'Error open Database',
Marc Egger's avatar
Marc Egger committed
157
                    ERROR_MESSAGE_TO_DEVELOPER => "Error open Database 'mysql:host=" . $config[SYSTEM_DB_SERVER] .
158
159
160
161
162
                        ";dbname=" . $config[SYSTEM_DB_NAME] .
                        ";dbuser=" . $config[SYSTEM_DB_USER] .
                        "'': " . $mysqli->connect_errno . PHP_EOL . $mysqli->connect_error]),
                ERROR_DB_OPEN);

163
164
        }

165
166
        // Necessary that mysqli::real_escape_string() functions properly.
        if (!$mysqli->set_charset('utf8')) {
Marc Egger's avatar
Marc Egger committed
167
            throw new \UserFormException (
168
                json_encode([ERROR_MESSAGE_TO_USER => "Error set_charset('utf8')",
Marc Egger's avatar
Marc Egger committed
169
                    ERROR_MESSAGE_TO_DEVELOPER => "Error set_charset('utf8') Database: " . $mysqli->connect_errno . PHP_EOL . $mysqli->connect_error]),
170
                ERROR_DB_SET_CHARSET);
171
172
        }

173
        return $mysqli;
174
175
176
    }

    /**
Carsten  Rose's avatar
Carsten Rose committed
177
178
     * Fires query $sql and fetches result as assoc array (all modes but ROW_KEYS) or as num array (mode: ROW_KEYS).
     * Throws exception.
179
     *
180
     * $mode
181
182
     *  ROW_REGULAR: Return 2-dimensional assoc array. Every query row is one array row.
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
183
     *  ROW_EXPECT_0: Return empty string if there is no record row, Else an exception.
184
     *  ROW_EXPECT_1: Return 1-dimensional assoc array if there are exact one row. Else an exception.
Carsten  Rose's avatar
Carsten Rose committed
185
186
     *  ROW_EXPECT_0_1: Return empty array if there is no row. Return 1-dimensional assoc array if there is one row.
     *  Else an exception. ROW_EXPECT_GE_1: Like 'ROW_REGULAR'. Throws an exception if there is an empty resultset.
187
     *  ROW_KEYS: Return 2-dimensional num(!) array. Every query row is one array row. $keys are the column names.
188
     *
Carsten  Rose's avatar
Carsten Rose committed
189
     * @param string $sql
190
     * @param string $mode
Carsten  Rose's avatar
Carsten Rose committed
191
     * @param array $parameterArray
192
     * @param string $specificMessage
Carsten  Rose's avatar
Carsten Rose committed
193
194
     * @param array $keys
     * @param array $stat DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
Carsten  Rose's avatar
Carsten Rose committed
195
     * @param array $skipErrno Array of ERRNO numbers, which should be skipped and not throw an error.
Carsten  Rose's avatar
Carsten Rose committed
196
     *
197
     * @return array|int
198
     *      SELECT | SHOW | DESCRIBE | EXPLAIN: see $mode
199
200
     *      INSERT: last_insert_id
     *      UPDATE | DELETE | REPLACE: affected rows
Marc Egger's avatar
Marc Egger committed
201
202
203
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
204
     */
Carsten  Rose's avatar
Carsten Rose committed
205
    public function sql($sql, $mode = ROW_REGULAR, array $parameterArray = array(), $specificMessage = '', array &$keys = array(), array &$stat = array(), array $skipErrno = array()) {
206
        $queryType = '';
207
208
        $result = array();
        $this->closeMysqliStmt();
209

210
        // CR (& EV) often forgets to specify the $mode and use prepared statement with parameter instead.
211
        if (is_array($mode)) {
Marc Egger's avatar
Marc Egger committed
212
            throw new \CodeException("Probably a parameter forgotten: $mode ?");
213
        }
214

215
        // for error reporting in exception
216
        if ($specificMessage) {
217
            $specificMessage .= " ";
218
        }
219

Carsten  Rose's avatar
Carsten Rose committed
220
        $count = $this->prepareExecute($sql, $parameterArray, $queryType, $stat, $specificMessage, $skipErrno);
221

222
        if ($count === false) {
Marc Egger's avatar
Marc Egger committed
223
            throw new \DbException($specificMessage . "No idea why this error happens - please take some time and check the problem.", ERROR_DB_GENERIC_CHECK);
224
225
        }

226
227
228
        if ($queryType === QUERY_TYPE_SELECT) {
            switch ($mode) {
                case ROW_IMPLODE_ALL:
229
                    $result = $this->fetchAll($mode);
230
231
232
233
234
235
                    break;
                case ROW_KEYS:
                case ROW_REGULAR:
                    $result = $this->fetchAll($mode, $keys);
                    break;
                case ROW_EXPECT_0:
236
                    if ($count === 0) {
237
                        $result = array();
238
                    } else {
Marc Egger's avatar
Marc Egger committed
239
                        throw new \DbException($specificMessage . "Expected none row, got $count rows", ERROR_DB_TOO_MANY_ROWS);
240
                    }
241
242
                    break;
                case ROW_EXPECT_1:
243
                    if ($count === 1) {
244
                        $result = $this->fetchAll($mode)[0];
245
                    } else {
Marc Egger's avatar
Marc Egger committed
246
                        throw new \DbException($specificMessage . "Expected one row, got $count rows", ERROR_DB_COUNT_DO_NOT_MATCH);
247
                    }
248
249
                    break;
                case ROW_EXPECT_0_1:
250
                    if ($count === 1) {
251
                        $result = $this->fetchAll($mode)[0];
252
                    } elseif ($count === 0) {
253
                        $result = array();
254
                    } else
Marc Egger's avatar
Marc Egger committed
255
                        throw new \DbException($specificMessage . "Expected zero or one rows, got $count rows", ERROR_DB_TOO_MANY_ROWS);
256
257
                    break;
                case ROW_EXPECT_GE_1:
258
                    if ($count > 0) {
259
                        $result = $this->fetchAll($mode);
260
                    } else {
Marc Egger's avatar
Marc Egger committed
261
                        throw new \DbException($specificMessage . "Expected at least one row, got none", ERROR_DB_TOO_FEW_ROWS);
262
                    }
263
                    break;
264

265
                default:
Marc Egger's avatar
Marc Egger committed
266
                    throw new \DbException($specificMessage . "Unknown mode: $mode", ERROR_UNKNOWN_MODE);
267
            }
268
269
270

            $this->freeResult();

271
272
        } elseif ($queryType === QUERY_TYPE_INSERT) {
            $result = $stat[DB_INSERT_ID];
273
274
        } else {
            $result = $count;
275
276
        }

277
278
        $this->closeMysqliStmt();

279
280
281
282
        $this->store->setVar(SYSTEM_SQL_RAW, '', STORE_SYSTEM);
        $this->store->setVar(SYSTEM_SQL_FINAL, '', STORE_SYSTEM);
        $this->store->setVar(SYSTEM_SQL_PARAM_ARRAY, '', STORE_SYSTEM);

283
284
285
286
287
288
        return $result;
    }

    /**
     * Close an optional open MySQLi Statement.
     *
Marc Egger's avatar
Marc Egger committed
289
     * @throws \DbException
290
291
292
293
294
295
296
297
298
     */
    private function closeMysqliStmt() {

        if ($this->mysqli_result !== null && $this->mysqli_result !== false) {
            $this->mysqli_result->free_result();
        }

        if ($this->mysqli_stmt !== null && $this->mysqli_stmt !== false) {
            $this->mysqli_stmt->free_result();
299
            if (!$this->mysqli_stmt->close()) {
Marc Egger's avatar
Marc Egger committed
300
                throw new \DbException('Error closing mysqli_stmt' . ERROR_DB_CLOSE_MYSQLI_STMT);
301
            }
302
303
304
        }
        $this->mysqli_stmt = null;
        $this->mysqli_result = null;
305
306
    }

307
308
309
310
    /**
     * Checks the problematic $sql if there is a common mistake.
     * If something is found, give a hint.
     *
311
312
     * @param string $sql
     * @param string $errorMsg
313
314
     * @return string
     */
315
    private function getSqlHint($sql, $errorMsg) {
316
        $msg = '';
317
318

        // Check if there is a comma before FROM: 'SELECT ... , FROM ...'
319
320
        $pos = stripos($sql, ' FROM ');
        if ($pos !== false && $pos > 0 && $sql[$pos - 1] == ',') {
321
            $msg .= "HINT: Remove extra ',' before FROM\n";
322
323
        }

324
        // Look for QFQ variables which haven't been replaced
325
326
        $matches = array();
        preg_match_all("/{{[^}}]*}}/", $sql, $matches);
327
328
329
330
331
332
        // '.line.count' might be replaced later and should not shown.
        foreach ($matches[0] as $key => $value) {
            if (false !== stripos($value, '.line.count')) {
                unset($matches[0][$key]);
            }
        }
333
        if (count($matches[0]) > 0) {
334
            $msg .= "HINT: The following variables couldn't be replaced: " . implode(', ', $matches[0]) . "\n";
335
        }
336

337
        // Look for missing '()' after FROM in case LEFT JOIN is used.
338
339
        $pos = stripos($sql, ' LEFT JOIN ');
        if (stripos($errorMsg, 'Unknown column') !== false && $pos !== false && ($sql[$pos - 1] ?? '') != ')') {
340
341
342
            $msg .= "HINT: Maybe the tables after 'FROM' should be enclosed by '()' \n";
        }

343
344
345
346
347
        // Check for double comma
        if (stripos($errorMsg, 'the right syntax to use near') && preg_match('/,[ ]*,/', $sql)) {
            $msg .= "HINT: There seems to be a double comma in your query.\n";
        }

348
        return $msg;
349
350
    }

351
    /**
352
     * Execute a prepared SQL statement like SELECT, INSERT, UPDATE, DELETE, SHOW, ...
353
     *
Carsten  Rose's avatar
Carsten Rose committed
354
355
356
     * Returns the number of selected rows (SELECT, SHOW, ..) or the affected rows (UPDATE, INSERT). $stat contains
     * appropriate num_rows, insert_id or rows_affected.
     *
357
358
359
     * In case of an error, throw an exception.
     * mysqli error code listed in $skipErrno[] do not throw an error.
     *
Carsten  Rose's avatar
Carsten Rose committed
360
361
362
     * @param string $sql SQL statement with prepared statement variable.
     * @param array $parameterArray parameter array for prepared statement execution.
     * @param string $queryType returns QUERY_TYPE_SELECT | QUERY_TYPE_UPDATE | QUERY_TYPE_INSERT, depending on
Carsten  Rose's avatar
Carsten Rose committed
363
     *                               the query.
Carsten  Rose's avatar
Carsten Rose committed
364
     * @param array $stat DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
365
     * @param string $specificMessage
Carsten  Rose's avatar
Carsten Rose committed
366
     * @param array $skipErrno Array of ERRNO numbers, which should be skipped and not throw an error.
367
     *
368
     * @return int|mixed
Marc Egger's avatar
Marc Egger committed
369
370
371
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
372
     */
373
    private function prepareExecute($sql, array $parameterArray, &$queryType, array &$stat, $specificMessage = '', array $skipErrno = array()) {
374

Carsten  Rose's avatar
Carsten Rose committed
375
        $sqlLogMode = $this->isSqlModify($sql) ? SQL_LOG_MODE_MODIFY : SQL_LOG_MODE_ALL;
376
        $errno = 0;
377
        $stat = array();
378
        $errorMsg[ERROR_MESSAGE_TO_USER] = empty($specificMessage) ? 'SQL error' : $specificMessage;
379

380
381
382
383
        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_FINAL, $sql, STORE_SYSTEM);
            $this->store->setVar(SYSTEM_SQL_PARAM_ARRAY, $parameterArray, STORE_SYSTEM);
        }
384

385
        // Logfile
386
        $this->dbLog($sqlLogMode, $sql, $parameterArray);
387

388
        if (false === ($this->mysqli_stmt = $this->mysqli->prepare($sql))) {
Carsten  Rose's avatar
Carsten Rose committed
389
            $errno = $this->mysqli->errno;
390
            if (false === array_search($errno, $skipErrno)) {  // removed nonsensical condition $skipErrno === array() &&
391
392
                $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
                $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
Carsten  Rose's avatar
Carsten Rose committed
393
                $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $errno . ' ] ' . $this->mysqli->error;
394
395
396

                throw new \DbException(json_encode($errorMsg), ERROR_DB_PREPARE);
            }
397
398
399
        }

        if (count($parameterArray) > 0) {
400
            if (false === $this->prepareBindParam($parameterArray)) {
Carsten  Rose's avatar
Carsten Rose committed
401
402
                $errno = $this->mysqli_stmt->errno;
                if ($skipErrno === array() && false === array_search($errno, $skipErrno)) {
403
404
                    $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
                    $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
Carsten  Rose's avatar
Carsten Rose committed
405
                    $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $errno . ' ] ' . $this->mysqli_stmt->error;
406
407
408

                    throw new \DbException(json_encode($errorMsg), ERROR_DB_BIND);
                }
409
410
411
412
            }
        }

        if (false === $this->mysqli_stmt->execute()) {
Carsten  Rose's avatar
Carsten Rose committed
413
414
            $errno = $this->mysqli->errno;
            if ($skipErrno === array() || false === array_search($errno, $skipErrno)) {
415
416
417
                $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
                $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
                $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error;
418

419
420
                throw new \DbException(json_encode($errorMsg), ERROR_DB_EXECUTE);
            }
421
422
        }

423
424
425
426
427
428
        if ($errno === 0) {
            $command = strtoupper(explode(' ', $sql, 2)[0]);
        } else {
            $command = 'FAILED';
        }

429
430
431
432
433
        switch ($command) {
            case 'SELECT':
            case 'SHOW':
            case 'DESCRIBE':
            case 'EXPLAIN':
434
            case 'CALL':
435
                if (false === ($result = $this->mysqli_stmt->get_result())) {
Marc Egger's avatar
Marc Egger committed
436
                    throw new \DbException(
Marc Egger's avatar
Marc Egger committed
437
                        json_encode([ERROR_MESSAGE_TO_USER => 'Error DB execute', ERROR_MESSAGE_TO_DEVELOPER => '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error . $specificMessage]),
438
439
                        ERROR_DB_EXECUTE);

440
                }
441
                $queryType = QUERY_TYPE_SELECT;
442
                $this->mysqli_result = $result;
443
444
445
                $stat[DB_NUM_ROWS] = $this->mysqli_result->num_rows;
                $count = $stat[DB_NUM_ROWS];
                $msg = 'Read rows: ' . $stat[DB_NUM_ROWS];
446
                break;
447
            case 'REPLACE':
448
            case 'INSERT':
449
                $queryType = QUERY_TYPE_INSERT;
450
451
452
                $stat[DB_INSERT_ID] = $this->mysqli->insert_id;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
453
                $msg = 'ID: ' . $this->mysqli->insert_id . ' - affected rows: ' . $count;
454
455
456
                break;
            case 'UPDATE':
            case 'DELETE':
457
            case 'TRUNCATE':
458
459
460
                $queryType = QUERY_TYPE_UPDATE;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
461
462
                $msg = 'Affected rows: ' . $count;
                break;
463

464
            case 'SET':
465
            case 'ALTER':
466
            case 'DROP':
467
            case 'CREATE':
468
469
470
471
472
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
                break;
473
474
475
476
477
478
            case 'FAILED':
                $queryType = QUERY_TYPE_FAILED;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = -1;
                $msg = '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error;
                break;
479

480
            default:
481
482
483
484
485
                // Unknown command: treat it as a control command
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
486
                break;
487
488
        }

489
490
491
        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_COUNT, $count, STORE_SYSTEM);
        }
492

Carsten  Rose's avatar
Carsten Rose committed
493
        $this->dbLog($sqlLogMode, $msg);
494

495
496
497
        return $count;
    }

498
499
500
501
    /**
     * Check if the given SQL Statement might modify data.
     *
     * @param $sql
Carsten  Rose's avatar
Carsten Rose committed
502
     *
503
504
505
     * @return bool  true is the statement might modify data, else: false
     */
    private function isSqlModify($sql) {
506

507
        $command = explode(' ', $sql, 2);
508

509
510
511
512
513
514
        switch (strtoupper($command[0])) {
            case 'INSERT':
            case 'UPDATE':
            case 'DELETE':
            case 'REPLACE':
            case 'TRUNCATE':
515
516
517
            case 'DROP':
            case 'CREATE':
            case 'ALTER':
518
519
                return true;
        }
520

521
522
523
        return false;
    }

524
    /**
525
     * Decide if the SQL statement has to be logged. If yes, create a timestamp and do the log.
526
     *
527
528
     * @param string $currentQueryMode
     * @param string $sql
529
     * @param array $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
530
     *
Marc Egger's avatar
Marc Egger committed
531
532
     * @throws \CodeException
     * @throws \UserFormException
533
     */
534
    private function dbLog($currentQueryMode = SQL_LOG_MODE_ALL, $sql = '', $parameterArray = array()) {
535

536
537
538
539
        if ($sql == '') {
            return;
        }

540
541
        $status = '';

542
543
544
545
546
        // If no sqlLogMode is defined/available, choose SQL_LOG_MODE_ERROR
        $sqlLogMode = $this->store->getVar(SYSTEM_SQL_LOG_MODE, STORE_SYSTEM);
        if ($sqlLogMode === false) {
            $sqlLogMode = SQL_LOG_MODE_ERROR;
        }
547

548
549
550
        // Check if string is known.
        foreach ([$sqlLogMode, $currentQueryMode] as $mode) {
            if (!isset($this->sqlLogModePrio[$mode])) {
Marc Egger's avatar
Marc Egger committed
551
                throw new \UserFormException("Unknown SQL_LOG_MODE: $mode", ERROR_UNKNOWN_SQL_LOG_MODE);
552
553
554
555
556
557
            }
        }

        // Log?
        if ($this->sqlLogModePrio[$sqlLogMode] < ($this->sqlLogModePrio[$currentQueryMode])) {
            return;
558
559
        }

560
        // Client IP Address
561
562
563
564
565
        $remoteAddress = ($this->store === null) ? '0.0.0.0' : $this->store->getVar(CLIENT_REMOTE_ADDRESS, STORE_CLIENT);


        $logArr = [
            ['FE', TYPO3_FE_USER, STORE_TYPO3],
566
            ['FESU', TYPO3_FE_USER, STORE_USER],
567
568
569
570
571
572
573
574
575
576
577
578
579
580
            ['Page', TYPO3_PAGE_ID, STORE_TYPO3],
            ['tt', TYPO3_TT_CONTENT_UID, STORE_TYPO3],
            ['level', SYSTEM_REPORT_FULL_LEVEL, STORE_SYSTEM],
            ['form', SIP_FORM, STORE_SIP],
        ];

        $t3msg = '';
        foreach ($logArr as $logItem) {
            $value = $this->store->getVar($logItem[1], $logItem[2]);
            if (!empty($value)) {
                $t3msg .= $logItem[0] . ":" . $value . ",";
            }
        }
        $t3msg = substr($t3msg, 0, strlen($t3msg) - 1);
581

582
        $msg = '[' . date('Y.m.d H:i:s O') . '][' . $remoteAddress . '][' . $t3msg . ']';
583

584
585
        if (count($parameterArray) > 0) {
            $sql = $this->preparedStatementInsertParameter($sql, $parameterArray);
586
587
        }

588
589
        if ($currentQueryMode == SQL_LOG_MODE_ERROR) {
            $status = 'FAILED: ';
590
        }
591
        $msg .= '[' . $status . $sql . ']';
592

593
        Logger::logMessage($msg, $this->sqlLogAbsolute);
594
595
    }

596
597
    /**
     * @param $sql
598
     * @param $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
599
     *
600
     * @return string
601
     */
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
    private function preparedStatementInsertParameter($sql, $parameterArray) {
        $msg = '';

        $sqlArray = explode('?', $sql);
        $ii = 0;
        foreach ($parameterArray as $value) {
            if (isset($sqlArray[$ii])) {
                if (is_array($value)) {
                    $value = OnArray::toString($value);
                }

                $msg .= $sqlArray[$ii++] . "'" . $value . "'";
            } else {
                $msg = '?';
            }
617
        }
618
619
620
621
622
        if (isset($sqlArray[$ii])) {
            $msg .= $sqlArray[$ii];
        }

        return $msg;
623
624
    }

625
    /**
626
     * @param $arr
627
     */
628
    private function prepareBindParam($arr) {
629

630
631
632
633
        $bindParam = new BindParam();

        for ($ii = 0; $ii < count($arr); $ii++) {
            $bindParam->add($arr[$ii]);
634
        }
635
        call_user_func_array([$this->mysqli_stmt, 'bind_param'], $bindParam->get());
636
637
    }

638
    /**
639
     * Fetch all rows of the result.
640
     *
641
642
     * mode:
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
643
     *  ROW_KEYS: Return num array with column names in $keys
644
645
646
     *  default: Return 2-dimensional assoc array
     *
     * @param string $mode
Carsten  Rose's avatar
Carsten Rose committed
647
     * @param array $keys
Carsten  Rose's avatar
Carsten Rose committed
648
     *
649
     * @return array|bool|mixed|string false in case of an error.
650
651
652
653
654
     *              Empty string is returned if the query didn't yield any rows.
     *              All rows as Multi Assoc array if $mode!=IMPLODE_ALL.
     *              All rows and all columns imploded to one string if $mode=IMPLODE_ALL
     *
     */
655
    private function fetchAll($mode = '', &$keys = array()) {
656
657
658

        $result = null;

659
        if ($this->mysqli_result == null || $this->mysqli_result == false) {
660
661
662
            return false;
        }

663
        if ($this->mysqli_result->num_rows === 0) {
664
665
666
            return ($mode === ROW_IMPLODE_ALL) ? "" : array();
        }

667
668
        switch ($mode) {
            case ROW_IMPLODE_ALL:
669
                $result = "";
670
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
671
                    $result .= implode($row);
672
673
674
675
676
677
678
679
680
                }
                break;

            case ROW_KEYS:
                $keys = array();

                for ($ii = 0; $ii < $this->mysqli_result->field_count; $ii++) {
                    $keys[$ii] = $this->mysqli_result->fetch_field_direct($ii)->name;
                }
Carsten  Rose's avatar
Carsten Rose committed
681

682
                $result = $this->mysqli_result->fetch_all(MYSQLI_NUM);
683
684
685
                break;

            default:
686
                $result = $this->mysqli_result->fetch_all(MYSQLI_ASSOC);
687
        }
688
689

        return $result;
690
691
    }

692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
    /**
     * Return the number of rows returned by the last call to execute().
     *
     * If execute() has never been called, returns FALSE.
     *
     * @return mixed Number of rows returned by last call to execute(). If Database::execute()
     *     has never been called prior a call to this method, false is returned.
     */
    public function getRowCount() {
        if ($this->mysqli_result == null) {
            return false;
        }

        return $this->mysqli_result->num_rows;
    }

    /**
     * Get the values for a given ENUM or SET column
     *
     * @param string $table name of the table
     * @param string $columnName name of the column
     *
     * @return array
Marc Egger's avatar
Marc Egger committed
715
716
717
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
     */
    public function getEnumSetValueList($table, $columnName) {

        $columnDefinition = $this->getFieldDefinitionFromTable($table, $columnName);
        $setEnumDefinition = $columnDefinition["Type"];

        // $setEnumDefinition holds now a string like
        // String:  enum('','red','blue','green')
        $len = mb_strlen($setEnumDefinition);

        # "enum('" = 6, "set('" = 5
        $tokenLength = strpos($setEnumDefinition, "'") + 1;

        // count("enum('") == 6, count("')") == 2
        $enumSetString = mb_substr($setEnumDefinition, $tokenLength, $len - (2 + $tokenLength));

        // String: ','red','blue','green

        if (($setEnumValueList = explode("','", $enumSetString)) === false) {
            return array();
        }

        return $setEnumValueList;
    }

    /**
     * Get database column definition.
     *
     * If the column is not found in the table, an exception is thrown.
     *
     * @param string $table name of the table
     *
     * @param string $columnName name of the column
Carsten  Rose's avatar
Carsten Rose committed
751
     *
752
753
     * @return array the definition of the column as retrieved by Database::getTableDefinition().
     *
Marc Egger's avatar
Marc Egger committed
754
755
756
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
757
758
     */
    private function getFieldDefinitionFromTable($table, $columnName) {
759

760
        $tableDefinition = $this->getTableDefinition($table);
761
        foreach ($tableDefinition as $row) {
762
763
764
765
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }
766

Marc Egger's avatar
Marc Egger committed
767
        throw new \DbException(
Marc Egger's avatar
Marc Egger committed
768
            json_encode([ERROR_MESSAGE_TO_USER => 'Column name not found', ERROR_MESSAGE_TO_DEVELOPER => "Column name '$columnName' not found in table '$table'."]),
769
            ERROR_DB_COLUMN_NOT_FOUND_IN_TABLE);
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
    }

    /**
     * Get all column definitions for a table. Return Assoc Array:
     *
     * Field      Type                      Null    Key    Default    Extra
     * --------------------------------------------------------------------------
     * id         bigint(20)                 NO     PRI    NULL    auto_increment
     * name       varchar(128)               YES           NULL
     * firstname  varchar(128)               YES           NULL
     * gender     enum('','male','female')   NO            male
     * groups     set('','a','b','c')        NO            a
     *
     * @param string $table table to retrieve column definition from
     *
     * @return array column definition of table as returned by SHOW FIELDS FROM as associative array.
Marc Egger's avatar
Marc Egger committed
786
787
788
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
789
790
     */
    public function getTableDefinition($table) {
791
        return $this->sql("SHOW FIELDS FROM `$table`", ROW_EXPECT_GE_1, array(), "No columns found for table '$table'");
792
793
    }

794
    /**
795
796
     * Wrapper for sql(), to simplyfy access.
     *
Carsten  Rose's avatar
Carsten Rose committed
797
     * @param              $sql
Carsten  Rose's avatar
Carsten Rose committed
798
799
     * @param array $keys
     * @param array $stat
Carsten  Rose's avatar
Carsten Rose committed
800
     *
801
     * @return array|bool
Marc Egger's avatar
Marc Egger committed
802
803
804
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
805
     */
806
    public function sqlKeys($sql, array &$keys, array &$stat = array()) {
807

808
        return $this->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
809
    }
810

811
812
813
814
815
816
    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
817
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
818
        return $this->mysqli->insert_id;
819
    }
Carsten  Rose's avatar
Carsten Rose committed
820

821
822
823
824
    /**
     * Searches for the table '$name'.
     *
     * @param $name
Carsten  Rose's avatar
Carsten Rose committed
825
     *
826
     * @return bool  true if found, else false
Marc Egger's avatar
Marc Egger committed
827
828
829
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
     */
    public function existTable($name) {
        $found = false;

        $tables = $this->sql("SHOW tables");

        foreach ($tables as $t) {
            foreach ($t as $key => $value) {
                if ($value === $name) {
                    $found = true;
                    break 2;
                }
            }
        }

        return $found;
    }

Carsten  Rose's avatar
Carsten Rose committed
848
849
850
851
    /**
     * @param $table
     * @param $columnDefinition
     * @param $mode
Marc Egger's avatar
Marc Egger committed
852
853
854
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
Carsten  Rose's avatar
Carsten Rose committed
855
856
857
858
859
860
861
     */
    public function createTable($table, $columnDefinition, $mode) {

        $cols = array();

        if (!$this->existTable($table)) {
            $sql = "CREATE TABLE $table (";
862
            foreach ($columnDefinition as $key => $value) {
Carsten  Rose's avatar
Carsten Rose committed
863
864
865
866
867
868
869
870
871
872
873
874
                $cols[] = "`" . $key . "` " . $value . " NOT NULL,";
            }
            $sql .= implode(',', $cols);
            $sql .= ") ENGINE=InnoDB DEFAULT CHARSET=utf8_general_ci";

            $this->sql($sql);
        }

        if ($mode == IMPORT_MODE_REPLACE) {
            $this->sql("TRUNCATE $table");
        }
    }
Carsten  Rose's avatar
Carsten Rose committed
875

876
877
878
    /**
     * Depending on $sql reads FormElements to a specific container or all. Preprocess all FormElements.
     *
Carsten  Rose's avatar
Carsten Rose committed
879
880
881
     * @param string $sql SQL_FORM_ELEMENT_SPECIFIC_CONTAINER | SQL_FORM_ELEMENT_ALL_CONTAINER
     * @param array $param Parameter which matches the prepared statement in $sql
     * @param array $formSpec Main FormSpec to copy generic parameter to FormElements
Carsten  Rose's avatar
Carsten Rose committed
882
     *
883
     * @return array|int
Marc Egger's avatar
Marc Egger committed
884
885
886
887
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
888
889
890
891
892
     */
    public function getNativeFormElements($sql, array $param, $formSpec) {

        $feSpecNative = $this->sql($sql, ROW_REGULAR, $param);

893
        $feSpecNative = HelperFormElement::formElementSetDefault($feSpecNative, $formSpec);
894

895
        // Explode and Do $FormElement.parameter
896
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);
897
898
899
900
901

        // Check for retype FormElements which have to duplicated.
        $feSpecNative = HelperFormElement::duplicateRetypeElements($feSpecNative);

        // Copy Attributes to FormElements
902
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);
903
904
905
906

        return $feSpecNative;
    }

907
908
909
910
911
    /**
     * Checks if there is the SQL keyword 'limit' at the end of the SQL statement.
     * returns true for '... LIMIT', '.... LIMIT 1, ... LIMIT 1,2, ... LIMIT 1 , 2
     *
     * @param $sql
Carsten  Rose's avatar
Carsten Rose committed
912
     *
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
     * @return bool
     */
    public function hasLimit($sql) {

        $sql = trim(strtolower($sql));
        $arr = explode(' ', $sql);

        $ii = 3;
        array_pop($arr); // the last token can't be 'limit'

        while ($ii > 0) {
            $item = array_pop($arr);
            if ($item === null) {
                return false;
            }
            if ($item != '') {
                if ($item == 'limit') {
                    return true;
                } else {
                    $ii--;
                }
            }
        }

        return false;
    }

    /**
941
942
943
944
945
946
     * $arr = [ 0 => [ $srcColumn1 => $value0_1, $srcColumn2 => $value0_2 ], 1 => [ $srcColumn1 => $value1_1, $srcColumn2 => $value1_2 ], ...]
     *
     * $arr will be converted to a two column array with keys $destColumn1 and $destColumn2.
     * If $destColumn1 or $destColumn2 is empty, take $srcColumn1, $srcColumn2 as names.
     * $arr might contain one or more columns. Only the first two columns are used.
     * If there is only one column, that column will be doubled.
947
     *
Carsten  Rose's avatar
Carsten Rose committed
948
     * @param array $arr
949
950
951
952
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
Carsten  Rose's avatar
Carsten Rose committed
953
     *
954
955
     * @return array
     */
956
957
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

958
959
960
961
962
        if ($arr == array() || $arr === null) {
            return array();
        }

        // Set defaults
963
964
965
966
967
968
969
970
        if ($destColumn1 == '') {
            $destColumn1 = $srcColumn1;
        }

        if ($destColumn2 == '') {
            $destColumn2 = $srcColumn2;
        }

971
        // Set final column names
972
973
974
        $row = $arr[0];
        $keys = array_keys($row);
        if (count($row) < 2) {
975
976
977
978
979
            $column1 = $keys[0];
            $column2 = $keys[0];
        } elseif (array_key_exists($srcColumn1, $row) && array_key_exists($srcColumn2, $row)) {
            $column1 = $srcColumn1;
            $column2 = $srcColumn2;