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

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\Store\Store;
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
     */
Carsten  Rose's avatar
Carsten Rose committed
53
    private $sqlLogModePrio = [SQL_LOG_MODE_NONE => 1, SQL_LOG_MODE_ERROR => 2, SQL_LOG_MODE_MODIFY => 3, SQL_LOG_MODE_ALL => 4];
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

77
        $this->sqlLog = $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 string $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 Array of ERRNO numbers, which should be skipped and not throw an error.
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
Carsten  Rose's avatar
Carsten Rose committed
352
     * @param array $skipErrno Array of ERRNO numbers, which should be skipped and not throw an error.
353
     *
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

Carsten  Rose's avatar
Carsten Rose committed
361
        $sqlLogMode = $this->isSqlModify($sql) ? SQL_LOG_MODE_MODIFY : SQL_LOG_MODE_ALL;
362
        $errno = 0;
363
        $stat = array();
364
        $errorMsg[ERROR_MESSAGE_TO_USER] = empty($specificMessage) ? 'SQL error' : $specificMessage;
365

366
367
368
369
        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_FINAL, $sql, STORE_SYSTEM);
            $this->store->setVar(SYSTEM_SQL_PARAM_ARRAY, $parameterArray, STORE_SYSTEM);
        }
370

371
        // Logfile
372
        $this->dbLog($sqlLogMode, $sql, $parameterArray);
373

374
        if (false === ($this->mysqli_stmt = $this->mysqli->prepare($sql))) {
Carsten  Rose's avatar
Carsten Rose committed
375
376
            $errno = $this->mysqli->errno;
            if ($skipErrno === array() && false === array_search($errno, $skipErrno)) {
377
378
                $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
379
                $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $errno . ' ] ' . $this->mysqli->error;
380
381
382

                throw new \DbException(json_encode($errorMsg), ERROR_DB_PREPARE);
            }
383
384
385
        }

        if (count($parameterArray) > 0) {
386
            if (false === $this->prepareBindParam($parameterArray)) {
Carsten  Rose's avatar
Carsten Rose committed
387
388
                $errno = $this->mysqli_stmt->errno;
                if ($skipErrno === array() && false === array_search($errno, $skipErrno)) {
389
390
                    $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
391
                    $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $errno . ' ] ' . $this->mysqli_stmt->error;
392
393
394

                    throw new \DbException(json_encode($errorMsg), ERROR_DB_BIND);
                }
395
396
397
398
            }
        }

        if (false === $this->mysqli_stmt->execute()) {
Carsten  Rose's avatar
Carsten Rose committed
399
400
            $errno = $this->mysqli->errno;
            if ($skipErrno === array() || false === array_search($errno, $skipErrno)) {
401
402
403
                $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;
404

405
406
                throw new \DbException(json_encode($errorMsg), ERROR_DB_EXECUTE);
            }
407
408
        }

409
410
411
412
413
414
        if ($errno === 0) {
            $command = strtoupper(explode(' ', $sql, 2)[0]);
        } else {
            $command = 'FAILED';
        }

415
416
417
418
419
420
        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
421
                    throw new \DbException(
Marc Egger's avatar
Marc Egger committed
422
                        json_encode([ERROR_MESSAGE_TO_USER => 'Error DB execute', ERROR_MESSAGE_TO_DEVELOPER => '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error . $specificMessage]),
423
424
                        ERROR_DB_EXECUTE);

425
                }
426
                $queryType = QUERY_TYPE_SELECT;
427
                $this->mysqli_result = $result;
428
429
430
                $stat[DB_NUM_ROWS] = $this->mysqli_result->num_rows;
                $count = $stat[DB_NUM_ROWS];
                $msg = 'Read rows: ' . $stat[DB_NUM_ROWS];
431
                break;
432
            case 'REPLACE':
433
            case 'INSERT':
434
                $queryType = QUERY_TYPE_INSERT;
435
436
437
                $stat[DB_INSERT_ID] = $this->mysqli->insert_id;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
438
                $msg = 'ID: ' . $this->mysqli->insert_id . ' - affected rows: ' . $count;
439
440
441
                break;
            case 'UPDATE':
            case 'DELETE':
442
            case 'TRUNCATE':
443
444
445
                $queryType = QUERY_TYPE_UPDATE;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
446
447
                $msg = 'Affected rows: ' . $count;
                break;
448

449
            case 'SET':
450
            case 'ALTER':
451
            case 'DROP':
452
            case 'CREATE':
453
            case 'CALL':
454
455
456
457
458
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
                break;
459
460
461
462
463
464
            case 'FAILED':
                $queryType = QUERY_TYPE_FAILED;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = -1;
                $msg = '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error;
                break;
465

466
            default:
467
468
469
470
471
                // Unknown command: treat it as a control command
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
472
                break;
473
474
        }

475
476
477
        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_COUNT, $count, STORE_SYSTEM);
        }
478

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

481
482
483
        return $count;
    }

