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

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

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

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

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

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

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

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

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

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

55
56
    private $dbName = '';

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

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

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

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
357
358
        // Only log a modify type statement here if sqlLogMode is (at least) modifyAll
        // If sqlLogMode is modify, log the statement after it has been executed and we know if there are affected rows.
        $sqlLogMode = $this->isSqlModify($sql) ? SQL_LOG_MODE_MODIFY_ALL : SQL_LOG_MODE_ALL;
359
        $result = 0;
360
        $stat = array();
361
        $errorMsg[ERROR_MESSAGE_TO_USER] = empty($specificMessage) ? 'SQL error' : $specificMessage;
362

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

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

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

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

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

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

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

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

        $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
409
                    throw new \DbException(
Marc Egger's avatar
Marc Egger committed
410
                        json_encode([ERROR_MESSAGE_TO_USER => 'Error DB execute', ERROR_MESSAGE_TO_DEVELOPER => '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error . $specificMessage]),
411
412
                        ERROR_DB_EXECUTE);

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

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

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

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

461
        // Logfile
462
463
464
465
466
467
468
469
470
471
472
        $pageContentSqlLogMode = $this->store->getVar(SYSTEM_SQL_LOG_MODE, STORE_SYSTEM);
        if ($pageContentSqlLogMode == SQL_LOG_MODE_MODIFY && $sqlLogMode == SQL_LOG_MODE_MODIFY_ALL)  {
            // sqlLogMode modify: need to log query and query result (if count > 0)
            if ($count > 0) {
                $this->dbLog(SQL_LOG_MODE_MODIFY, $sql, $parameterArray);
                $this->dbLog(SQL_LOG_MODE_MODIFY, $msg);
            }
        } else {
            // Query result
            $this->dbLog($sqlLogMode, $msg);
        }
473

474
475
476
        return $count;
    }

477
478
479
480
    /**
     * Check if the given SQL Statement might modify data.
     *
     * @param $sql
Carsten  Rose's avatar
Carsten Rose committed
481
     *
482
483
484
     * @return bool  true is the statement might modify data, else: false
     */
    private function isSqlModify($sql) {
485

486
        $command = explode(' ', $sql, 2);
487

488
489
490
491
492
493
        switch (strtoupper($command[0])) {
            case 'INSERT':
            case 'UPDATE':
            case 'DELETE':
            case 'REPLACE':
            case 'TRUNCATE':
494
495
496
            case 'DROP':
            case 'CREATE':
            case 'ALTER':
497
498
                return true;
        }
499

500
501
502
        return false;
    }

503
    /**
504
     * Decide if the SQL statement has to be logged. If yes, create a timestamp and do the log.
505
     *
506
507
     * @param string $currentQueryMode
     * @param string $sql
508
     * @param array $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
509
     *
Marc Egger's avatar
Marc Egger committed
510
511
     * @throws \CodeException
     * @throws \UserFormException
512
     */
513
    private function dbLog($currentQueryMode = SQL_LOG_MODE_ALL, $sql = '', $parameterArray = array()) {
514

515
516
517
518
        if ($sql == '') {
            return;
        }

519
520
        $status = '';

521
522
523
524
525
        // 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;
        }
526

527
528
529
        // Check if string is known.
        foreach ([$sqlLogMode, $currentQueryMode] as $mode) {
            if (!isset($this->sqlLogModePrio[$mode])) {
Marc Egger's avatar
Marc Egger committed
530
                throw new \UserFormException("Unknown SQL_LOG_MODE: $mode", ERROR_UNKNOWN_SQL_LOG_MODE);
531
532
533
534
535
536
            }
        }

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

539
        // Client IP Address
540
541
542
543
544
        $remoteAddress = ($this->store === null) ? '0.0.0.0' : $this->store->getVar(CLIENT_REMOTE_ADDRESS, STORE_CLIENT);


        $logArr = [
            ['FE', TYPO3_FE_USER, STORE_TYPO3],
545
            ['FESU', TYPO3_FE_USER, STORE_USER],
546
547
548
549
550
551
552
553
554
555
556
557
558
559
            ['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);
560

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

563
564
        if (count($parameterArray) > 0) {
            $sql = $this->preparedStatementInsertParameter($sql, $parameterArray);
565
566
        }

567
568
        if ($currentQueryMode == SQL_LOG_MODE_ERROR) {
            $status = 'FAILED: ';
569
        }
570
        $msg .= '[' . $status . $sql . ']';
571

572
        Logger::logMessage($msg, $this->sqlLog);
573
574
    }

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

        return $msg;
602
603
    }

604
    /**
605
     * @param $arr
606
     */
607
    private function prepareBindParam($arr) {
608

609
610
611
612
        $bindParam = new BindParam();

        for ($ii = 0; $ii < count($arr); $ii++) {
            $bindParam->add($arr[$ii]);
613
        }
614
        call_user_func_array([$this->mysqli_stmt, 'bind_param'], $bindParam->get());
615
616
    }

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

639
        if ($this->mysqli_result->num_rows === 0) {
640
641
642
            return ($mode === ROW_IMPLODE_ALL) ? "" : array();
        }

643
644
645
        switch ($mode) {
            case ROW_IMPLODE_ALL:
                $str = "";
646
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
647
648
                    $str .= implode($row);
                }
Carsten  Rose's avatar
Carsten Rose committed
649

650
651
652
653
654
655
656
657
658
                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
659

660
661
662
663
664
                return $this->mysqli_result->fetch_all(MYSQLI_NUM);
                break;

            default:
                return $this->mysqli_result->fetch_all(MYSQLI_ASSOC);
665
666
667
        }
    }

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

