booktics / base / abstracts / booktics-database.php

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

264 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	sprintf( esc_html__( 'Invalid database column identifier: %s', 'booktics' ), esc_html( $identifier ) )
52	);
53	}
54
55	return "`{$identifier}`";
56	}
57
58	/**
59	* Begin database transaction
60	*
61	* @return void
62	*/
63	public function begin_transaction(): void {
64	$this->db->query( 'START TRANSACTION' );
65	}
66
67	/**
68	* Commit transactions to database
69	*
70	* @return void
71	*/
72	public function commit(): void {
73	$this->db->query( 'COMMIT' );
74	}
75
76	/**
77	* Rollback uncommited transactions
78	*
79	* @return void
80	*/
81	public function roll_back(): void {
82	$this->db->query( 'ROLLBACK' );
83	}
84
85	/**
86	* Delete records of specific table
87	*
88	* @param string $table
89	* @param array $where
90	*
91	* @return void
92	*/
93	public function delete( string $table, array $where ): void {
94	$table = booktics_get_table_name( $table );
95	$conditions = array();
96	$values = array();
97
98	foreach ( $where as $key => $value ) {
99	$conditions[] = $this->sanitize_identifier( $key ) . ' = %s';
100	$values[] = $value;
101	}
102
103	$condition_string = implode( ' AND ', $conditions );
104
105	$sql = "DELETE FROM $table WHERE $condition_string";
106
107	$result = $this->db->query(
108	$this->db->prepare( $sql, ...$values )
109	);
110
111	if ( $result === false ) {
112	throw new RuntimeException(
113	// translators: %s is the database error message.
114	sprintf( esc_html__( 'Failed to delete records: %s', 'booktics' ), esc_html( $this->db->last_error ) )
115	);
116	}
117	}
118
119	/**
120	* Batch insert records
121	*
122	* @param string $table
123	* @param array $records
124	*
125	* @return void
126	*/
127	public function insert_batch( string $table, array $records ): void {
128	if ( empty( $records ) ) {
129	return;
130	}
131
132	$table = booktics_get_table_name( $table );
133	$columns = array_keys( $records[0] );
134	$safe_columns = array_map( array( $this, 'sanitize_identifier' ), $columns );
135	$placeholders = array_fill( 0, count( $columns ), '%s' );
136	$placeholder_string = '(' . implode( ',', $placeholders ) . ')';
137
138	$values = array();
139	$placeholders_groups = array();
140
141	foreach ( $records as $record ) {
142	$placeholders_groups[] = $placeholder_string;
143	foreach ( $columns as $column ) {
144	$values[] = $record[ $column ] ?? '';
145	}
146	}
147
148	$column_string = implode( ',', $safe_columns );
149	$placeholders_string = implode( ',', $placeholders_groups );
150
151	$sql = "INSERT INTO $table ($column_string) VALUES $placeholders_string";
152
153	$result = $this->db->query(
154	$this->db->prepare( $sql, ...$values )
155	);
156
157	if ( $result === false ) {
158	// translators: %s is the database error message.
159	sprintf( esc_html__( 'Failed to insert records: %s', 'booktics' ), esc_html( $this->db->last_error ) );
160	}
161	}
162
163	/**
164	* Select records from a table
165	*
166	* @param string $table
167	* @param array $where Optional WHERE conditions
168	* @param array $columns Columns to select
169	*
170	* @return array
171	*/
172	public function select( string $table, array $where = array(), array $columns = array( '*' ) ) {
173	$table = booktics_get_table_name( $table );
174	$columns_s = implode(
175	', ',
176	array_map(
177	function ( $column ) {
178	return '' === $column ? '' : $this->sanitize_identifier( $column );
179	},
180	$columns
181	)
182	);
183	$sql = "SELECT $columns_s FROM $table";
184
185	$values = array();
186	if ( ! empty( $where ) ) {
187	$conditions = array();
188
189	foreach ( $where as $key => $value ) {
190	$conditions[] = $this->sanitize_identifier( $key ) . ' = %s';
191	$values[] = $value;
192	}
193
194	$condition_string = implode( ' AND ', $conditions );
195	$sql .= " WHERE $condition_string";
196	}
197
198	$prepared = $this->db->prepare( $sql, ...$values );
199	$results = $this->db->get_results( $prepared );
200
201	if ( $results === null ) {
202	throw new RuntimeException(
203	// translators: %s is the database error message.
204	sprintf( esc_html__( 'Failed to select records: %s', 'booktics' ), esc_html( $this->db->last_error ) )
205	);
206	}
207
208	return $results;
209	}
210
211	/**
212	* Initialize query builder instance
213	*
214	* @param string $table
215	*
216	* @return DB_Query_Builder
217	*/
218	public function table( string $table ): DB_Query_Builder {
219	return ( new DB_Query_Builder( $this->db ) )->table( $table );
220	}
221
222	/**
223	* Update records in a table
224	*
225	* @param string $table
226	* @param array $data
227	* @param array $where
228	*
229	* @return void
230	*/
231	public function update( string $table, array $data, array $where ) {
232	$table = booktics_get_table_name( $table );
233	$set_values = array();
234	$where_conditions = array();
235	$values = array();
236
237	foreach ( $data as $key => $value ) {
238	$set_values[] = $this->sanitize_identifier( $key ) . ' = %s';
239	$values[] = $value;
240	}
241
242	foreach ( $where as $key => $value ) {
243	$where_conditions[] = $this->sanitize_identifier( $key ) . ' = %s';
244	$values[] = $value;
245	}
246
247	$set_string = implode( ', ', $set_values );
248	$where_string = implode( ' AND ', $where_conditions );
249
250	$sql = "UPDATE $table SET $set_string WHERE $where_string";
251
252	$result = $this->db->query(
253	$this->db->prepare( $sql, ...$values )
254	);
255
256	if ( $result === false ) {
257	throw new RuntimeException(
258	// translators: %s: Records Name
259	sprintf( esc_html__( 'Failed to update records: %s', 'booktics' ), esc_html( $this->db->last_error ) )
260	);
261	}
262	}
263	}
264