booktics / base / abstracts / booktics-database.php

booktics-database.php in Booktics – Appointment Booking Calendar for Service Businesses 1.0.29, at base/abstracts/booktics-database.php

265 lines 7.6 KB

Raw Download Zip

1	<?php
2
3	namespace Booktics\Abstracts;
4
5	use Booktics\Contracts\Database_Interface;
6	use RuntimeException;
7	use wpdb;
8
9	/**
10	* Database Manager for booktics database
11	*
12	* @package Booktics/Abstracts
13	*/
14	class Booktics_Database implements Database_Interface {
15
16	/**
17	* WordPress database global variable
18	*
19	* @var wpdb
20	*/
21	private $db;
22
23	/**
24	* Set global wpdb instance
25	*/
26	public function __construct() {
27	global $wpdb;
28	if ( ! $wpdb instanceof wpdb ) {
29	throw new RuntimeException( 'WordPress database object not available' );
30	}
31	$this->db = $wpdb;
32	}
33
34	/**
35	* Validate a SQL identifier (column name) against a strict allow-list.
36	*
37	* Only plain identifiers ([A-Za-z0-9_]) are permitted. Since column names
38	* cannot be parameterized by wpdb::prepare(), any value used as a column
39	* name must be validated here to prevent SQL injection when the array keys
40	* originate from user-controlled request bodies.
41	*
42	* @param string $identifier
43	*
44	* @return string The validated identifier, wrapped in backticks.
45	*
46	* @throws RuntimeException When the identifier is not a plain column name.
47	*/
48	private function sanitize_identifier( string $identifier ): string {
49	if ( ! preg_match( '/^[a-zA-Z0-9_]+$/', $identifier ) ) {
50	throw new RuntimeException(
51	/* translators: %s: column name. */
52	sprintf( esc_html__( 'Invalid database column identifier: %s', 'booktics' ), esc_html( $identifier ) )
53	);
54	}
55
56	return "`{$identifier}`";
57	}
58
59	/**
60	* Begin database transaction
61	*
62	* @return void
63	*/
64	public function begin_transaction(): void {
65	$this->db->query( 'START TRANSACTION' );
66	}
67
68	/**
69	* Commit transactions to database
70	*
71	* @return void
72	*/
73	public function commit(): void {
74	$this->db->query( 'COMMIT' );
75	}
76
77	/**
78	* Rollback uncommited transactions
79	*
80	* @return void
81	*/
82	public function roll_back(): void {
83	$this->db->query( 'ROLLBACK' );
84	}
85
86	/**
87	* Delete records of specific table
88	*
89	* @param string $table
90	* @param array $where
91	*
92	* @return void
93	*/
94	public function delete( string $table, array $where ): void {
95	$table = booktics_get_table_name( $table );
96	$conditions = array();
97	$values = array();
98
99	foreach ( $where as $key => $value ) {
100	$conditions[] = $this->sanitize_identifier( $key ) . ' = %s';
101	$values[] = $value;
102	}
103
104	$condition_string = implode( ' AND ', $conditions );
105
106	$sql = "DELETE FROM $table WHERE $condition_string";
107
108	$result = $this->db->query(
109	$this->db->prepare( $sql, ...$values )
110	);
111
112	if ( $result === false ) {
113	throw new RuntimeException(
114	// translators: %s is the database error message.
115	sprintf( esc_html__( 'Failed to delete records: %s', 'booktics' ), esc_html( $this->db->last_error ) )
116	);
117	}
118	}
119
120	/**
121	* Batch insert records
122	*
123	* @param string $table
124	* @param array $records
125	*
126	* @return void
127	*/
128	public function insert_batch( string $table, array $records ): void {
129	if ( empty( $records ) ) {
130	return;
131	}
132
133	$table = booktics_get_table_name( $table );
134	$columns = array_keys( $records[0] );
135	$safe_columns = array_map( array( $this, 'sanitize_identifier' ), $columns );
136	$placeholders = array_fill( 0, count( $columns ), '%s' );
137	$placeholder_string = '(' . implode( ',', $placeholders ) . ')';
138
139	$values = array();
140	$placeholders_groups = array();
141
142	foreach ( $records as $record ) {
143	$placeholders_groups[] = $placeholder_string;
144	foreach ( $columns as $column ) {
145	$values[] = $record[ $column ] ?? '';
146	}
147	}
148
149	$column_string = implode( ',', $safe_columns );
150	$placeholders_string = implode( ',', $placeholders_groups );
151
152	$sql = "INSERT INTO $table ($column_string) VALUES $placeholders_string";
153
154	$result = $this->db->query(
155	$this->db->prepare( $sql, ...$values )
156	);
157
158	if ( $result === false ) {
159	// translators: %s is the database error message.
160	sprintf( esc_html__( 'Failed to insert records: %s', 'booktics' ), esc_html( $this->db->last_error ) );
161	}
162	}
163
164	/**
165	* Select records from a table
166	*
167	* @param string $table
168	* @param array $where Optional WHERE conditions
169	* @param array $columns Columns to select
170	*
171	* @return array
172	*/
173	public function select( string $table, array $where = array(), array $columns = array( '*' ) ) {
174	$table = booktics_get_table_name( $table );
175	$columns_s = implode(
176	', ',
177	array_map(
178	function ( $column ) {
179	return '' === $column ? '' : $this->sanitize_identifier( $column );
180	},
181	$columns
182	)
183	);
184	$sql = "SELECT $columns_s FROM $table";
185
186	$values = array();
187	if ( ! empty( $where ) ) {
188	$conditions = array();
189
190	foreach ( $where as $key => $value ) {
191	$conditions[] = $this->sanitize_identifier( $key ) . ' = %s';
192	$values[] = $value;
193	}
194
195	$condition_string = implode( ' AND ', $conditions );
196	$sql .= " WHERE $condition_string";
197	}
198
199	$prepared = $this->db->prepare( $sql, ...$values );
200	$results = $this->db->get_results( $prepared );
201
202	if ( $results === null ) {
203	throw new RuntimeException(
204	// translators: %s is the database error message.
205	sprintf( esc_html__( 'Failed to select records: %s', 'booktics' ), esc_html( $this->db->last_error ) )
206	);
207	}
208
209	return $results;
210	}
211
212	/**
213	* Initialize query builder instance
214	*
215	* @param string $table
216	*
217	* @return DB_Query_Builder
218	*/
219	public function table( string $table ): DB_Query_Builder {
220	return ( new DB_Query_Builder( $this->db ) )->table( $table );
221	}
222
223	/**
224	* Update records in a table
225	*
226	* @param string $table
227	* @param array $data
228	* @param array $where
229	*
230	* @return void
231	*/
232	public function update( string $table, array $data, array $where ) {
233	$table = booktics_get_table_name( $table );
234	$set_values = array();
235	$where_conditions = array();
236	$values = array();
237
238	foreach ( $data as $key => $value ) {
239	$set_values[] = $this->sanitize_identifier( $key ) . ' = %s';
240	$values[] = $value;
241	}
242
243	foreach ( $where as $key => $value ) {
244	$where_conditions[] = $this->sanitize_identifier( $key ) . ' = %s';
245	$values[] = $value;
246	}
247
248	$set_string = implode( ', ', $set_values );
249	$where_string = implode( ' AND ', $where_conditions );
250
251	$sql = "UPDATE $table SET $set_string WHERE $where_string";
252
253	$result = $this->db->query(
254	$this->db->prepare( $sql, ...$values )
255	);
256
257	if ( $result === false ) {
258	throw new RuntimeException(
259	// translators: %s: Records Name
260	sprintf( esc_html__( 'Failed to update records: %s', 'booktics' ), esc_html( $this->db->last_error ) )
261	);
262	}
263	}
264	}
265