736
737
738
739
740
741
        $tableDefinition = $this->getTableDefinition($table);
        foreach ($tableDefinition AS $row) {
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }
742

Marc Egger's avatar
Marc Egger committed
743
        throw new \DbException(
Marc Egger's avatar
Marc Egger committed
744
            json_encode([ERROR_MESSAGE_TO_USER => 'Column name not found', ERROR_MESSAGE_TO_DEVELOPER => "Column name '$columnName' not found in table '$table'."]),
745
            ERROR_DB_COLUMN_NOT_FOUND_IN_TABLE);
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
    }

    /**
     * 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
762
763
764
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
765
766
767
768
769
     */
    public function getTableDefinition($table) {
        return $this->sql("SHOW FIELDS FROM `$table`");
    }

770
    /**
771
772
     * Wrapper for sql(), to simplyfy access.
     *
Carsten  Rose's avatar
Carsten Rose committed
773
     * @param              $sql
Carsten  Rose's avatar
Carsten Rose committed
774
775
     * @param array $keys
     * @param array $stat
Carsten  Rose's avatar
Carsten Rose committed
776
     *
777
     * @return array|bool
Marc Egger's avatar
Marc Egger committed
778
779
780
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
781
     */
782
    public function sqlKeys($sql, array &$keys, array &$stat = array()) {
783

784
        return $this->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
785
    }
786

787
788
789
790
791
792
    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
793
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
794
        return $this->mysqli->insert_id;
795
    }
Carsten  Rose's avatar
Carsten Rose committed
796

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

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

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

869
        $feSpecNative = HelperFormElement::formElementSetDefault($feSpecNative, $formSpec);
870

871
        // Explode and Do $FormElement.parameter
872
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);
873
874
875
876
877

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

        // Copy Attributes to FormElements
878
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);
879
880
881
882

        return $feSpecNative;
    }

883
884
885
886
887
    /**
     * 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
888
     *
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
     * @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.
918
     * $arr might contain one or more columns.
Carsten  Rose's avatar
Carsten Rose committed
919
920
     * 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.
921
     *
Carsten  Rose's avatar
Carsten Rose committed
922
     * @param array $arr
923
924
925
926
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
Carsten  Rose's avatar
Carsten Rose committed
927
     *
928
929
     * @return array
     */
