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

namespace qfq;

use qfq;

13
14
15
require_once(__DIR__ . '/../exceptions/UserFormException.php');
require_once(__DIR__ . '/../exceptions/CodeException.php');
require_once(__DIR__ . '/../exceptions/DbException.php');
16

17
18
19
20
21
require_once(__DIR__ . '/../store/Store.php');
require_once(__DIR__ . '/../store/Config.php');
require_once(__DIR__ . '/../helper/Support.php');
require_once(__DIR__ . '/../helper/Logger.php');
require_once(__DIR__ . '/../helper/BindParam.php');
22

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

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

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

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

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

49
50
51
    /**
     * @var string
     */
52
    private $sqlLog = '';
53

54
55
56
57
58
    /**
     * @var array
     */
    private $sqlLogModePrio = [SQL_LOG_MODE_NONE => 1, SQL_LOG_MODE_ERROR => 2, SQL_LOG_MODE_MODIFY => 3, SQL_LOG_MODE_ALL => 4];

59
60
    private $dbName = '';

61
62
63
64
    /**
     * 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]
     *
65
     * @param string $dbIndex Typically '1' for Data, optional 2 for external Form/FormElement
66
     *
67
     * @throws CodeException
68
     * @throws DbException
69
     * @throws UserFormException
70
     * @throws UserReportException
71
     */
72
73
74
75
    public function __construct($dbIndex = DB_INDEX_DEFAULT) {
        if (empty($dbIndex)) {
            $dbIndex = DB_INDEX_DEFAULT;
        }
76

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

80
81
        $this->sqlLog = $storeSystem[SYSTEM_SQL_LOG];
        $dbInit = $storeSystem[SYSTEM_DB_INIT];
82

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

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

        // DB Init
        if ($dbInit !== false && $dbInit != '') {
            $this->sql($dbInit);
        }
94
95
    }

96
97
98
    /**
     * @return mixed|string
     */
99
100
101
102
    public function getDbName() {
        return $this->dbName;
    }

103
104
105
106
107
108
    /**
     * 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,...
     *
109
     * @param array $config
110
111
112
     * @return array
     * @throws UserFormException
     */
113
    private function getConnectionDetails($dbIndex, array $config) {
114
115
116
117
118
119
120

        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) {
121
            // Backward compatibility: old configs use SYSTEM_DB_SERVER (without index) and index=1 might mean 'legacy config'.
122
123
124
125
126
            throw new UserFormException("DB Handle not found in config: $dbIndex", ERROR_INVALID_VALUE);
        }

        return $config;
    }
127

128
    /**
129
130
     * Open mysqli database connection if not already done.
     *
131
     * @param $config
132
     * @return \mysqli
133
     * @throws UserFormException
134
     */
135
    private function dbConnect($config) {
136
137
        $mysqli = null;

138
        $mysqli = new \mysqli($config[SYSTEM_DB_SERVER], $config[SYSTEM_DB_USER], $config[SYSTEM_DB_PASSWORD], $config[SYSTEM_DB_NAME]);
139
140

        if ($mysqli->connect_error) {
141
142
143
144
145
146
147
148
            throw new UserFormException (
                json_encode([ERROR_MESSAGE_TO_USER => 'Error open Database',
                    ERROR_MESSAGE_SUPPORT => "Error open Database 'mysql:host=" . $config[SYSTEM_DB_SERVER] .
                        ";dbname=" . $config[SYSTEM_DB_NAME] .
                        ";dbuser=" . $config[SYSTEM_DB_USER] .
                        "'': " . $mysqli->connect_errno . PHP_EOL . $mysqli->connect_error]),
                ERROR_DB_OPEN);

149
150
        }

151
152
        // Necessary that mysqli::real_escape_string() functions properly.
        if (!$mysqli->set_charset('utf8')) {
153
154
155
156
            throw new UserFormException (
                json_encode([ERROR_MESSAGE_TO_USER => "Error set_charset('utf8')",
                    ERROR_MESSAGE_SUPPORT => "Error set_charset('utf8') Database: " . $mysqli->connect_errno . PHP_EOL . $mysqli->connect_error]),
                ERROR_DB_SET_CHARSET);
157
158
        }

159
        return $mysqli;
160
161
162
    }

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

195
        // CR (& EV) often forgets to specify the $mode and use prepared statement with parameter instead.
196
        if (is_array($mode)) {
197
            throw new CodeException("Probably a parameter forgotten: $mode ?");
198
        }
199

200
        // for error reporting in exception
201
        if ($specificMessage) {
202
            $specificMessage .= " ";
203
        }
