Database.php 33.8 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
53
54
    /**
     * @var array
     */
    private $sqlLogModePrio = [SQL_LOG_MODE_NONE => 1, SQL_LOG_MODE_ERROR => 2, SQL_LOG_MODE_MODIFY => 3, SQL_LOG_MODE_ALL => 4];

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
78
        $this->sqlLog = $storeSystem[SYSTEM_SQL_LOG];
        $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
     *
185
     * @return array|int
186
     *      SELECT | SHOW | DESCRIBE | EXPLAIN: see $mode
187
188
     *      INSERT: last_insert_id
     *      UPDATE | DELETE | REPLACE: affected rows
Marc Egger's avatar
Marc Egger committed
189
190
191
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
192
     */
193
    public function sql($sql, $mode = ROW_REGULAR, array $parameterArray = array(), $specificMessage = '', array &$keys = array(), array &$stat = array()) {
194
        $queryType = '';
195
196
        $result = array();
        $this->closeMysqliStmt();
197

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

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

208
        $count = $this->prepareExecute($sql, $parameterArray, $queryType, $stat, $specificMessage);
209

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

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

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

262
263
        $this->closeMysqliStmt();

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

268
269
270
271
272
273
        return $result;
    }

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

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

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

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

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

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

333
        return $msg;
334
335
    }

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

356
        $sqlLogMode = $this->isSqlModify($sql) ? SQL_LOG_MODE_MODIFY : SQL_LOG_MODE_ALL;
357
        $result = 0;
358
        $stat = array();
359
        $errorMsg[ERROR_MESSAGE_TO_USER] = empty($specificMessage) ? 'SQL error' : $specificMessage;
360

361
362
363
364
        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_FINAL, $sql, STORE_SYSTEM);
            $this->store->setVar(SYSTEM_SQL_PARAM_ARRAY, $parameterArray, STORE_SYSTEM);
        }
365

366
367
368
//        if ($specificMessage !== '') {
//            $specificMessage = ' - ' . $specificMessage;
//        }
369
        // Logfile
370
        $this->dbLog($sqlLogMode, $sql, $parameterArray);
371

372
        if (false === ($this->mysqli_stmt = $this->mysqli->prepare($sql))) {
373
            $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
Marc Egger's avatar
Marc Egger committed
374
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
375
376
            $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $this->mysqli->errno . ' ] ' . $this->mysqli->error;

Marc Egger's avatar
Marc Egger committed
377
            throw new \DbException(json_encode($errorMsg), ERROR_DB_PREPARE);
378
379
380
        }

        if (count($parameterArray) > 0) {
381
            if (false === $this->prepareBindParam($parameterArray)) {
382
                $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
Marc Egger's avatar
Marc Egger committed
383
                $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
384
385
                $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error;

Marc Egger's avatar
Marc Egger committed
386
                throw new \DbException(json_encode($errorMsg), ERROR_DB_BIND);
387
388
389
390
            }
        }

        if (false === $this->mysqli_stmt->execute()) {
391
            $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
Marc Egger's avatar
Marc Egger committed
392
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
393
            $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error;
394

Marc Egger's avatar
Marc Egger committed
395
            throw new \DbException(json_encode($errorMsg), ERROR_DB_EXECUTE);
396
397
398
399
400
401
402
403
404
405
406
        }

        $msg = '';
        $count = 0;
        $command = strtoupper(explode(' ', $sql, 2)[0]);
        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
