← All changes | WPDataAccess/API/WPDA_Table.php +507 -154 5.5.4 → 5.5.84 View file →

		@@ -25,8 +25,10 @@
25	25	'lessThan',
26	26	'lessThanOrEqualTo'
27	27	);
28	28
	29	+ const RELATIONTABLEPREFIX = 'relationTableColumn___';
	30	+
29	31	public function register_rest_routes() {
30	32	register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/meta', array(
31	33	'methods' => array('POST'),
32	34	'callback' => array($this, 'table_meta'),
		@@ -57,8 +59,26 @@
57	59	'sorting' => $this->get_param( 'sorting' ),
58	60	'row_count' => $this->get_param( 'row_count' ),
59	61	'row_count_estimate' => $this->get_param( 'row_count_estimate' ),
60	62	'media' => $this->get_param( 'media' ),
	63	+ 'client_side' => $this->get_param( 'client_side' ),
	64	+ 'global_search' => array(
	65	+ 'required' => false,
	66	+ 'type' => 'mixed',
	67	+ 'description' => __( 'Global search', 'wp-data-access' ),
	68	+ 'sanitize_callback' => function ( $param ) {
	69	+ $global_search = array();
	70	+ foreach ( $param as $key => $value ) {
	71	+ if ( $key === 's' || $key === 'c' ) {
	72	+ $global_search[sanitize_text_field( wp_unslash( $key ) )] = sanitize_text_field( wp_unslash( $value ) );
	73	+ }
	74	+ }
	75	+ return $global_search;
	76	+ },
	77	+ 'validate_callback' => function ( $param ) {
	78	+ return is_array( $param ) && isset( $param['s'], $param['c'] );
	79	+ },
	80	+ ),
61	81	),
62	82	) );
63	83	register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/get', array(
64	84	'methods' => array('GET', 'POST'),
		@@ -116,9 +136,9 @@