930
931
932
933
934
935
936
937
938
939
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

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

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

940
941
942
943
944
945
946
947
948
        $new = array();

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

        $row = $arr[0];
        $keys = array_keys($row);
        if (count($row) < 2) {
949
950
951
952
953
            $column1 = $keys[0];
            $column2 = $keys[0];
        } elseif (array_key_exists($srcColumn1, $row) && array_key_exists($srcColumn2, $row)) {
            $column1 = $srcColumn1;
            $column2 = $srcColumn2;
954
        } else {
955
956
            $column1 = $keys[0];
            $column2 = $keys[1];
957
958
959
960
        }

        $row = array_shift($arr);
        while (null !== $row) {
961
            $new[] = [$destColumn1 => $row[$column1], $destColumn2 => $row[$column2]];
962
963
964
965
966
            $row = array_shift($arr);
        }

        return $new;
    }
967
968
969
970
971

    /**
     * Proxy for mysqli::real_escape_string()
     *
     * @param string $value
Carsten  Rose's avatar
Carsten Rose committed
972
     *
973
974
975
976
977
     * @return string
     */
    public function realEscapeString($value) {
        return $this->mysqli->real_escape_string($value);
    }
Carsten  Rose's avatar
Carsten Rose committed
978
979

    /**
Marc Egger's avatar
Marc Egger committed
980
     * @param $sqlStatements
Carsten  Rose's avatar
Carsten Rose committed
981
     *
Marc Egger's avatar
Marc Egger committed
982
     * @throws \CodeException
Carsten  Rose's avatar
Carsten Rose committed
983
     */
Marc Egger's avatar
Marc Egger committed
984
    public function playMultiQuery($sqlStatements) {
Carsten  Rose's avatar
Carsten Rose committed
985

Marc Egger's avatar
Marc Egger committed
986
987
988
        $executed = $this->mysqli->multi_query($sqlStatements);
        if (false === $executed) {
            $errorMsg[ERROR_MESSAGE_TO_USER] = 'SQL Error.';
Nicola Chiapolini's avatar
Nicola Chiapolini committed
989
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = "Error playing multi query: " . $this->mysqli->error . "\n\nSQL Query:\n\n" . $sqlStatements;
Marc Egger's avatar
Marc Egger committed
990
            throw new \CodeException(json_encode($errorMsg), ERROR_PLAY_SQL_MULTIQUERY);
Carsten  Rose's avatar
Carsten Rose committed
991
992
993
994
995
996
997
998
999
1000
        }

        // 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());

    }
1001

Marc Egger's avatar
Marc Egger committed
1002
1003
1004
    /**
     * @param $filename
     *
Marc Egger's avatar
Marc Egger committed
1005
     * @throws \CodeException
Marc Egger's avatar
Marc Egger committed
1006
1007
1008
1009
1010
1011
1012
1013
     */
    public function playSqlFile($filename) {

        $query = file_get_contents($filename);

        try {
            $this->playMultiQuery($query);
        }
Marc Egger's avatar
Marc Egger committed
1014
1015
        catch (\CodeException $e) {
            throw new \CodeException("Error playing $filename", ERROR_PLAY_SQL_FILE);
Marc Egger's avatar
Marc Egger committed
1016
1017
1018
1019
        }

    }

1020
    /**
1021
     * @return string
Marc Egger's avatar
Marc Egger committed
1022
1023
     * @throws \CodeException
     * @throws \UserFormException
1024
1025
1026
1027
     */
    public function getQfqLogFile() {
        return ($this->store == null) ? SYSTEM_QFQ_LOG_FILE : $this->store->getVar(SYSTEM_QFQ_LOG, STORE_SYSTEM);
    }
1028
1029
1030
1031
1032