407
                    throw new \DbException(
Marc Egger's avatar
Marc Egger committed
408
                        json_encode([ERROR_MESSAGE_TO_USER => 'Error DB execute', ERROR_MESSAGE_TO_DEVELOPER => '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error . $specificMessage]),
409
410
                        ERROR_DB_EXECUTE);

411
                }
412
                $queryType = QUERY_TYPE_SELECT;
413
                $this->mysqli_result = $result;
414
415
416
                $stat[DB_NUM_ROWS] = $this->mysqli_result->num_rows;
                $count = $stat[DB_NUM_ROWS];
                $msg = 'Read rows: ' . $stat[DB_NUM_ROWS];
417
                break;
418
            case 'REPLACE':
419
            case 'INSERT':
420
                $queryType = QUERY_TYPE_INSERT;
421
422
423
                $stat[DB_INSERT_ID] = $this->mysqli->insert_id;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
424
                $msg = 'ID: ' . $this->mysqli->insert_id;
425
426
427
                break;
            case 'UPDATE':
            case 'DELETE':
428
            case 'TRUNCATE':
429
430
431
                $queryType = QUERY_TYPE_UPDATE;
                $stat[DB_AFFECTED_ROWS] = $this->mysqli->affected_rows;
                $count = $stat[DB_AFFECTED_ROWS];
432
433
                $msg = 'Affected rows: ' . $count;
                break;
434

435
            case 'SET':
436
            case 'ALTER':
437
            case 'DROP':
438
            case 'CREATE':
439
            case 'CALL':
440
441
442
443
444
445
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
                break;

446
            default:
447
448
449
450
451
                // Unknown command: treat it as a control command
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
452
                break;
453
454
        }

455
456
457
        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_COUNT, $count, STORE_SYSTEM);
        }
458

459
        // Logfile
460
        $this->dbLog($sqlLogMode, $msg);
461

462
463
464
        return $count;
    }

465
466
467
468
    /**
     * Check if the given SQL Statement might modify data.
     *
     * @param $sql
Carsten  Rose's avatar
Carsten Rose committed
469
     *
470
471
472
     * @return bool  true is the statement might modify data, else: false
     */
    private function isSqlModify($sql) {
473

474
        $command = explode(' ', $sql, 2);
475

476
477
478
479
480
481
482
483
        switch (strtoupper($command[0])) {
            case 'INSERT':
            case 'UPDATE':
            case 'DELETE':
            case 'REPLACE':
            case 'TRUNCATE':
                return true;
        }
484

485
486
487
        return false;
    }

488
    /**
489
     * Decide if the SQL statement has to be logged. If yes, create a timestamp and do the log.
490
     *
491
492
     * @param string $currentQueryMode
     * @param string $sql
493
     * @param array $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
494
     *
Marc Egger's avatar
Marc Egger committed
495
496
     * @throws \CodeException
     * @throws \UserFormException
497
     */
498
    private function dbLog($currentQueryMode = SQL_LOG_MODE_ALL, $sql = '', $parameterArray = array()) {
499

500
501
502
503
        if ($sql == '') {
            return;
        }

504
505
        $status = '';

506
507
508
509
510
        // 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;
        }
511

512
513
514
        // Check if string is known.
        foreach ([$sqlLogMode, $currentQueryMode] as $mode) {
            if (!isset($this->sqlLogModePrio[$mode])) {
Marc Egger's avatar
Marc Egger committed
515
                throw new \UserFormException("Unknown SQL_LOG_MODE: $mode", ERROR_UNKNOWN_SQL_LOG_MODE);
516
517
518
519
520
521
            }
        }

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

524
        // Client IP Address
525
526
527
528
529
        $remoteAddress = ($this->store === null) ? '0.0.0.0' : $this->store->getVar(CLIENT_REMOTE_ADDRESS, STORE_CLIENT);


        $logArr = [
            ['FE', TYPO3_FE_USER, STORE_TYPO3],
530
            ['FESU', TYPO3_FE_USER, STORE_USER],
531
532
533
534
535
536
537
538
539
540
541
542
543
544
            ['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);
545

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

548
549
        if (count($parameterArray) > 0) {
            $sql = $this->preparedStatementInsertParameter($sql, $parameterArray);
550
551
        }

552
553
        if ($currentQueryMode == SQL_LOG_MODE_ERROR) {
            $status = 'FAILED: ';
554
        }
555
        $msg .= '[' . $status . $sql . ']';
556

557
        Logger::logMessage($msg, $this->sqlLog);
558
559
    }

560
561
    /**
     * @param $sql
562
     * @param $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
563
     *
564
     * @return string
565
     */
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
    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 = '?';
            }
581
        }
582
583
584
585
586
        if (isset($sqlArray[$ii])) {
            $msg .= $sqlArray[$ii];
        }

        return $msg;
587
588
    }

589
    /**
590
     * @param $arr
591
     */