484
485
486
487
    /**
     * Check if the given SQL Statement might modify data.
     *
     * @param $sql
Carsten  Rose's avatar
Carsten Rose committed
488
     *
489
490
491
     * @return bool  true is the statement might modify data, else: false
     */
    private function isSqlModify($sql) {
492

493
        $command = explode(' ', $sql, 2);
494

495
496
497
498
499
500
        switch (strtoupper($command[0])) {
            case 'INSERT':
            case 'UPDATE':
            case 'DELETE':
            case 'REPLACE':
            case 'TRUNCATE':
501
502
503
            case 'DROP':
            case 'CREATE':
            case 'ALTER':
504
505
                return true;
        }
506

507
508
509
        return false;
    }

510
    /**
511
     * Decide if the SQL statement has to be logged. If yes, create a timestamp and do the log.
512
     *
513
514
     * @param string $currentQueryMode
     * @param string $sql
515
     * @param array $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
516
     *
Marc Egger's avatar
Marc Egger committed
517
518
     * @throws \CodeException
     * @throws \UserFormException
519
     */
520
    private function dbLog($currentQueryMode = SQL_LOG_MODE_ALL, $sql = '', $parameterArray = array()) {
521

522
523
524
525
        if ($sql == '') {
            return;
        }

526
527
        $status = '';

528
529
530
531
532
        // 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;
        }
533

534
535
536
        // Check if string is known.
        foreach ([$sqlLogMode, $currentQueryMode] as $mode) {
            if (!isset($this->sqlLogModePrio[$mode])) {
Marc Egger's avatar
Marc Egger committed
537
                throw new \UserFormException("Unknown SQL_LOG_MODE: $mode", ERROR_UNKNOWN_SQL_LOG_MODE);
538
539
540
541
542
543
            }
        }

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

546
        // Client IP Address
547
548
549
550
551
        $remoteAddress = ($this->store === null) ? '0.0.0.0' : $this->store->getVar(CLIENT_REMOTE_ADDRESS, STORE_CLIENT);


        $logArr = [
            ['FE', TYPO3_FE_USER, STORE_TYPO3],
552
            ['FESU', TYPO3_FE_USER, STORE_USER],
553
554
555
556
557
558
559
560
561
562
563
564
565
566
            ['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);
567

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

570
571
        if (count($parameterArray) > 0) {
            $sql = $this->preparedStatementInsertParameter($sql, $parameterArray);
572
573
        }

574
575
        if ($currentQueryMode == SQL_LOG_MODE_ERROR) {
            $status = 'FAILED: ';
576
        }
577
        $msg .= '[' . $status . $sql . ']';
578

579
        Logger::logMessage($msg, $this->sqlLog);
580
581
    }

582
583
    /**
     * @param $sql
584
     * @param $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
585
     *
586
     * @return string
587
     */
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
    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 = '?';
            }
603
        }
604
605
606
607
608
        if (isset($sqlArray[$ii])) {
            $msg .= $sqlArray[$ii];
        }

        return $msg;
609
610
    }

611
    /**
612
     * @param $arr
613
     */
614
    private function prepareBindParam($arr) {
615

616
617
618
619
        $bindParam = new BindParam();

        for ($ii = 0; $ii < count($arr); $ii++) {
            $bindParam->add($arr[$ii]);
620
        }
621
        call_user_func_array([$this->mysqli_stmt, 'bind_param'], $bindParam->get());
622
623
    }

624
625
626
    /**
     * Fetch all rows of the result as associative array.
     *
627
628
     * mode:
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
629
     *  ROW_KEYS: Return num array with column names in $keys
630
631
632
     *  default: Return 2-dimensional assoc array
     *
     * @param string $mode
Carsten  Rose's avatar
Carsten Rose committed
633
     * @param array $keys
Carsten  Rose's avatar
Carsten Rose committed
634
     *
635
     * @return array|bool|mixed|string false in case of an error.
636
637
638
639
640
     *              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
     *
     */
641
642
    private function fetchAll($mode = '', &$keys = array()) {
        if ($this->mysqli_result == null || $this->mysqli_result == false) {
643
644
645
            return false;
        }

646
        if ($this->mysqli_result->num_rows === 0) {
647
648
649
            return ($mode === ROW_IMPLODE_ALL) ? "" : array();
        }

650
651
652
        switch ($mode) {
            case ROW_IMPLODE_ALL:
                $str = "";
653
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
654
655
                    $str .= implode($row);
                }
Carsten  Rose's avatar
Carsten Rose committed
656

657
658
659
660
661
662
663
664
665
                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
666

667
668
669
670
671
                return $this->mysqli_result->fetch_all(MYSQLI_NUM);
                break;

            default:
                return $this->mysqli_result->fetch_all(MYSQLI_ASSOC);
672
673
674
        }
    }

675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
    /**
     * 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
698
699
700
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
     */
    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
734
     *
735
736
     * @return array the definition of the column as retrieved by Database::getTableDefinition().
     *
