bookit / includes / classes / vendor / DatabaseModel.php

DatabaseModel.php in Bookit — Booking & Appointment Calendar 2.6.0.5, at includes/classes/vendor/DatabaseModel.php

236 lines 6.3 KB

Raw Download Zip

1	<?php
2
3	namespace Bookit\Classes\Vendor;
4
5	abstract class DatabaseModel {
6
7	public static $primary_key = 'id';
8	protected static $table_prefix = 'bookit_';
9
10	/**
11	* Generate Table Name from Called Class
12	* @return string
13	*/
14	public static function _table() {
15	global $wpdb;
16	$classname = explode( '\\', strtolower( get_called_class() ) );
17	$tablename = self::$table_prefix . end( $classname );
18	return $wpdb->prefix . $tablename;
19	}
20
21	/**
22	* SQL Fetch from Table
23	* @param $key
24	* @param $value
25	* @return mixed
26	*/
27	private static function _fetch_sql( $key, $value ) {
28	global $wpdb;
29	$sql = sprintf( 'SELECT * FROM `%s` WHERE `%s` = %%s', esc_sql( self::_table() ), esc_sql( $key ) );
30	return $wpdb->prepare( $sql, $value );
31	}
32
33	/**
34	* Get Rows with Pagination
35	*
36	* Security: $search must be a trusted SQL fragment (e.g. "WHERE col = %s") with placeholders;
37	* use $prepare_values for any user input. $sort and $order are validated to prevent SQL injection.
38	*
39	* @param int $limit Number of rows.
40	* @param int $offset Offset.
41	* @param string $search Optional SQL fragment (e.g. WHERE clause with %s placeholders). Must not contain user input.
42	* @param string $sort Column name for ORDER BY; only alphanumeric and underscore allowed, or empty for primary key.
43	* @param string $order 'ASC' or 'DESC' (case-insensitive).
44	* @param array $prepare_values Values for $search placeholders when using $wpdb->prepare().
45	*
46	* @return mixed
47	*/
48	public static function get_paged( $limit, $offset, $search = '', $sort = '', $order = '', $prepare_values = array() ) {
49	global $wpdb;
50	$table = esc_sql( self::_table() );
51	$sort_col = esc_sql( empty( $sort ) ? static::$primary_key : $sort );
52	$order_dir = esc_sql( empty( $order ) ? 'DESC' : $order );
53	$limit = absint( $limit );
54	$offset = absint( $offset );
55
56	if ( ! empty( $prepare_values ) ) {
57	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
58	$sql = "SELECT * FROM `{$table}` {$search} ORDER BY `{$sort_col}` {$order_dir} LIMIT %d OFFSET %d";
59	return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( $prepare_values, array( $limit, $offset ) ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
60	}
61
62	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
63	$sql = "SELECT * FROM `{$table}` {$search} ORDER BY `{$sort_col}` {$order_dir} LIMIT %d OFFSET %d";
64	return $wpdb->get_results( $wpdb->prepare( $sql, $limit, $offset ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
65	}
66
67	/**
68	* Get All Rows
69	*/
70	public static function get_all() {
71	global $wpdb;
72	return $wpdb->get_results(
73	sprintf( 'SELECT * FROM `%s` ORDER BY `%s` DESC', esc_sql( self::_table() ), esc_sql( static::$primary_key ) ),
74	ARRAY_A
75	);
76	}
77
78	/**
79	* Get Total Count of Rows
80	* @return mixed
81	*/
82	public static function get_count() {
83	global $wpdb;
84	return $wpdb->get_var( sprintf( 'SELECT COUNT(*) FROM `%s`', esc_sql( self::_table() ) ) );
85	}
86
87	/**
88	* Get Row by ID
89	* @param $key
90	* @param $value
91	* @return mixed
92	*/
93	public static function get( $key, $value ) {
94	global $wpdb;
95	return $wpdb->get_row( self::_fetch_sql( $key, $value ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
96	}
97
98	/**
99	* Insert data to Table
100	* @param $data
101	*/
102	public static function insert( $data ) {
103	global $wpdb;
104
105	add_filter( 'query', array( self::class, 'wp_db_null_value' ) );
106
107	if ( isset( $data['nonce'] ) ) {
108	unset( $data['nonce'] );
109	}
110
111	$data = array_map(
112	function( $item ) {
113	$item = (string) $item; // Cast to string to prevent null values.
114	if ( trim( $item, ' \'"' ) ) {
115	return trim( $item );
116	}
117	return null;
118	},
119	$data
120	);
121
122	$wpdb->insert( self::_table(), $data );
123
124	remove_filter( 'query', array( self::class, 'wp_db_null_value' ) );
125	}
126
127	/**
128	* Update data in Table with $where clause
129	* @param $data
130	* @param $where
131	*/
132	public static function update( $data, $where ) {
133	global $wpdb;
134
135	add_filter( 'query', array( self::class, 'wp_db_null_value' ) );
136
137	if ( isset( $data['nonce'] ) ) {
138	unset( $data['nonce'] );
139	}
140
141	$data = array_map(
142	function( $item ) {
143	$item = (string) $item; // Cast to string to prevent null values.
144	if ( trim( $item, ' \'"' ) ) {
145	return trim( $item );
146	}
147	return null;
148	},
149	$data
150	);
151
152	$wpdb->update( self::_table(), $data, $where );
153
154	remove_filter( 'query', array( self::class, 'wp_db_null_value' ) );
155	}
156
157	/**
158	* Delete data from Table by ID
159	* @param $value
160	* @return mixed
161	*/
162	public static function delete( $value ) {
163	global $wpdb;
164	$sql = sprintf( 'DELETE FROM `%s` WHERE `%s` = %%s', esc_sql( self::_table() ), esc_sql( static::$primary_key ) );
165	return $wpdb->query( $wpdb->prepare( $sql, $value ) );
166	}
167
168	/**
169	* Delete data from Table Where
170	* @param $key
171	* @param $value
172	* @return mixed
173	*/
174	public static function delete_where( $key, $value ) {
175	global $wpdb;
176	$sql = sprintf( 'DELETE FROM `%s` WHERE `%s` = %%s', esc_sql( self::_table() ), esc_sql( $key ) );
177	return $wpdb->query( $wpdb->prepare( $sql, $value ) );
178	}
179
180	/**
181	* Get Inserted data ID
182	* @return mixed
183	*/
184	public static function insert_id() {
185	global $wpdb;
186	return $wpdb->insert_id;
187	}
188
189	/**
190	* Replace the 'NULL' string with NULL
191	*
192	* @param string $query
193	* @return string $query
194	*/
195	public static function wp_db_null_value( $query ) {
196	return str_ireplace( "'NULL'", 'NULL', $query );
197	}
198
199	/**
200	* Show Last Query
201	* @return mixed
202	*/
203	public static function show_last_query() {
204	global $wpdb;
205	echo $wpdb->last_query; // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
206	}
207
208	/**
209	* Drop all bookit tables on uninstall
210	*/
211	public static function drop_tables() {
212	global $wpdb;
213
214	$sql = self::generate_drop_table_statement();
215	$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
216	}
217
218	/**
219	* Generate sql statement to remove all bookit tables by prefix
220	*/
221	private static function generate_drop_table_statement() {
222	global $wpdb;
223
224	$prefix = $wpdb->prefix . self::$table_prefix;
225	$like = '%' . $wpdb->esc_like( $prefix ) . '%';
226	return $wpdb->get_var(
227	$wpdb->prepare(
228	"SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(DISTINCT( table_name) ) , ';' ) AS statement
229	FROM information_schema.tables
230	WHERE table_name LIKE %s",
231	$like
232	)
233	);
234	}
235	}
236