Database.php 38 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
use IMATHUZH\Qfq\Core\Exception\Thrower;
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\Helper\SqlQuery;
19
use IMATHUZH\Qfq\Core\Store\Store;
20

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

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

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

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

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

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

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

57
58
    private $dbName = '';

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

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

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

79
        $this->sqlLogAbsolute = Path::absoluteSqlLogFile();
80
        $dbInit = $storeSystem[SYSTEM_DB_INIT];
81

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

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

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

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

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

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

        return $config;
    }
132

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

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

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

154
155
        }

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

164
        return $mysqli;
165
166
167
    }

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

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

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

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

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

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

256
                default:
Marc Egger's avatar
Marc Egger committed
257
                    throw new \DbException($specificMessage . "Unknown mode: $mode", ERROR_UNKNOWN_MODE);
258
            }
259
260
261

            $this->freeResult();

262
263
        } elseif ($queryType === QUERY_TYPE_INSERT) {
            $result = $stat[DB_INSERT_ID];
264
265
        } else {
            $result = $count;
266
267
        }

268
269
        $this->closeMysqliStmt();

270
271
272
273
        $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);

274
275
276
277
278
279
        return $result;
    }

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

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

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

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

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

334
335
336
337
338
        // 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";
        }

339
        return $msg;
340
341
    }

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

Carsten  Rose's avatar
Carsten Rose committed
366
        $sqlLogMode = $this->isSqlModify($sql) ? SQL_LOG_MODE_MODIFY : SQL_LOG_MODE_ALL;
367
        $errno = 0;
368
        $stat = array();
369
        $errorMsg[ERROR_MESSAGE_TO_USER] = empty($specificMessage) ? 'SQL error' : $specificMessage;
370

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

376
        // Logfile
377
        $this->dbLog($sqlLogMode, $sql, $parameterArray);
378

379
        if (false === ($this->mysqli_stmt = $this->mysqli->prepare($sql))) {
Carsten  Rose's avatar
Carsten Rose committed
380
            $errno = $this->mysqli->errno;
381
            if (false === array_search($errno, $skipErrno)) {  // removed nonsensical condition $skipErrno === array() &&
382
383
                $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
                $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
Carsten  Rose's avatar
Carsten Rose committed
384
                $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $errno . ' ] ' . $this->mysqli->error;
385
386
387

                throw new \DbException(json_encode($errorMsg), ERROR_DB_PREPARE);
            }
388
389
390
        }

        if (count($parameterArray) > 0) {
391
            if (false === $this->prepareBindParam($parameterArray)) {
Carsten  Rose's avatar
Carsten Rose committed
392
393
                $errno = $this->mysqli_stmt->errno;
                if ($skipErrno === array() && false === array_search($errno, $skipErrno)) {
394
395
                    $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
                    $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $this->getSqlHint($sql, $this->mysqli->error);
Carsten  Rose's avatar
Carsten Rose committed
396
                    $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $errno . ' ] ' . $this->mysqli_stmt->error;
397
398
399

                    throw new \DbException(json_encode($errorMsg), ERROR_DB_BIND);
                }
400
401
402
403
            }
        }

        if (false === $this->mysqli_stmt->execute()) {
Carsten  Rose's avatar
Carsten Rose committed
404
405
            $errno = $this->mysqli->errno;
            if ($skipErrno === array() || false === array_search($errno, $skipErrno)) {
406
407
408
                $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;
409

410
411
                throw new \DbException(json_encode($errorMsg), ERROR_DB_EXECUTE);
            }
412
413
        }

414
415
416
417
418
419
        if ($errno === 0) {
            $command = strtoupper(explode(' ', $sql, 2)[0]);
        } else {
            $command = 'FAILED';
        }

420
421
422
423
424
        switch ($command) {
            case 'SELECT':
            case 'SHOW':
            case 'DESCRIBE':
            case 'EXPLAIN':
425
            case 'CALL':
426
                if (false === ($result = $this->mysqli_stmt->get_result())) {
Marc Egger's avatar
Marc Egger committed
427
                    throw new \DbException(
Marc Egger's avatar
Marc Egger committed
428
                        json_encode([ERROR_MESSAGE_TO_USER => 'Error DB execute', ERROR_MESSAGE_TO_DEVELOPER => '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error . $specificMessage]),
429
430
                        ERROR_DB_EXECUTE);

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

455
            case 'SET':
456
            case 'ALTER':
457
            case 'DROP':
458
            case 'CREATE':
459
460
461
462
463
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
                break;
464
465
466
467
468
469
            case 'FAILED':
                $queryType = QUERY_TYPE_FAILED;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = -1;
                $msg = '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error;
                break;
470

471
            default:
472
473
474
475
476
                // Unknown command: treat it as a control command
                $queryType = QUERY_TYPE_CONTROL;
                $stat[DB_AFFECTED_ROWS] = 0;
                $count = $stat[DB_AFFECTED_ROWS];
                $msg = '';
477
                break;
478
479
        }

480
481
482
        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_COUNT, $count, STORE_SYSTEM);
        }
483

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

486
487
488
        return $count;
    }

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