Marc Egger's avatar
Marc Egger committed
737
738
739
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
740
741
     */
    private function getFieldDefinitionFromTable($table, $columnName) {
742

743
744
745
746
747
748
        $tableDefinition = $this->getTableDefinition($table);
        foreach ($tableDefinition AS $row) {
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }
749

Marc Egger's avatar
Marc Egger committed
750
        throw new \DbException(
Marc Egger's avatar
Marc Egger committed
751
            json_encode([ERROR_MESSAGE_TO_USER => 'Column name not found', ERROR_MESSAGE_TO_DEVELOPER => "Column name '$columnName' not found in table '$table'."]),
752
            ERROR_DB_COLUMN_NOT_FOUND_IN_TABLE);
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
    }

    /**
     * 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
769
770
771
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
772
773
     */
    public function getTableDefinition($table) {
774
        return $this->sql("SHOW FIELDS FROM `$table`", ROW_EXPECT_GE_1, array(), "No columns found for table '$table'");
775
776
    }

777
    /**
778
779
     * Wrapper for sql(), to simplyfy access.
     *
Carsten  Rose's avatar
Carsten Rose committed
780
     * @param              $sql
Carsten  Rose's avatar
Carsten Rose committed
781
782
     * @param array $keys
     * @param array $stat
Carsten  Rose's avatar
Carsten Rose committed
783
     *
784
     * @return array|bool
Marc Egger's avatar
Marc Egger committed
785
786
787
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
788
     */
789
    public function sqlKeys($sql, array &$keys, array &$stat = array()) {
790

791
        return $this->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
792
    }
793

794
795
796
797
798
799
    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
800
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
801
        return $this->mysqli->insert_id;
802
    }
Carsten  Rose's avatar
Carsten Rose committed
803

804
805
806
807
    /**
     * Searches for the table '$name'.
     *
     * @param $name
Carsten  Rose's avatar
Carsten Rose committed
808
     *
809
     * @return bool  true if found, else false
Marc Egger's avatar
Marc Egger committed
810
811
812
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
     */
    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
831
832
833
834
    /**
     * @param $table
     * @param $columnDefinition
     * @param $mode
Marc Egger's avatar
Marc Egger committed
835
836
837
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
Carsten  Rose's avatar
Carsten Rose committed
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
     */
    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
858

859
860
861
    /**
     * Depending on $sql reads FormElements to a specific container or all. Preprocess all FormElements.
     *
Carsten  Rose's avatar
Carsten Rose committed
862
863
864
     * @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
865
     *
866
     * @return array|int
Marc Egger's avatar
Marc Egger committed
867
868
869
870
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
871
872
873
874
875
     */
    public function getNativeFormElements($sql, array $param, $formSpec) {

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

876
        $feSpecNative = HelperFormElement::formElementSetDefault($feSpecNative, $formSpec);
877

878
        // Explode and Do $FormElement.parameter
879
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);
880
881
882
883
884

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

        // Copy Attributes to FormElements
885
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);
886
887
888
889

        return $feSpecNative;
    }

890
891
892
893
894
    /**
     * 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
895
     *
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
     * @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;
    }

    /**
924
925
926
927
928
929
     * $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.
930
     *
Carsten  Rose's avatar
Carsten Rose committed
931
     * @param array $arr
932
933
934
935
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
Carsten  Rose's avatar
Carsten Rose committed
936
     *
937
938
     * @return array
     */
939
940
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

941
942
943
944
945
        if ($arr == array() || $arr === null) {
            return array();
        }

        // Set defaults
946
947
948
949
950
951
952
953
        if ($destColumn1 == '') {
            $destColumn1 = $srcColumn1;
        }

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

954
        // Set final column names
955
956
957
        $row = $arr[0];
        $keys = array_keys($row);
        if (count($row) < 2) {
958
959
960
961
962
            $column1 = $keys[0];
            $column2 = $keys[0];
        } elseif (array_key_exists($srcColumn1, $row) && array_key_exists($srcColumn2, $row)) {
            $column1 = $srcColumn1;
            $column2 = $srcColumn2;
963
        } else {
964
965
            $column1 = $keys[0];
            $column2 = $keys[1];
966
967
        }

968
        $new = array();
969
970
        $row = array_shift($arr);
        while (null !== $row) {
971
            $new[] = [$destColumn1 => $row[$column1], $destColumn2 => $row[$column2]];
972
//            $new[] = [$destColumn1 => htmlentities($row[$column1], ENT_QUOTES), $destColumn2 => htmlentities($row[$column2], ENT_QUOTES)];
973
974
975
976
977
            $row = array_shift($arr);
        }

        return $new;
    }
978
979
980
981
982

    /**
     * Proxy for mysqli::real_escape_string()
     *
     * @param string $value
Carsten  Rose's avatar
Carsten Rose committed
983
     *
984
985
986
987
988
     * @return string
     */
    public function realEscapeString($value) {
        return $this->mysqli->real_escape_string($value);
    }
Carsten  Rose's avatar
Carsten Rose committed
989
990

    /**
Marc Egger's avatar
Marc Egger committed
991
     * @param $sqlStatements
Carsten  Rose's avatar
Carsten Rose committed
992
     *
Marc Egger's avatar
Marc Egger committed
993
     * @throws \CodeException
Carsten  Rose's avatar
Carsten Rose committed
994
     */
Marc Egger's avatar
Marc Egger committed
995
    public function playMultiQuery($sqlStatements) {
Carsten  Rose's avatar
Carsten Rose committed
996

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