Database.php 35.2 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
     */
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        $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

Carsten  Rose's avatar
Carsten Rose committed
361
        $sqlLogMode = $this->isSqlModify($sql) ? SQL_LOG_MODE_MODIFY : SQL_LOG_MODE_ALL;
362
363
        $errno = 0;

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

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

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

379
        if (false === ($this->mysqli_stmt = $this->mysqli->prepare($sql))) {
380

381
382
383
384
385
386
387
388
389
            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;
            }
390
391
392
        }

        if (count($parameterArray) > 0) {
393
            if (false === $this->prepareBindParam($parameterArray)) {
394
395
396
397
398
399
400
401
402
                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;
                }
403
404
405
406
            }
        }

        if (false === $this->mysqli_stmt->execute()) {
407
408
409
410
            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;
411

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

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

426
427
428
429
430
431
        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
432
                    throw new \DbException(
Marc Egger's avatar
Marc Egger committed
433
                        json_encode([ERROR_MESSAGE_TO_USER => 'Error DB execute', ERROR_MESSAGE_TO_DEVELOPER => '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error . $specificMessage]),
434
435
                        ERROR_DB_EXECUTE);

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

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

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

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

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

492
493
494
        return $count;
    }

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

504
        $command = explode(' ', $sql, 2);
505

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

518
519
520
        return false;
    }

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

533
534
535
536
        if ($sql == '') {
            return;
        }

537
538
        $status = '';

539
540
541
542
543
        // 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;
        }
544

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

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

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


        $logArr = [
            ['FE', TYPO3_FE_USER, STORE_TYPO3],
563
            ['FESU', TYPO3_FE_USER, STORE_USER],
564
565
566
567
568
569
570
571
572
573
574
575
576
577
            ['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);
578

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

581
582
        if (count($parameterArray) > 0) {
            $sql = $this->preparedStatementInsertParameter($sql, $parameterArray);
583
584
        }

585
586
        if ($currentQueryMode == SQL_LOG_MODE_ERROR) {
            $status = 'FAILED: ';
587
        }
588
        $msg .= '[' . $status . $sql . ']';
589

590
        Logger::logMessage($msg, $this->sqlLog);
591
592
    }

593
594
    /**
     * @param $sql
595
     * @param $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
596
     *
597
     * @return string
598
     */
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
    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 = '?';
            }
614
        }
615
616
617
618
619
        if (isset($sqlArray[$ii])) {
            $msg .= $sqlArray[$ii];
        }

        return $msg;
620
621
    }

622
    /**
623
     * @param $arr
624
     */
625
    private function prepareBindParam($arr) {
626

627
628
629
630
        $bindParam = new BindParam();

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

635
636
637
    /**
     * Fetch all rows of the result as associative array.
     *
638
639
     * mode:
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
640
     *  ROW_KEYS: Return num array with column names in $keys
641
642
643
     *  default: Return 2-dimensional assoc array
     *
     * @param string $mode
Carsten  Rose's avatar
Carsten Rose committed
644
     * @param array $keys
Carsten  Rose's avatar
Carsten Rose committed
645
     *
646
     * @return array|bool|mixed|string false in case of an error.
647
648
649
650
651
     *              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
     *
     */
652
653
    private function fetchAll($mode = '', &$keys = array()) {
        if ($this->mysqli_result == null || $this->mysqli_result == false) {
654
655
656
            return false;
        }

657
        if ($this->mysqli_result->num_rows === 0) {
658
659
660
            return ($mode === ROW_IMPLODE_ALL) ? "" : array();
        }

661
662
663
        switch ($mode) {
            case ROW_IMPLODE_ALL:
                $str = "";
664
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
665
666
                    $str .= implode($row);
                }
Carsten  Rose's avatar
Carsten Rose committed
667

668
669
670
671
672
673
674
675
676
                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
677

678
679
680
681
682
                return $this->mysqli_result->fetch_all(MYSQLI_NUM);
                break;

            default:
                return $this->mysqli_result->fetch_all(MYSQLI_ASSOC);
683
684
685
        }
    }

686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
    /**
     * 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
709
710
711
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
712
713
714
715
716
717
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
     */
    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
745
     *
746
747
     * @return array the definition of the column as retrieved by Database::getTableDefinition().
     *
