Database.php 36 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

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

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

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

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

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

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

45
46
47
    /**
     * @var string
     */
48
    private $sqlLog = '';
49

50
51
52
    /**
     * @var array
     */
53
    private $sqlLogModePrio = [SQL_LOG_MODE_NONE => 1, SQL_LOG_MODE_ERROR => 2, SQL_LOG_MODE_MODIFY => 3, SQL_LOG_MODE_MODIFY_ALL => 4, SQL_LOG_MODE_ALL => 5];
54

55
56
    private $dbName = '';

57
58
59
60
    /**
     * 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]
     *
61
     * @param string $dbIndex Typically '1' for Data, optional 2 for external Form/FormElement
62
     *
Marc Egger's avatar
Marc Egger committed
63
64
65
66
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
67
     */
68
    public function __construct($dbIndex = DB_INDEX_DEFAULT) {
Carsten  Rose's avatar
Carsten Rose committed
69

70
71
72
        if (empty($dbIndex)) {
            $dbIndex = DB_INDEX_DEFAULT;
        }
73

74
        $this->store = Store::getInstance();
75
        $storeSystem = $this->store->getStore(STORE_SYSTEM);
76

Marc Egger's avatar
Marc Egger committed
77
        $this->sqlLog = $storeSystem[SYSTEM_SITE_PATH] . '/' . $storeSystem[SYSTEM_SQL_LOG];
78
        $dbInit = $storeSystem[SYSTEM_DB_INIT];
79

80
81
        $config = $this->getConnectionDetails($dbIndex, $storeSystem);
        $this->dbName = $config[SYSTEM_DB_NAME];
82

83
        if ($this->mysqli === null) {
84
            $this->mysqli = $this->dbConnect($config);
85
        }
86
87
88

        // DB Init
        if ($dbInit !== false && $dbInit != '') {
89
90
91
92
93
94
95
            $arr = explode(';', $dbInit);
            foreach ($arr AS $sql) {
                $sql = trim($sql);
                if ('' != $sql) {
                    $this->sql($sql);
                }
            }
96
        }
97
98
    }

99
100
101
    /**
     * @return mixed|string
     */
102
103
104
105
    public function getDbName() {
        return $this->dbName;
    }

106
107
108
109
110
111
    /**
     * 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,...
     *
112
     * @param array $config
113
     * @return array
Marc Egger's avatar
Marc Egger committed
114
     * @throws \UserFormException
115
     */
116
    private function getConnectionDetails($dbIndex, array $config) {
117
118
119
120
121
122
123

        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) {
124
            // Backward compatibility: old configs use SYSTEM_DB_SERVER (without index) and index=1 might mean 'legacy config'.
Marc Egger's avatar
Marc Egger committed
125
            throw new \UserFormException("DB Handle not found in config: $dbIndex", ERROR_INVALID_VALUE);
126
127
128
129
        }

        return $config;
    }
130

131
    /**
132
133
     * Open mysqli database connection if not already done.
     *
134
     * @param $config
135
     * @return \mysqli
Marc Egger's avatar
Marc Egger committed
136
     * @throws \UserFormException
137
     */