204

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

207
        if ($count === false) {
208
            throw new DbException($specificMessage . "No idea why this error happens - please take some time and check the problem.", ERROR_DB_GENERIC_CHECK);
209
210
        }

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

250
251
252
                default:
                    throw new DbException($specificMessage . "Unknown mode: $mode", ERROR_UNKNOWN_MODE);
            }
253
254
        } elseif ($queryType === QUERY_TYPE_INSERT) {
            $result = $stat[DB_INSERT_ID];
255
256
        } else {
            $result = $count;
257
258
        }

259
260
        $this->closeMysqliStmt();

261
262
263
264
        $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);

265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
        return $result;
    }

    /**
     * Close an optional open MySQLi Statement.
     *
     * @throws \qfq\DbException
     */
    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();
281
            if (!$this->mysqli_stmt->close()) {
282
                throw new DbException('Error closing mysqli_stmt' . ERROR_DB_CLOSE_MYSQLI_STMT);
283
            }
284
285
286
        }
        $this->mysqli_stmt = null;
        $this->mysqli_result = null;
287
288
    }

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

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

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

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

326
327
328
329
330
        // 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";
        }

331
        return $msg;
332
333
    }

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

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

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

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

370
        if (false === ($this->mysqli_stmt = $this->mysqli->prepare($sql))) {
371
            $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
372
            $errorMsg[ERROR_MESSAGE_SUPPORT] = $this->getSqlHint($sql, $this->mysqli->error);
373
374
375
            $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $this->mysqli->errno . ' ] ' . $this->mysqli->error;

            throw new DbException(json_encode($errorMsg), ERROR_DB_PREPARE);
376
377
378
        }

        if (count($parameterArray) > 0) {
379
            if (false === $this->prepareBindParam($parameterArray)) {
380
                $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
381
                $errorMsg[ERROR_MESSAGE_SUPPORT] = $this->getSqlHint($sql, $this->mysqli->error);
382
383
384
                $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error;

                throw new DbException(json_encode($errorMsg), ERROR_DB_BIND);
385
386
387
388
            }
        }

        if (false === $this->mysqli_stmt->execute()) {
389
            $this->dbLog(SQL_LOG_MODE_ERROR, $sql, $parameterArray);
390
391
            $errorMsg[ERROR_MESSAGE_SUPPORT] = $this->getSqlHint($sql, $this->mysqli->error);
            $errorMsg[ERROR_MESSAGE_OS] = '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error;
392

393
            throw new DbException(json_encode($errorMsg), ERROR_DB_EXECUTE);
394
395
396
397
398
399
400
401
402
403
404
        }

        $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())) {
405
406
407
408
                    throw new DbException(
                        json_encode([ERROR_MESSAGE_TO_USER => 'Error DB execute', ERROR_MESSAGE_SUPPORT => '[ mysqli: ' . $this->mysqli_stmt->errno . ' ] ' . $this->mysqli_stmt->error . $specificMessage]),
                        ERROR_DB_EXECUTE);

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

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

444
            default:
Carsten  Rose's avatar
Carsten Rose committed
445
                throw new DbException('Unknown command: "' . $command . '"' . $specificMessage, ERROR_DB_UNKNOWN_COMMAND);
446
                break;
447
448
        }

449
450
451
        if ($this->store !== null) {
            $this->store->setVar(SYSTEM_SQL_COUNT, $count, STORE_SYSTEM);
        }
452

453
        // Logfile
454
        $this->dbLog($sqlLogMode, $msg);
455

456
457
458
        return $count;
    }

459
460
461
462
    /**
     * Check if the given SQL Statement might modify data.
     *
     * @param $sql
Carsten  Rose's avatar
Carsten Rose committed
463
     *
464
465
466
     * @return bool  true is the statement might modify data, else: false
     */
    private function isSqlModify($sql) {
467

468
        $command = explode(' ', $sql, 2);
469

470
471
472
473
474
475
476
477
        switch (strtoupper($command[0])) {
            case 'INSERT':
            case 'UPDATE':
            case 'DELETE':
            case 'REPLACE':
            case 'TRUNCATE':
                return true;
        }
478

479
480
481
        return false;
    }

482
    /**
483
     * Decide if the SQL statement has to be logged. If yes, create a timestamp and do the log.
484
     *
485
486
     * @param string $currentQueryMode
     * @param string $sql
487
     * @param array $parameterArray
Carsten  Rose's avatar
Carsten Rose committed
488
     *
489
490
     * @throws CodeException
     * @throws UserFormException
491
     */