Marc Egger's avatar
Marc Egger committed
748
749
750
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
751
752
     */
    private function getFieldDefinitionFromTable($table, $columnName) {
753

754
755
756
757
758
759
        $tableDefinition = $this->getTableDefinition($table);
        foreach ($tableDefinition AS $row) {
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }
760

Marc Egger's avatar
Marc Egger committed
761
        throw new \DbException(
Marc Egger's avatar
Marc Egger committed
762
            json_encode([ERROR_MESSAGE_TO_USER => 'Column name not found', ERROR_MESSAGE_TO_DEVELOPER => "Column name '$columnName' not found in table '$table'."]),
763
            ERROR_DB_COLUMN_NOT_FOUND_IN_TABLE);
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
    }

    /**
     * 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
780
781
782
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
783
784
785
786
787
     */
    public function getTableDefinition($table) {
        return $this->sql("SHOW FIELDS FROM `$table`");
    }

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

802
        return $this->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
803
    }
804

805
806
807
808
809
810
    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
811
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
812
        return $this->mysqli->insert_id;
813
    }
Carsten  Rose's avatar
Carsten Rose committed
814

815
816
817
818
    /**
     * Searches for the table '$name'.
     *
     * @param $name
Carsten  Rose's avatar
Carsten Rose committed
819
     *
820
     * @return bool  true if found, else false
Marc Egger's avatar
Marc Egger committed
821
822
823
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
     */
    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
842
843
844
845
    /**
     * @param $table
     * @param $columnDefinition
     * @param $mode
Marc Egger's avatar
Marc Egger committed
846
847
848
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
Carsten  Rose's avatar
Carsten Rose committed
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
     */
    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
869

870
871
872
    /**
     * Depending on $sql reads FormElements to a specific container or all. Preprocess all FormElements.
     *
Carsten  Rose's avatar
Carsten Rose committed
873
874
875
     * @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
876
     *
877
     * @return array|int
Marc Egger's avatar
Marc Egger committed
878
879
880
881
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
882
883
884
885
886
     */
    public function getNativeFormElements($sql, array $param, $formSpec) {

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

887
        $feSpecNative = HelperFormElement::formElementSetDefault($feSpecNative, $formSpec);
888

889
        // Explode and Do $FormElement.parameter
890
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);
891
892
893
894
895

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

        // Copy Attributes to FormElements
896
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);
897
898
899
900

        return $feSpecNative;
    }

901
902
903
904
905
    /**
     * 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
906
     *
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
     * @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.
936
     * $arr might contain one or more columns.
Carsten  Rose's avatar
Carsten Rose committed
937
938
     * 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.
939
     *
Carsten  Rose's avatar
Carsten Rose committed
940
     * @param array $arr
941
942
943
944
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
Carsten  Rose's avatar
Carsten Rose committed
945
     *
946
947
     * @return array
     */
948
949
950
951
952
953
954
955
956
957
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

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

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

958
959
960
961
962
963
964
965
966
        $new = array();

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

        $row = $arr[0];
        $keys = array_keys($row);
        if (count($row) < 2) {
967
968
969
970
971
            $column1 = $keys[0];
            $column2 = $keys[0];
        } elseif (array_key_exists($srcColumn1, $row) && array_key_exists($srcColumn2, $row)) {
            $column1 = $srcColumn1;
            $column2 = $srcColumn2;
972
        } else {
973
974
            $column1 = $keys[0];
            $column2 = $keys[1];
975
976
977
978
        }

        $row = array_shift($arr);
        while (null !== $row) {
979
            $new[] = [$destColumn1 => $row[$column1], $destColumn2 => $row[$column2]];
980
981
982
983
984
            $row = array_shift($arr);
        }

        return $new;
    }
985
986
987
988
989

    /**
     * Proxy for mysqli::real_escape_string()
     *
     * @param string $value
Carsten  Rose's avatar
Carsten Rose committed
990
     *
991
992
993
994
995
     * @return string
     */
    public function realEscapeString($value) {
        return $this->mysqli->real_escape_string($value);
    }
Carsten  Rose's avatar
Carsten Rose committed
996
997

    /**
Marc Egger's avatar
Marc Egger committed
998
     * @param $sqlStatements
Carsten  Rose's avatar
Carsten Rose committed
999
     *
Marc Egger's avatar
Marc Egger committed
1000
     * @throws \CodeException
For faster browsing, not all history is shown. View entire blame