138
    private function dbConnect($config) {
139
140
        $mysqli = null;

141
        $mysqli = new \mysqli($config[SYSTEM_DB_SERVER], $config[SYSTEM_DB_USER], $config[SYSTEM_DB_PASSWORD], $config[SYSTEM_DB_NAME]);
142
143

        if ($mysqli->connect_error) {
Marc Egger's avatar
Marc Egger committed
144
            throw new \UserFormException (
145
                json_encode([ERROR_MESSAGE_TO_USER => 'Error open Database',
Marc Egger's avatar
Marc Egger committed
146
                    ERROR_MESSAGE_TO_DEVELOPER => "Error open Database 'mysql:host=" . $config[SYSTEM_DB_SERVER] .
147
148
149
150
151
                        ";dbname=" . $config[SYSTEM_DB_NAME] .
                        ";dbuser=" . $config[SYSTEM_DB_USER] .
                        "'': " . $mysqli->connect_errno . PHP_EOL . $mysqli->connect_error]),
                ERROR_DB_OPEN);

152
153
        }

154
155
        // Necessary that mysqli::real_escape_string() functions properly.
        if (!$mysqli->set_charset('utf8')) {
Marc Egger's avatar
Marc Egger committed
156
            throw new \UserFormException (
157
                json_encode([ERROR_MESSAGE_TO_USER => "Error set_charset('utf8')",
Marc Egger's avatar
Marc Egger committed
158
                    ERROR_MESSAGE_TO_DEVELOPER => "Error set_charset('utf8') Database: " . $mysqli->connect_errno . PHP_EOL . $mysqli->connect_error]),
159
                ERROR_DB_SET_CHARSET);
160
161
        }

162
        return $mysqli;
163
164
165
    }

    /**
Carsten  Rose's avatar
Carsten Rose committed
166
167
     * Fires query $sql and fetches result as assoc array (all modes but ROW_KEYS) or as num array (mode: ROW_KEYS).
     * Throws exception.
168
     *
169
     * $mode
170
171
     *  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.
172
     *  ROW_EXPECT_0: Return empty string if there is no record row, Else an exception.
173
     *  ROW_EXPECT_1: Return 1-dimensional assoc array if there are exact one row. Else an exception.
Carsten  Rose's avatar
Carsten Rose committed
174
175
     *  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.
176
     *  ROW_KEYS: Return 2-dimensional num(!) array. Every query row is one array row. $keys are the column names.
177
     *
Carsten  Rose's avatar
Carsten Rose committed
178
     * @param        $sql
179
     * @param string $mode
Carsten  Rose's avatar
Carsten Rose committed
180
     * @param array $parameterArray
181
     * @param string $specificMessage
Carsten  Rose's avatar
Carsten Rose committed
182
183
     * @param array $keys
     * @param array $stat DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
Carsten  Rose's avatar
Carsten Rose committed
184
     * @param array $skipErrno
Carsten  Rose's avatar
Carsten Rose committed
185
     *
186
     * @return array|int
187
     *      SELECT | SHOW | DESCRIBE | EXPLAIN: see $mode
188
189
     *      INSERT: last_insert_id
     *      UPDATE | DELETE | REPLACE: affected rows
Marc Egger's avatar
Marc Egger committed
190
191
192
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
193
     */
Carsten  Rose's avatar
Carsten Rose committed
194
    public function sql($sql, $mode = ROW_REGULAR, array $parameterArray = array(), $specificMessage = '', array &$keys = array(), array &$stat = array(), array $skipErrno = array()) {
195
        $queryType = '';
196
197
        $result = array();
        $this->closeMysqliStmt();
198

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

204
        // for error reporting in exception
205
        if ($specificMessage) {
206
            $specificMessage .= " ";
207
        }
208

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

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

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

254
                default:
Marc Egger's avatar
Marc Egger committed
255
                    throw new \DbException($specificMessage . "Unknown mode: $mode", ERROR_UNKNOWN_MODE);
256
            }
257
258
        } elseif ($queryType === QUERY_TYPE_INSERT) {
            $result = $stat[DB_INSERT_ID];
259
260
        } else {
            $result = $count;
261
262
        }

263
264
        $this->closeMysqliStmt();

265
266
267
268
        $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);

269
270
271
272
273
274
        return $result;
    }

    /**
     * Close an optional open MySQLi Statement.
     *
Marc Egger's avatar
Marc Egger committed
275
     * @throws \DbException
276
277
278
279
280
281
282
283
284
     */
    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();
285
            if (!$this->mysqli_stmt->close()) {
Marc Egger's avatar
Marc Egger committed
286
                throw new \DbException('Error closing mysqli_stmt' . ERROR_DB_CLOSE_MYSQLI_STMT);
287
            }
288
289
290
        }
        $this->mysqli_stmt = null;
        $this->mysqli_result = null;
291
292
    }

293
294
295
296
    /**
     * Checks the problematic $sql if there is a common mistake.
     * If something is found, give a hint.
     *
297
298
     * @param string $sql
     * @param string $errorMsg
299
300
     * @return string
     */
