ci4

1
<?php
2

3
declare(strict_types=1);
4

5
/**
6
 * This file is part of CodeIgniter 4 framework.
7
 *
8
 * (c) CodeIgniter Foundation <admin@codeigniter.com>
9
 *
10
 * For the full copyright and license information, please view
11
 * the LICENSE file that was distributed with this source code.
12
 */
13

14
namespace CodeIgniter\Database;
15

16
use Stringable;
17

18
/**
19
 * Query builder
20
 */
21
class Query implements QueryInterface, Stringable
22
{
23
    /**
24
     * The query string, as provided by the user.
25
     *
26
     * @var string
27
     */
28
    protected $originalQueryString;
29

30
    /**
31
     * The query string if table prefix has been swapped.
32
     *
33
     * @var string|null
34
     */
35
    protected $swappedQueryString;
36

37
    /**
38
     * The final query string after binding, etc.
39
     *
40
     * @var string|null
41
     */
42
    protected $finalQueryString;
43

44
    /**
45
     * The binds and their values used for binding.
46
     *
47
     * @var array
48
     */
49
    protected $binds = [];
50

51
    /**
52
     * Bind marker
53
     *
54
     * Character used to identify values in a prepared statement.
55
     *
56
     * @var string
57
     */
58
    protected $bindMarker = '?';
59

60
    /**
61
     * The start time in seconds with microseconds
62
     * for when this query was executed.
63
     *
64
     * @var float|string
65
     */
66
    protected $startTime;
67

68
    /**
69
     * The end time in seconds with microseconds
70
     * for when this query was executed.
71
     *
72
     * @var float
73
     */
74
    protected $endTime;
75

76
    /**
77
     * The error code, if any.
78
     *
79
     * @var int
80
     */
81
    protected $errorCode;
82

83
    /**
84
     * The error message, if any.
85
     *
86
     * @var string
87
     */
88
    protected $errorString;
89

90
    /**
91
     * Pointer to database connection.
92
     * Mainly for escaping features.
93
     *
94
     * @var ConnectionInterface
95
     */
96
    public $db;
97

98
    public function __construct(ConnectionInterface $db)
99
    {
100
        $this->db = $db;
101
    }
102

103
    /**
104
     * Sets the raw query string to use for this statement.
105
     *
106
     * @param mixed $binds
107
     *
108
     * @return $this
109
     */
110
    public function setQuery(string $sql, $binds = null, bool $setEscape = true)
111
    {
112
        $this->originalQueryString = $sql;
113
        unset($this->swappedQueryString);
114

115
        if ($binds !== null) {
116
            if (! is_array($binds)) {
117
                $binds = [$binds];
118
            }
119

120
            if ($setEscape) {
121
                array_walk($binds, static function (&$item): void {
122
                    $item = [
123
                        $item,
124
                        true,
125
                    ];
126
                });
127
            }
128
            $this->binds = $binds;
129
        }
130

131
        unset($this->finalQueryString);
132

133
        return $this;
134
    }
135

136
    /**
137
     * Will store the variables to bind into the query later.
138
     *
139
     * @return $this
140
     */
141
    public function setBinds(array $binds, bool $setEscape = true)
142
    {
143
        if ($setEscape) {
144
            array_walk($binds, static function (&$item): void {
145
                $item = [$item, true];
146
            });
147
        }
148

149
        $this->binds = $binds;
150

151
        unset($this->finalQueryString);
152

153
        return $this;
154
    }
155

156
    /**
157
     * Returns the final, processed query string after binding, etal
158
     * has been performed.
159
     */
160
    public function getQuery(): string
161
    {
162
        if (empty($this->finalQueryString)) {
163
            $this->compileBinds();
164
        }
165

166
        return $this->finalQueryString;
167
    }
168

169
    /**
170
     * Records the execution time of the statement using microtime(true)
171
     * for it's start and end values. If no end value is present, will
172
     * use the current time to determine total duration.
173
     *
174
     * @return $this
175
     */
176
    public function setDuration(float $start, ?float $end = null)
177
    {
178
        $this->startTime = $start;
179

180
        if ($end === null) {
181
            $end = microtime(true);
182
        }
183

184
        $this->endTime = $end;
185

186
        return $this;
187
    }
188

189
    /**
190
     * Returns the start time in seconds with microseconds.
191
     *
192
     * @return float|string
193
     */
194
    public function getStartTime(bool $returnRaw = false, int $decimals = 6)
195
    {
196
        if ($returnRaw) {
197
            return $this->startTime;
198
        }
199

200
        return number_format($this->startTime, $decimals);
201
    }
202

203
    /**
204
     * Returns the duration of this query during execution, or null if
205
     * the query has not been executed yet.
206
     *
207
     * @param int $decimals The accuracy of the returned time.
208
     */
209
    public function getDuration(int $decimals = 6): string
210
    {
211
        return number_format(($this->endTime - $this->startTime), $decimals);
212
    }
213

214
    /**
215
     * Stores the error description that happened for this query.
216
     *
217
     * @return $this
218
     */
219
    public function setError(int $code, string $error)
220
    {
221
        $this->errorCode   = $code;
222
        $this->errorString = $error;
223

224
        return $this;
225
    }
226

227
    /**
228
     * Reports whether this statement created an error not.
229
     */
230
    public function hasError(): bool
231
    {
232
        return ! empty($this->errorString);
233
    }
234

235
    /**
236
     * Returns the error code created while executing this statement.
237
     */
238
    public function getErrorCode(): int
239
    {
240
        return $this->errorCode;
241
    }
242

243
    /**
244
     * Returns the error message created while executing this statement.
245
     */
246
    public function getErrorMessage(): string
247
    {
248
        return $this->errorString;
249
    }
250

251
    /**
252
     * Determines if the statement is a write-type query or not.
253
     */
254
    public function isWriteType(): bool
255
    {
256
        return $this->db->isWriteType($this->originalQueryString);
257
    }
258

259
    /**
260
     * Swaps out one table prefix for a new one.
261
     *
262
     * @return $this
263
     */
264
    public function swapPrefix(string $orig, string $swap)
265
    {
266
        $sql = $this->swappedQueryString ?? $this->originalQueryString;
267

268
        $from = '/(\W)' . $orig . '(\S)/';
269
        $to   = '\\1' . $swap . '\\2';
270

271
        $this->swappedQueryString = preg_replace($from, $to, $sql);
272

273
        unset($this->finalQueryString);
274

275
        return $this;
276
    }
277

278
    /**
279
     * Returns the original SQL that was passed into the system.
280
     */
281
    public function getOriginalQuery(): string
282
    {
283
        return $this->originalQueryString;
284
    }
285

286
    /**
287
     * Escapes and inserts any binds into the finalQueryString property.
288
     *
289
     * @see https://regex101.com/r/EUEhay/5
290
     */
291
    protected function compileBinds()
292
    {
293
        $sql   = $this->swappedQueryString ?? $this->originalQueryString;
294
        $binds = $this->binds;
295

296
        if (empty($binds)) {
297
            $this->finalQueryString = $sql;
298

299
            return;
300
        }
301

302
        if (is_int(array_key_first($binds))) {
303
            $bindCount = count($binds);
304
            $ml        = strlen($this->bindMarker);
305

306
            $this->finalQueryString = $this->matchSimpleBinds($sql, $binds, $bindCount, $ml);
307
        } else {
308
            // Reverse the binds so that duplicate named binds
309
            // will be processed prior to the original binds.
310
            $binds = array_reverse($binds);
311

312
            $this->finalQueryString = $this->matchNamedBinds($sql, $binds);
313
        }
314
    }
315

316
    /**
317
     * Match bindings
318
     */
319
    protected function matchNamedBinds(string $sql, array $binds): string
320
    {
321
        $replacers = [];
322

323
        foreach ($binds as $placeholder => $value) {
324
            // $value[1] contains the boolean whether should be escaped or not
325
            $escapedValue = $value[1] ? $this->db->escape($value[0]) : $value[0];
326

327
            // In order to correctly handle backlashes in saved strings
328
            // we will need to preg_quote, so remove the wrapping escape characters
329
            // otherwise it will get escaped.
330
            if (is_array($value[0])) {
331
                $escapedValue = '(' . implode(',', $escapedValue) . ')';
332
            }
333

334
            $replacers[":{$placeholder}:"] = $escapedValue;
335
        }
336

337
        return strtr($sql, $replacers);
338
    }
339

340
    /**
341
     * Match bindings
342
     */
343
    protected function matchSimpleBinds(string $sql, array $binds, int $bindCount, int $ml): string
344
    {
345
        if ($c = preg_match_all("/'[^']*'/", $sql, $matches)) {
346
            $c = preg_match_all('/' . preg_quote($this->bindMarker, '/') . '/i', str_replace($matches[0], str_replace($this->bindMarker, str_repeat(' ', $ml), $matches[0]), $sql, $c), $matches, PREG_OFFSET_CAPTURE);
347

348
            // Bind values' count must match the count of markers in the query
349
            if ($bindCount !== $c) {
350
                return $sql;
351
            }
352
        } elseif (($c = preg_match_all('/' . preg_quote($this->bindMarker, '/') . '/i', $sql, $matches, PREG_OFFSET_CAPTURE)) !== $bindCount) {
353
            return $sql;
354
        }
355

356
        do {
357
            $c--;
358
            $escapedValue = $binds[$c][1] ? $this->db->escape($binds[$c][0]) : $binds[$c][0];
359

360
            if (is_array($escapedValue)) {
361
                $escapedValue = '(' . implode(',', $escapedValue) . ')';
362
            }
363

364
            $sql = substr_replace($sql, (string) $escapedValue, $matches[0][$c][1], $ml);
365
        } while ($c !== 0);
366

367
        return $sql;
368
    }
369

370
    /**
371
     * Returns string to display in debug toolbar
372
     */
373
    public function debugToolbarDisplay(): string
374
    {
375
        // Key words we want bolded
376
        static $highlight = [
377
            'AND',
378
            'AS',
379
            'ASC',
380
            'AVG',
381
            'BY',
382
            'COUNT',
383
            'DESC',
384
            'DISTINCT',
385
            'FROM',
386
            'GROUP',
387
            'HAVING',
388
            'IN',
389
            'INNER',
390
            'INSERT',
391
            'INTO',
392
            'IS',
393
            'JOIN',
394
            'LEFT',
395
            'LIKE',
396
            'LIMIT',
397
            'MAX',
398
            'MIN',
399
            'NOT',
400
            'NULL',
401
            'OFFSET',
402
            'ON',
403
            'OR',
404
            'ORDER',
405
            'RIGHT',
406
            'SELECT',
407
            'SUM',
408
            'UPDATE',
409
            'VALUES',
410
            'WHERE',
411
        ];
412

413
        $sql = esc($this->getQuery());
414

415
        /**
416
         * @see https://stackoverflow.com/a/20767160
417
         * @see https://regex101.com/r/hUlrGN/4
418
         */
419
        $search = '/\b(?:' . implode('|', $highlight) . ')\b(?![^(&#039;)]*&#039;(?:(?:[^(&#039;)]*&#039;){2})*[^(&#039;)]*$)/';
420

421
        return preg_replace_callback($search, static fn ($matches) => '<strong>' . str_replace(' ', '&nbsp;', $matches[0]) . '</strong>', $sql);
422
    }
423

424
    /**
425
     * Return text representation of the query
426
     */
427
    public function __toString(): string
428
    {
429
        return $this->getQuery();
430
    }
431
}
432