This project does not seem to handle request data directly as such no vulnerable execution paths were found.
include
, or for example
via PHP's auto-loading mechanism.
These results are based on our legacy PHP analysis, consider migrating to our new PHP analysis engine instead. Learn more
1 | <?php |
||
2 | |||
3 | namespace Mos\Database; |
||
4 | |||
5 | /** |
||
6 | * Database wrapper, provides a database API for the framework but hides details of implementation. |
||
7 | * |
||
8 | */ |
||
9 | trait TSQLQueryBuilderBasic |
||
10 | { |
||
11 | /** |
||
12 | * Properties |
||
13 | */ |
||
14 | private $dialect; // SQL dialect for a certain database |
||
15 | private $sql; // The query built |
||
16 | private $prefix; // Prefix to attach to all table names |
||
17 | |||
18 | private $columns; // Columns to select |
||
19 | private $from; // From part |
||
20 | private $join; // Join part |
||
21 | private $where; // Where part |
||
22 | private $groupby; // Group by part |
||
23 | private $orderby; // Order by part |
||
24 | private $limit; // Limit by part |
||
25 | private $offset; // Offset by part |
||
26 | |||
27 | |||
28 | |||
29 | |||
30 | /** |
||
31 | * Get SQL. |
||
32 | * |
||
33 | * @return string as sql-query |
||
34 | */ |
||
35 | 25 | public function getSQL() |
|
36 | { |
||
37 | 25 | if ($this->sql) { |
|
38 | 14 | return $this->sql; |
|
39 | } |
||
40 | 16 | return $this->build(); |
|
41 | } |
||
42 | |||
43 | |||
44 | |||
45 | /** |
||
46 | * Build SQL. |
||
47 | * |
||
48 | * @return string as SQL query |
||
49 | */ |
||
50 | 16 | protected function build() |
|
51 | { |
||
52 | $sql = "SELECT\n\t" |
||
53 | 16 | . $this->columns . "\n" |
|
54 | 16 | . $this->from . "\n" |
|
55 | 16 | . ($this->join ? $this->join : null) |
|
56 | 16 | . ($this->where ? $this->where . "\n" : null) |
|
57 | 16 | . ($this->groupby ? $this->groupby . "\n" : null) |
|
58 | 16 | . ($this->orderby ? $this->orderby . "\n" : null) |
|
59 | 16 | . ($this->limit ? $this->limit . "\n" : null) |
|
60 | 16 | . ($this->offset ? $this->offset . "\n" : null) |
|
61 | 16 | . ";"; |
|
62 | |||
63 | 16 | return $sql; |
|
64 | } |
||
65 | |||
66 | |||
67 | |||
68 | /** |
||
69 | * Create a inner or outer join. |
||
70 | * |
||
71 | * @param string $table name of table. |
||
72 | * @param string $condition to join. |
||
73 | * @param string $type what type of join to create. |
||
74 | * |
||
75 | * @return void |
||
76 | */ |
||
77 | 3 | private function createJoin($table, $condition, $type) |
|
78 | { |
||
79 | 3 | $this->join .= $type . " JOIN " . $this->prefix . $table |
|
80 | 3 | . "\n\tON " . $condition . "\n"; |
|
81 | |||
82 | 3 | return $this; |
|
83 | } |
||
84 | |||
85 | |||
86 | |||
87 | /** |
||
88 | * Set database type to consider when generating SQL. |
||
89 | * |
||
90 | * @param string $dialect representing database type. |
||
91 | * |
||
92 | * @return void |
||
93 | */ |
||
94 | 9 | public function setSQLDialect($dialect) |
|
95 | { |
||
96 | 9 | $this->dialect = $dialect; |
|
97 | 9 | } |
|
98 | |||
99 | |||
100 | |||
101 | /** |
||
102 | * Set a table prefix. |
||
103 | * |
||
104 | * @param string $prefix to use in front of all tables. |
||
105 | * |
||
106 | * @return void |
||
107 | */ |
||
108 | 18 | public function setTablePrefix($prefix) |
|
109 | { |
||
110 | 18 | $this->prefix = $prefix; |
|
111 | 18 | } |
|
112 | |||
113 | |||
114 | |||
115 | /** |
||
116 | * Utility to check if array is associative array. |
||
117 | * |
||
118 | * http://stackoverflow.com/questions/173400/php-arrays-a-good-way-to-check-if-an-array-is-associative-or-sequential/4254008#4254008 |
||
119 | * |
||
120 | * @param array $array input array to check. |
||
121 | * |
||
122 | * @return boolean true if array is associative array with at least one key, else false. |
||
123 | * |
||
124 | */ |
||
125 | 4 | private function isAssoc($array) |
|
126 | { |
||
127 | 4 | return (bool) count(array_filter(array_keys($array), 'is_string')); |
|
128 | } |
||
129 | |||
130 | |||
131 | |||
132 | /** |
||
133 | * Create a table. |
||
134 | * |
||
135 | * @param string $name the table name. |
||
136 | * @param array $columns the columns in the table. |
||
137 | * |
||
138 | * @return $this |
||
139 | */ |
||
140 | 2 | public function createTable($name, $columns) |
|
141 | { |
||
142 | 2 | $cols = null; |
|
143 | |||
144 | 2 | foreach ($columns as $col => $options) { |
|
145 | 2 | $cols .= "\t" . $col . ' ' . implode(' ', $options) . ",\n"; |
|
146 | 2 | } |
|
147 | 2 | $cols = substr($cols, 0, -2); |
|
148 | |||
149 | 2 | $this->sql = "CREATE TABLE " |
|
150 | 2 | . $this->prefix |
|
151 | 2 | . $name |
|
152 | 2 | . "\n(\n" |
|
153 | 2 | . $cols |
|
154 | 2 | . "\n);\n"; |
|
155 | |||
156 | 2 | if ($this->dialect == 'sqlite') { |
|
157 | 1 | $this->sql = str_replace('auto_increment', '', $this->sql); |
|
158 | 1 | } |
|
159 | |||
160 | 2 | return $this; |
|
161 | } |
||
162 | |||
163 | |||
164 | |||
165 | /** |
||
166 | * Drop a table. |
||
167 | * |
||
168 | * @param string $name the table name. |
||
169 | * |
||
170 | * @return $this |
||
171 | */ |
||
172 | 2 | public function dropTable($name) |
|
173 | { |
||
174 | 2 | $this->sql = "DROP TABLE " |
|
175 | 2 | . $this->prefix |
|
176 | 2 | . $name |
|
177 | 2 | . ";\n"; |
|
178 | |||
179 | 2 | return $this; |
|
180 | } |
||
181 | |||
182 | |||
183 | |||
184 | /** |
||
185 | * Drop a table if it exists. |
||
186 | * |
||
187 | * @param string $name the table name. |
||
188 | * |
||
189 | * @return $this |
||
190 | */ |
||
191 | 1 | public function dropTableIfExists($name) |
|
192 | { |
||
193 | 1 | $this->sql = "DROP TABLE IF EXISTS " |
|
194 | 1 | . $this->prefix |
|
195 | 1 | . $name |
|
196 | 1 | . ";\n"; |
|
197 | |||
198 | 1 | return $this; |
|
199 | } |
||
200 | |||
201 | |||
202 | |||
203 | /** |
||
204 | * Create a proper column value arrays from incoming $columns and $values. |
||
205 | * |
||
206 | * @param array $columns |
||
207 | * @param array|null $values |
||
208 | * |
||
209 | * @return array that can be parsed with list($columns, $values) |
||
210 | */ |
||
211 | 9 | public function mapColumnsWithValues($columns, $values) |
|
212 | { |
||
213 | // If $values is null, then use $columns to build it up |
||
214 | 9 | if (is_null($values)) { |
|
0 ignored issues
–
show
Coding Style
introduced
by
Loading history...
|
|||
215 | |||
216 | 4 | if ($this->isAssoc($columns)) { |
|
0 ignored issues
–
show
|
|||
217 | |||
218 | // Incoming is associative array, split it up in two |
||
219 | 3 | $values = array_values($columns); |
|
220 | 3 | $columns = array_keys($columns); |
|
221 | |||
0 ignored issues
–
show
|
|||
222 | 3 | } else { |
|
0 ignored issues
–
show
|
|||
223 | |||
224 | // Create an array of '?' to match number of columns |
||
225 | 1 | $max = count($columns); |
|
226 | 1 | for ($i = 0; $i < $max; $i++) { |
|
227 | 1 | $values[] = '?'; |
|
228 | 1 | } |
|
229 | } |
||
230 | 4 | } |
|
231 | |||
232 | 9 | return [$columns, $values]; |
|
233 | } |
||
234 | |||
235 | |||
236 | |||
237 | /** |
||
238 | * Build a insert-query. |
||
239 | * |
||
240 | * @param string $table the table name. |
||
241 | * @param array $columns to insert och key=>value with columns and values. |
||
242 | * @param array $values to insert or empty if $columns has both columns and values. |
||
243 | * |
||
244 | * @return void |
||
245 | */ |
||
246 | 6 | public function insert($table, $columns, $values = null) |
|
247 | { |
||
248 | 6 | list($columns, $values) = $this->mapColumnsWithValues($columns, $values); |
|
249 | |||
250 | 6 | if (count($columns) !== count($values)) { |
|
251 | 1 | throw new \Exception("Columns does not match values, not equal items."); |
|
252 | } |
||
253 | |||
254 | 5 | $cols = null; |
|
255 | 5 | $vals = null; |
|
256 | |||
257 | 5 | $max = count($columns); |
|
258 | 5 | for ($i = 0; $i < $max; $i++) { |
|
259 | 5 | $cols .= $columns[$i] . ', '; |
|
260 | |||
261 | 5 | $val = $values[$i]; |
|
262 | |||
263 | 5 | if ($val == '?') { |
|
264 | 1 | $vals .= $val . ', '; |
|
265 | 1 | } else { |
|
266 | 4 | $vals .= (is_string($val) |
|
267 | 4 | ? "'$val'" |
|
268 | 4 | : $val) |
|
269 | 4 | . ', '; |
|
270 | } |
||
271 | 5 | } |
|
272 | |||
273 | 5 | $cols = substr($cols, 0, -2); |
|
274 | 5 | $vals = substr($vals, 0, -2); |
|
275 | |||
276 | 5 | $this->sql = "INSERT INTO " |
|
277 | 5 | . $this->prefix |
|
278 | 5 | . $table |
|
279 | 5 | . "\n\t(" |
|
280 | 5 | . $cols |
|
281 | 5 | . ")\n" |
|
282 | 5 | . "\tVALUES\n\t(" |
|
283 | 5 | . $vals |
|
284 | 5 | . ");\n"; |
|
285 | 5 | } |
|
286 | |||
287 | |||
288 | |||
289 | /** |
||
290 | * Build an update-query. |
||
291 | * |
||
292 | * @param string $table the table name. |
||
293 | * @param array $columns to update or key=>value with columns and values. |
||
294 | * @param array $values to update or empty if $columns has bot columns and values. |
||
295 | * @param array $where limit which rows are updated. |
||
296 | * |
||
297 | * @return void |
||
298 | */ |
||
299 | 3 | public function update($table, $columns, $values = null, $where = null) |
|
300 | { |
||
301 | // If $values is string, then move that to $where |
||
302 | 3 | if (is_string($values)) { |
|
303 | 1 | $where = $values; |
|
304 | 1 | $values = null; |
|
305 | 1 | } |
|
306 | |||
307 | 3 | list($columns, $values) = $this->mapColumnsWithValues($columns, $values); |
|
308 | |||
309 | 3 | if (count($columns) != count($values)) { |
|
310 | 1 | throw new \Exception("Columns does not match values, not equal items."); |
|
311 | } |
||
312 | |||
313 | 2 | $cols = null; |
|
314 | 2 | $max = count($columns); |
|
315 | |||
316 | 2 | for ($i = 0; $i < $max; $i++) { |
|
317 | 2 | $cols .= "\t" . $columns[$i] . ' = '; |
|
318 | |||
319 | 2 | $val = $values[$i]; |
|
320 | 2 | if ($val == '?') { |
|
321 | 1 | $cols .= $val . ",\n"; |
|
322 | 1 | } else { |
|
323 | 1 | $cols .= (is_string($val) |
|
324 | 1 | ? "'$val'" |
|
325 | 1 | : $val) |
|
326 | 1 | . ",\n"; |
|
327 | } |
||
328 | 2 | } |
|
329 | |||
330 | 2 | $cols = substr($cols, 0, -2); |
|
331 | |||
332 | 2 | $this->sql = "UPDATE " |
|
333 | 2 | . $this->prefix |
|
334 | 2 | . $table |
|
335 | 2 | . "\nSET\n" |
|
336 | 2 | . $cols |
|
337 | 2 | . "\nWHERE " |
|
338 | 2 | . $where |
|
339 | 2 | . "\n;\n"; |
|
340 | 2 | } |
|
341 | |||
342 | |||
343 | |||
344 | /** |
||
345 | * Build a delete-query. |
||
346 | * |
||
347 | * @param string $table the table name. |
||
348 | * @param array $where limit which rows are updated. |
||
349 | * |
||
350 | * @return void |
||
351 | */ |
||
352 | 2 | public function delete($table, $where = null) |
|
353 | { |
||
354 | 2 | if (isset($where)) { |
|
355 | 1 | $where = " WHERE " . $where; |
|
356 | 1 | } |
|
357 | |||
358 | 2 | $this->sql = "DELETE FROM " |
|
359 | 2 | . $this->prefix |
|
360 | 2 | . $table |
|
361 | 2 | . $where |
|
362 | 2 | . ";\n"; |
|
363 | 2 | } |
|
364 | |||
365 | |||
366 | |||
367 | /** |
||
368 | * Clear all previous sql-code. |
||
369 | * |
||
370 | * @return void |
||
371 | */ |
||
372 | 16 | protected function clear() |
|
373 | { |
||
374 | 16 | $this->sql = null; |
|
375 | 16 | $this->columns = null; |
|
376 | 16 | $this->from = null; |
|
377 | 16 | $this->join = null; |
|
378 | 16 | $this->where = null; |
|
379 | 16 | $this->groupby = null; |
|
380 | 16 | $this->orderby = null; |
|
381 | 16 | $this->limit = null; |
|
382 | 16 | $this->offset = null; |
|
383 | 16 | } |
|
384 | |||
385 | |||
386 | |||
387 | /** |
||
388 | * Build a select-query. |
||
389 | * |
||
390 | * @param string $columns which columns to select. |
||
391 | * |
||
392 | * @return $this |
||
393 | */ |
||
394 | 16 | public function select($columns = '*') |
|
395 | { |
||
396 | 16 | $this->clear(); |
|
397 | 16 | $this->columns = $columns; |
|
398 | |||
399 | 16 | return $this; |
|
400 | } |
||
401 | |||
402 | |||
403 | |||
404 | /** |
||
405 | * Build the from part. |
||
406 | * |
||
407 | * @param string $table name of table. |
||
408 | * |
||
409 | * @return $this |
||
410 | */ |
||
411 | 16 | public function from($table) |
|
412 | { |
||
413 | 16 | $this->from = "FROM " . $this->prefix . $table; |
|
414 | |||
415 | 16 | return $this; |
|
416 | } |
||
417 | |||
418 | |||
419 | |||
420 | /** |
||
421 | * Build the inner join part. |
||
422 | * |
||
423 | * @param string $table name of table. |
||
424 | * @param string $condition to join. |
||
425 | * |
||
426 | * @return $this |
||
427 | */ |
||
428 | 1 | public function join($table, $condition) |
|
429 | { |
||
430 | |||
431 | 1 | return $this->createJoin($table, $condition, 'INNER'); |
|
432 | } |
||
433 | |||
434 | |||
435 | |||
436 | /** |
||
437 | * Build the right join part. |
||
438 | * |
||
439 | * @param string $table name of table. |
||
440 | * @param string $condition to join. |
||
441 | * |
||
442 | * @return $this |
||
443 | */ |
||
444 | 1 | public function rightJoin($table, $condition) |
|
445 | { |
||
446 | 1 | return $this->createJoin($table, $condition, 'RIGHT OUTER'); |
|
447 | } |
||
448 | |||
449 | |||
450 | |||
451 | /** |
||
452 | * Build the left join part. |
||
453 | * |
||
454 | * @param string $table name of table. |
||
455 | * @param string $condition to join. |
||
456 | * |
||
457 | * @return $this |
||
458 | */ |
||
459 | 1 | public function leftJoin($table, $condition) |
|
460 | { |
||
461 | 1 | return $this->createJoin($table, $condition, 'LEFT OUTER'); |
|
462 | } |
||
463 | |||
464 | |||
465 | |||
466 | /** |
||
467 | * Build the where part. |
||
468 | * |
||
469 | * @param string $condition for building the where part of the query. |
||
470 | * |
||
471 | * @return $this |
||
472 | */ |
||
473 | 1 | public function where($condition) |
|
474 | { |
||
475 | 1 | $this->where = "WHERE \n\t(" . $condition . ")"; |
|
476 | |||
477 | 1 | return $this; |
|
478 | } |
||
479 | |||
480 | |||
481 | |||
482 | /** |
||
483 | * Build the where part with conditions. |
||
484 | * |
||
485 | * @param string $condition for building the where part of the query. |
||
486 | * |
||
487 | * @return $this |
||
488 | */ |
||
489 | 1 | public function andWhere($condition) |
|
490 | { |
||
491 | 1 | $this->where .= "\n\tAND (" . $condition . ")"; |
|
492 | |||
493 | 1 | return $this; |
|
494 | } |
||
495 | |||
496 | |||
497 | |||
498 | /** |
||
499 | * Build the group by part. |
||
500 | * |
||
501 | * @param string $condition for building the group by part of the query. |
||
502 | * |
||
503 | * @return $this |
||
504 | */ |
||
505 | 1 | public function groupBy($condition) |
|
506 | { |
||
507 | 1 | $this->groupby = "GROUP BY " . $condition; |
|
508 | |||
509 | 1 | return $this; |
|
510 | } |
||
511 | |||
512 | |||
513 | |||
514 | /** |
||
515 | * Build the order by part. |
||
516 | * |
||
517 | * @param string $condition for building the where part of the query. |
||
518 | * |
||
519 | * @return $this |
||
520 | */ |
||
521 | 1 | public function orderBy($condition) |
|
522 | { |
||
523 | 1 | $this->orderby = "ORDER BY " . $condition; |
|
524 | |||
525 | 1 | return $this; |
|
526 | } |
||
527 | |||
528 | |||
529 | |||
530 | /** |
||
531 | * Build the LIMIT by part. |
||
532 | * |
||
533 | * @param string $condition for building the LIMIT part of the query. |
||
534 | * |
||
535 | * @return $this |
||
536 | */ |
||
537 | 1 | public function limit($condition) |
|
538 | { |
||
539 | 1 | $this->limit = "LIMIT \n\t" . intval($condition); |
|
540 | |||
541 | 1 | return $this; |
|
542 | } |
||
543 | |||
544 | |||
545 | |||
546 | /** |
||
547 | * Build the OFFSET by part. |
||
548 | * |
||
549 | * @param string $condition for building the OFFSET part of the query. |
||
550 | * |
||
551 | * @return $this |
||
552 | */ |
||
553 | 1 | public function offset($condition) |
|
554 | { |
||
555 | 1 | $this->offset = "OFFSET \n\t" . intval($condition); |
|
556 | |||
557 | 1 | return $this; |
|
558 | } |
||
559 | } |
||
560 |