301
    private function getSqlHint($sql, $errorMsg) {
302
        $msg = '';
303
304

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

310
        // Look for QFQ variables which haven't been replaced
311
312
        $matches = array();
        preg_match_all("/{{[^}}]*}}/", $sql, $matches);
313
314
315
316
317
318
        // '.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]);
            }
        }
319
        if (count($matches[0]) > 0) {
320
            $msg .= "HINT: The following variables couldn't be replaced: " . implode(', ', $matches[0]) . "\n";
321
        }
322

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

329
330
331
332
333
        // 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";
        }

334
        return $msg;
335
336
    }

337
    /**
338
     * Execute a prepared SQL statement like SELECT, INSERT, UPDATE, DELETE, SHOW, ...
339
     *
Carsten  Rose's avatar
Carsten Rose committed
340
341
342
     * Returns the number of selected rows (SELECT, SHOW, ..) or the affected rows (UPDATE, INSERT). $stat contains
     * appropriate num_rows, insert_id or rows_affected.
     *
343
344
345
     * 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
346
347
348
     * @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
349
     *                               the query.
Carsten  Rose's avatar
Carsten Rose committed
350
     * @param array $stat DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
351
     * @param string $specificMessage
352
353
     * @param array $skipErrno
     *
354
     * @return int|mixed
Marc Egger's avatar
Marc Egger committed
355
356
357
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
358
     */
359
    private function prepareExecute($sql, array $parameterArray, &$queryType, array &$stat, $specificMessage = '', array $skipErrno = array()) {
360

361
362
363
        // Only log a modify type statement here if sqlLogMode is (at least) modifyAll
        // If sqlLogMode is modify, log the statement after it has been executed and we know if there are affected rows.
        $sqlLogMode = $this->isSqlModify($sql) ? SQL_LOG_MODE_MODIFY_ALL : SQL_LOG_MODE_ALL;
364
365
        $errno = 0;

366
        $result = 0;
367
        $stat = array();
368
        $errorMsg[ERROR_MESSAGE_TO_USER] = empty($specificMessage) ? 'SQL error' : $specificMessage;
369

370
371
372
373
        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_FINAL, $sql, STORE_SYSTEM);
            $this->store->setVar(SYSTEM_SQL_PARAM_ARRAY, $parameterArray, STORE_SYSTEM);
        }
374

375
376
377
//        if ($specificMessage !== '') {
//            $specificMessage = ' - ' . $specificMessage;
//        }
378
        // Logfile
379
        $this->dbLog($sqlLogMode, $sql, $parameterArray);
380

381
        if (false === ($this->mysqli_stmt = $this->mysqli->prepare($sql))) {
382

383
384
385
386
387
388
389
390
391
            if ($skipErrno === array() && false === array_search($this->mysqli->errno, $skipErrno)) {
                $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->errno . ' ] ' . $this->mysqli->error;

                throw new \DbException(json_encode($errorMsg), ERROR_DB_PREPARE);
            } else {
                $errno = $this->mysqli->errno;
            }
392
393
394
        }

        if (count($parameterArray) > 0) {
395
            if (false === $this->prepareBindParam($parameterArray)) {
396
397
398
399
400
401
402
403
404
                if ($skipErrno !== array() && false === array_search($this->mysqli->errno, $skipErrno)) {
                    $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;

                    throw new \DbException(json_encode($errorMsg), ERROR_DB_BIND);
                } else {
                    $errno = $this->mysqli->errno;
                }
405
406
407
408
            }
        }

        if (false === $this->mysqli_stmt->execute()) {
409
410
411
412
            if ($skipErrno !== array() && false === array_search($this->mysqli->errno, $skipErrno)) {
                $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;
413

414
415
416
417
                throw new \DbException(json_encode($errorMsg), ERROR_DB_EXECUTE);
            } else {
                $errno = $this->mysqli->errno;
            }
418
419
420
421
        }

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

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

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

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

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

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

492
        // Logfile
