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

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

516 lines 16.5 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	$no_pds = !wpda_freemius()->can_use_premium_code() &&
94	self::string_decrypt( $val[0] ) === 'wpdafree.youniquedata.com';
95
96	if ( ! $no_pds ) {
97	$decrypted_databases[ self::string_decrypt( $key ) ] = array(
98	'host' => self::string_decrypt( $val[0] ),
99	'username' => self::string_decrypt( $val[1] ),
100	'password' => self::string_decrypt( $val[2] ),
101	'port' => self::string_decrypt( $val[3] ),
102	'database' => self::string_decrypt( $val[4] ),
103	'disabled' => $val[5],
104	'ssl' => isset( $val[6] ) ? self::string_decrypt( $val[6] ) : 'off',
105	'ssl_key' => isset( $val[7] ) ? self::string_decrypt( $val[7] ) : '',
106	'ssl_cert' => isset( $val[8] ) ? self::string_decrypt( $val[8] ) : '',
107	'ssl_ca' => isset( $val[9] ) ? self::string_decrypt( $val[9] ) : '',
108	'ssl_path' => isset( $val[10] ) ? self::string_decrypt( $val[10] ) : '',
109	'ssl_cipher' => isset( $val[11] ) ? self::string_decrypt( $val[11] ) : '',
110	);
111	}
112	}
113	}
114	self::$remote_databases = $decrypted_databases;
115	}
116	}
117
118	/**
119	* Save remote database connections
120	*
121	* @return void
122	*/
123	public static function save_remote_databases() {
124	self::load_remote_databases();
125
126	$encrypted_databases = array();
127	foreach ( self::$remote_databases as $key => $val ) {
128	$encrypted_databases[ self::string_encrypt( $key ) ] = array(
129	0 => self::string_encrypt( $val['host'] ),
130	1 => self::string_encrypt( $val['username'] ),
131	2 => self::string_encrypt( $val['password'] ),
132	3 => self::string_encrypt( $val['port'] ),
133	4 => self::string_encrypt( $val['database'] ),
134	5 => $val['disabled'],
135	6 => isset( $val['ssl'] ) ? self::string_encrypt( $val['ssl'] ) : 'off',
136	7 => isset( $val['ssl_key'] ) ? self::string_encrypt( $val['ssl_key'] ) : '',
137	8 => isset( $val['ssl_cert'] ) ? self::string_encrypt( $val['ssl_cert'] ) : '',
138	9 => isset( $val['ssl_ca'] ) ? self::string_encrypt( $val['ssl_ca'] ) : '',
139	10 => isset( $val['ssl_path'] ) ? self::string_encrypt( $val['ssl_path'] ) : '',
140	11 => isset( $val['ssl_cipher'] ) ? self::string_encrypt( $val['ssl_cipher'] ) : '',
141	);
142	}
143	update_option( 'wpda_remote_databases', $encrypted_databases );
144	}
145
146	/**
147	* Get all available remote databas econnections
148	*
149	* @param boolean $include_disabled Include disabled remote connections.
150	* @return array\|bool
151	*/
152	public static function get_remote_databases( $include_disabled = false ) {
153	self::load_remote_databases();
154
155	if ( $include_disabled ) {
156	return self::$remote_databases;
157	} else {
158	$exclude_disabled = self::$remote_databases;
159	foreach ( self::$remote_databases as $key => $remote_database ) {
160	if ( $remote_database['disabled'] ) {
161	unset( $exclude_disabled[ $key ] );
162	}
163	}
164	return $exclude_disabled;
165	}
166	}
167
168	/**
169	* Get one specific remote database connection
170	*
171	* @param string $database Remote database connection name.
172	* @param boolean $include_disabled Include disabled remote connections.
173	* @return false\|mixed
174	*/
175	public static function get_remote_database( $database, $include_disabled = false ) {
176	self::load_remote_databases();
177
178	if ( isset( self::$remote_databases[ $database ] ) ) {
179	if ( $include_disabled ) {
180	return self::$remote_databases[ $database ];
181	} else {
182	if ( ! self::$remote_databases[ $database ]['disabled'] ) {
183	return self::$remote_databases[ $database ];
184	} else {
185	return false;
186	}
187	}
188	} else {
189	return false;
190	}
191	}
192
193	/**
194	* Add remote database connection
195	*
196	* @param string $database Database name.
197	* @param string $host Host name.
198	* @param string $user User name.
199	* @param string $passwd Password.
200	* @param string $port Remote port.
201	* @param string $schema Database schema name.
202	* @param string $ssl SSL.
203	* @param string $ssl_key SSL key.
204	* @param string $ssl_cert SSL certificate.
205	* @param string $ssl_ca SSL CA.
206	* @param string $ssl_path SSL path.
207	* @param string $ssl_cipher SSL cipher.
208	* @return bool
209	*/
210	public static function add_remote_database(
211	$database, $host, $user, $passwd, $port, $schema,
212	$ssl, $ssl_key, $ssl_cert, $ssl_ca, $ssl_path, $ssl_cipher
213	) {
214	self::load_remote_databases();
215
216	if ( false === self::get_remote_database( $database ) ) {
217	self::$remote_databases[ $database ] = array(
218	'host' => $host,
219	'username' => $user,
220	'password' => $passwd,
221	'port' => $port,
222	'database' => $schema,
223	'disabled' => false,
224	'ssl' => $ssl,
225	'ssl_key' => $ssl_key,
226	'ssl_cert' => $ssl_cert,
227	'ssl_ca' => $ssl_ca,
228	'ssl_path' => $ssl_path,
229	'ssl_cipher' => $ssl_cipher,
230	);
231	self::save_remote_databases();
232
233	return true;
234	} else {
235	return false;
236	}
237	}
238
239	/**
240	* Delete remote database connection
241	*
242	* @param string $database Remote database connection name.
243	* @return bool
244	*/
245	public static function del_remote_database( $database ) {
246	self::load_remote_databases();
247
248	if ( false === self::get_remote_database( $database, true ) ) {
249	return false;
250	} else {
251	unset( self::$remote_databases[ $database ] );
252	self::save_remote_databases();
253
254	return true;
255	}
256	}
257
258	/**
259	* Update remote connection
260	*
261	* @param string $database Database name.
262	* @param string $host Host name.
263	* @param string $user User name.
264	* @param string $passwd Password.
265	* @param string $port Remote port.
266	* @param string $schema Database schema name.
267	* @param string $disabled Disabled.
268	* @param string $database_old Old remote database connection name.
269	* @param string $ssl SSL.
270	* @param string $ssl_key SSL key.
271	* @param string $ssl_cert SSL certificate.
272	* @param string $ssl_ca SSL CA.
273	* @param string $ssl_path SSL path.
274	* @param string $ssl_cipher SSL cipher.
275	* @return bool
276	*/
277	public static function upd_remote_database(
278	$database, $host, $user, $passwd, $port, $schema, $disabled, $database_old,
279	$ssl, $ssl_key, $ssl_cert, $ssl_ca, $ssl_path, $ssl_cipher
280	) {
281	self::load_remote_databases();
282
283	if ( false !== $database_old && $database !== $database_old ) {
284	self::add_remote_database(
285	$database,
286	$host,
287	$user,
288	$passwd,
289	$port,
290	$schema,
291	$ssl,
292	$ssl_key,
293	$ssl_cert,
294	$ssl_ca,
295	$ssl_path,
296	$ssl_cipher
297	);
298	self::del_remote_database( $database_old );
299
300	return true;
301	} else {
302	if ( false === self::get_remote_database( $database, true ) ) {
303	return false;
304	} else {
305	self::$remote_databases[ $database ] = array(
306	'host' => $host,
307	'username' => $user,
308	'password' => $passwd,
309	'port' => $port,
310	'database' => $schema,
311	'disabled' => $disabled,
312	'ssl' => $ssl,
313	'ssl_key' => $ssl_key,
314	'ssl_cert' => $ssl_cert,
315	'ssl_ca' => $ssl_ca,
316	'ssl_path' => $ssl_path,
317	'ssl_cipher' => $ssl_cipher,
318	);
319	self::save_remote_databases();
320
321	return true;
322	}
323	}
324	}
325
326	public static function change_password( $database, $passwd ) {
327	self::load_remote_databases();
328
329	if ( false === self::get_remote_database( $database, true ) ) {
330	return false;
331	} else {
332	self::$remote_databases[ $database ]['password'] = $passwd;
333	self::save_remote_databases();
334
335	return true;
336	}
337	}
338
339	/**
340	* Get database connection
341	*
342	* Remote schema name starts with prefix "rdb:"
343	*
344	* @param string $schema_name Database (schema) name.
345	*
346	* @return mixed\|\wpdb
347	*/
348	public static function get_db_connection( $schema_name ) {
349	global $wpdb;
350	if ( 'rdb:' === substr( $schema_name, 0, 4 ) ) {
351	// Remote database (other ip\|port).
352	self::load_remote_databases();
353	if ( ! isset( self::$db_connections[ $schema_name ] ) ) {
354	if (
355	isset( self::$remote_databases[ $schema_name ] ) &&
356	! self::$remote_databases[ $schema_name ]['disabled']
357	) {
358	$host = self::$remote_databases[ $schema_name ]['host'];
359	if (
360	'' !== self::$remote_databases[ $schema_name ]['port'] &&
361	'3306' !== self::$remote_databases[ $schema_name ]['port']
362	) {
363	$host .= ':' . self::$remote_databases[ $schema_name ]['port'];
364	}
365	$wpda_remote_database = new WPDADB_WPDB(
366	self::$remote_databases[ $schema_name ]['username'],
367	self::$remote_databases[ $schema_name ]['password'],
368	self::$remote_databases[ $schema_name ]['database'],
369	$host,
370	self::$remote_databases[ $schema_name ]['ssl'],
371	self::$remote_databases[ $schema_name ]['ssl_key'],
372	self::$remote_databases[ $schema_name ]['ssl_cert'],
373	self::$remote_databases[ $schema_name ]['ssl_ca'],
374	self::$remote_databases[ $schema_name ]['ssl_path'],
375	self::$remote_databases[ $schema_name ]['ssl_cipher']
376	);
377	if ( $wpda_remote_database->get_connect_errno() === 0 ) {
378	self::$db_connections[ $schema_name ] = $wpda_remote_database;
379	} else {
380	// Connection failed.
381	self::$db_connections[ $schema_name ] = null;
382	}
383	} else {
384	// Remote schema name not found.
385	self::$db_connections[ $schema_name ] = null;
386	}
387	}
388
389	do_action( 'wpda_dbinit', self::$db_connections[ $schema_name ] );
390	return self::$db_connections[ $schema_name ];
391	} else {
392	// Database runs in local WordPress instance.
393	if ( '' === $schema_name \|\| null === $schema_name \|\| self::iswpdb( $schema_name ) ) {
394	do_action( 'wpda_dbinit', $wpdb );
395	return $wpdb;
396	} else {
397	if ( ! WPDA_Dictionary_Exist::schema_exists( $schema_name ) ) {
398	do_action( 'wpda_dbinit', $wpdb );
399	return $wpdb;
400	}
401
402	if ( ! isset( self::$db_connections[ $schema_name ] ) ) {
403	self::$db_connections[ $schema_name ] = new \wpdb( DB_USER, DB_PASSWORD, $schema_name, DB_HOST );
404	do_action( 'wpda_dbinit', self::$db_connections[ $schema_name ] );
405	}
406
407	return self::$db_connections[ $schema_name ];
408	}
409	}
410	}
411
412	/**
413	* Check if WordPress is installed in the given database schema name
414	*
415	* @param string $schema_name Schema name.
416	* @return bool
417	*/
418	public static function iswpdb( $schema_name ) {
419	global $wpdb;
420	if ( null === self::$lower_case_table_names ) {
421	$lower_case_table_names = $wpdb->get_results( "SHOW VARIABLES LIKE 'lower_case_table_names'", 'ARRAY_N' ); // db call ok; no-cache ok.
422	if ( is_array( $lower_case_table_names ) && isset( $lower_case_table_names[0][1] ) ) {
423	self::$lower_case_table_names = $lower_case_table_names[0][1];
424	} else {
425	self::$lower_case_table_names = 0;
426	}
427	}
428	switch ( self::$lower_case_table_names ) {
429	case 1:
430	case 2:
431	return strtolower( $wpdb->dbname ) === $schema_name;
432	default:
433	return $wpdb->dbname === $schema_name;
434	}
435	}
436
437	/**
438	* Check if a connection with a remote database can be established
439	*
440	* @return \wpdb
441	*/
442	public function check_remote_database_connection() {
443	echo 'Preparing connection...<br/>';
444
445	$host = isset( $_REQUEST['host'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['host'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
446	$user = isset( $_REQUEST['user'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['user'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
447	// Cannot use sanitize_text_field on password field!
448	$passwd = isset( $_REQUEST['passwd'] ) ? // phpcs:ignore WordPress.Security.NonceVerification
449	wp_unslash( $_REQUEST['passwd'] ) : ''; // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized, WordPress.Security.NonceVerification
450	$port = isset( $_REQUEST['port'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['port'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
451	$schema = isset( $_REQUEST['schema'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['schema'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
452
453	// Add ssl.
454	$ssl = isset( $_REQUEST['ssl'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['ssl'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
455	$ssl_key = isset( $_REQUEST['ssl_key'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['ssl_key'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
456	$ssl_cert = isset( $_REQUEST['ssl_cert'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['ssl_cert'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
457	$ssl_ca = isset( $_REQUEST['ssl_ca'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['ssl_ca'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
458	$ssl_path = isset( $_REQUEST['ssl_path'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['ssl_path'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
459	$ssl_cipher = isset( $_REQUEST['ssl_cipher'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['ssl_cipher'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification
460
461	if ( '' === $host \|\| '' === $user \|\| '' === $passwd \|\| '' === $schema ) {
462	return false;
463	}
464
465	if ( '' !== $port && '3306' !== $port ) {
466	$host .= ':' . $port;
467	}
468
469	echo 'Establishing connection...<br/>';
470
471	$wpdadb = new WPDADB_WPDB(
472	$user,
473	$passwd,
474	$schema,
475	$host,
476	$ssl,
477	$ssl_key,
478	$ssl_cert,
479	$ssl_ca,
480	$ssl_path,
481	$ssl_cipher
482	);
483
484	if ( $wpdadb->get_connect_errno() !== 0 ) {
485	echo '
486	<br/>
487	ERROR ' . esc_attr( $wpdadb->get_connect_errno() ) . ' - ' . esc_attr( $wpdadb->get_connect_error() ) . '
488	<br/><br/>
489	<strong>Connection failed!</strong>
490	';
491	} else {
492	$query = $wpdadb->prepare(
493	'select 1 from information_schema.tables where table_schema = %s',
494	array(
495	$schema,
496	)
497	);
498	$wpdadb->get_results( $query, 'ARRAY_A' );
499
500	echo '
501	<br/>
502	Connection established...
503	<br/>
504	Counting tables...
505	<br/>
506	Found ' . esc_attr( $wpdadb->num_rows ) . ' tables on remote host...
507	<br/><br/>
508	<strong>Remote database connection valid!</strong>
509	';
510	}
511	}
512
513	}
514
515	}
516