592
    private function prepareBindParam($arr) {
593

594
595
596
597
        $bindParam = new BindParam();

        for ($ii = 0; $ii < count($arr); $ii++) {
            $bindParam->add($arr[$ii]);
598
        }
599
        call_user_func_array([$this->mysqli_stmt, 'bind_param'], $bindParam->get());
600
601
    }

602
603
604
    /**
     * Fetch all rows of the result as associative array.
     *
605
606
     * mode:
     *  ROW_IMPLODE_ALL: Return string. All cells of all rows imploded to one string.
607
     *  ROW_KEYS: Return num array with column names in $keys
608
609
610
     *  default: Return 2-dimensional assoc array
     *
     * @param string $mode
Carsten  Rose's avatar
Carsten Rose committed
611
     * @param array $keys
Carsten  Rose's avatar
Carsten Rose committed
612
     *
613
     * @return array|bool|mixed|string false in case of an error.
614
615
616
617
618
     *              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
     *
     */
619
620
    private function fetchAll($mode = '', &$keys = array()) {
        if ($this->mysqli_result == null || $this->mysqli_result == false) {
621
622
623
            return false;
        }

624
        if ($this->mysqli_result->num_rows === 0) {
625
626
627
            return ($mode === ROW_IMPLODE_ALL) ? "" : array();
        }

628
629
630
        switch ($mode) {
            case ROW_IMPLODE_ALL:
                $str = "";
631
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
632
633
                    $str .= implode($row);
                }
Carsten  Rose's avatar
Carsten Rose committed
634

635
636
637
638
639
640
641
642
643
                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
644

645
646
647
648
649
                return $this->mysqli_result->fetch_all(MYSQLI_NUM);
                break;

            default:
                return $this->mysqli_result->fetch_all(MYSQLI_ASSOC);
650
651
652
        }
    }

653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
    /**
     * 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
676
677
678
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
679
680
681
682
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
709
710
711
     */
    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
712
     *
713
714
     * @return array the definition of the column as retrieved by Database::getTableDefinition().
     *
Marc Egger's avatar
Marc Egger committed
715
716
717
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
718
719
     */
    private function getFieldDefinitionFromTable($table, $columnName) {
720

721
722
723
724
725
726
        $tableDefinition = $this->getTableDefinition($table);
        foreach ($tableDefinition AS $row) {
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }
727

Marc Egger's avatar
Marc Egger committed
728
        throw new \DbException(
Marc Egger's avatar
Marc Egger committed
729
            json_encode([ERROR_MESSAGE_TO_USER => 'Column name not found', ERROR_MESSAGE_TO_DEVELOPER => "Column name '$columnName' not found in table '$table'."]),
730
            ERROR_DB_COLUMN_NOT_FOUND_IN_TABLE);
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
    }

    /**
     * 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
747
748
749
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
750
751
752
753
754
     */
    public function getTableDefinition($table) {
        return $this->sql("SHOW FIELDS FROM `$table`");
    }

755
    /**
756
757
     * Wrapper for sql(), to simplyfy access.
     *
Carsten  Rose's avatar
Carsten Rose committed
758
     * @param              $sql
Carsten  Rose's avatar
Carsten Rose committed
759
760
     * @param array $keys
     * @param array $stat
Carsten  Rose's avatar
Carsten Rose committed
761
     *
762
     * @return array|bool
Marc Egger's avatar
Marc Egger committed
763
764
765
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
766
     */
767
    public function sqlKeys($sql, array &$keys, array &$stat = array()) {
768

769
        return $this->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
770
    }
771

772
773
774
775
776
777
    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
778
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
779
        return $this->mysqli->insert_id;
780
    }
Carsten  Rose's avatar
Carsten Rose committed
781

782
783
784
785
    /**
     * Searches for the table '$name'.
     *
     * @param $name
Carsten  Rose's avatar
Carsten Rose committed
786
     *
787
     * @return bool  true if found, else false
Marc Egger's avatar
Marc Egger committed
788
789
790
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
     */
    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
809
810
811
812
    /**
     * @param $table
     * @param $columnDefinition
     * @param $mode
Marc Egger's avatar
Marc Egger committed
813
814
815
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
Carsten  Rose's avatar
Carsten Rose committed
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
     */
    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
836