493
494
495
496
497
498
499
500
501
502
503
        $pageContentSqlLogMode = $this->store->getVar(SYSTEM_SQL_LOG_MODE, STORE_SYSTEM);
        if ($pageContentSqlLogMode == SQL_LOG_MODE_MODIFY && $sqlLogMode == SQL_LOG_MODE_MODIFY_ALL)  {
            // sqlLogMode modify: need to log query and query result (if count > 0)
            if ($count > 0) {
                $this->dbLog(SQL_LOG_MODE_MODIFY, $sql, $parameterArray);
                $this->dbLog(SQL_LOG_MODE_MODIFY, $msg);
            }
        } else {
            // Query result
            $this->dbLog($sqlLogMode, $msg);
        }
504

505
506
507
        return $count;
    }

508
509
510
511
    /**
     * Check if the given SQL Statement might modify data.
     *
     * @param $sql
Carsten  Rose's avatar
Carsten Rose committed
512
     *
513
514
515
     * @return bool  true is the statement might modify data, else: false
     */
    private function isSqlModify($sql) {
516

517
        $command = explode(' ', $sql, 2);
518

519
520
521
522
523
524
        switch (strtoupper($command[0])) {
            case 'INSERT':
            case 'UPDATE':
            case 'DELETE':
            case 'REPLACE':
            case 'TRUNCATE':
525
526
527
            case 'DROP':
            case 'CREATE':
            case 'ALTER':
528
529
                return true;
        }
530

531
532
533
        return false;
    }

534
    /**
535
     * Decide if the SQL statement has to be logged. If yes, create a timestamp and do the log.
536
     *
537
538
     * @param string $currentQueryMode
     * @param string $sql
539
     * @param array $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
540
     *
Marc Egger's avatar
Marc Egger committed
541
542
     * @throws \CodeException
     * @throws \UserFormException
543
     */
544
    private function dbLog($currentQueryMode = SQL_LOG_MODE_ALL, $sql = '', $parameterArray = array()) {
545

546
547
548
549
        if ($sql == '') {
            return;
        }

550
551
        $status = '';

552
553
554
555
556
        // 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;
        }
557

558
559
560
        // Check if string is known.
        foreach ([$sqlLogMode, $currentQueryMode] as $mode) {
            if (!isset($this->sqlLogModePrio[$mode])) {
Marc Egger's avatar
Marc Egger committed
561
                throw new \UserFormException("Unknown SQL_LOG_MODE: $mode", ERROR_UNKNOWN_SQL_LOG_MODE);
562
563
564
565
566
567
            }
        }

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

570
        // Client IP Address
571
572
573
574
575
        $remoteAddress = ($this->store === null) ? '0.0.0.0' : $this->store->getVar(CLIENT_REMOTE_ADDRESS, STORE_CLIENT);


        $logArr = [
            ['FE', TYPO3_FE_USER, STORE_TYPO3],
576
            ['FESU', TYPO3_FE_USER, STORE_USER],
577
578
579
580
581
582
583
584
585
586
587
588
589
590
            ['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);
591

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

594
595
        if (count($parameterArray) > 0) {
            $sql = $this->preparedStatementInsertParameter($sql, $parameterArray);
596
597
        }

598
599
        if ($currentQueryMode == SQL_LOG_MODE_ERROR) {
            $status = 'FAILED: ';
600
        }
601
        $msg .= '[' . $status . $sql . ']';
602

603
        Logger::logMessage($msg, $this->sqlLog);
604
605
    }

606
607
    /**
     * @param $sql
608
     * @param $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
609
     *
610
     * @return string
611
     */
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
    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 = '?';
            }
627
        }
628
629
630
631
632
        if (isset($sqlArray[$ii])) {
            $msg .= $sqlArray[$ii];
        }

        return $msg;
633
634
    }

635
    /**
636
     * @param $arr
637
     */
638
    private function prepareBindParam($arr) {
639

640
641
642
643
        $bindParam = new BindParam();

        for ($ii = 0; $ii < count($arr); $ii++) {
            $bindParam->add($arr[$ii]);
644
        }
645
        call_user_func_array([$this->mysqli_stmt, 'bind_param'], $bindParam->get());
646
647
    }

