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

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

655 lines 15.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	global $wpdb;
165
166	$op_lower = strtolower( $operator );
167
168	// Handle IN / NOT IN operators.
169	if ( in_array( $op_lower, array( 'in', 'not in' ), true ) ) {
170	return $this->compile_where_in( $column, $operator, $value );
171	}
172
173	// Handle BETWEEN operator.
174	if ( 'between' === $op_lower ) {
175	return $this->compile_where_between( $column, $operator, $value );
176	}
177
178	// Handle basic comparison operators.
179	$placeholder = $this->guess_var_type( $value );
180
181	// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
182	return $wpdb->prepare( "`$column` $operator $placeholder", $value );
183	}
184
185	/**
186	* Compile a WHERE IN / NOT IN clause.
187	*
188	* @param string $column The column name.
189	* @param string $operator The operator (IN or NOT IN).
190	* @param mixed $values The values (should be array, invalid inputs skipped).
191	*
192	* @return string\|null The compiled SQL or null if empty values.
193	*/
194	private function compile_where_in( string $column, string $operator, $values ): ?string {
195	if ( ! is_array( $values ) \|\| 0 === count( $values ) ) {
196	return null;
197	}
198
199	global $wpdb;
200
201	$placeholders = array();
202	foreach ( $values as $val ) {
203	$placeholders[] = $this->guess_var_type( $val );
204	}
205
206	$sql_template = "`$column` $operator (" . implode( ', ', $placeholders ) . ')';
207
208	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
209	return $wpdb->prepare( $sql_template, ...$values );
210	}
211
212	/**
213	* Compile a WHERE BETWEEN clause.
214	*
215	* @param string $column The column name.
216	* @param string $operator The operator (BETWEEN).
217	* @param mixed $values The values (should be array with min/max, invalid inputs skipped).
218	*
219	* @return string\|null The compiled SQL or null if invalid values.
220	*/
221	private function compile_where_between( string $column, string $operator, $values ): ?string {
222	if ( ! is_array( $values ) \|\| count( $values ) < 2 ) {
223	return null;
224	}
225
226	global $wpdb;
227
228	$placeholder_min = $this->guess_var_type( $values[0] );
229	$placeholder_max = $this->guess_var_type( $values[1] );
230
231	return $wpdb->prepare(
232	"`$column` $operator $placeholder_min AND $placeholder_max", // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
233	$values[0],
234	$values[1]
235	);
236	}
237
238	/**
239	* Guess the type of value for correcting placeholder.
240	*
241	* @param mixed $value The value to guess.
242	*
243	* @return string
244	*/
245	private function guess_var_type( $value ): string {
246	if ( false !== filter_var( $value, FILTER_VALIDATE_INT ) ) {
247	return '%d';
248	}
249
250	if ( false !== filter_var( $value, FILTER_VALIDATE_FLOAT ) ) {
251	return '%f';
252	}
253
254	return '%s';
255	}
256
257	/**
258	* Find a record by its ID.
259	*
260	* @param int $id The ID of the record.
261	*
262	* @return $this
263	*/
264	public function find_by_id( $id ) {
265	global $wpdb;
266	$this->where[] = $wpdb->prepare( 'id = %d', $id );
267
268	return $this;
269	}
270
271	/**
272	* Set the group by clause for the SQL query based on the provided argument.
273	*
274	* @param string $group_by The column to group by.
275	*
276	* @return $this
277	*/
278	public function group_by( $group_by ) {
279	global $wpdb;
280	$this->group = str_replace(
281	"'",
282	'',
283	$wpdb->prepare( 'GROUP BY %s', $group_by )
284	);
285
286	return $this;
287	}
288
289	/**
290	* Set the order for the SQL query based on the provided arguments.
291	*
292	* @param mixed $order_by The column to order by.
293	* @param string $order The order direction, defaults to 'asc'.
294	*
295	* @return $this
296	*/
297	public function order_by( $order_by, $order = 'asc' ) {
298	global $wpdb;
299	if ( ! in_array( $order, array( 'asc', 'desc' ), true ) ) {
300	// Fall it back.
301	$order = 'asc';
302	}
303	$this->order = str_replace(
304	"'",
305	'',
306	$wpdb->prepare( 'ORDER BY %s %s', $order_by, $order )
307	);
308
309	return $this;
310	}
311
312	/**
313	* Set the limit for the SQL query based on the provided value.
314	*
315	* @param int $limit The limit value.
316	* @param int\|null $offset The offset value.
317	*
318	* @return $this
319	*/
320	public function limit( $limit, $offset = null ) {
321	global $wpdb;
322
323	if ( null === $offset ) {
324	$this->limit = $wpdb->prepare( 'LIMIT %d', $limit );
325	} else {
326	$this->limit = $wpdb->prepare( 'LIMIT %d OFFSET %d', $limit, $offset );
327	}
328
329	return $this;
330	}
331
332	/**
333	* Find the first.
334	*
335	* @return null\|Model
336	*/
337	public function first() {
338	$this->limit = 'LIMIT 0,1';
339	$sql = $this->query_build(); // SQL is prepared here. We will ignore prepare rules.
340	$this->saved_queries = $sql;
341	global $wpdb;
342	$data = $wpdb->get_row( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
343	if ( is_null( $data ) ) {
344	return null;
345	}
346	// Check if we have any json string in property.
347	foreach ( $data as &$datum ) {
348	if ( is_string( $datum ) ) {
349	$tmp = json_decode( $datum, true );
350	if ( is_array( $tmp ) ) {
351	$datum = $tmp;
352	}
353	}
354	}
355	$class_name = $this->repository;
356	$model = new $class_name();
357	$model->import( $data );
358
359	return $model;
360	}
361
362	/**
363	* Retrieves the models based on the data obtained from get_results().
364	*
365	* @return array
366	*/
367	public function get() {
368	$data = $this->get_results();
369	$models = array();
370	foreach ( $data as $row ) {
371	foreach ( $row as &$property ) {
372	if ( is_string( $property ) ) {
373	$tmp = json_decode( $property, true );
374	if ( is_array( $tmp ) ) {
375	$property = $tmp;
376	}
377	}
378	}
379	$class_name = $this->repository;
380	$model = new $class_name();
381	$model->import( $row );
382	$models[] = $model;
383	}
384
385	return $models;
386	}
387
388	/**
389	* Get records in array form.
390	*
391	* @return array
392	* @since 2.7.0
393	*/
394	public function get_results() {
395	$sql = $this->query_build(); // SQL is prepared here.
396	$this->saved_queries = $sql;
397
398	global $wpdb;
399	$data = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
400	if ( is_null( $data ) ) {
401	$data = array();
402	}
403
404	return $data;
405	}
406
407	/**
408	* Get the count of records based on the provided query.
409	*
410	* @return string\|null The count of records.
411	*/
412	public function count() {
413	global $wpdb;
414	$sql = $this->query_build( 'COUNT(*)' ); // SQL is prepared here.
415
416	$result = $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
417
418	return $result;
419	}
420
421	/**
422	* Handle the insert/update of current model.
423	*
424	* @param Model $model The model to save.
425	*
426	* @return int\|bool The ID of current record OR false.
427	* @throws \ReflectionException If class is not defined.
428	*/
429	public function save( Model &$model ) {
430	global $wpdb;
431	$data = $model->export();
432	$data_type = array();
433	$exported_type = $model->export_type();
434	unset( $data['table'] );
435	unset( $data['safe'] );
436	foreach ( $data as $key => &$val ) {
437	if ( is_array( $val ) ) {
438	$val = wp_json_encode( $val );
439	} elseif ( is_bool( $val ) ) {
440	$val = $val ? 1 : 0;
441	}
442
443	$data_type[] = $exported_type[ $key ] ?? '%s';
444	}
445	$table = self::table( $model );
446	if ( $model->id ) {
447	$ret = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
448	$table,
449	$data,
450	array( 'id' => $model->id ),
451	$data_type,
452	array( '%d' )
453	);
454	} else {
455	$ret = $wpdb->insert( $table, $data, $data_type ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
456	// Bind this for later use.
457	$model->id = $wpdb->insert_id;
458	}
459
460	if ( false === $ret ) {
461	return false;
462	}
463
464	return $wpdb->insert_id;
465	}
466
467	/**
468	* Delete a record from the database table based on the provided conditions.
469	*
470	* @param mixed $where The conditions to apply when deleting the record.
471	*
472	* @return int\|false The number of rows affected or false on failure.
473	* @throws \ReflectionException If class is not defined.
474	*/
475	public function delete( $where ) {
476	$table = self::table();
477	global $wpdb;
478
479	return $wpdb->delete( $table, $where ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
480	}
481
482	/**
483	* Delete all records from the database table based on the provided conditions.
484	*
485	* @return int\|false The number of rows affected or false on failure.
486	* @throws \ReflectionException If class is not defined.
487	*/
488	public function delete_all() {
489	$table = self::table();
490	global $wpdb;
491
492	$where = implode( ' AND ', $this->where );
493	$sql = "DELETE FROM $table WHERE $where";
494	$this->clear();
495
496	return $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
497	}
498
499	/**
500	* Delete records from the database table based on the provided conditions and limit.
501	*
502	* @return int\|false The number of rows affected or false on failure.
503	* @throws \ReflectionException If class is not defined.
504	*/
505	public function delete_by_limit() {
506	$table = self::table();
507	global $wpdb;
508
509	$where = implode( ' AND ', $this->where );
510	$limit = $this->limit;
511	$order = $this->order;
512	$sql = "DELETE FROM $table WHERE $where $order $limit";
513	$this->clear();
514
515	return $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
516	}
517
518	/**
519	* Handle the truncation of a database table.
520	*
521	* @return int\|false The number of rows affected or false on failure.
522	* @throws \ReflectionException If class is not defined.
523	*/
524	public function truncate() {
525	$table = self::table();
526	global $wpdb;
527
528	// Uninstall/reset flows can run in mixed Free/Pro states where some tables were never created.
529	$exists = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
530	$wpdb->prepare(
531	'SHOW TABLES LIKE %s',
532	$wpdb->esc_like( $table )
533	)
534	);
535
536	if ( empty( $exists ) ) {
537	return false;
538	}
539
540	$query = "TRUNCATE TABLE $table"; // SQL is prepared here. so we can ignore WordPress.DB.PreparedSQL.NotPrepared.
541
542	return $wpdb->query( $query ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
543	}
544
545	/**
546	* It is used to retrieve the table name associated with a given model.
547	*
548	* @param mixed $model (optional) The model object or class name. If not provided, it uses the "repository"
549	* property of the class.
550	*
551	* @return string\|false The table name with the WordPress database prefix, or false if the table property doesn't
552	* exist or an exception occurs.
553	* @throws \ReflectionException If the model class doesn't exist.
554	*/
555	private function table( $model = null ) {
556	if ( is_null( $model ) ) {
557	$model = $this->get_model();
558	}
559	if ( is_object( $model ) && method_exists( $model, 'get_table' ) ) {
560	$table = $model->get_table();
561	if ( null !== $table ) {
562	global $wpdb;
563
564	// Have to set the prefix.
565	return $wpdb->base_prefix . $table;
566	}
567	}
568	// This when class doesn't exist.
569	return false;
570	}
571
572	/**
573	* Reset all the queries prepare after an action.
574	*/
575	private function clear() {
576	$this->select = '';
577	$this->where = array();
578	$this->group = '';
579	$this->order = '';
580	$this->limit = '';
581	}
582
583	/**
584	* Join the stuff on the table to make a full query statement.
585	* SQL params e.g. WHERE, ORDER or LIMIT were escaped on separate methods.
586	*
587	* @param string $select Columns to select.
588	*
589	* @return string
590	* @throws \ReflectionException If class is not defined.
591	*/
592	private function query_build( string $select = '*' ) {
593	$table = $this->table();
594	$where_parts = array_filter(
595	$this->where,
596	static function ( $clause ) {
597	return null !== $clause && '' !== trim( (string) $clause );
598	}
599	);
600	$where = implode( ' AND ', $where_parts );
601	$where = '' === $where ? '1=1' : $where;
602
603	$select = '' !== $this->select ? $this->select : $select;
604	$group_by = $this->group;
605	$order_by = $this->order;
606	$limit = $this->limit;
607	$sql = "SELECT $select FROM $table WHERE $where $group_by $order_by $limit";
608	$this->clear();
609
610	return $sql;
611	}
612
613	/**
614	* Checks if the given operator is valid.
615	*
616	* @param string $operator The operator to check.
617	*
618	* @return bool True if the operator is valid, false otherwise.
619	*/
620	private function valid_operator( $operator ) {
621	$operator = strtolower( $operator );
622	$allowed = array(
623	'in',
624	'not in',
625	'>',
626	'<',
627	'=',
628	'<=',
629	'>=',
630	'like',
631	'between',
632	'regexp',
633	'not regexp',
634	);
635
636	return in_array( $operator, $allowed, true );
637	}
638
639	/**
640	* Cache the model instance for clone & reference use.
641	*
642	* @return mixed
643	*/
644	private function get_model() {
645	if ( isset( $this->known[ $this->repository ] ) ) {
646	return $this->known[ $this->repository ];
647	}
648	$class = $this->repository;
649	$model = new $class();
650	$this->known[ $this->repository ] = $model;
651
652	return $model;
653	}
654	}
655