492
    private function dbLog($currentQueryMode = SQL_LOG_MODE_ALL, $sql = '', $parameterArray = array()) {
493

494
495
496
497
        if ($sql == '') {
            return;
        }

498
499
        $status = '';

500
501
502
503
504
        // 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;
        }
505

506
507
508
        // Check if string is known.
        foreach ([$sqlLogMode, $currentQueryMode] as $mode) {
            if (!isset($this->sqlLogModePrio[$mode])) {
509
                throw new UserFormException("Unknown SQL_LOG_MODE: $mode", ERROR_UNKNOWN_SQL_LOG_MODE);
510
511
512
513
514
515
            }
        }

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

518
        // Client IP Address
519
520
521
522
523
        $remoteAddress = ($this->store === null) ? '0.0.0.0' : $this->store->getVar(CLIENT_REMOTE_ADDRESS, STORE_CLIENT);


        $logArr = [
            ['FE', TYPO3_FE_USER, STORE_TYPO3],
524
            ['FESU', TYPO3_FE_USER, STORE_USER],
525
526
527
528
529
530
531
532
533
534
535
536
537
538
            ['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);
539

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

542
543
        if (count($parameterArray) > 0) {
            $sql = $this->preparedStatementInsertParameter($sql, $parameterArray);
544
545
        }

546
547
        if ($currentQueryMode == SQL_LOG_MODE_ERROR) {
            $status = 'FAILED: ';
548
        }
549
        $msg .= '[' . $status . $sql . ']';
550

551
        Logger::logMessage($msg, $this->sqlLog);
552
553
    }

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

        return $msg;
581
582
    }

583
    /**
584
     * @param $arr
585
     */
586
    private function prepareBindParam($arr) {
587

588
589
590
591
        $bindParam = new BindParam();

        for ($ii = 0; $ii < count($arr); $ii++) {
            $bindParam->add($arr[$ii]);
592
        }
593
        call_user_func_array([$this->mysqli_stmt, 'bind_param'], $bindParam->get());
594
595
    }

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

618
        if ($this->mysqli_result->num_rows === 0) {
619
620
621
            return ($mode === ROW_IMPLODE_ALL) ? "" : array();
        }

622
623
624
        switch ($mode) {
            case ROW_IMPLODE_ALL:
                $str = "";
625
                foreach ($this->mysqli_result->fetch_all(MYSQLI_NUM) as $row) {
626
627
                    $str .= implode($row);
                }
Carsten  Rose's avatar
Carsten Rose committed
628

629
630
631
632
633
634
635
636
637
                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
638

639
640
641
642
643
                return $this->mysqli_result->fetch_all(MYSQLI_NUM);
                break;

            default:
                return $this->mysqli_result->fetch_all(MYSQLI_ASSOC);
644
645
646
        }
    }

647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
    /**
     * 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
670
671
672
     * @throws CodeException
     * @throws DbException
     * @throws UserFormException
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
     */
    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
706
     *
707
708
     * @return array the definition of the column as retrieved by Database::getTableDefinition().
     *
Carsten  Rose's avatar
Carsten Rose committed
709
710
711
     * @throws CodeException
     * @throws DbException
     * @throws UserFormException
712
713
     */
    private function getFieldDefinitionFromTable($table, $columnName) {
714

715
716
717
718
719
720
        $tableDefinition = $this->getTableDefinition($table);
        foreach ($tableDefinition AS $row) {
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }
721
722
723
724

        throw new DbException(
            json_encode([ERROR_MESSAGE_TO_USER => 'Column name not found', ERROR_MESSAGE_SUPPORT => "Column name '$columnName' not found in table '$table'."]),
            ERROR_DB_COLUMN_NOT_FOUND_IN_TABLE);
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
    }

    /**
     * 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.
741
742
     * @throws CodeException
     * @throws DbException
Carsten  Rose's avatar
Carsten Rose committed
743
     * @throws UserFormException
744
745
746
747
748
     */
    public function getTableDefinition($table) {
        return $this->sql("SHOW FIELDS FROM `$table`");
    }

749
    /**
750
751
     * Wrapper for sql(), to simplyfy access.
     *
Carsten  Rose's avatar
Carsten Rose committed
752
     * @param              $sql
Carsten  Rose's avatar
Carsten Rose committed
753
754
     * @param array $keys
     * @param array $stat
Carsten  Rose's avatar
Carsten Rose committed
755
     *
756
     * @return array|bool
Carsten  Rose's avatar
Carsten Rose committed
757
758
759
     * @throws CodeException
     * @throws DbException
     * @throws UserFormException
760
     */