498
        $command = explode(' ', $sql, 2);
499

500
501
502
503
504
505
        switch (strtoupper($command[0])) {
            case 'INSERT':
            case 'UPDATE':
            case 'DELETE':
            case 'REPLACE':
            case 'TRUNCATE':
506
507
508
            case 'DROP':
            case 'CREATE':
            case 'ALTER':
509
510
                return true;
        }
511

512
513
514
        return false;
    }

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

527
528
529
530
        if ($sql == '') {
            return;
        }

531
532
        $status = '';

533
534
535
536
537
        // 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;
        }
538

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

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

551
        // Client IP Address
552
553
554
555
556
        $remoteAddress = ($this->store === null) ? '0.0.0.0' : $this->store->getVar(CLIENT_REMOTE_ADDRESS, STORE_CLIENT);


        $logArr = [
            ['FE', TYPO3_FE_USER, STORE_TYPO3],
557
            ['FESU', TYPO3_FE_USER, STORE_USER],
558
559
560
561
562
563
564
565
566
567
568
569
570
571
            ['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);
572

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

575
576
        if (count($parameterArray) > 0) {
            $sql = $this->preparedStatementInsertParameter($sql, $parameterArray);
577
578
        }

579
580
        if ($currentQueryMode == SQL_LOG_MODE_ERROR) {
            $status = 'FAILED: ';
581
        }
582
        $msg .= '[' . $status . $sql . ']';
583

584
        Logger::logMessage($msg, $this->sqlLogAbsolute);
585
586
    }

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

        return $msg;
614
615
    }

616
    /**
617
     * @param $arr
618
     */
619
    private function prepareBindParam($arr) {
620

621
622
623
624
        $bindParam = new BindParam();

        for ($ii = 0; $ii < count($arr); $ii++) {
            $bindParam->add($arr[$ii]);
625
        }
626
        call_user_func_array([$this->mysqli_stmt, 'bind_param'], $bindParam->get());
627
628
    }

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

        $result = null;

650
        if ($this->mysqli_result == null || $this->mysqli_result == false) {
651
652
653
            return false;
        }

654
        if ($this->mysqli_result->num_rows === 0) {
655
656
657
            return ($mode === ROW_IMPLODE_ALL) ? "" : array();
        }

658
659
        switch ($mode) {
            case ROW_IMPLODE_ALL:
660
                $result = "";
661
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
662
                    $result .= implode($row);
663
664
665
666
667
668
669
670
671
                }
                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
672

673
                $result = $this->mysqli_result->fetch_all(MYSQLI_NUM);
674
675
676
                break;

            default:
677
                $result = $this->mysqli_result->fetch_all(MYSQLI_ASSOC);
678
        }
679
680

        return $result;
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
    /**
     * 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
706
707
708
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
     */
    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
742
     *
743
744
     * @return array the definition of the column as retrieved by Database::getTableDefinition().
     *
Marc Egger's avatar
Marc Egger committed
745
746
747
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
748
749
     */
    private function getFieldDefinitionFromTable($table, $columnName) {
750

751
        $tableDefinition = $this->getTableDefinition($table);
752
        foreach ($tableDefinition as $row) {
753
754
755
756
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }
757

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

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

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

799
        return $this->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
800
    }
801

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

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

        $cols = array();

        if (!$this->existTable($table)) {
            $sql = "CREATE TABLE $table (";
853
            foreach ($columnDefinition as $key => $value) {
Carsten  Rose's avatar
Carsten Rose committed
854
855
856
857
858
859
860
861
862
863
864
865
                $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
866

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

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

884
        $feSpecNative = HelperFormElement::formElementSetDefault($feSpecNative, $formSpec);
885

886
        // Explode and Do $FormElement.parameter
887
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);
888
889
890
891
892

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

        // Copy Attributes to FormElements
893
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);
894
895
896
897

        return $feSpecNative;
    }

898
899
900
901
902
    /**
     * 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
903
     *
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
     * @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;
    }

    /**
932
933
934
935
936
937
     * $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.
938
     *
Carsten  Rose's avatar
Carsten Rose committed
939
     * @param array $arr
940
941
942
943
     * @param string $srcColumn1
     * @param string $srcColumn2
     * @param string $destColumn1
     * @param string $destColumn2
Carsten  Rose's avatar
Carsten Rose committed
944
     *
945
946
     * @return array
     */
947
948
    public function makeArrayDict(array $arr, $srcColumn1, $srcColumn2, $destColumn1 = '', $destColumn2 = '') {

949
950
951
952
953
        if ($arr == array() || $arr === null) {
            return array();
        }

        // Set defaults
954
955
956
957
958
959
960
961
        if ($destColumn1 == '') {
            $destColumn1 = $srcColumn1;
        }

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

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

976
        $new = array();