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

11

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

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

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

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

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

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

46
47
48
    /**
     * @var string
     */
49
    private $sqlLogAbsolute = '';
50

51
52
53
    /**
     * @var array
     */
Carsten  Rose's avatar
Carsten Rose committed
54
    private $sqlLogModePrio = [SQL_LOG_MODE_NONE => 1, SQL_LOG_MODE_ERROR => 2, SQL_LOG_MODE_MODIFY => 3, SQL_LOG_MODE_ALL => 4];
55

56
57
    private $dbName = '';

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

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

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

78
        $this->sqlLogAbsolute = $storeSystem[SYSTEM_SQL_LOG_ABSOLUTE];
79
        $dbInit = $storeSystem[SYSTEM_DB_INIT];
80

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

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

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

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

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

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

        return $config;
    }
131

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

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

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

153
154
        }

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

163
        return $mysqli;
164
165
166
    }

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

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

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

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

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

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

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

264
265
        $this->closeMysqliStmt();

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

270
271
272
273
274
275
        return $result;
    }

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

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

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

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

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

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

335
        return $msg;
336
337
    }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

493
494
495
        return $count;
    }

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

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

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

519
520
521
        return false;
    }

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

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

538
539
        $status = '';

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

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

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

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


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

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

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

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

591
        Logger::logMessage($msg, $this->sqlLogAbsolute);
592
593
    }

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

        return $msg;
621
622
    }

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

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

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

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

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

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

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

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

            default:
                return $this->mysqli_result->fetch_all(MYSQLI_ASSOC);
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
    /**
     * 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
710
711
712
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
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
745
     */
    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
746
     *
747
748
     * @return array the definition of the column as retrieved by Database::getTableDefinition().
     *
Marc Egger's avatar
Marc Egger committed
749
750
751
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
752
753
     */
    private function getFieldDefinitionFromTable($table, $columnName) {
754

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

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

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

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

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

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

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

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

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

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

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

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

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

        return $feSpecNative;
    }

902
903
904
905
906
    /**
     * 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
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;
    }

    /**
936
937
938
939
940
941
     * $arr = [ 0 => [ $srcColumn1 => $value0_1, $srcColumn2 => $value0_2 ], 1 => [ $srcColumn1 => $value1_1, $srcColumn2 => $value1_2 ], ...]
     *
     * $arr will be converted to a two column array with keys $destColumn1 and $destColumn2.
     * If $destColumn1 or $destColumn2 is empty, take $srcColumn1, $srcColumn2 as names.
     * $arr might contain one or more columns. Only the first two columns are used.
     * If there is only one column, that column will be doubled.
942
     *
Carsten  Rose's avatar
Carsten Rose committed
943
     * @param array $arr
944
945
946
947
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
Carsten  Rose's avatar
Carsten Rose committed
948
     *
949
950
     * @return array
     */
951
952
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

953
954
955
956
957
        if ($arr == array() || $arr === null) {
            return array();
        }

        // Set defaults
958
959
960
961
962
963
964
965
        if ($destColumn1 == '') {
            $destColumn1 = $srcColumn1;
        }

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

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

980
        $new = array();
981
982
        $row = array_shift($arr);
        while (null !== $row) {
983
            $new[] = [$destColumn1 => $row[$column1], $destColumn2 => $row[$column2]];
984
//            $new[] = [$destColumn1 => htmlentities($row[$column1], ENT_QUOTES), $destColumn2 => htmlentities($row[$column2], ENT_QUOTES)];
985
986
987
988
989
            $row = array_shift($arr);
        }

        return $new;
    }
990
991
992
993
994

    /**
     * Proxy for mysqli::real_escape_string()
     *
     * @param string $value
Carsten  Rose's avatar
Carsten Rose committed
995
     *
996
997
998
999
1000
     * @return string
     */
    public function realEscapeString($value) {
        return $this->mysqli->real_escape_string($value);
    }
For faster browsing, not all history is shown. View entire blame