defender-security / framework / db / class-mapper.php

class-mapper.php in Defender Security – Malware Scanner, Login Security & Firewall 6.3.0, at framework/db/class-mapper.php

693 lines 16.5 KB

Raw Download Zip

1	<?php
2	/**
3	* Data mapper for CRUD.
4	*
5	* @package Calotes\DB
6	*/
7
8	namespace Calotes\DB;
9
10	use Calotes\Base\Model;
11	use Calotes\Base\Component;
12
13	/**
14	* Responsible for performing CRUD operations.
15	*/
16	class Mapper extends Component {
17
18	/**
19	* Contain the current model class name.
20	*
21	* @var string
22	*/
23	private $repository;
24
25	/**
26	* The columns to select in the query.
27	*
28	* @var string
29	*/
30	private $select = '';
31
32	/**
33	* Where statements for the query.
34	*
35	* @var array
36	*/
37	private $where = array();
38
39	/**
40	* The grouping parameter for the query.
41	*
42	* @var string
43	*/
44	private $group = '';
45
46	/**
47	* The ordering parameter for the query.
48	*
49	* @var string
50	*/
51	private $order = '';
52
53	/**
54	* The limit for the query results.
55	*
56	* @var string
57	*/
58	private $limit = '';
59
60	/**
61	* Cache for storing retrieved records.
62	*
63	* @var array
64	*/
65	private $known = array();
66
67	/**
68	* Store the last executed query.
69	*
70	* @var string
71	*/
72	public $saved_queries = '';
73
74	/**
75	* Set the repository class name.
76	*
77	* @param mixed $class_name The class name to set for the repository.
78	*
79	* @return $this
80	*/
81	public function get_repository( $class_name ) {
82	$this->repository = $class_name;
83
84	return $this;
85	}
86
87	/**
88	* Set the columns to select in the SQL query.
89	*
90	* @param mixed $select The columns to select.
91	*
92	* @return $this
93	*/
94	public function select( $select ) {
95	$this->select = $select;
96
97	return $this;
98	}
99
100	/**
101	* Set the WHERE clause for the query based on the provided arguments.
102	*
103	* Supports multiple call signatures:
104	* - where($column, $value) - equals comparison
105	* - where($column, $operator, $value) - custom operator comparison
106	*
107	* @param mixed ...$args The conditions to apply in the WHERE clause.
108	*
109	* @return $this
110	*/
111	public function where( ...$args ) {
112	$result = $this->prepare_where_args( $args );
113
114	if ( null === $result ) {
115	return $this;
116	}
117
118	[ $column, $operator, $value ] = $result;
119
120	if ( ! $this->valid_operator( $operator ) ) {
121	return $this;
122	}
123
124	$sql = $this->compile_where( $column, $operator, $value );
125
126	if ( $sql ) {
127	$this->where[] = $sql;
128	}
129
130	return $this;
131	}
132
133	/**
134	* Prepare where arguments - handles both 2-arg and 3-arg signatures.
135	*
136	* @param array $args The arguments passed to where().
137	*
138	* @return array\|null [$column, $operator, $value] or null if invalid argument count.
139	*/
140	private function prepare_where_args( array $args ): ?array {
141	$count = count( $args );
142
143	if ( 2 === $count ) {
144	return array( $args[0], '=', $args[1] );
145	}
146
147	if ( 3 === $count ) {
148	return array( $args[0], $args[1], $args[2] );
149	}
150
151	return null;
152	}
153
154	/**
155	* Compile a where clause into SQL.
156	*
157	* @param string $column The column name.
158	* @param string $operator The operator.
159	* @param mixed $value The value to compare against.
160	*
161	* @return string\|null The compiled SQL or null if invalid.
162	*/
163	private function compile_where( string $column, string $operator, $value ): ?string {
164	if ( ! preg_match( '/^[a-zA-Z0-9_]+$/', $column ) ) {
165	return null;
166	}
167
168	global $wpdb;
169
170	$op_lower = strtolower( $operator );
171
172	// Handle IN / NOT IN operators.
173	if ( in_array( $op_lower, array( 'in', 'not in' ), true ) ) {
174	return $this->compile_where_in( $column, $operator, $value );
175	}
176
177	// Handle BETWEEN operator.
178	if ( 'between' === $op_lower ) {
179	return $this->compile_where_between( $column, $operator, $value );
180	}
181
182	// Handle basic comparison operators.
183	$placeholder = $this->guess_var_type( $value );
184
185	// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
186	return $wpdb->prepare( "%i $operator $placeholder", $column, $value );
187	}
188
189	/**
190	* Compile a WHERE IN / NOT IN clause.
191	*
192	* @param string $column The column name.
193	* @param string $operator The operator (IN or NOT IN).
194	* @param mixed $values The values (should be array, invalid inputs skipped).
195	*
196	* @return string\|null The compiled SQL or null if empty values.
197	*/
198	private function compile_where_in( string $column, string $operator, $values ): ?string {
199	if ( ! preg_match( '/^[a-zA-Z0-9_]+$/', $column ) ) {
200	return null;
201	}
202
203	if ( ! is_array( $values ) \|\| 0 === count( $values ) ) {
204	return null;
205	}
206
207	global $wpdb;
208
209	$placeholders = array();
210	foreach ( $values as $val ) {
211	$placeholders[] = $this->guess_var_type( $val );
212	}
213
214	$sql_template = '%i ' . $operator . ' (' . implode( ', ', $placeholders ) . ')';
215
216	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
217	return $wpdb->prepare( $sql_template, $column, ...$values );
218	}
219
220	/**
221	* Compile a WHERE BETWEEN clause.
222	*
223	* @param string $column The column name.
224	* @param string $operator The operator (BETWEEN).
225	* @param mixed $values The values (should be array with min/max, invalid inputs skipped).
226	*
227	* @return string\|null The compiled SQL or null if invalid values.
228	*/
229	private function compile_where_between( string $column, string $operator, $values ): ?string {
230	if ( ! preg_match( '/^[a-zA-Z0-9_]+$/', $column ) ) {
231	return null;
232	}
233
234	if ( ! is_array( $values ) \|\| count( $values ) < 2 ) {
235	return null;
236	}
237
238	global $wpdb;
239
240	$placeholder_min = $this->guess_var_type( $values[0] );
241	$placeholder_max = $this->guess_var_type( $values[1] );
242
243	// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
244	return $wpdb->prepare(
245	"%i $operator $placeholder_min AND $placeholder_max", // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
246	$column,
247	$values[0],
248	$values[1]
249	);
250	}
251
252	/**
253	* Guess the type of value for correcting placeholder.
254	*
255	* @param mixed $value The value to guess.
256	*
257	* @return string
258	*/
259	private function guess_var_type( $value ): string {
260	if ( false !== filter_var( $value, FILTER_VALIDATE_INT ) ) {
261	return '%d';
262	}
263
264	if ( false !== filter_var( $value, FILTER_VALIDATE_FLOAT ) ) {
265	return '%f';
266	}
267
268	return '%s';
269	}
270
271	/**
272	* Find a record by its ID.
273	*
274	* @param int $id The ID of the record.
275	*
276	* @return $this
277	*/
278	public function find_by_id( $id ) {
279	global $wpdb;
280	$this->where[] = $wpdb->prepare( 'id = %d', $id );
281
282	return $this;
283	}
284
285	/**
286	* Set the group by clause for the SQL query based on the provided argument.
287	*
288	* @param string $group_by The column to group by.
289	*
290	* @return $this
291	*/
292	public function group_by( $group_by ) {
293	global $wpdb;
294
295	if ( ! is_string( $group_by ) \|\| '' === trim( $group_by ) ) {
296	return $this;
297	}
298
299	$parts = array_map( 'trim', explode( ',', $group_by ) );
300	$safe_parts = array();
301	foreach ( $parts as $part ) {
302	if ( preg_match( '/^[a-zA-Z0-9_]+$/', $part ) ) {
303	$safe_parts[] = $wpdb->prepare( '%i', $part );
304	}
305	}
306
307	if ( empty( $safe_parts ) ) {
308	return $this;
309	}
310
311	$this->group = 'GROUP BY ' . implode( ', ', $safe_parts );
312
313	return $this;
314	}
315
316	/**
317	* Set the order for the SQL query based on the provided arguments.
318	*
319	* @param mixed $order_by The column to order by.
320	* @param string $order The order direction, defaults to 'asc'.
321	*
322	* @return $this
323	*/
324	public function order_by( $order_by, $order = 'asc' ) {
325	global $wpdb;
326
327	$order = 'desc' === strtolower( (string) $order ) ? 'DESC' : 'ASC';
328
329	if ( ! is_string( $order_by ) \|\| '' === trim( $order_by ) ) {
330	return $this;
331	}
332
333	$parts = array_map( 'trim', explode( ',', $order_by ) );
334	$safe_parts = array();
335	foreach ( $parts as $part ) {
336	if ( preg_match( '/^[a-zA-Z0-9_]+$/', $part ) ) {
337	$safe_parts[] = $wpdb->prepare( '%i', $part );
338	}
339	}
340
341	if ( empty( $safe_parts ) ) {
342	return $this;
343	}
344
345	$this->order = 'ORDER BY ' . implode( ', ', $safe_parts ) . ' ' . $order;
346
347	return $this;
348	}
349
350	/**
351	* Set the limit for the SQL query based on the provided value.
352	*
353	* @param int $limit The limit value.
354	* @param int\|null $offset The offset value.
355	*
356	* @return $this
357	*/
358	public function limit( $limit, $offset = null ) {
359	global $wpdb;
360
361	if ( null === $offset ) {
362	$this->limit = $wpdb->prepare( 'LIMIT %d', $limit );
363	} else {
364	$this->limit = $wpdb->prepare( 'LIMIT %d OFFSET %d', $limit, $offset );
365	}
366
367	return $this;
368	}
369
370	/**
371	* Find the first.
372	*
373	* @return null\|Model
374	*/
375	public function first() {
376	$this->limit = 'LIMIT 0,1';
377	$sql = $this->query_build(); // SQL is prepared here. We will ignore prepare rules.
378	$this->saved_queries = $sql;
379	global $wpdb;
380	$data = $wpdb->get_row( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
381	if ( is_null( $data ) ) {
382	return null;
383	}
384	// Check if we have any json string in property.
385	foreach ( $data as &$datum ) {
386	if ( is_string( $datum ) ) {
387	$tmp = json_decode( $datum, true );
388	if ( is_array( $tmp ) ) {
389	$datum = $tmp;
390	}
391	}
392	}
393	$class_name = $this->repository;
394	$model = new $class_name();
395	$model->import( $data );
396
397	return $model;
398	}
399
400	/**
401	* Retrieves the models based on the data obtained from get_results().
402	*
403	* @return array
404	*/
405	public function get() {
406	$data = $this->get_results();
407	$models = array();
408	foreach ( $data as $row ) {
409	foreach ( $row as &$property ) {
410	if ( is_string( $property ) ) {
411	$tmp = json_decode( $property, true );
412	if ( is_array( $tmp ) ) {
413	$property = $tmp;
414	}
415	}
416	}
417	$class_name = $this->repository;
418	$model = new $class_name();
419	$model->import( $row );
420	$models[] = $model;
421	}
422
423	return $models;
424	}
425
426	/**
427	* Get records in array form.
428	*
429	* @return array
430	* @since 2.7.0
431	*/
432	public function get_results() {
433	$sql = $this->query_build(); // SQL is prepared here.
434	$this->saved_queries = $sql;
435
436	global $wpdb;
437	$data = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
438	if ( is_null( $data ) ) {
439	$data = array();
440	}
441
442	return $data;
443	}
444
445	/**
446	* Get the count of records based on the provided query.
447	*
448	* @return string\|null The count of records.
449	*/
450	public function count() {
451	global $wpdb;
452	$sql = $this->query_build( 'COUNT(*)' ); // SQL is prepared here.
453
454	$result = $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
455
456	return $result;
457	}
458
459	/**
460	* Handle the insert/update of current model.
461	*
462	* @param Model $model The model to save.
463	*
464	* @return int\|bool The ID of current record OR false.
465	* @throws \ReflectionException If class is not defined.
466	*/
467	public function save( Model &$model ) {
468	global $wpdb;
469	$data = $model->export();
470	$data_type = array();
471	$exported_type = $model->export_type();
472	unset( $data['table'] );
473	unset( $data['safe'] );
474	foreach ( $data as $key => &$val ) {
475	if ( is_array( $val ) ) {
476	$val = wp_json_encode( $val );
477	} elseif ( is_bool( $val ) ) {
478	$val = $val ? 1 : 0;
479	}
480
481	$data_type[] = $exported_type[ $key ] ?? '%s';
482	}
483	$table = self::table( $model );
484	if ( $model->id ) {
485	$ret = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
486	$table,
487	$data,
488	array( 'id' => $model->id ),
489	$data_type,
490	array( '%d' )
491	);
492	} else {
493	$ret = $wpdb->insert( $table, $data, $data_type ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
494	// Bind this for later use.
495	$model->id = $wpdb->insert_id;
496	}
497
498	if ( false === $ret ) {
499	return false;
500	}
501
502	return $wpdb->insert_id;
503	}
504
505	/**
506	* Delete a record from the database table based on the provided conditions.
507	*
508	* @param mixed $where The conditions to apply when deleting the record.
509	*
510	* @return int\|false The number of rows affected or false on failure.
511	* @throws \ReflectionException If class is not defined.
512	*/
513	public function delete( $where ) {
514	$table = self::table();
515	global $wpdb;
516
517	return $wpdb->delete( $table, $where ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
518	}
519
520	/**
521	* Delete all records from the database table based on the provided conditions.
522	*
523	* @return int\|false The number of rows affected or false on failure.
524	* @throws \ReflectionException If class is not defined.
525	*/
526	public function delete_all() {
527	$table = self::table();
528	global $wpdb;
529
530	$where = implode( ' AND ', $this->where );
531	$sql = "DELETE FROM $table WHERE $where";
532	$this->clear();
533
534	return $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
535	}
536
537	/**
538	* Delete records from the database table based on the provided conditions and limit.
539	*
540	* @return int\|false The number of rows affected or false on failure.
541	* @throws \ReflectionException If class is not defined.
542	*/
543	public function delete_by_limit() {
544	$table = self::table();
545	global $wpdb;
546
547	$where = implode( ' AND ', $this->where );
548	$limit = $this->limit;
549	$order = $this->order;
550	$sql = "DELETE FROM $table WHERE $where $order $limit";
551	$this->clear();
552
553	return $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
554	}
555
556	/**
557	* Handle the truncation of a database table.
558	*
559	* @return int\|false The number of rows affected or false on failure.
560	* @throws \ReflectionException If class is not defined.
561	*/
562	public function truncate() {
563	$table = self::table();
564	global $wpdb;
565
566	// Uninstall/reset flows can run in mixed Free/Pro states where some tables were never created.
567	$exists = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
568	$wpdb->prepare(
569	'SHOW TABLES LIKE %s',
570	$wpdb->esc_like( $table )
571	)
572	);
573
574	if ( empty( $exists ) ) {
575	return false;
576	}
577
578	$query = "TRUNCATE TABLE $table"; // SQL is prepared here. so we can ignore WordPress.DB.PreparedSQL.NotPrepared.
579
580	return $wpdb->query( $query ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
581	}
582
583	/**
584	* It is used to retrieve the table name associated with a given model.
585	*
586	* @param mixed $model (optional) The model object or class name. If not provided, it uses the "repository"
587	* property of the class.
588	*
589	* @return string\|false The table name with the WordPress database prefix, or false if the table property doesn't
590	* exist or an exception occurs.
591	* @throws \ReflectionException If the model class doesn't exist.
592	*/
593	private function table( $model = null ) {
594	if ( is_null( $model ) ) {
595	$model = $this->get_model();
596	}
597	if ( is_object( $model ) && method_exists( $model, 'get_table' ) ) {
598	$table = $model->get_table();
599	if ( null !== $table ) {
600	global $wpdb;
601
602	// Have to set the prefix.
603	return $wpdb->base_prefix . $table;
604	}
605	}
606	// This when class doesn't exist.
607	return false;
608	}
609
610	/**
611	* Reset all the queries prepare after an action.
612	*/
613	private function clear() {
614	$this->select = '';
615	$this->where = array();
616	$this->group = '';
617	$this->order = '';
618	$this->limit = '';
619	}
620
621	/**
622	* Join the stuff on the table to make a full query statement.
623	* SQL params e.g. WHERE, ORDER or LIMIT were escaped on separate methods.
624	*
625	* @param string $select Columns to select.
626	*
627	* @return string
628	* @throws \ReflectionException If class is not defined.
629	*/
630	private function query_build( string $select = '*' ) {
631	$table = $this->table();
632	$where_parts = array_filter(
633	$this->where,
634	static function ( $clause ) {
635	return null !== $clause && '' !== trim( (string) $clause );
636	}
637	);
638	$where = implode( ' AND ', $where_parts );
639	$where = '' === $where ? '1=1' : $where;
640
641	$select = '' !== $this->select ? $this->select : $select;
642	$group_by = $this->group;
643	$order_by = $this->order;
644	$limit = $this->limit;
645	$sql = "SELECT $select FROM $table WHERE $where $group_by $order_by $limit";
646	$this->clear();
647
648	return $sql;
649	}
650
651	/**
652	* Checks if the given operator is valid.
653	*
654	* @param string $operator The operator to check.
655	*
656	* @return bool True if the operator is valid, false otherwise.
657	*/
658	private function valid_operator( $operator ) {
659	$operator = strtolower( $operator );
660	$allowed = array(
661	'in',
662	'not in',
663	'>',
664	'<',
665	'=',
666	'<=',
667	'>=',
668	'like',
669	'between',
670	'regexp',
671	'not regexp',
672	);
673
674	return in_array( $operator, $allowed, true );
675	}
676
677	/**
678	* Cache the model instance for clone & reference use.
679	*
680	* @return mixed
681	*/
682	private function get_model() {
683	if ( isset( $this->known[ $this->repository ] ) ) {
684	return $this->known[ $this->repository ];
685	}
686	$class = $this->repository;
687	$model = new $class();
688	$this->known[ $this->repository ] = $model;
689
690	return $model;
691	}
692	}
693