648
649
650
    /**
     * Fetch all rows of the result as associative array.
     *
651
652
     * mode:
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
653
     *  ROW_KEYS: Return num array with column names in $keys
654
655
656
     *  default: Return 2-dimensional assoc array
     *
     * @param string $mode
Carsten  Rose's avatar
Carsten Rose committed
657
     * @param array $keys
Carsten  Rose's avatar
Carsten Rose committed
658
     *
659
     * @return array|bool|mixed|string false in case of an error.
660
661
662
663
664
     *              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
     *
     */
665
666
    private function fetchAll($mode = '', &$keys = array()) {
        if ($this->mysqli_result == null || $this->mysqli_result == false) {
667
668
669
            return false;
        }

670
        if ($this->mysqli_result->num_rows === 0) {
671
672
673
            return ($mode === ROW_IMPLODE_ALL) ? "" : array();
        }

674
675
676
        switch ($mode) {
            case ROW_IMPLODE_ALL:
                $str = "";
677
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
678
679
                    $str .= implode($row);
                }
Carsten  Rose's avatar
Carsten Rose committed
680

681
682
683
684
685
686
687
688
689
                return $str;
                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
690

691
692
693
694
695
                return $this->mysqli_result->fetch_all(MYSQLI_NUM);
                break;

            default:
                return $this->mysqli_result->fetch_all(MYSQLI_ASSOC);
696
697
698
        }
    }

699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
    /**
     * 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
722
723
724
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
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
751
752
753
754
755
756
757
     */
    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
758
     *
759
760
     * @return array the definition of the column as retrieved by Database::getTableDefinition().
     *
Marc Egger's avatar
Marc Egger committed
761
762
763
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
764
765
     */
    private function getFieldDefinitionFromTable($table, $columnName) {
766

767
768
769
770
771
772
        $tableDefinition = $this->getTableDefinition($table);
        foreach ($tableDefinition AS $row) {
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }
773

Marc Egger's avatar
Marc Egger committed
774
        throw new \DbException(
Marc Egger's avatar
Marc Egger committed
775
            json_encode([ERROR_MESSAGE_TO_USER => 'Column name not found', ERROR_MESSAGE_TO_DEVELOPER => "Column name '$columnName' not found in table '$table'."]),
776
            ERROR_DB_COLUMN_NOT_FOUND_IN_TABLE);
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
    }

    /**
     * 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
793
794
795
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
796
797
798
799
800
     */
    public function getTableDefinition($table) {
        return $this->sql("SHOW FIELDS FROM `$table`");
    }

801
    /**
802
803
     * Wrapper for sql(), to simplyfy access.
     *
Carsten  Rose's avatar
Carsten Rose committed
804
     * @param              $sql
Carsten  Rose's avatar
Carsten Rose committed
805
806
     * @param array $keys
     * @param array $stat
Carsten  Rose's avatar
Carsten Rose committed
807
     *
808
     * @return array|bool
Marc Egger's avatar
Marc Egger committed
809
810
811
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
812
     */
813
    public function sqlKeys($sql, array &$keys, array &$stat = array()) {
814

815
        return $this->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
816
    }
817

818
819
820
821
822
823
    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
824
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
825
        return $this->mysqli->insert_id;
826
    }
Carsten  Rose's avatar
Carsten Rose committed
827

828
829
830
831
    /**
     * Searches for the table '$name'.
     *
     * @param $name
Carsten  Rose's avatar
Carsten Rose committed
832
     *
833
     * @return bool  true if found, else false
Marc Egger's avatar
Marc Egger committed
834
835
836
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
     */
    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
