wp-data-access / WPDataAccess / Connection / WPDADB.php

WPDADB.php in WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards 5.2, at WPDataAccess/Connection/WPDADB.php

492 lines 15.7 KB

Raw Download Zip

1	<?php // phpcs:ignore Standard.Category.SniffName.ErrorCode
2	/**
3	* Suppress "error - 0 - No summary was found for this file" on phpdoc generation
4	*
5	* @package WPDataAccess\Connection
6	*/
7
8	namespace WPDataAccess\Connection {
9
10	use WPDataAccess\Data_Dictionary\WPDA_Dictionary_Exist;
11	use WPDataAccess\WPDA;
12
13	/**
14	* Class WPDADB
15	*
16	* Manage local and remote database connections.
17	*
18	* @author Peter Schulz
19	* @since 3.0.0
20	*/
21	class WPDADB {
22
23	/**
24	* Database connections cached per database name (schema_name)
25	*
26	* Remote database are prefixed with "rdb:"
27	*
28	* @var array
29	*/
30	protected static $db_connections = array();
31
32	/**
33	* Remote database access definitions
34	*
35	* @var array\|bool
36	*/
37	protected static $remote_databases = false;
38
39	/**
40	* Stores te lower_case_table_names db value
41	*
42	* @var null\|int
43	*/
44	protected static $lower_case_table_names = null;
45
46	/**
47	* Encrypt a string with the WPDA secret key and iv
48	*
49	* @param string $string String to be encrypted.
50	*
51	* @return string
52	*/
53	public static function string_encrypt( $string ) {
54	$secret_key = WPDA::get_option( WPDA::OPTION_PLUGIN_SECRET_KEY );
55	$secret_iv = WPDA::get_option( WPDA::OPTION_PLUGIN_SECRET_IV );
56
57	$encrypt_method = 'AES-256-CBC';
58	$key = hash( 'sha256', $secret_key );
59	$iv = substr( hash( 'sha256', $secret_iv ), 0, 16 );
60
61	return base64_encode( openssl_encrypt( $string, $encrypt_method, $key, 0, $iv ) ); // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_encode
62	}
63
64	/**
65	* Decrypt a string with the WPDA secret key and iv
66	*
67	* @param string $string String to be decrypted.
68	*
69	* @return string
70	*/
71	public static function string_decrypt( $string ) {
72	$secret_key = WPDA::get_option( WPDA::OPTION_PLUGIN_SECRET_KEY );
73	$secret_iv = WPDA::get_option( WPDA::OPTION_PLUGIN_SECRET_IV );
74
75	$encrypt_method = 'AES-256-CBC';
76	$key = hash( 'sha256', $secret_key );
77	$iv = substr( hash( 'sha256', $secret_iv ), 0, 16 );
78
79	return openssl_decrypt( base64_decode( $string ), $encrypt_method, $key, 0, $iv ); // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_decode
80	}
81
82	/**
83	* Load remote database connections into memory
84	*
85	* @return void
86	*/
87	public static function load_remote_databases() {
88	if ( false === self::$remote_databases ) {
89	$decrypted_databases = array();
90	$encrypted_databases = get_option( 'wpda_remote_databases' );
91	if ( false !== $encrypted_databases ) {
92	foreach ( $encrypted_databases as $key => $val ) {
93	$decrypted_databases[ self::string_decrypt( $key ) ] = array(
94	'host' => self::string_decrypt( $val[0] ),
95	'username' => self::string_decrypt( $val[1] ),
96	'password' => self::string_decrypt( $val[2] ),
97	'port' => self::string_decrypt( $val[3] ),
98	'database' => self::string_decrypt( $val[4] ),
99	'disabled' => $val[5],
100	'ssl' => isset( $val[6] ) ? self::string_decrypt( $val[6] ) : 'off',
101	'ssl_key' => isset( $val[7] ) ? self::string_decrypt( $val[7] ) : '',
102	'ssl_cert' => isset( $val[8] ) ? self::string_decrypt( $val[8] ) : '',
103	'ssl_ca' => isset( $val[9] ) ? self::string_decrypt( $val[9] ) : '',
104	'ssl_path' => isset( $val[10] ) ? self::string_decrypt( $val[10] ) : '',
105	'ssl_cipher' => isset( $val[11] ) ? self::string_decrypt( $val[11] ) : '',
106	);
107	}
108	}
109	self::$remote_databases = $decrypted_databases;
110	}
111	}
112
113	/**
114	* Save remote database connections
115	*
116	* @return void
117	*/
118	public static function save_remote_databases() {
119	self::load_remote_databases();
120
121	$encrypted_databases = array();
122	foreach ( self::$remote_databases as $key => $val ) {
123	$encrypted_databases[ self::string_encrypt( $key ) ] = array(
124	0 => self::string_encrypt( $val['host'] ),
125	1 => self::string_encrypt( $val['username'] ),
126	2 => self::string_encrypt( $val['password'] ),
127	3 => self::string_encrypt( $val['port'] ),
128	4 => self::string_encrypt( $val['database'] ),
129	5 => $val['disabled'],
130	6 => isset( $val['ssl'] ) ? self::string_encrypt( $val['ssl'] ) : 'off',
131	7 => isset( $val['ssl_key'] ) ? self::string_encrypt( $val['ssl_key'] ) : '',
132	8 => isset( $val['ssl_cert'] ) ? self::string_encrypt( $val['ssl_cert'] ) : '',
133	9 => isset( $val['ssl_ca'] ) ? self::string_encrypt( $val['ssl_ca'] ) : '',
134	10 => isset( $val['ssl_path'] ) ? self::string_encrypt( $val['ssl_path'] ) : '',
135	11 => isset( $val['ssl_cipher'] ) ? self::string_encrypt( $val['ssl_cipher'] ) : '',
136	);
137	}
138	update_option( 'wpda_remote_databases', $encrypted_databases );
139	}
140
141	/**
142	* Get all available remote databas econnections
143	*
144	* @param boolean $include_disabled Include disabled remote connections.
145	* @return array\|bool
146	*/
147	public static function get_remote_databases( $include_disabled = false ) {
148	self::load_remote_databases();
149
150	if ( $include_disabled ) {
151	return self::$remote_databases;
152	} else {
153	$exclude_disabled = self::$remote_databases;
154	foreach ( self::$remote_databases as $key => $remote_database ) {
155	if ( $remote_database['disabled'] ) {
156	unset( $exclude_disabled[ $key ] );
157	}
158	}
159	return $exclude_disabled;
160	}
161	}
162
163	/**
164	* Get one specific remote database connection
165	*
166	* @param string $database Remote database connection name.
167	* @param boolean $include_disabled Include disabled remote connections.
168	* @return false\|mixed
169	*/
170	public static function get_remote_database( $database, $include_disabled = false ) {
171	self::load_remote_databases();
172
173	if ( isset( self::$remote_databases[ $database ] ) ) {
174	if ( $include_disabled ) {
175	return self::$remote_databases[ $database ];
176	} else {
177	if ( ! self::$remote_databases[ $database ]['disabled'] ) {
178	return self::$remote_databases[ $database ];
179	} else {
180	return false;
181	}
182	}
183	} else {
184	return false;
185	}
186	}
187
188	/**
189	* Add remote database connection
190	*
191	* @param string $database Database name.
192	* @param string $host Host name.
193	* @param string $user User name.
194	* @param string $passwd Password.
195	* @param string $port Remote port.
196	* @param string $schema Database schema name.
197	* @param string $ssl SSL.
198	* @param string $ssl_key SSL key.
199	* @param string $ssl_cert SSL certificate.
200	* @param string $ssl_ca SSL CA.
201	* @param string $ssl_path SSL path.
202	* @param string $ssl_cipher SSL cipher.
203	* @return bool
204	*/
205	public static function add_remote_database(
206	$database, $host, $user, $passwd, $port, $schema,
207	$ssl, $ssl_key, $ssl_cert, $ssl_ca, $ssl_path, $ssl_cipher
208	) {
209	self::load_remote_databases();
210
211	if ( false === self::get_remote_database( $database ) ) {
212	self::$remote_databases[ $database ] = array(
213	'host' => $host,
214	'username' => $user,
215	'password' => $passwd,
216	'port' => $port,
217	'database' => $schema,
218	'disabled' => false,
219	'ssl' => $ssl,
220	'ssl_key' => $ssl_key,
221	'ssl_cert' => $ssl_cert,
222	'ssl_ca' => $ssl_ca,
223	'ssl_path' => $ssl_path,
224	'ssl_cipher' => $ssl_cipher,
225	);
226	self::save_remote_databases();
227
228	return true;
229	} else {
230	return false;
231	}
232	}
233
234	/**
235	* Delete remote database connection
236	*
237	* @param string $database Remote database connection name.
238	* @return bool
239	*/
240	public static function del_remote_database( $database ) {
241	self::load_remote_databases();
242
243	if ( false === self::get_remote_database( $database, true ) ) {
244	return false;
245	} else {
246	unset( self::$remote_databases[ $database ] );
247	self::save_remote_databases();
248
249	return true;
250	}
251	}
252
253	/**
254	* Update remote connection
255	*
256	* @param string $database Database name.
257	* @param string $host Host name.
258	* @param string $user User name.
259	* @param string $passwd Password.
260	* @param string $port Remote port.
261	* @param string $schema Database schema name.
262	* @param string $disabled Disabled.
263	* @param string $database_old Old remote database connection name.
264	* @param string $ssl SSL.
265	* @param string $ssl_key SSL key.
266	* @param string $ssl_cert SSL certificate.
267	* @param string $ssl_ca SSL CA.
268	* @param string $ssl_path SSL path.
269	* @param string $ssl_cipher SSL cipher.
270	* @return bool
271	*/
272	public static function upd_remote_database(
273	$database, $host, $user, $passwd, $port, $schema, $disabled, $database_old,
274	$ssl, $ssl_key, $ssl_cert, $ssl_ca, $ssl_path, $ssl_cipher
275	) {
276	self::load_remote_databases();
277
278	if ( false !== $database_old && $database !== $database_old ) {
279	self::add_remote_database(
280	$database,
281	$host,
282	$user,
283	$passwd,
284	$port,
285	$schema,
286	$ssl,
287	$ssl_key,
288	$ssl_cert,
289	$ssl_ca,
290	$ssl_path,
291	$ssl_cipher
292	);
293	self::del_remote_database( $database_old );
294
295	return true;
296	} else {
297	if ( false === self::get_remote_database( $database, true ) ) {
298	return false;
299	} else {
300	self::$remote_databases[ $database ] = array(
301	'host' => $host,
302	'username' => $user,
303	'password' => $passwd,
304	'port' => $port,
305	'database' => $schema,
306	'disabled' => $disabled,
307	'ssl' => $ssl,
308	'ssl_key' => $ssl_key,
309	'ssl_cert' => $ssl_cert,
310	'ssl_ca' => $ssl_ca,
311	'ssl_path' => $ssl_path,
312	'ssl_cipher' => $ssl_cipher,
313	);
314	self::save_remote_databases();
315
316	return true;
317	}
318	}
319	}
320
321	/**
322	* Get database connection
323	*
324	* Remote schema name starts with prefix "rdb:"
325	*
326	* @param string $schema_name Database (schema) name.
327	*
328	* @return mixed\|\wpdb
329	*/
330	public static function get_db_connection( $schema_name ) {
331	global $wpdb;
332	if ( 'rdb:' === substr( $schema_name, 0, 4 ) ) {
333	// Remote database (other ip\|port).
334	self::load_remote_databases();
335	if ( ! isset( self::$db_connections[ $schema_name ] ) ) {
336	if (
337	isset( self::$remote_databases[ $schema_name ] ) &&
338	! self::$remote_databases[ $schema_name ]['disabled']
339	) {
340	$host = self::$remote_databases[ $schema_name ]['host'];
341	if (
342	'' !== self::$remote_databases[ $schema_name ]['port'] &&
343	'3306' !== self::$remote_databases[ $schema_name ]['port']
344	) {
345	$host .= ':' . self::$remote_databases[ $schema_name ]['port'];
346	}
347	$wpda_remote_database = new WPDADB_WPDB(
348	self::$remote_databases[ $schema_name ]['username'],
349	self::$remote_databases[ $schema_name ]['password'],
350	self::$remote_databases[ $schema_name ]['database'],
351	$host,
352	self::$remote_databases[ $schema_name ]['ssl'],
353	self::$remote_databases[ $schema_name ]['ssl_key'],
354	self::$remote_databases[ $schema_name ]['ssl_cert'],
355	self::$remote_databases[ $schema_name ]['ssl_ca'],
356	self::$remote_databases[ $schema_name ]['ssl_path'],
357	self::$remote_databases[ $schema_name ]['ssl_cipher']
358	);
359	if ( $wpda_remote_database->get_connect_errno() === 0 ) {
360	self::$db_connections[ $schema_name ] = $wpda_remote_database;
361	} else {
362	// Connection failed.
363	self::$db_connections[ $schema_name ] = null;
364	}
365	} else {
366	// Remote schema name not found.
367	self::$db_connections[ $schema_name ] = null;
368	}
369	}
370
371	return self::$db_connections[ $schema_name ];
372	} else {
373	// Database runs in local WordPress instance.
374	if ( '' === $schema_name \|\| null === $schema_name \|\| self::iswpdb( $schema_name ) ) {
375	return $wpdb;
376	} else {
377	if ( ! WPDA_Dictionary_Exist::schema_exists( $schema_name ) ) {
378	return $wpdb;
379	}
380
381	if ( ! isset( self::$db_connections[ $schema_name ] ) ) {
382	self::$db_connections[ $schema_name ] = new \wpdb( DB_USER, DB_PASSWORD, $schema_name, DB_HOST );
383	}
384
385	return self::$db_connections[ $schema_name ];
386	}
387	}
388	}
389
390	/**
391	* Check if WordPress is installed in the given database schema name
392	*
393	* @param string $schema_name Schema name.
394	* @return bool
395	*/
396	public static function iswpdb( $schema_name ) {
397	global $wpdb;
398	if ( null === self::$lower_case_table_names ) {
399	$lower_case_table_names = $wpdb->get_results( "SHOW VARIABLES LIKE 'lower_case_table_names'", 'ARRAY_N' ); // db call ok; no-cache ok.
400	if ( is_array( $lower_case_table_names ) && isset( $lower_case_table_names[0][1] ) ) {
401	self::$lower_case_table_names = $lower_case_table_names[0][1];
402	} else {
403	self::$lower_case_table_names = 0;
404	}
405	}
406	switch ( self::$lower_case_table_names ) {
407	case 1:
408	case 2:
409	return strtolower( $wpdb->dbname ) === $schema_name;
410	default:
411	return $wpdb->dbname === $schema_name;
412	}
413	}
414
415	/**
416	* Check if a connection with a remote database can be established
417	*
418	* @return \wpdb
419	*/
420	public function check_remote_database_connection() {
421	echo 'Preparing connection...<br/>';
422
423	$host = isset( $_REQUEST['host'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['host'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
424	$user = isset( $_REQUEST['user'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['user'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
425	$passwd = isset( $_REQUEST['passwd'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['passwd'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
426	$port = isset( $_REQUEST['port'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['port'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
427	$schema = isset( $_REQUEST['schema'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['schema'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
428
429	// Add ssl.
430	$ssl = isset( $_REQUEST['ssl'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['ssl'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
431	$ssl_key = isset( $_REQUEST['ssl_key'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['ssl_key'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
432	$ssl_cert = isset( $_REQUEST['ssl_cert'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['ssl_cert'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
433	$ssl_ca = isset( $_REQUEST['ssl_ca'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['ssl_ca'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
434	$ssl_path = isset( $_REQUEST['ssl_path'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['ssl_path'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
435	$ssl_cipher = isset( $_REQUEST['ssl_cipher'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['ssl_cipher'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
436
437	if ( '' === $host \|\| '' === $user \|\| '' === $passwd \|\| '' === $schema ) {
438	return false;
439	}
440
441	if ( '' !== $port && '3306' !== $port ) {
442	$host .= ':' . $port;
443	}
444
445	echo 'Establishing connection...<br/>';
446
447	$wpdadb = new WPDADB_WPDB(
448	$user,
449	$passwd,
450	$schema,
451	$host,
452	$ssl,
453	$ssl_key,
454	$ssl_cert,
455	$ssl_ca,
456	$ssl_path,
457	$ssl_cipher
458	);
459
460	if ( $wpdadb->get_connect_errno() !== 0 ) {
461	echo '
462	<br/>
463	ERROR ' . esc_attr( $wpdadb->get_connect_errno() ) . ' - ' . esc_attr( $wpdadb->get_connect_error() ) . '
464	<br/><br/>
465	<strong>Connection failed!</strong>
466	';
467	} else {
468	$query = $wpdadb->prepare(
469	'select 1 from information_schema.tables where table_schema = %s',
470	array(
471	$schema,
472	)
473	);
474	$wpdadb->get_results( $query, 'ARRAY_A' );
475
476	echo '
477	<br/>
478	Connection established...
479	<br/>
480	Counting tables...
481	<br/>
482	Found ' . esc_attr( $wpdadb->num_rows ) . ' tables on remote host...
483	<br/><br/>
484	<strong>Remote database connection valid!</strong>
485	';
486	}
487	}
488
489	}
490
491	}
492