gdpr-framework / src / Database / WordpressDatabase.php

WordpressDatabase.php in The GDPR Framework By Data443 trunk, at src/Database/WordpressDatabase.php

292 lines 7.1 KB

Raw Download Zip

1	<?php
2
3	namespace Codelight\GDPR\Database;
4
5	if (!defined('ABSPATH')) {
6	exit;
7	}
8
9	/**
10	* DB base class
11	* https://pippinsplugins.com/custom-database-api-the-basic-api-class/
12	*/
13	abstract class WordpressDatabase
14	{
15
16	/**
17	* The name of our database table
18	*
19	* @access public
20	* @since 2.1
21	*/
22	public $tableName;
23
24	/**
25	* The version of our database table
26	*
27	* @access public
28	* @since 2.1
29	*/
30	public $version;
31
32	/**
33	* The name of the primary column
34	*
35	* @access public
36	* @since 2.1
37	*/
38	public $primaryKey;
39
40	/**
41	* Get things started
42	*
43	* @access public
44	* @since 2.1
45	*/
46	public function __construct()
47	{
48	}
49
50	/**
51	* Whitelist of columns
52	*
53	* @access public
54	* @since 2.1
55	* @return array
56	*/
57	public function getColumns()
58	{
59	return [];
60	}
61
62	/**
63	* Default column values
64	*
65	* @access public
66	* @since 2.1
67	* @return array
68	*/
69	public function getColumnDefaults()
70	{
71	return [];
72	}
73
74	/**
75	* Retrieve a row by the primary key
76	*
77	* @access public
78	* @since 2.1
79	* @return object
80	*/
81	public function get($row_id)
82	{
83	global $wpdb;
84
85	return $wpdb->get_row($wpdb->prepare(
86	"SELECT * FROM $this->tableName WHERE $this->primaryKey = %s LIMIT 1;", $row_id
87	));
88	}
89
90	/**
91	* Retrieve a row by a specific column / value
92	*
93	* @access public
94	* @since 2.1
95	* @return object
96	*/
97	public function getBy($column, $row_id)
98	{
99	global $wpdb;
100
101	// Security fix (SECURITY-AUDIT.md Finding 4): esc_sql() only escapes
102	// quote/backslash characters -- it does not protect a value used in
103	// an identifier position (e.g. a column name), which can carry an
104	// injection payload that needs no quotes at all (UNION, subqueries,
105	// comments). $wpdb->prepare()'s %s only protects the value
106	// placeholder, not $column. Whitelist against the known columns
107	// instead of relying on esc_sql() here.
108	if (!array_key_exists($column, $this->getColumns())) {
109	return null;
110	}
111
112	return $wpdb->get_row($wpdb->prepare(
113	"SELECT * FROM $this->tableName WHERE $column = %s LIMIT 1;", $row_id
114	));
115	}
116
117	/**
118	* Retrieve a specific column's value by the primary key
119	*
120	* @access public
121	* @since 2.1
122	* @return string
123	*/
124	public function getColumn($column, $row_id)
125	{
126	global $wpdb;
127
128	// Security fix (SECURITY-AUDIT.md Finding 4): see getBy() above --
129	// whitelist the column name instead of relying on esc_sql(), which
130	// does not protect an identifier position.
131	if (!array_key_exists($column, $this->getColumns())) {
132	return null;
133	}
134
135	return $wpdb->get_var($wpdb->prepare(
136	"SELECT $column FROM $this->tableName WHERE $this->primaryKey = %s LIMIT 1;", $row_id
137	));
138	}
139
140	/**
141	* Retrieve a specific column's value by the the specified column / value
142	*
143	* @access public
144	* @since 2.1
145	* @return string
146	*/
147	public function getColumnBy($column, $column_where, $column_value)
148	{
149	global $wpdb;
150
151	// Security fix (SECURITY-AUDIT.md Finding 4): see getBy() above --
152	// whitelist both column names instead of relying on esc_sql(),
153	// which does not protect an identifier position.
154	$columns = $this->getColumns();
155	if (!array_key_exists($column, $columns) \|\| !array_key_exists($column_where, $columns)) {
156	return null;
157	}
158
159	return $wpdb->get_var($wpdb->prepare(
160	"SELECT $column FROM $this->tableName WHERE $column_where = %s LIMIT 1;", $column_value
161	));
162	}
163
164	/**
165	* Insert a new row
166	*
167	* @access public
168	* @since 2.1
169	* @return int
170	*/
171	public function insert($data, $type = '')
172	{
173	global $wpdb;
174
175	// Set default values
176	$data = wp_parse_args($data, $this->getColumnDefaults());
177
178	do_action('bs_db_pre_insert_' . $type, $data);
179
180	// Initialise column format array
181	$columnFormats = $this->getColumns();
182
183	// Force fields to lower case
184	$data = array_change_key_case($data);
185
186	// White list columns
187	$data = array_intersect_key($data, $columnFormats);
188
189	// Reorder $columnFormats to match the order of columns given in $data
190	$data_keys = array_keys($data);
191	$columnFormats = array_merge(array_flip($data_keys), $columnFormats);
192
193	$wpdb->insert($this->tableName, $data, $columnFormats);
194
195	do_action('bs_db_post_insert_' . $type, $wpdb->insert_id, $data);
196
197	return $wpdb->insert_id;
198	}
199
200	/**
201	* Update a row
202	*
203	* @access public
204	* @since 2.1
205	* @return bool
206	*/
207	public function update($row_id, $data = [], $where = '')
208	{
209	global $wpdb;
210
211	// Row ID must be positive integer
212	$row_id = absint($row_id);
213
214	if (empty($row_id)) {
215	return false;
216	}
217
218	if (empty($where)) {
219	$where = $this->primaryKey;
220	}
221
222	// Initialise column format array
223	$columnFormats = $this->getColumns();
224
225	// Force fields to lower case
226	$data = array_change_key_case($data);
227
228	// White list columns
229	$data = array_intersect_key($data, $columnFormats);
230
231	// Reorder $columnFormats to match the order of columns given in $data
232	$data_keys = array_keys($data);
233	$columnFormats = array_merge(array_flip($data_keys), $columnFormats);
234
235	if (false === $wpdb->update($this->tableName, $data, [$where => $row_id], $columnFormats)) {
236	return false;
237	}
238
239	return true;
240	}
241
242
243	/**
244	* Delete a row identified by the primary key
245	*
246	* @access public
247	* @since 2.1
248	* @return bool
249	*/
250	public function delete($row_id = 0)
251	{
252	global $wpdb;
253
254	// Row ID must be positive integer
255	$row_id = absint($row_id);
256
257	if (empty($row_id)) {
258	return false;
259	}
260
261	if (false === $wpdb->query($wpdb->prepare(
262	"DELETE FROM $this->tableName WHERE $this->primaryKey = %d", $row_id
263	))) {
264	return false;
265	}
266
267	return true;
268	}
269
270	/**
271	* Check if the given table exists
272	*
273	* @since 2.4
274	* @param string $table The table name
275	* @return bool If the table name exists
276	*/
277	public function tableExists($table)
278	{
279	global $wpdb;
280	$table = sanitize_text_field($table);
281
282	// Security fix (SECURITY-AUDIT.md Finding 4): %s inside literal
283	// quotes is a $wpdb->prepare() misuse -- prepare() already quotes
284	// %s placeholders itself, so the literal quotes here just changed
285	// what ended up being matched rather than adding protection.
286	return $wpdb->get_var($wpdb->prepare(
287	'SHOW TABLES LIKE %s', $table
288	)) === $table;
289	}
290
291
292	}