855
856
857
858
    /**
     * @param $table
     * @param $columnDefinition
     * @param $mode
Marc Egger's avatar
Marc Egger committed
859
860
861
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
Carsten  Rose's avatar
Carsten Rose committed
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
     */
    public function createTable($table, $columnDefinition, $mode) {

        $cols = array();

        if (!$this->existTable($table)) {
            $sql = "CREATE TABLE $table (";
            foreach ($columnDefinition AS $key => $value) {
                $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
882

883
884
885
    /**
     * Depending on $sql reads FormElements to a specific container or all. Preprocess all FormElements.
     *
Carsten  Rose's avatar
Carsten Rose committed
886
887
888
     * @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
889
     *
890
     * @return array|int
Marc Egger's avatar
Marc Egger committed
891
892
893
894
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
895
896
897
898
899
     */
    public function getNativeFormElements($sql, array $param, $formSpec) {

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

900
        $feSpecNative = HelperFormElement::formElementSetDefault($feSpecNative, $formSpec);
901

902
        // Explode and Do $FormElement.parameter
903
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);
904
905
906
907
908

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

        // Copy Attributes to FormElements
909
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);
910
911
912
913

        return $feSpecNative;
    }

914
915
916
917
918
    /**
     * 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
919
     *
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
     * @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;
    }

    /**
     * $arr will be converted to a two column array with keys $keyName1 and $keyName2.
949
     * $arr might contain one or more columns.
Carsten  Rose's avatar
Carsten Rose committed
950
951
     * Only when $keyName1 and $keyName2 exist, those will be used. Else the first column becomes $keyName1 and the
     * second becomes $keyName2. If there is only one column, that column will be doubled.
952
     *
Carsten  Rose's avatar
Carsten Rose committed
953
     * @param array $arr
954
955
956
957
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
Carsten  Rose's avatar
Carsten Rose committed
958
     *
959
960
     * @return array
     */
961
962
963
964
965
966
967
968
969
970
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

        if ($destColumn1 == '') {
            $destColumn1 = $srcColumn1;
        }

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

971
972
973
974
975
976
977
978
979
        $new = array();

        if ($arr == array() || $arr === null) {
            return array();
        }

        $row = $arr[0];
        $keys = array_keys($row);
        if (count($row) < 2) {
980
981
982
983
984
            $column1 = $keys[0];
            $column2 = $keys[0];
        } elseif (array_key_exists($srcColumn1, $row) && array_key_exists($srcColumn2, $row)) {
            $column1 = $srcColumn1;
            $column2 = $srcColumn2;
985
        } else {
986
987
            $column1 = $keys[0];
            $column2 = $keys[1];
988
989
990
991
        }

        $row = array_shift($arr);
        while (null !== $row) {
992
            $new[] = [$destColumn1 => $row[$column1], $destColumn2 => $row[$column2]];
993
994
995
996
997
            $row = array_shift($arr);
        }

        return $new;
    }
998
999
1000
1001
1002

    /**
     * Proxy for mysqli::real_escape_string()
     *
     * @param string $value
Carsten  Rose's avatar
Carsten Rose committed
1003
     *
1004
1005
1006
1007
1008
     * @return string
     */
    public function realEscapeString($value) {
        return $this->mysqli->real_escape_string($value);
    }
Carsten  Rose's avatar
Carsten Rose committed
1009
1010

    /**
Marc Egger's avatar
Marc Egger committed
1011
     * @param $sqlStatements
Carsten  Rose's avatar
Carsten Rose committed
1012
     *
Marc Egger's avatar
Marc Egger committed
1013
     * @throws \CodeException
Carsten  Rose's avatar
Carsten Rose committed
1014
     */
Marc Egger's avatar
Marc Egger committed
1015
    public function playMultiQuery($sqlStatements) {
Carsten  Rose's avatar
Carsten Rose committed
1016

Marc Egger's avatar
Marc Egger committed
1017
1018
1019
        $executed = $this->mysqli->multi_query($sqlStatements);
        if (false === $executed) {
            $errorMsg[ERROR_MESSAGE_TO_USER] = 'SQL Error.';
Nicola Chiapolini's avatar
Nicola Chiapolini committed
1020
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = "Error playing multi query: " . $this->mysqli->error . "\n\nSQL Query:\n\n" . $sqlStatements;
Marc Egger's avatar
Marc Egger committed