116	136
117	137	/**
118	138	* Get table meta info.
119	139	*
120		- * @param WP_REST_Request $request Rest API request.
	140	+ * @param \WP_REST_Request $request Rest API request.
121	141	* @return \WP_Error|\WP_REST_Response
122	142	*/
123	143	public function table_meta( $request ) {
124	144	$dbs = $request->get_param( 'dbs' );
		@@ -145,9 +165,9 @@
145	165
146	166	/**
147	167	* Database table query using the full primary key. Must return exactly one row.
148	168	*
149		- * @param WP_REST_Request $request Rest API request.
	169	+ * @param \WP_REST_Request $request Rest API request.
150	170	* @return \WP_Error|\WP_REST_Response
151	171	*/
152	172	public function table_get( $request ) {
153	173	$dbs = $request->get_param( 'dbs' );
		@@ -180,9 +200,9 @@
180	200
181	201	/**
182	202	* Insert one row.
183	203	*
184		- * @param WP_REST_Request $request Rest API request.
	204	+ * @param \WP_REST_Request $request Rest API request.
185	205	* @return \WP_Error|\WP_REST_Response
186	206	*/
187	207	public function table_insert( $request ) {
188	208	$dbs = $request->get_param( 'dbs' );
		@@ -209,9 +229,9 @@
209	229
210	230	/**
211	231	* Update uses primary key. Must return exactly one row.
212	232	*
213		- * @param WP_REST_Request $request Rest API request.
	233	+ * @param \WP_REST_Request $request Rest API request.
214	234	* @return \WP_Error|\WP_REST_Response
215	235	*/
216	236	public function table_update( $request ) {
217	237	$dbs = $request->get_param( 'dbs' );
		@@ -244,9 +264,9 @@
244	264
245	265	/**
246	266	* Delete uses primary key. Must return exactly one row.
247	267	*
248		- * @param WP_REST_Request $request Rest API request.
	268	+ * @param \WP_REST_Request $request Rest API request.
249	269	* @return \WP_Error|\WP_REST_Response
250	270	*/
251	271	public function table_delete( $request ) {
252	272	$dbs = $request->get_param( 'dbs' );
		@@ -273,12 +293,13 @@
273	293
274	294	/**
275	295	* Database table query to populate a list of values for a specific table/column.
276	296	*
277		- * @param WP_REST_Request $request Rest API request.
	297	+ * @param \WP_REST_Request $request Rest API request.
278	298	* @return \WP_Error|\WP_REST_Response
279	299	*/
280	300	public function table_lov( $request ) {
	301	+ return null;
281	302	}
282	303
283	304	/**
284	305	* Database table query.
		@@ -284,9 +305,9 @@
284	305	* Database table query.
285	306	*
286	307	* Supports: searching, ordering and pagination.
287	308	*
288		- * @param WP_REST_Request $request Rest API request.
	309	+ * @param \WP_REST_Request $request Rest API request.
289	310	* @return \WP_Error|\WP_REST_Response
290	311	*/
291	312	public function table_select( $request ) {
292	313	$dbs = $request->get_param( 'dbs' );
		@@ -296,12 +317,15 @@
296	317	$page_size = $request->get_param( 'page_size' );
297	318	$search = $request->get_param( 'search' );
298	319	$search_columns = $request->get_param( 'search_columns' );
299	320	$search_column_fns = $request->get_param( 'search_column_fns' );
	321	+ $search_data_types = $request->get_param( 'search_data_types' );
300	322	$sorting = $request->get_param( 'sorting' );
301	323	$row_count = $request->get_param( 'row_count' );
302	324	$row_count_estimate = $request->get_param( 'row_count_estimate' );
303	325	$media = $request->get_param( 'media' );
	326	+ $client_side = '1' === $request->get_param( 'client_side' );
	327	+ $global_search = $request->get_param( 'global_search' );
304	328	if ( $this->check_table_access(
305	329	$dbs,
306	330	$tbl,
307	331	$request,
		@@ -319,9 +343,19 @@
319	343	$search_column_fns,
320	344	$sorting,
321	345	$row_count,
322	346	$row_count_estimate,
323		- $media
	347	+ $media,
	348	+ '',
	349	+ '',
	350	+ array(),
	351	+ array(),
	352	+ array(),
	353	+ $search_data_types,
	354	+ $client_side,
	355	+ array(),
	356	+ array(),
	357	+ $global_search
324	358	);
325	359	} else {
326	360	if ( 'rest_cookie_invalid_nonce' === $msg ) {
327	361	return $this->invalid_nonce();
		@@ -347,12 +381,20 @@
347	381	public function lov(
348	382	$dbs,
349	383	$tbl,
350	384	$column_name,
351		- $search,
352		- $search_columns,
353		- $search_column_fns
	385	+ $cascade = false,
	386	+ $default_where = '',
	387	+ $search = '',
	388	+ $column_names = array(),
	389	+ $search_columns = array(),
	390	+ $search_column_fns = array(),
	391	+ $lookups = array(),
	392	+ $md = array(),
	393	+ $m2m_relationship = array(),
	394	+ $search_data_types = array()
354	395	) {
	396	+ return null;
355	397	}
356	398
357	399	public function lookup(
358	400	$dbs,
		@@ -359,9 +401,21 @@
359	401	$tbl,
360	402	$column_key,
361	403	$column_value,
362	404	$column_dynamic_values,
363		- $default_where
	405	+ $default_where,
	406	+ $cascade = false,
	407	+ $cascade_table = '',
	408	+ $cascade_column = '',
	409	+ $cascade_where = '',
	410	+ $search = '',
	411	+ $column_names = array(),
	412	+ $search_columns = array(),
	413	+ $search_column_fns = array(),
	414	+ $lookups = array(),
	415	+ $md = array(),
	416	+ $m2m_relationship = array(),
	417	+ $search_data_types = array()
364	418	) {
365	419	$wpdadb = WPDADB::get_db_connection( $dbs );
366	420	if ( null === $wpdadb ) {
367	421	// Error connecting.
		@@ -370,8 +424,9 @@
370	424	));
371	425	} else {
372	426	// Connected, perform queries.
373	427	$suppress = $wpdadb->suppress_errors( true );
	428	+ $subquery = '';
374	429	$where = '';
375	430	if ( '' !== trim( $default_where ) ) {
376	431	if ( 'where' !== strtolower( substr( trim( $default_where ), 0, 5 ) ) ) {
377	432	$where = "where {$default_where}";
		@@ -380,23 +435,69 @@
380	435	}
381	436	}
382	437	$dynamic_where = array();
383	438	if ( is_array( $column_dynamic_values ) && 0 < count( $column_dynamic_values ) ) {
	439	+ $dynamic_allowed = array();
	440	+ $dynamic_table = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
	441	+ $dynamic_columns = $dynamic_table->get_table_columns();
	442	+ foreach ( $dynamic_columns as $column ) {
	443	+ if ( isset( $column['column_name'] ) ) {
	444	+ $dynamic_allowed[] = $column['column_name'];
	445	+ }
	446	+ }
384	447	foreach ( $column_dynamic_values as $key => $value ) {
385		- $dynamic_where[] = $wpdadb->prepare( " `{$key}` = %s ", $value );
	448	+ if ( !in_array( $key, $dynamic_allowed, true ) ) {
	449	+ continue;
	450	+ }
	451	+ $dynamic_where[] = $wpdadb->prepare( " %i = %s ", array($key, $value) );
386	452	}
387	453	$where .= (( '' === $where ? ' where ' : ' and ' )) . ' (' . implode( ' and ', $dynamic_where ) . ') ';
388	454	}
	455	+ $column_count = ( '' === $subquery ? '' : ", stats.total_rows as 'count'" );
389	456	if ( strpos( $column_value, ',' ) !== false ) {
390	457	$columns = explode( ',', $column_value );
391		- $dataset = $wpdadb->get_results( $wpdadb->prepare( "\n\t\t\t\t\t\t\tselect distinct `%1s` as 'key'\n\t\t\t\t\t\t\t, `%1s`\n\t\t\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t\t\t{$where}\n\t\t\t\t\t\t\torder by 2\n\t\t\t\t\t\t", array($column_key, implode( '`,`', $columns ), $tbl) ), 'OBJECT' );
	458	+ $columns = array_map( function ( $column ) use($wpdadb, $tbl) {
	459	+ return $wpdadb->prepare( "`%1s`.`%1s`", [$tbl, $column] );
	460	+ }, $columns );
	461	+ $sql = $wpdadb->prepare( "\n\t\t\t\t\t\t\tselect distinct `%1s`.`%1s` as 'key'\n\t\t\t\t\t\t\t, %1s\n\t\t\t\t\t\t\t{$column_count}\n\t\t\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t\t", array(
	462	+ $tbl,
	463	+ $column_key,
	464	+ implode( ',', $columns ),
	465	+ $tbl
	466	+ ) );
392	467	} else {
393		- $dataset = $wpdadb->get_results( $wpdadb->prepare( "\n\t\t\t\t\t\t\tselect distinct `%1s` as 'key'\n\t\t\t\t\t\t\t, `%1s` as 'value' \n\t\t\t\t\t\t\tfrom `%1s` \n\t\t\t\t\t\t\t{$where}\n\t\t\t\t\t\t\torder by 2\n\t\t\t\t\t\t", array($column_key, $column_value, $tbl) ), 'OBJECT' );
	468	+ $sql = $wpdadb->prepare( "\n\t\t\t\t\t\t\tselect distinct `%1s`.`%1s` as 'key'\n\t\t\t\t\t\t\t, `%1s`.`%1s` as 'value'\n\t\t\t\t\t\t\t{$column_count}\n\t\t\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t\t", array(
	469	+ $tbl,
	470	+ $column_key,
	471	+ $tbl,
	472	+ $column_value,
	473	+ $tbl
	474	+ ) );
394	475	}
	476	+ $orderby = ' order by 2 ';
	477	+ $sql .= " {$where} {$orderby} ";
	478	+ // $where and $orderby already sanitized and prepared
	479	+ $dataset = $wpdadb->get_results( $sql, 'OBJECT' );
395	480	$wpdadb->suppress_errors( $suppress );
396	481	// Send response.
397	482	if ( '' === $wpdadb->last_error ) {
398		- return $this->WPDA_Rest_Response( '', $dataset );
	483	+ // Prepare debug info.
	484	+ if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
	485	+ $debug = array(
	486	+ 'debug' => array(
	487	+ 'sql' => preg_replace( "/\\s+/", " ", $sql ),
	488	+ 'where' => $where ?? '',
	489	+ ),
	490	+ );
	491	+ } else {
	492	+ $debug = null;
	493	+ }
	494	+ // Add context node to response.
	495	+ $context = array();
	496	+ if ( isset( $debug['debug'] ) && 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
	497	+ $context['debug'] = $debug['debug'];
	498	+ }
	499	+ return $this->WPDA_Rest_Response( '', $dataset, $context );
399	500	} else {
400	501	return new \WP_Error('error', $wpdadb->last_error, array(
401	502	'status' => 420,
402	503	));
		@@ -417,11 +518,12 @@
417	518	public function get(
418	519	$dbs,
419	520	$tbl,
420	521	$primary_key,
421		- $media_columns,
	522	+ $media_columns = array(),
422	523	$column_names = array(),
423		- $default_where = ''
	524	+ $default_where = '',
	525	+ $docs = array()
424	526	) {
425	527	$wpdadb = WPDADB::get_db_connection( $dbs );
426	528	if ( null === $wpdadb ) {
427	529	// Error connecting.
		@@ -442,19 +544,28 @@
442	544	} else {
443	545	$where .= " and {$default_where} ";
444	546	}
445	547	}
446		- $selected_columns = '*';
447		- if ( 0 < count( $column_names ) ) {
448		- $selected_columns = '`' . implode( '`,`', array_map( function ( $column_name ) {
449		- return WPDA::remove_backticks( $column_name );
450		- }, $column_names ) ) . '`';
	548	+ // Get table column data types
	549	+ $column_list = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
	550	+ $table_columns = $column_list->get_table_columns();
	551	+ // Prepare selected column list
	552	+ $columns_selected = array();
	553	+ $search_data_types = array();
	554	+ foreach ( $table_columns as $table_column ) {
	555	+ if ( isset( $table_column['column_name'], $table_column['data_type'] ) && (in_array( $table_column['column_name'], $column_names ) || empty( $column_names )) ) {
	556	+ $columns_selected[$table_column['column_name']] = true;
	557	+ $search_data_types[$table_column['column_name']] = $table_column['data_type'];
	558	+ }
451	559	}
452		- $dataset = $wpdadb->get_results( $wpdadb->prepare( "\n\t\t\t\t\t\t\tselect {$selected_columns}\n\t\t\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t\t\t{$where}\n\t\t\t\t\t\t", array($tbl) ), 'ARRAY_A' );
	560	+ $selected_columns = $this->get_selected_columns( $columns_selected, $search_data_types );
	561	+ $sql = $wpdadb->prepare( "\n select {$selected_columns}\n from `%1s`\n {$where}\n ", array($tbl) );
	562	+ $dataset = $wpdadb->get_results( $sql, 'ARRAY_A' );
453	563	// Prepare debug info.
454	564	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
455	565	$debug = array(
456	566	'debug' => array(
	567	+ 'sql' => $sql,
457	568	'where' => $where,
458	569	),
459	570	);
460	571	} else {
		@@ -461,38 +572,37 @@
461	572	$debug = null;
462	573	}
463	574	$wpdadb->suppress_errors( $suppress );
464	575	// Send response.
465		- if ( 0 === count( $dataset ) ) {
466		- return new \WP_Error('error', "No data found", array(
467		- 'status' => 420,
468		- ));
469		- } elseif ( 1 === count( $dataset ) ) {
470		- $media = array();
471		- if ( 0 < count( $media_columns ) ) {
472		- foreach ( $media_columns as $media_column_name => $media_column_type ) {
473		- if ( isset( $dataset[0][$media_column_name] ) ) {
474		- if ( in_array( $media_column_type, [
475		- 'WP-Image',
476		- 'WP-Attachment',
477		- 'WP-Audio',
478		- 'WP-Video'
479		- ] ) ) {
480		- $media[$media_column_name] = WPDA_WP_Media::get_media_url( $dataset[0][$media_column_name] );
481		- }
	576	+ $media = array();
	577	+ if ( is_array( $media_columns ) && 0 < count( $media_columns ) ) {
	578	+ foreach ( $media_columns as $media_column_name => $media_column_type ) {
	579	+ if ( isset( $dataset[0][$media_column_name] ) ) {
	580	+ if ( in_array( $media_column_type, [
	581	+ 'WP-Image',
	582	+ 'WP-Attachment',
	583	+ 'WP-Audio',
	584	+ 'WP-Video'
	585	+ ] ) ) {
	586	+ $media[$media_column_name] = WPDA_WP_Media::get_media_url( $dataset[0][$media_column_name] );
482	587	}
483	588	}
484	589	}
485		- $context = array();
486		- $context['media'] = $media;
487		- if ( isset( $debug['debug'] ) && 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
488		- $context['debug'] = $debug['debug'];
	590	+ }
	591	+ $context = array();
	592	+ // Add media
	593	+ $context['media'] = $media;
	594	+ if ( isset( $debug['debug'] ) && 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
	595	+ $context['debug'] = $debug['debug'];
	596	+ }
	597	+ if ( 0 === count( $dataset ) ) {
	598	+ return $this->WPDA_Rest_Response( 'No data found', $dataset, $context );
	599	+ } else {
	600	+ if ( 1 === count( $dataset ) ) {
	601	+ return $this->WPDA_Rest_Response( '', $dataset, $context );
	602	+ } else {
	603	+ return $this->WPDA_Rest_Response( 'Query returned more than one row', $dataset, $context );
489	604	}
490		- return $this->WPDA_Rest_Response( '', $dataset, $context );
491		- } else {
492		- return new \WP_Error('error', "Invalid arguments", array(
493		- 'status' => 420,
494		- ));
495	605	}
496	606	}
497	607	}
498	608
		@@ -503,8 +613,17 @@
503	613	return new \WP_Error('error', "Error connecting to database {$dbs}", array(
504	614	'status' => 420,
505	615	));
506	616	} else {
	617	+ // Get column default values
	618	+ $column_list = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
	619	+ $table_columns = $column_list->get_table_columns();
	620	+ foreach ( $table_columns as $table_column_type ) {
	621	+ if ( isset( $column_values[$table_column_type['column_name']] ) && $column_values[$table_column_type['column_name']] === $table_column_type['column_default'] ) {
	622	+ // Remove default values if send values equals column default to support defaults using functions
	623	+ unset($column_values[$table_column_type['column_name']]);
	624	+ }
	625	+ }
507	626	// Sanitize column names and values.
508	627	$sanitized_column_values = self::sanitize_column_values( $dbs, $tbl, $column_values );
509	628	if ( false === $sanitized_column_values ) {
510	629	return new \WP_Error('error', "Invalid arguments", array(
		@@ -536,9 +655,11 @@
536	655	$dbs,
537	656	$tbl,
538	657	$primary_key,
539	658	$column_values,
540		- $column_names = array()
	659	+ $column_names = array(),
	660	+ $code_columns = array(),
	661	+ $html_columns = array()
541	662	) {
542	663	$wpdadb = WPDADB::get_db_connection( $dbs );
543	664	if ( null === $wpdadb ) {
544	665	// Error connecting.
		@@ -546,9 +667,15 @@
546	667	'status' => 420,
547	668	));
548	669	} else {
549	670	// Sanitize column names and values.
550		- $sanitized_column_values = self::sanitize_column_values( $dbs, $tbl, $column_values );
	671	+ $sanitized_column_values = self::sanitize_column_values(
	672	+ $dbs,
	673	+ $tbl,
	674	+ $column_values,
	675	+ $code_columns,
	676	+ $html_columns
	677	+ );
551	678	if ( false === $sanitized_column_values ) {
552	679	return new \WP_Error('error', "Invalid arguments", array(
553	680	'status' => 420,
554	681	));
		@@ -632,9 +759,10 @@
632	759	$lookups,
633	760	$column_name,
634	761	$search_values,
635	762	$search_column_fns,
636		- $filter_mode = null
	763	+ $filter_mode = null,
	764	+ $filter_key = false
637	765	) {
638	766	$lookup = $lookups[$column_name];
639	767	$lookup_table = $lookup['tbl'];
640	768	$lookup_key = $lookup['key'];
		@@ -639,9 +767,14 @@
639	767	$lookup_table = $lookup['tbl'];
640	768	$lookup_key = $lookup['key'];
641	769	$lookup_columns = explode( ',', $lookup['value'] );
642	770	$lookup_where = array();
643		- foreach ( $lookup_columns as $lookup_column ) {
	771	+ if ( $filter_key ) {
	772	+ $filter_columns = array($lookup_key);
	773	+ } else {
	774	+ $filter_columns = $lookup_columns;
	775	+ }
	776	+ foreach ( $filter_columns as $lookup_column ) {
644	777	foreach ( $search_values as $search_value ) {
645	778	$lookup_where[] = $this->add_filter(
646	779	$wpdadb,
647	780	$lookup_column,
		@@ -662,8 +795,152 @@
662	795	return null;
663	796	}
664	797	}
665	798
	799	+ public static function remove_where_from_sql( $sql ) {
	800	+ if ( 'where' === substr( trim( $sql ), 0, 5 ) ) {
	801	+ $pos = strpos( $sql, 'where' );
	802	+ if ( false !== $pos ) {
	803	+ $sql = substr_replace(
	804	+ $sql,
	805	+ '',
	806	+ $pos,
	807	+ 5
	808	+ );
	809	+ }
	810	+ }
	811	+ return $sql;
	812	+ }
	813	+
	814	+ private function get_md( $md, $wpdadb, $m2m_relationship ) {
	815	+ return null;
	816	+ }
	817	+
	818	+ private function get_global_filter(
	819	+ $wpdadb,
	820	+ $search,
	821	+ $column_names,
	822	+ $lookups,
	823	+ $m2m_relationship
	824	+ ) {
	825	+ $where_global = array();
	826	+ if ( null !== $search && "" !== $search ) {
	827	+ foreach ( $column_names as $column_name => $queryable ) {
	828	+ if ( $queryable ) {
	829	+ if ( isset( $lookups[$column_name] ) ) {
	830	+ // Perform look search.
	831	+ $condition = $this->generate_lookup_condition(
	832	+ $wpdadb,
	833	+ $lookups,
	834	+ $column_name,
	835	+ array($search),
	836	+ array(),
	837	+ 'contains'
	838	+ );
	839	+ if ( null !== $condition ) {
	840	+ $where_global[] = $condition;
	841	+ }
	842	+ } else {
	843	+ $where_global[] = $wpdadb->prepare( " `%1s` like '%s' ", array($this->convert_column_name( $m2m_relationship, $column_name ), '%' . esc_sql( $search ) . '%') );
	844	+ }
	845	+ }
	846	+ }
	847	+ }
	848	+ return $where_global;
	849	+ }
	850	+
	851	+ private function get_column_filters(
	852	+ $wpdadb,
	853	+ $search_columns,
	854	+ $search_column_fns,
	855	+ $lookups,
	856	+ $m2m_relationship,
	857	+ $search_data_types
	858	+ ) {
	859	+ return null;
	860	+ }
	861	+
	862	+ private function get_where(
	863	+ $wpdadb,
	864	+ $default_where,
	865	+ $md,
	866	+ $m2m_relationship,
	867	+ $search,
	868	+ $column_names,
	869	+ $lookups,
	870	+ $search_columns,
	871	+ $search_column_fns,
	872	+ $search_data_types,
	873	+ $geo_radius = array(),
	874	+ $operator = 'and'
	875	+ ) {
	876	+ // Default where.
	877	+ if ( '' !== trim( $default_where ) && 'where' !== strtolower( substr( trim( $default_where ), 0, 5 ) ) ) {
	878	+ $where = "where {$default_where}";
	879	+ } else {
	880	+ $where = $default_where;
	881	+ }
	882	+ // Global filter.
	883	+ $where_global = $this->get_global_filter(
	884	+ $wpdadb,
	885	+ $search,
	886	+ $column_names,
	887	+ $lookups,
	888	+ $m2m_relationship
	889	+ );
	890	+ if ( 0 < count( $where_global ) ) {
	891	+ $where .= (( '' === trim( $where ) ? ' where ' : ' and ' )) . $this->add_condition( $where_global, 'or' );
	892	+ }
	893	+ if ( is_array( $geo_radius ) && 0 < count( $geo_radius ) ) {
	894	+ // Add geo radius to query
	895	+ // Variable $geo_radius already sanitized in REST API
	896	+ $unit = ( "km" == $geo_radius['unit'] ? 1000 : 1609.344 );
	897	+ // km versus miles
	898	+ if ( $geo_radius['col']['lat'] === $geo_radius['col']['lng'] ) {
	899	+ // Location stored in GEOMETRY or POINT data type
	900	+ $geocol = $geo_radius['col']['lat'];
	901	+ $geo_where = " ( st_distance_sphere(point(st_y(`{$geocol}`), st_x(`{$geocol}`)), point({$geo_radius['loc']['lng']}, {$geo_radius['loc']['lat']})) / {$unit} ) < {$geo_radius['radius']} ";
	902	+ } else {
	903	+ // Latitude and longitude stored separately
	904	+ $geo_where = " ( st_distance_sphere(point(`{$geo_radius['col']['lng']}`, `{$geo_radius['col']['lat']}`), point({$geo_radius['loc']['lng']}, {$geo_radius['loc']['lat']})) / {$unit} ) < {$geo_radius['radius']} ";
	905	+ }
	906	+ if ( '' === $where ) {
	907	+ $where = " where {$geo_where} ";
	908	+ } else {
	909	+ $where .= " and {$geo_where} ";
	910	+ }
	911	+ }
	912	+ return $where;
	913	+ }
	914	+
	915	+ private function get_selected_columns( $column_names, $search_data_types ) {
	916	+ if ( !is_array( $column_names ) ) {
	917	+ return '*';
	918	+ // select all columns
	919	+ }
	920	+ if ( 0 === count( $column_names ) ) {
	921	+ return '*';
	922	+ // select all columns
	923	+ }
	924	+ // Check for geo columns
	925	+ $geometryColumns = array();
	926	+ if ( is_array( $search_data_types ) ) {
	927	+ foreach ( $search_data_types as $column_name => $search_data_type ) {
	928	+ if ( 'geometry' === strtolower( $search_data_type ) || 'point' === strtolower( $search_data_type ) ) {
	929	+ $geometryColumns[] = $column_name;
	930	+ }
	931	+ }
	932	+ }
	933	+ return implode( ",", array_map( function ( $column_name ) use($geometryColumns) {
	934	+ if ( in_array( $column_name, $geometryColumns ) ) {
	935	+ return 'ST_AsText(`' . WPDA::remove_backticks( $column_name ) . '`) ' . " as `{$column_name}` ";
	936	+ // Convert geo data to string
	937	+ } else {
	938	+ return '`' . WPDA::remove_backticks( $column_name ) . '`';
	939	+ }
	940	+ }, array_keys( $column_names ) ) );
	941	+ }
	942	+
666	943	/**
667	944	* Perform query and return result as JSON response.
668	945	*
669	946	* @param string $dbs Schema name (database).
		@@ -693,13 +970,19 @@
693	970	$search_column_fns,
694	971	$sorting,
695	972	$last_row_count,
696	973	$row_count_estimate,
697		- $media_columns,
	974	+ $media_columns = array(),
698	975	$default_where = '',
699	976	$default_orderby = '',
700	977	$lookups = array(),
701		- $md = array()
	978	+ $md = array(),
	979	+ $m2m_relationship = array(),
	980	+ $search_data_types = array(),
	981	+ $client_side = false,
	982	+ $geo_radius = array(),
	983	+ $docs = array(),
	984	+ $search_global = null
702	985	) {
703	986	$wpdadb = WPDADB::get_db_connection( $dbs );
704	987	if ( null === $wpdadb ) {
705	988	// Error connecting.
		@@ -707,41 +990,44 @@
707	990	'status' => 420,
708	991	));
709	992	} else {
710	993	$suppress = $wpdadb->suppress_errors( true );
711		- if ( '' !== trim( $default_where ) && 'where' !== strtolower( substr( trim( $default_where ), 0, 5 ) ) ) {
712		- $where = "where {$default_where}";
713		- } else {
714		- $where = $default_where;
715		- }
716		- // Global search.
717		- $where_global = array();
718		- if ( null !== $search && "" !== $search ) {
719		- foreach ( $column_names as $column_name => $queryable ) {
720		- if ( $queryable ) {
721		- if ( isset( $lookups[$column_name] ) ) {
722		- // Perform look search.
723		- $condition = $this->generate_lookup_condition(
724		- $wpdadb,
725		- $lookups,
726		- $column_name,
727		- array($search),
728		- array(),
729		- 'contains'
730		- );
731		- if ( null !== $condition ) {
732		- $where_global[] = $condition;
733		- }
734		- } else {
735		- $where_global[] = $wpdadb->prepare( " `%1s` like '%s' ", array($column_name, '%' . esc_sql( $search ) . '%') );
736		- }
	994	+ // Build where clause.
	995	+ $where = $this->get_where(
	996	+ $wpdadb,
	997	+ $default_where,
	998	+ $md,
	999	+ $m2m_relationship,
	1000	+ $search,
	1001	+ $column_names,
	1002	+ $lookups,
	1003	+ $search_columns,
	1004	+ $search_column_fns,
	1005	+ $search_data_types,
	1006	+ $geo_radius,
	1007	+ 'and'
	1008	+ );
	1009	+ if ( $this->current_user_can_access() && isset( $search_global['s'], $search_global['c'] ) ) {
	1010	+ // Perform global search (admins only)
	1011	+ // ???
	1012	+ $wpda_list_columns = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
	1013	+ $table_columns = $wpda_list_columns->get_table_columns();
	1014	+ $where_global = WPDA::construct_where_clause(
	1015	+ $dbs,
	1016	+ $tbl,
	1017	+ $table_columns,
	1018	+ $search_global['s'],
	1019	+ 'false' !== $search_global['c']
	1020	+ );
	1021	+ if ( trim( $where_global ) !== '' ) {
	1022	+ if ( '' !== trim( $where ) && 'where' !== strtolower( substr( trim( $where ), 0, 5 ) ) ) {
	1023	+ $where .= " and {$where_global} ";
	1024	+ } else {
	1025	+ $where .= " where {$where_global} ";
737	1026	}
738	1027	}
739	1028	}
740		- if ( 0 < count( $where_global ) ) {
741		- $where .= (( '' === trim( $where ) ? ' where ' : ' and ' )) . $this->add_condition( $where_global, 'or' );
742		- }
743		- // Order by.
	1029	+ // Build order by.
744	1030	$sqlorder = '';
745	1031	if ( is_array( $sorting ) && 0 < count( $sorting ) ) {
746	1032	foreach ( $sorting as $sort ) {
747	1033	if ( '' === $sqlorder ) {
		@@ -748,15 +1034,37 @@
748	1034	$sqlorder = 'order by ';
749	1035	} else {
750	1036	$sqlorder .= ',';
751	1037	}
752		- $sqlorder .= sanitize_sql_orderby( $sort['id'] ) . ' ' . (( $sort['desc'] ? 'desc' : 'asc' ));
	1038	+ if ( !$client_side && isset( $lookups[$sort['id']] ) ) {
	1039	+ // Use lookup table to sort
	1040	+ $lookup = $lookups[$sort['id']];
	1041	+ $lookup_dbs = $lookup['dbs'];
	1042	+ $lookup_wpdadb = ( $dbs === $lookup_dbs ? $wpdadb : WPDADB::get_db_connection( $lookup_dbs ) );
	1043	+ if ( $lookup_wpdadb !== null ) {
	1044	+ $lookup_tbl = $lookup['tbl'];
	1045	+ $lookup_key = $lookup['key'];
	1046	+ $lookup_value = $lookup['value'];
	1047	+ $lookup_dataset = $lookup_wpdadb->get_results( $lookup_wpdadb->prepare( "select `%1s`, `%1s` from `%1s` order by 2", array($lookup_key, $lookup_value, $lookup_tbl) ), 'ARRAY_N' );
	1048	+ $lookup_orderby = 'case `' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` ';
	1049	+ foreach ( $lookup_dataset as $index => $value ) {
	1050	+ $lookup_orderby .= $lookup_wpdadb->prepare( 'when %s then %d ', array($value[0], $index) );
	1051	+ }
	1052	+ $lookup_orderby .= 'else `' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` end ' . (( $sort['desc'] ? 'desc' : 'asc' ));
	1053	+ $sqlorder .= $lookup_orderby;
	1054	+ } else {
	1055	+ $sqlorder .= '`' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` ' . (( $sort['desc'] ? 'desc' : 'asc' ));
	1056	+ }
	1057	+ } else {
	1058	+ // Normal sort
	1059	+ $sqlorder .= '`' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` ' . (( $sort['desc'] ? 'desc' : 'asc' ));
	1060	+ }
753	1061	}
754	1062	}
755	1063	if ( '' === $sqlorder && '' !== trim( $default_orderby ) ) {
756	1064	$sqlorder = $default_orderby;
757	1065	}
758		- // Pagination.
	1066	+ // Add pagination.
759	1067	if ( !is_numeric( $page_size ) ) {
760	1068	$page_size = 10;
761	1069	}
762	1070	$offset = $page_index * $page_size;
		@@ -764,64 +1072,71 @@
764	1072	if ( !is_numeric( $offset ) ) {
765	1073	$offset = 0;
766	1074	}
767	1075	// Prepare query.
768		- $sql = "\n\t\t\t\t\tselect `" . implode( "`,`", array_keys( $column_names ) ) . "`\n\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t{$where}\n\t\t\t\t\t{$sqlorder}\n\t\t\t\t" . (( 0 < $page_size ? " limit {$page_size} offset {$offset} " : '' ));
	1076	+ $sql = "\n\t\t\t\t\tselect " . $this->get_selected_columns( $column_names, $search_data_types ) . "\n\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t{$where}\n\t\t\t\t\t{$sqlorder}\n\t\t\t\t";
	1077	+ $sql_tables = array($tbl);
	1078	+ // Perpare query.
	1079	+ $sql = $wpdadb->prepare( ( true === $client_side ? $sql : $sql . (( 0 < $page_size ? " limit {$page_size} offset {$offset} " : '' )) ), $sql_tables );
769	1080	// Prepare debug info.
770	1081	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
771	1082	$debug = array(
772		- 'debug' => array(
773		- 'sql' => preg_replace( "/\\s+/", " ", $sql ),
774		- 'where' => $where,
775		- 'order by' => $sqlorder,
776		- ),
	1083	+ 'sql' => preg_replace( "/\\s+/", " ", $sql ),
	1084	+ 'where' => $where,
	1085	+ 'order by' => $sqlorder,
777	1086	);
778	1087	} else {
779	1088	$debug = null;
780	1089	}
781	1090	// Perform query.
782		- $dataset = $wpdadb->get_results( $wpdadb->prepare( $sql, array($tbl) ), 'ARRAY_A' );
	1091	+ $dataset = $wpdadb->get_results( $sql, 'ARRAY_A' );
783	1092	if ( $wpdadb->last_error ) {
784	1093	// Handle SQL errors.
785	1094	return new \WP_Error('error', $wpdadb->last_error, array(
786	1095	'status' => 420,
787		- 'debug' => ( isset( $debug['debug'] ) ? $debug : null ),
	1096	+ 'debug' => $debug,
788	1097	));
789	1098	}
790	1099	if ( is_numeric( $last_row_count ) and 0 <= $last_row_count ) {
791		- // Prevents an extra query.
	1100	+ // Prevents additional unnecessary queries.
792	1101	$rowcount = $last_row_count;
793	1102	} else {
794		- $estimate = false;
795		- if ( '1' === $row_count_estimate && '' === $where ) {
796		- // Perform row count estimate
797		- $countrows = $wpdadb->get_results( $wpdadb->prepare( "\n\t\t\t\t\t\t\t\t\tselect table_rows as rowcount\n\t\t\t\t\t\t\t\t\t from information_schema.tables\n\t\t\t\t\t\t\t\t\twhere table_schema = %s\n\t\t\t\t\t\t\t\t\t and table_name = %s\n\t\t\t\t\t\t\t\t", [$wpdadb->dbname, $tbl] ), 'ARRAY_A' );
	1103	+ if ( true === $client_side ) {
	1104	+ $rowcount = 0;
	1105	+ } else {
	1106	+ $estimate = false;
	1107	+ if ( '1' === $row_count_estimate && '' === $where ) {
	1108	+ // Perform row count estimate
	1109	+ $countrows = $wpdadb->get_results( $wpdadb->prepare( "\n\t\t\t\t\t\t\t\t\tselect table_rows as rowcount\n\t\t\t\t\t\t\t\t\t from information_schema.tables\n\t\t\t\t\t\t\t\t\twhere table_schema = %s\n\t\t\t\t\t\t\t\t\t and table_name = %s\n\t\t\t\t\t\t\t\t", [$wpdadb->dbname, $tbl] ), 'ARRAY_A' );
	1110	+ if ( isset( $countrows[0]['rowcount'] ) && 0 != $countrows[0]['rowcount'] ) {
	1111	+ $estimate = true;
	1112	+ }
	1113	+ }
	1114	+ if ( !$estimate ) {
	1115	+ if ( !$estimate ) {
	1116	+ // (Re)Count rows.
	1117	+ $countrows = $wpdadb->get_results( $wpdadb->prepare( "\n\t\t\t\t\t\t\t\t\t\tselect count(1) as rowcount\n\t\t\t\t\t\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t\t\t\t\t\t{$where}\n\t\t\t\t\t\t\t\t\t", array($tbl) ), 'ARRAY_A' );
	1118	+ }
	1119	+ }
	1120	+ if ( $wpdadb->last_error ) {
	1121	+ // Handle SQL errors.
	1122	+ return new \WP_Error('error', $wpdadb->last_error, array(
	1123	+ 'status' => 420,
	1124	+ ));
	1125	+ }
798	1126	if ( isset( $countrows[0]['rowcount'] ) ) {
799		- $estimate = true;
	1127	+ $rowcount = $countrows[0]['rowcount'];
	1128	+ } else {
	1129	+ $rowcount = 0;
800	1130	}
801	1131	}
802		- if ( !$estimate ) {
803		- // (Re)Count rows.
804		- $countrows = $wpdadb->get_results( $wpdadb->prepare( "\n\t\t\t\t\t\t\t\t\tselect count(1) as rowcount\n\t\t\t\t\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t\t\t\t\t{$where}\n\t\t\t\t\t\t\t\t", array($tbl) ), 'ARRAY_A' );
805		- }
806		- if ( $wpdadb->last_error ) {
807		- // Handle SQL errors.
808		- return new \WP_Error('error', $wpdadb->last_error, array(
809		- 'status' => 420,
810		- ));
811		- }
812		- if ( isset( $countrows[0]['rowcount'] ) ) {
813		- $rowcount = $countrows[0]['rowcount'];
814		- } else {
815		- $rowcount = 0;
816		- }
817	1132	}
818	1133	// Add context node to response
819	1134	$context = array();
820		- if ( isset( $debug['debug'] ) && 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
821		- $context['debug'] = $debug['debug'];
	1135	+ if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
	1136	+ $context['debug'] = $debug;
822	1137	}
823		- if ( 0 < count( $media_columns ) ) {
	1138	+ if ( is_array( $media_columns ) && 0 < count( $media_columns ) ) {
824	1139	// Handle WP media library
825	1140	$media = array();
826	1141	for ($i = 0; $i < count( $dataset ); $i++) {
827	1142	$media_row = array();
		@@ -862,17 +1177,33 @@
862	1177	return $response;
863	1178	}
864	1179	}
865	1180
	1181	+ private function convert_column_name( $m2m_relationship, $column_name ) {
	1182	+ // Return plain column name.
	1183	+ return $this->sanitize_db_identifier( $column_name );
	1184	+ }
	1185	+
	1186	+ private function map_columns( $prefix, $column_names ) {
	1187	+ return implode( ",", array_map( function ( $v ) use($prefix) {
	1188	+ $c = $this->sanitize_db_identifier( $v );
	1189	+ $r = ( 'd' === $prefix ? static::RELATIONTABLEPREFIX . $c : $c );
	1190	+ return "`{$prefix}`.`{$c}` as \"{$r}\"";
	1191	+ }, array_keys( $column_names ) ) );
	1192	+ }
	1193	+
866	1194	public function add_filter(
867	1195	$wpdadb,
868		- $searchColumn,
	1196	+ $search_column,
869	1197	$search_column_fns,
870		- $searchValue
	1198	+ $search_value,
	1199	+ $m2m_relationship = array(),
	1200	+ $search_data_types = array()
871	1201	) {
	1202	+ return null;
872	1203	}
873	1204
874		- private function add_condition( $where_lines, $operand = 'and' ) {
	1205	+ public static function add_condition( $where_lines, $operand = 'and' ) {
875	1206	if ( 0 < count( array_filter( $where_lines ) ) ) {
876	1207	// Apply all searches.
877	1208	return ' ( (' . implode( ") {$operand} (", array_filter( $where_lines ) ) . ') ) ';
878	1209	} else {
		@@ -885,13 +1216,13 @@
885	1216	*
886	1217	* @param string $dbs Database schema name.
887	1218	* @param string $tbl Database table name.
888	1219	* @param string $waa With admin actions.
889		- * @return array\object
	1220	+ * @return array | object
890	1221	*/
891	1222	public function get_table_meta_data( $dbs, $tbl, $waa ) {
892	1223	$sql_create_table = '';
893		- if ( current_user_can( 'manage_options' ) ) {
	1224	+ if ( WPDA::current_user_is_admin() ) {
894	1225	// Admin user has access to all resources
895	1226	$access = array(
896	1227	'select' => array('POST'),
897	1228	'insert' => array('POST'),
		@@ -902,9 +1233,10 @@
902	1233	$wpdadb = WPDADB::get_db_connection( $dbs );
903	1234	if ( null !== $wpdadb ) {
904	1235	$suppress_errors = $wpdadb->suppress_errors;
905	1236	$wpdadb->suppress_errors = true;
906		- $wpdadb->query( "SET sql_mode = 'NO_TABLE_OPTIONS'" );
	1237	+ // NO_TABLE_OPTIONS is deprecated in V8
	1238	+ // $wpdadb->query( "SET sql_mode = 'NO_TABLE_OPTIONS'" );
907	1239	$sql = $wpdadb->get_results( $wpdadb->prepare( 'show create table `%1s`', array($tbl) ), 'ARRAY_N' );
908	1240	if ( isset( $sql[0][1] ) ) {
909	1241	$sql_create_table = $sql[0][1];
910	1242	}
		@@ -936,18 +1268,21 @@
936	1268	$wp_nonce_refresh = null;
937	1269	$connect = null;
938	1270	global $wpdb;
939	1271	$settings->wp = [
940		- 'roles' => $this->get_wp_roles(),
941		- 'users' => $this->get_wp_users(),
942		- 'home' => admin_url( 'admin.php' ),
943		- 'homea' => admin_url( 'admin-ajax.php' ),
944		- 'tables' => array_values( $wpdb->tables() ),
945		- 'date_format' => get_option( 'date_format' ),
946		- 'time_format' => get_option( 'time_format' ),
947		- 'alter' => $wp_nonce_alter,
948		- 'refresh' => $wp_nonce_refresh,
949		- 'connect' => $connect,
	1272	+ 'roles' => $this->get_wp_roles(),
	1273	+ 'users' => $this->get_wp_users(),
	1274	+ 'home' => admin_url( 'admin.php' ),
	1275	+ 'homea' => admin_url( 'admin-ajax.php' ),
	1276	+ 'tables' => array_values( $wpdb->tables() ),
	1277	+ 'date_format' => get_option( 'date_format' ),
	1278	+ 'time_format' => get_option( 'time_format' ),
	1279	+ 'alter' => $wp_nonce_alter,
	1280	+ 'refresh' => $wp_nonce_refresh,
	1281	+ 'connect' => $connect,
	1282	+ 'copyinprogress' => WPDA_Actions::copy_in_progress(),
	1283	+ 'scroll_offset' => WPDA::get_option( WPDA::OPTION_APPS_SCROLL_OFFSET ),
	1284	+ 'upload' => @ini_get( 'upload_max_filesize' ),
950	1285	];
951	1286	if ( true === $waa ) {
952	1287	$settings->wp['aonce'] = implode( '-', array(
953	1288	wp_create_nonce( 'wpda-export-' . json_encode( $tbl ) ),
		@@ -954,26 +1289,34 @@
954	1289	// Table export
955	1290	wp_create_nonce( 'wpda-rename-' . $tbl ),
956	1291	) );
957	1292	}
958		- $media = $this->get_media( $dbs, $tbl, $columns->get_table_columns() );
	1293	+ $table_columns = $columns->get_table_columns();
	1294	+ $media = $this->get_media( $dbs, $tbl, $table_columns );
	1295	+ $columns_sorted = array();
	1296	+ foreach ( $table_columns as $column ) {
	1297	+ if ( isset( $column['column_name'] ) ) {
	1298	+ $columns_sorted[$column['column_name']] = $column;
	1299	+ }
	1300	+ }
959	1301	}
960	1302	return array(
961		- 'columns' => $columns->get_table_columns(),
962		- 'table_labels' => $columns->get_table_header_labels(),
963		- 'form_labels' => $columns->get_table_column_headers(),
964		- 'primary_key' => $columns->get_table_primary_key(),
965		- 'access' => $access,
966		- 'settings' => $settings,
967		- 'media' => $media['media'],
968		- 'wp_media' => $media['wp_media'],
969		- 'table_info' => $this->get_table_info( $dbs, $tbl ),
970		- 'create' => $sql_create_table,
	1303	+ 'columns' => $table_columns,
	1304	+ 'columns_sorted' => $columns_sorted,
	1305	+ 'table_labels' => $columns->get_table_header_labels(),
	1306	+ 'form_labels' => $columns->get_table_column_headers(),
	1307	+ 'primary_key' => $columns->get_table_primary_key(),
	1308	+ 'access' => $access,
	1309	+ 'settings' => $settings,
	1310	+ 'media' => $media['media'],
	1311	+ 'wp_media' => $media['wp_media'],
	1312	+ 'table_info' => $this->get_table_info( $dbs, $tbl ),
	1313	+ 'create' => $sql_create_table,
971	1314	);
972	1315	}
973	1316
974	1317	private function get_table_access( $dbs, $tbl ) {
975		- if ( current_user_can( 'manage_options' ) ) {
	1318	+ if ( WPDA::current_user_is_admin() ) {
976	1319	// Check administrator rights
977	1320	if ( is_admin() ) {
978	1321	$access = WPDA_Dictionary_Access::check_table_access_backend( $dbs, $tbl, $done );
979	1322	} else {
		@@ -1011,9 +1354,9 @@
1011	1354	if ( isset( $table[$action]['authorized_users'] ) && is_array( $table[$action]['authorized_users'] ) && 0 < count( $table[$action]['authorized_users'] ) && in_array( (string) $this->get_user_login(), $table[$action]['authorized_users'] ) ) {
1012	1355	return $table[$action]['methods'];
1013	1356	}
1014	1357	// Check authorized roles
1015		- if ( isset( $table[$action]['authorized_roles'] ) && is_array( $table[$action]['authorized_roles'] ) && 0 < count( $table[$action]['authorized_roles'] ) && 0 < count( array_intersect( $this->get_user_roles(), $table[$action]['authorized_roles'] ) ) ) {
	1358	+ if ( isset( $table[$action]['authorized_roles'] ) && is_array( $table[$action]['authorized_roles'] ) && 0 < count( $table[$action]['authorized_roles'] ) && 0 < count( array_intersect( ( is_array( $this->get_user_roles() ) ? $this->get_user_roles() : array() ), $table[$action]['authorized_roles'] ) ) ) {
1016	1359	return $table[$action]['methods'];
1017	1360	}
1018	1361	}
1019	1362	}
		@@ -1024,9 +1367,9 @@
1024	1367	* Check if access is grant for requested database/table.
1025	1368	*
1026	1369	* @param string $dbs Remote or local database connection string.
1027	1370	* @param string $tbl Database table name.
1028		- * @param onject $request Request object.
	1371	+ * @param object $request Request object.
1029	1372	* @param string $action Possible values: select, insert, update, delete.
1030	1373	* @return bool
1031	1374	*/
1032	1375	private function check_table_access(
		@@ -1035,9 +1378,9 @@
1035	1378	$request,
1036	1379	$action,
1037	1380	&$msg = ''
1038	1381	) {
1039		- if ( current_user_can( 'manage_options' ) ) {
	1382	+ if ( WPDA::current_user_is_admin() ) {
1040	1383	// Grant access to administrators always.
1041	1384	return true;
1042	1385	}
1043	1386	$tables = get_option( WPDA_API::WPDA_REST_API_TABLE_ACCESS );
		@@ -1051,9 +1394,9 @@
1051	1394	$msg = __( 'Unauthorized', 'wp-data-access' );
1052	1395	return false;
1053	1396	} else {
1054	1397	if ( !in_array( $request->get_method(), $tables[$dbs][$tbl][$action]['methods'] ) ) {
1055		- //phpcs:ignore - 8.1 proof
	1398	+ // phpcs:ignore -- 8.1 proof
1056	1399	$msg = __( 'Unauthorized', 'wp-data-access' );
1057	1400	return false;
1058	1401	}
1059	1402	}
		@@ -1110,9 +1453,15 @@
1110	1453	return false;
1111	1454	}
1112	1455	}
1113	1456
1114		- private function sanitize_column_values( $dbs, $tbl, $column_values ) {
	1457	+ private function sanitize_column_values(
	1458	+ $dbs,
	1459	+ $tbl,
	1460	+ $column_values,
	1461	+ $code_columns = array(),
	1462	+ $html_columns = array()
	1463	+ ) {
1115	1464	$wpda_list_columns = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
1116	1465	$sanitized_column_values = [];
1117	1466	foreach ( $column_values as $column_name => $column_value ) {
1118	1467	$column_value = $column_values[$column_name];
		@@ -1121,9 +1470,13 @@
1121	1470	case 'text':
1122	1471	case 'mediumtext':
1123	1472	case 'longtext':
1124	1473	if ( null !== $column_value ) {
1125		- $column_value = wp_kses_post( $column_value );
	1474	+ if ( in_array( $column_name, $html_columns ) ) {
	1475	+ $column_value = sanitize_textarea_field( $column_value );
	1476	+ } else {
	1477	+ $column_value = wp_kses_post( $column_value );
	1478	+ }
1126	1479	}
1127	1480	break;
1128	1481	default:
1129	1482	if ( null !== $column_value ) {