wp-data-access / WPDataAccess / Data_Dictionary / WPDA_Dictionary_Exist.php

WPDA_Dictionary_Exist.php in WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards 5.5.36, at WPDataAccess/Data_Dictionary/WPDA_Dictionary_Exist.php

456 lines 12.0 KB

Raw Download Zip

1	<?php
2
3	/**
4	* Suppress "error - 0 - No summary was found for this file" on phpdoc generation
5	*
6	* @package WPDataAccess\Data_Dictionary
7	*/
8
9	namespace WPDataAccess\Data_Dictionary {
10
11	use WPDataAccess\Connection\WPDADB;
12	use WPDataAccess\WPDA;
13
14	/**
15	* Class WPDA_Dictionary_Exist
16	*
17	* WP Data Access provides interactive database access. Users might follow the interaction provided by the
18	* plugin, but might as well influence it's behaviour through plugin arguments (hack query strings and post
19	* values). The dynamic nature of WP Data Access allows users to change table and column names that are
20	* communicated from one page to another, which clearly leads to a SQL injection risk. This class deals with
21	* this issue through checks of existence of database object, like tables, columns, ect.
22	*
23	* @author Peter Schulz
24	* @since 1.0.0
25	*/
26	class WPDA_Dictionary_Exist {
27
28	/**
29	* Cached schema names
30	*
31	* @var array
32	*/
33	protected static $schema_name_cache = array();
34
35	/**
36	* Cached table names
37	*
38	* @var array
39	*/
40	protected static $table_name_cache = array();
41
42	/**
43	* Indicator to identify if standard database schema and plugin tables are loaded
44	*
45	* @var bool
46	*/
47	protected static $plugin_schema_and_tables_loaded = false;
48
49	/**
50	* Database schema name
51	*
52	* @var string
53	*/
54	protected $schema_name = '';
55
56	/**
57	* Database table name
58	*
59	* @var string
60	*/
61	protected $table_name = '';
62
63	/**
64	* WPDA_Dictionary_Checks constructor
65	*
66	* For table and column checks this class should be used by instantiating it. The table name must be
67	* provided as an argument. The schema name might be part of the table name provided in which case the
68	* schema name and table name should be seperated by a '.' (dot) as in MySQL notations. If no schema name
69	* is provided the WordPress schema is used as the default.
70	*
71	* @param string $schema_name Database schema name.
72	* @param string $table_name Database table name.
73	*
74	* @since 1.0.0
75	*/
76	public function __construct( $schema_name, $table_name ) {
77	// DO NOT ADD PLUGIN TABLES TO CACHE (causes problems if not found)
78	// self::load_plugin_tables();
79	global $wpdb;
80
81	if ( '' !== $schema_name ) {
82
83	if ( ! isset( self::$schema_name_cache[ $schema_name ] ) ) {
84	// Check if schema name is valid!
85	// Remote check...
86	$rdb = WPDADB::get_remote_database( $schema_name );
87	if ( false !== $rdb ) {
88	self::$schema_name_cache[ $schema_name ] = true;
89	} else {
90	// Local check...
91	// Since a table name (and therefor schema name as well) can be provided on the url we need to check for
92	// sql injection. We'll do this by checking of the schema name exists in our database.
93	$wpdb->get_results(
94	$wpdb->prepare(
95	'
96	SELECT TRUE
97	FROM information_schema.schemata
98	WHERE schema_name = %s
99	',
100	array(
101	$schema_name,
102	)
103	)
104	); // db call ok; no-cache ok.
105	if ( 1 !== $wpdb->num_rows ) {
106	// Schema name doesn't exist! It makes no sense to continue.
107	wp_die( __( 'ERROR: Wrong arguments [schema name not found]', 'wp-data-access' ) );
108	} else {
109	self::$schema_name_cache[ $schema_name ] = true;
110	}
111	}
112	}
113
114	$this->schema_name = $schema_name;
115	$this->table_name = $table_name;
116
117	} else {
118
119	// Table or view is located in the WordPress database schema.
120	$this->schema_name = $wpdb->dbname; // Taken from wpdb: no check needed.
121	$this->table_name = $table_name;
122
123	}
124	}
125
126	/**
127	* Load plugin table name into cache
128	*
129	* Loads the plugin tables into a named array to be cached for fast table named access.
130	*
131	* @since 2.0.11
132	*/
133	protected static function load_plugin_tables() {
134	if ( ! self::$plugin_schema_and_tables_loaded ) {
135	global $wpdb;
136	self::$schema_name_cache[ $wpdb->dbname ] = true;
137
138	$plugin_tables = WPDA::get_wpda_tables();
139	foreach ( $plugin_tables as $plugin_table ) {
140	self::$table_name_cache[ "{$wpdb->dbname}.$plugin_table" ] = true;
141	}
142
143	self::$plugin_schema_and_tables_loaded = true;
144	}
145	}
146
147	/**
148	* Check if function exists
149	*
150	* @param string $schema_name Database schema name.
151	* @param string $function_name Database function name.
152	*
153	* @return bool TRUE = function exists, FALSE = function does not exist.
154	* @since 1.0.0
155	*/
156	public static function function_exists( $schema_name, $function_name ) {
157	return self::routine_exists( $schema_name, $function_name, 'FUNCTION' );
158	}
159
160	/**
161	* Check is routine exists
162	*
163	* @param string $schema_name Database schema name.
164	* @param string $routine_name Database routine name.
165	* @param string $routine_type Database routine type.
166	*
167	* @return bool TRUE = routine exists, FALSE = routine does not exist.
168	* @since 1.0.0
169	*/
170	protected static function routine_exists( $schema_name, $routine_name, $routine_type ) {
171	$wpdadb = WPDADB::get_db_connection( $schema_name );
172	if ( $wpdadb === null ) {
173	return false;
174	}
175
176	$wpdadb->query(
177	$wpdadb->prepare(
178	'
179	SELECT TRUE
180	FROM information_schema.routines
181	WHERE routine_schema = %s
182	AND routine_name = %s
183	AND routine_type = %s
184	',
185	array(
186	$wpdadb->dbname,
187	$routine_name,
188	$routine_type,
189	)
190	)
191	); // db call ok; no-cache ok.
192	$wpdadb->get_results(); // phpcs:ignore Standard.Category.SniffName.ErrorCode
193
194	return ( 1 === $wpdadb->num_rows );
195	}
196
197	/**
198	* Check if procedure exists
199	*
200	* @param string $schema_name Database schema name.
201	* @param string $procedure_name Database procedure name.
202	*
203	* @return bool TRUE = procedure exists, FALSE = procedure does not exist.
204	* @since 1.0.0
205	*/
206	public static function procedure_exists( $schema_name, $procedure_name ) {
207	return self::routine_exists( $schema_name, $procedure_name, 'PROCEDURE' );
208	}
209
210	/**
211	* Check if trigger exists
212	*
213	* @param string $schema_name Database schema name.
214	* @param string $trigger_name Database trigger name.
215	*
216	* @return bool TRUE = trigger exists, FALSE = trigger does not exist.
217	* @since 1.0.0
218	*/
219	public static function trigger_exists( $schema_name, $trigger_name ) {
220	$wpdadb = WPDADB::get_db_connection( $schema_name );
221	if ( $wpdadb === null ) {
222	return false;
223	}
224
225	$wpdadb->query(
226	$wpdadb->prepare(
227	'
228	SELECT TRUE
229	FROM information_schema.triggers
230	WHERE trigger_schema = %s
231	AND trigger_name = %s
232	',
233	array(
234	$wpdadb->dbname,
235	$trigger_name,
236	)
237	)
238	); // db call ok; no-cache ok.
239	$wpdadb->get_results(); // phpcs:ignore Standard.Category.SniffName.ErrorCode
240
241	return ( 1 === $wpdadb->num_rows );
242	}
243
244	/**
245	* Checks if a table exists
246	*
247	* A dynamic SQL statement might use a table or view name which can be supplied as argument on the url. This
248	* is a source of a possible SQL injection attack. In cases where users have the possibility to change a table
249	* or view name argument, we must check if the table or view name exists in the WordPress database to protect
250	* ourselves against SQL injection attacks.
251	*
252	* Before the existence of a table is checked against the MySQL data dictionary, an access check is performed.
253	* If the access check returns FALSE, a query for existence is no longer needed. The access check is performed
254	* prior to the existence check as it quicker and might save us a more expensive action.
255	*
256	* Works with tables as well as views.
257	*
258	* @param bool $use_table_access_settings Indicator whether settings should be checked (default = true).
259	* @param bool $check_back_end TRUE = back-end check, FALSE = font-end check.
260	*
261	* @return bool TRUE means table name is valid.
262	* @since 1.0.0
263	*/
264	public function table_exists( $use_table_access_settings = true, $check_back_end = true ) {
265	if ( isset( self::$table_name_cache[ "$this->schema_name.$this->table_name" ] ) ) {
266	return true;
267	}
268
269	if ( $use_table_access_settings ) {
270	// First check if access is granted.
271	if ( $check_back_end ) {
272	// Check back-end access.
273	$access = WPDA_Dictionary_Access::check_table_access_backend(
274	$this->schema_name,
275	$this->table_name,
276	$done
277	);
278	if ( $done ) {
279	return $access;
280	}
281	if ( ! $access ) {
282	return false;
283	}
284	} else {
285	// Check front-end access.
286	$access = WPDA_Dictionary_Access::check_table_access_frontend(
287	$this->schema_name,
288	$this->table_name,
289	$done
290	);
291	if ( $done ) {
292	return $access;
293	}
294	if ( ! $access ) {
295	return false;
296	}
297	}
298	}
299
300	$wpdadb = WPDADB::get_db_connection( $this->schema_name );
301	if ( $wpdadb === null ) {
302	return false;
303	}
304
305	// In all other cases check if table or view exists.
306	$wpdadb->query(
307	$wpdadb->prepare(
308	'
309	SELECT TRUE
310	FROM information_schema.tables
311	WHERE table_schema = %s
312	AND table_name = %s
313	',
314	array(
315	$wpdadb->dbname,
316	$this->table_name,
317	)
318	)
319	); // db call ok; no-cache ok.
320
321	$wpdadb->get_results(); // phpcs:ignore Standard.Category.SniffName.ErrorCode
322
323	if ( 1 === $wpdadb->num_rows ) {
324	self::$table_name_cache[ "$this->schema_name.$this->table_name" ] = true;
325
326	return true;
327	} else {
328	return false;
329	}
330	}
331
332	/**
333	* Plain check if table exists
334	*
335	* No access control, back-end or front-end checks are taken into account. This method only checks the
336	* existence of the table.
337	*
338	* @return bool
339	*/
340	public function plain_table_exists() {
341	$wpdadb = WPDADB::get_db_connection( $this->schema_name );
342	if ( $wpdadb === null ) {
343	return false;
344	}
345
346	$wpdadb->query(
347	$wpdadb->prepare(
348	'
349	SELECT TRUE
350	FROM information_schema.tables
351	WHERE table_schema = %s
352	AND table_name = %s
353	',
354	array(
355	$wpdadb->dbname,
356	$this->table_name,
357	)
358	)
359	); // db call ok; no-cache ok.
360	$wpdadb->get_results(); // phpcs:ignore Standard.Category.SniffName.ErrorCode
361
362	return ( 1 === $wpdadb->num_rows );
363	}
364
365	/**
366	* Check is a column exists
367	*
368	* Checks whether a column name exists in the WordPress database. Used to prevent SQL injection. Also read
369	* {@see WPDA_Dictionary_Exist::table_exists()}.
370	*
371	* Works with tables as well as views.
372	*
373	* @param string $column_name Database columns name.
374	*
375	* @return bool TRUE = column exists.
376	* @since 1.0.0
377	*/
378	public function column_exists( $column_name ) {
379	$wpdadb = WPDADB::get_db_connection( $this->schema_name );
380	if ( $wpdadb === null ) {
381	return false;
382	}
383
384	$wpdadb->query(
385	$wpdadb->prepare(
386	'
387	SELECT TRUE
388	FROM information_schema.columns
389	WHERE table_schema = %s
390	AND table_name = %s
391	AND column_name = %s
392	',
393	array(
394	$wpdadb->dbname,
395	$this->table_name,
396	$column_name,
397	)
398	)
399	); // db call ok; no-cache ok.
400	$wpdadb->get_results(); // phpcs:ignore Standard.Category.SniffName.ErrorCode
401
402	return ( 1 === $wpdadb->num_rows );
403	}
404
405	/**
406	* Is provided table name a view?
407	*
408	* @return bool TRUE = $this->table_name is a view, FALSE = $this->table_name is a table
409	*/
410	public function is_view() {
411	$wpdadb = WPDADB::get_db_connection( $this->schema_name );
412	if ( $wpdadb === null ) {
413	return false;
414	}
415
416	$wpdadb->query(
417	$wpdadb->prepare(
418	"
419	SELECT TRUE
420	FROM information_schema.tables
421	WHERE table_schema = %s
422	AND table_name = %s
423	AND table_type LIKE '%VIEW'
424	",
425	array(
426	$wpdadb->dbname,
427	$this->table_name,
428	)
429	)
430	); // db call ok; no-cache ok.
431	$wpdadb->get_results(); // phpcs:ignore Standard.Category.SniffName.ErrorCode
432
433	return ( 1 === $wpdadb->num_rows );
434	}
435
436	public static function schema_exists( $schema_name ) {
437	global $wpdb;
438	$wpdb->get_results(
439	$wpdb->prepare(
440	'
441	SELECT TRUE
442	FROM information_schema.schemata
443	WHERE schema_name = %s
444	',
445	array(
446	$schema_name,
447	)
448	)
449	); // db call ok; no-cache ok.
450	return ( 1 === $wpdb->num_rows );
451	}
452
453	}
454
455	}
456