837
838
839
    /**
     * Depending on $sql reads FormElements to a specific container or all. Preprocess all FormElements.
     *
Carsten  Rose's avatar
Carsten Rose committed
840
841
842
     * @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
843
     *
844
     * @return array|int
Marc Egger's avatar
Marc Egger committed
845
846
847
848
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
849
850
851
852
853
     */
    public function getNativeFormElements($sql, array $param, $formSpec) {

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

854
        $feSpecNative = HelperFormElement::formElementSetDefault($feSpecNative, $formSpec);
855

856
        // Explode and Do $FormElement.parameter
857
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);
858
859
860
861
862

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

        // Copy Attributes to FormElements
863
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);
864
865
866
867

        return $feSpecNative;
    }

868
869
870
871
872
    /**
     * 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
873
     *
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
     * @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.
903
     * $arr might contain one or more columns.
Carsten  Rose's avatar
Carsten Rose committed
904
905
     * 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.
906
     *
Carsten  Rose's avatar
Carsten Rose committed
907
     * @param array $arr
908
909
910
911
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
Carsten  Rose's avatar
Carsten Rose committed
912
     *
913
914
     * @return array
     */
915
916
917
918
919
920
921
922
923
924
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

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

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

925
926
927
928
929
930
931
932
933
        $new = array();

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

        $row = $arr[0];
        $keys = array_keys($row);
        if (count($row) < 2) {
934
935
936
937
938
            $column1 = $keys[0];
            $column2 = $keys[0];
        } elseif (array_key_exists($srcColumn1, $row) && array_key_exists($srcColumn2, $row)) {
            $column1 = $srcColumn1;
            $column2 = $srcColumn2;
939
        } else {
940
941
            $column1 = $keys[0];
            $column2 = $keys[1];
942
943
944
945
        }

        $row = array_shift($arr);
        while (null !== $row) {
946
            $new[] = [$destColumn1 => $row[$column1], $destColumn2 => $row[$column2]];
947
948
949
950
951
            $row = array_shift($arr);
        }

        return $new;
    }
952
953
954
955
956

    /**
     * Proxy for mysqli::real_escape_string()
     *
     * @param string $value
Carsten  Rose's avatar
Carsten Rose committed
957
     *
958
959
960
961
962
     * @return string
     */
    public function realEscapeString($value) {
        return $this->mysqli->real_escape_string($value);
    }
Carsten  Rose's avatar
Carsten Rose committed
963
964

    /**
Marc Egger's avatar
Marc Egger committed
965
     * @param $sqlStatements
Carsten  Rose's avatar
Carsten Rose committed
966
     *
Marc Egger's avatar
Marc Egger committed
967
     * @throws \CodeException
Carsten  Rose's avatar
Carsten Rose committed
968
     */
Marc Egger's avatar
Marc Egger committed
969
    public function playMultiQuery($sqlStatements) {
Carsten  Rose's avatar
Carsten Rose committed
970

Marc Egger's avatar
Marc Egger committed
971
972
973
974
        $executed = $this->mysqli->multi_query($sqlStatements);
        if (false === $executed) {
            $errorMsg[ERROR_MESSAGE_TO_USER] = 'SQL Error.';
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = "Error playing multi query: " . $sqlStatements;
Marc Egger's avatar
Marc Egger committed
975
            throw new \CodeException($errorMsg, ERROR_PLAY_SQL_MULTIQUERY);
Carsten  Rose's avatar
Carsten Rose committed
976
977
978
979
980
981
982
983
984
985
        }

        // discard all results: this is important - if missed, following calls on $mysqli will fail.
        do {
            if ($res = $this->mysqli->store_result()) {
                $res->free();
            }
        } while ($this->mysqli->more_results() && $this->mysqli->next_result());

    }
986

Marc Egger's avatar
Marc Egger committed
987
988
989
    /**
     * @param $filename
     *
Marc Egger's avatar
Marc Egger committed
990
     * @throws \CodeException
Marc Egger's avatar
Marc Egger committed
991
992
993
994
995
996
997
998
     */
    public function playSqlFile($filename) {

        $query = file_get_contents($filename);

        try {
            $this->playMultiQuery($query);
        }
Marc Egger's avatar
Marc Egger committed
999
1000
        catch (\CodeException $e) {
            throw new \CodeException("Error playing $filename", ERROR_PLAY_SQL_FILE);