761
    public function sqlKeys($sql, array &$keys, array &$stat = array()) {
762

763
        return $this->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
764
    }
765

766
767
768
769
770
771
    /**
     * Returns lastInsertId
     *
     * @return string
     */
    public function getLastInsertId() {
772
        // Do not try to use $this->mysqli->lastInsertId - this is not valid at any given time.
773
        return $this->mysqli->insert_id;
774
    }
Carsten  Rose's avatar
Carsten Rose committed
775

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

831
832
833
    /**
     * Depending on $sql reads FormElements to a specific container or all. Preprocess all FormElements.
     *
Carsten  Rose's avatar
Carsten Rose committed
834
835
836
     * @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
837
     *
838
     * @return array|int
839
840
841
     * @throws CodeException
     * @throws DbException
     * @throws UserFormException
Carsten  Rose's avatar
Carsten Rose committed
842
     * @throws UserReportException
843
844
845
846
847
     */
    public function getNativeFormElements($sql, array $param, $formSpec) {

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

848
849
        $feSpecNative = HelperFormElement::formElementSetDefault($feSpecNative);

850
        // Explode and Do $FormElement.parameter
851
        HelperFormElement::explodeParameterInArrayElements($feSpecNative, FE_PARAMETER);
852
853
854
855
856

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

        // Copy Attributes to FormElements
857
        $feSpecNative = HelperFormElement::copyAttributesToFormElements($formSpec, $feSpecNative);
858
859
860
861

        return $feSpecNative;
    }

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

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

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

919
920
921
922
923
924
925
926
927
        $new = array();

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

        $row = $arr[0];
        $keys = array_keys($row);
        if (count($row) < 2) {
928
929
930
931
932
            $column1 = $keys[0];
            $column2 = $keys[0];
        } elseif (array_key_exists($srcColumn1, $row) && array_key_exists($srcColumn2, $row)) {
            $column1 = $srcColumn1;
            $column2 = $srcColumn2;
933
        } else {
934
935
            $column1 = $keys[0];
            $column2 = $keys[1];
936
937
938
939
        }

        $row = array_shift($arr);
        while (null !== $row) {
940
            $new[] = [$destColumn1 => $row[$column1], $destColumn2 => $row[$column2]];
941
942
943
944
945
            $row = array_shift($arr);
        }

        return $new;
    }
946
947
948
949
950

    /**
     * Proxy for mysqli::real_escape_string()
     *
     * @param string $value
Carsten  Rose's avatar
Carsten Rose committed
951
     *
952
953
954
955
956
     * @return string
     */
    public function realEscapeString($value) {
        return $this->mysqli->real_escape_string($value);
    }
Carsten  Rose's avatar
Carsten Rose committed
957
958
959

    /**
     * @param $filename
Carsten  Rose's avatar
Carsten Rose committed
960
     *
Carsten  Rose's avatar
Carsten Rose committed
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
     * @throws \qfq\CodeException
     */
    public function playSqlFile($filename) {

        $sqlStatements = file_get_contents($filename);

        if (false === $this->mysqli->multi_query($sqlStatements)) {
            throw new CodeException("Error playing $filename", ERROR_PLAY_SQL_FILE);
        }

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

    }
979
980

    /**
981
982
983
     * @return string
     * @throws CodeException
     * @throws UserFormException
984
985
986
987
     */
    public function getQfqLogFile() {
        return ($this->store == null) ? SYSTEM_QFQ_LOG_FILE : $this->store->getVar(SYSTEM_QFQ_LOG, STORE_SYSTEM);
    }
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011

    /**
     * Selects in table 'Split' all records with tableName='$tableName' and xId='$xId'. Deletes all referenced files and records.
     *
     * @param $xId
     * @param $tableName
     * @throws CodeException
     * @throws DbException
     * @throws UserFormException
     */
    public function deleteSplitFileAndRecord($xId, $tableName) {

        $sql = 'SELECT pathFileName FROM ' . TABLE_NAME_SPLIT . ' WHERE tableName=? AND xId=?';
        $data = $this->sql($sql, ROW_REGULAR, [$tableName, $xId]);

        foreach ($data AS $row) {
            if (!empty($row['pathFileName']) && is_writable($row['pathFileName'])) {
                HelperFile::unlink($row['pathFileName']);
            }
        }

        $this->sql('DELETE FROM ' . TABLE_NAME_SPLIT . ' WHERE tableName=? AND xId=?', ROW_REGULAR, [$tableName, $xId]);
    }

1012
}