← All changes | WPDataAccess/API/WPDA_Table.php +310 -110 5.5.22 → 5.5.84 View file →

		@@ -59,8 +59,26 @@
59	59	'sorting' => $this->get_param( 'sorting' ),
60	60	'row_count' => $this->get_param( 'row_count' ),
61	61	'row_count_estimate' => $this->get_param( 'row_count_estimate' ),
62	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	+ ),
63	81	),
64	82	) );
65	83	register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/get', array(
66	84	'methods' => array('GET', 'POST'),
		@@ -118,9 +136,9 @@
118	136
119	137	/**
120	138	* Get table meta info.
121	139	*
122		- * @param WP_REST_Request $request Rest API request.
	140	+ * @param \WP_REST_Request $request Rest API request.
123	141	* @return \WP_Error|\WP_REST_Response
124	142	*/
125	143	public function table_meta( $request ) {
126	144	$dbs = $request->get_param( 'dbs' );
		@@ -147,9 +165,9 @@
147	165
148	166	/**
149	167	* Database table query using the full primary key. Must return exactly one row.
150	168	*
151		- * @param WP_REST_Request $request Rest API request.
	169	+ * @param \WP_REST_Request $request Rest API request.
152	170	* @return \WP_Error|\WP_REST_Response
153	171	*/
154	172	public function table_get( $request ) {
155	173	$dbs = $request->get_param( 'dbs' );
		@@ -182,9 +200,9 @@
182	200
183	201	/**
184	202	* Insert one row.
185	203	*
186		- * @param WP_REST_Request $request Rest API request.
	204	+ * @param \WP_REST_Request $request Rest API request.
187	205	* @return \WP_Error|\WP_REST_Response
188	206	*/
189	207	public function table_insert( $request ) {
190	208	$dbs = $request->get_param( 'dbs' );
		@@ -211,9 +229,9 @@
211	229
212	230	/**
213	231	* Update uses primary key. Must return exactly one row.
214	232	*
215		- * @param WP_REST_Request $request Rest API request.
	233	+ * @param \WP_REST_Request $request Rest API request.
216	234	* @return \WP_Error|\WP_REST_Response
217	235	*/
218	236	public function table_update( $request ) {
219	237	$dbs = $request->get_param( 'dbs' );
		@@ -246,9 +264,9 @@
246	264
247	265	/**
248	266	* Delete uses primary key. Must return exactly one row.
249	267	*
250		- * @param WP_REST_Request $request Rest API request.
	268	+ * @param \WP_REST_Request $request Rest API request.
251	269	* @return \WP_Error|\WP_REST_Response
252	270	*/
253	271	public function table_delete( $request ) {
254	272	$dbs = $request->get_param( 'dbs' );
		@@ -275,12 +293,13 @@
275	293
276	294	/**
277	295	* Database table query to populate a list of values for a specific table/column.
278	296	*
279		- * @param WP_REST_Request $request Rest API request.
	297	+ * @param \WP_REST_Request $request Rest API request.
280	298	* @return \WP_Error|\WP_REST_Response
281	299	*/
282	300	public function table_lov( $request ) {
	301	+ return null;
283	302	}
284	303
285	304	/**
286	305	* Database table query.
		@@ -286,9 +305,9 @@
286	305	* Database table query.
287	306	*
288	307	* Supports: searching, ordering and pagination.
289	308	*
290		- * @param WP_REST_Request $request Rest API request.
	309	+ * @param \WP_REST_Request $request Rest API request.
291	310	* @return \WP_Error|\WP_REST_Response
292	311	*/
293	312	public function table_select( $request ) {
294	313	$dbs = $request->get_param( 'dbs' );
		@@ -298,12 +317,15 @@
298	317	$page_size = $request->get_param( 'page_size' );
299	318	$search = $request->get_param( 'search' );
300	319	$search_columns = $request->get_param( 'search_columns' );
301	320	$search_column_fns = $request->get_param( 'search_column_fns' );
	321	+ $search_data_types = $request->get_param( 'search_data_types' );
302	322	$sorting = $request->get_param( 'sorting' );
303	323	$row_count = $request->get_param( 'row_count' );
304	324	$row_count_estimate = $request->get_param( 'row_count_estimate' );
305	325	$media = $request->get_param( 'media' );
	326	+ $client_side = '1' === $request->get_param( 'client_side' );
	327	+ $global_search = $request->get_param( 'global_search' );
306	328	if ( $this->check_table_access(
307	329	$dbs,
308	330	$tbl,
309	331	$request,
		@@ -321,9 +343,19 @@
321	343	$search_column_fns,
322	344	$sorting,
323	345	$row_count,
324	346	$row_count_estimate,
325		- $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
326	358	);
327	359	} else {
328	360	if ( 'rest_cookie_invalid_nonce' === $msg ) {
329	361	return $this->invalid_nonce();
		@@ -360,8 +392,9 @@
360	392	$md = array(),
361	393	$m2m_relationship = array(),
362	394	$search_data_types = array()
363	395	) {
	396	+ return null;
364	397	}
365	398
366	399	public function lookup(
367	400	$dbs,
		@@ -402,21 +435,48 @@
402	435	}
403	436	}
404	437	$dynamic_where = array();
405	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	+ }
406	447	foreach ( $column_dynamic_values as $key => $value ) {
407		- $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) );
408	452	}
409	453	$where .= (( '' === $where ? ' where ' : ' and ' )) . ' (' . implode( ' and ', $dynamic_where ) . ') ';
410	454	}
	455	+ $column_count = ( '' === $subquery ? '' : ", stats.total_rows as 'count'" );
411	456	if ( strpos( $column_value, ',' ) !== false ) {
412	457	$columns = explode( ',', $column_value );
413		- $sql = $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", array($column_key, implode( '`,`', $columns ), $tbl) );
	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	+ ) );
414	467	} else {
415		- $sql = $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", array($column_key, $column_value, $tbl) );
	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	+ ) );
416	475	}
417		- $sql .= " {$where} order by 2 ";
418		- // $where already sanitized
	476	+ $orderby = ' order by 2 ';
	477	+ $sql .= " {$where} {$orderby} ";
	478	+ // $where and $orderby already sanitized and prepared
419	479	$dataset = $wpdadb->get_results( $sql, 'OBJECT' );
420	480	$wpdadb->suppress_errors( $suppress );
421	481	// Send response.
422	482	if ( '' === $wpdadb->last_error ) {
		@@ -458,11 +518,12 @@
458	518	public function get(
459	519	$dbs,
460	520	$tbl,
461	521	$primary_key,
462		- $media_columns,
	522	+ $media_columns = array(),
463	523	$column_names = array(),
464		- $default_where = ''
	524	+ $default_where = '',
	525	+ $docs = array()
465	526	) {
466	527	$wpdadb = WPDADB::get_db_connection( $dbs );
467	528	if ( null === $wpdadb ) {
468	529	// Error connecting.
		@@ -483,15 +544,22 @@
483	544	} else {
484	545	$where .= " and {$default_where} ";
485	546	}
486	547	}
487		- $selected_columns = '*';
488		- if ( 0 < count( $column_names ) ) {
489		- $selected_columns = '`' . implode( '`,`', array_map( function ( $column_name ) {
490		- return WPDA::remove_backticks( $column_name );
491		- }, $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	+ }
492	559	}
493		- $sql = $wpdadb->prepare( "select {$selected_columns} from `%1s` {$where}", array($tbl) );
	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) );
494	562	$dataset = $wpdadb->get_results( $sql, 'ARRAY_A' );
495	563	// Prepare debug info.
496	564	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
497	565	$debug = array(
		@@ -504,39 +572,37 @@
504	572	$debug = null;
505	573	}
506	574	$wpdadb->suppress_errors( $suppress );
507	575	// Send response.
508		- if ( 0 === count( $dataset ) ) {
509		- return new \WP_Error('error', 'No data found', array(
510		- 'status' => 420,
511		- 'debug' => $debug['debug'],
512		- ));
513		- } elseif ( 1 === count( $dataset ) ) {
514		- $media = array();
515		- if ( 0 < count( $media_columns ) ) {
516		- foreach ( $media_columns as $media_column_name => $media_column_type ) {
517		- if ( isset( $dataset[0][$media_column_name] ) ) {
518		- if ( in_array( $media_column_type, [
519		- 'WP-Image',
520		- 'WP-Attachment',
521		- 'WP-Audio',
522		- 'WP-Video'
523		- ] ) ) {
524		- $media[$media_column_name] = WPDA_WP_Media::get_media_url( $dataset[0][$media_column_name] );
525		- }
	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] );
526	587	}
527	588	}
528	589	}
529		- $context = array();
530		- $context['media'] = $media;
531		- if ( isset( $debug['debug'] ) && 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
532		- $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 );
533	604	}
534		- return $this->WPDA_Rest_Response( '', $dataset, $context );
535		- } else {
536		- return new \WP_Error('error', "Invalid arguments", array(
537		- 'status' => 420,
538		- ));
539	605	}
540	606	}
541	607	}
542	608
		@@ -589,9 +655,11 @@
589	655	$dbs,
590	656	$tbl,
591	657	$primary_key,
592	658	$column_values,
593		- $column_names = array()
	659	+ $column_names = array(),
	660	+ $code_columns = array(),
	661	+ $html_columns = array()
594	662	) {
595	663	$wpdadb = WPDADB::get_db_connection( $dbs );
596	664	if ( null === $wpdadb ) {
597	665	// Error connecting.
		@@ -599,9 +667,15 @@
599	667	'status' => 420,
600	668	));
601	669	} else {
602	670	// Sanitize column names and values.
603		- $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	+ );
604	678	if ( false === $sanitized_column_values ) {
605	679	return new \WP_Error('error', "Invalid arguments", array(
606	680	'status' => 420,
607	681	));
		@@ -737,8 +811,9 @@
737	811	return $sql;
738	812	}
739	813
740	814	private function get_md( $md, $wpdadb, $m2m_relationship ) {
	815	+ return null;
741	816	}
742	817
743	818	private function get_global_filter(
744	819	$wpdadb,
		@@ -780,8 +855,9 @@
780	855	$lookups,
781	856	$m2m_relationship,
782	857	$search_data_types
783	858	) {
	859	+ return null;
784	860	}
785	861
786	862	private function get_where(
787	863	$wpdadb,
		@@ -792,9 +868,11 @@
792	868	$column_names,
793	869	$lookups,
794	870	$search_columns,
795	871	$search_column_fns,
796		- $search_data_types
	872	+ $search_data_types,
	873	+ $geo_radius = array(),
	874	+ $operator = 'and'
797	875	) {
798	876	// Default where.
799	877	if ( '' !== trim( $default_where ) && 'where' !== strtolower( substr( trim( $default_where ), 0, 5 ) ) ) {
800	878	$where = "where {$default_where}";
		@@ -811,11 +889,58 @@
811	889	);
812	890	if ( 0 < count( $where_global ) ) {
813	891	$where .= (( '' === trim( $where ) ? ' where ' : ' and ' )) . $this->add_condition( $where_global, 'or' );
814	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	+ }
815	912	return $where;
816	913	}
817	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	+
818	943	/**
819	944	* Perform query and return result as JSON response.
820	945	*
821	946	* @param string $dbs Schema name (database).
		@@ -845,15 +970,19 @@
845	970	$search_column_fns,
846	971	$sorting,
847	972	$last_row_count,
848	973	$row_count_estimate,
849		- $media_columns,
	974	+ $media_columns = array(),
850	975	$default_where = '',
851	976	$default_orderby = '',
852	977	$lookups = array(),
853	978	$md = array(),
854	979	$m2m_relationship = array(),
855		- $search_data_types = array()
	980	+ $search_data_types = array(),
	981	+ $client_side = false,
	982	+ $geo_radius = array(),
	983	+ $docs = array(),
	984	+ $search_global = null
856	985	) {
857	986	$wpdadb = WPDADB::get_db_connection( $dbs );
858	987	if ( null === $wpdadb ) {
859	988	// Error connecting.
		@@ -872,10 +1001,32 @@
872	1001	$column_names,
873	1002	$lookups,
874	1003	$search_columns,
875	1004	$search_column_fns,
876		- $search_data_types
	1005	+ $search_data_types,
	1006	+ $geo_radius,
	1007	+ 'and'
877	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} ";
	1026	+ }
	1027	+ }
	1028	+ }
878	1029	// Build order by.
879	1030	$sqlorder = '';
880	1031	if ( is_array( $sorting ) && 0 < count( $sorting ) ) {
881	1032	foreach ( $sorting as $sort ) {
		@@ -883,9 +1034,31 @@
883	1034	$sqlorder = 'order by ';
884	1035	} else {
885	1036	$sqlorder .= ',';
886	1037	}
887		- $sqlorder .= '`' . $this->convert_column_name( $m2m_relationship, $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	+ }
888	1061	}
889	1062	}
890	1063	if ( '' === $sqlorder && '' !== trim( $default_orderby ) ) {
891	1064	$sqlorder = $default_orderby;
		@@ -899,12 +1072,12 @@
899	1072	if ( !is_numeric( $offset ) ) {
900	1073	$offset = 0;
901	1074	}
902	1075	// Prepare query.
903		- $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";
	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";
904	1077	$sql_tables = array($tbl);
905	1078	// Perpare query.
906		- $sql = $wpdadb->prepare( $sql . (( 0 < $page_size ? " limit {$page_size} offset {$offset} " : '' )), $sql_tables );
	1079	+ $sql = $wpdadb->prepare( ( true === $client_side ? $sql : $sql . (( 0 < $page_size ? " limit {$page_size} offset {$offset} " : '' )) ), $sql_tables );
907	1080	// Prepare debug info.
908	1081	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
909	1082	$debug = array(
910	1083	'sql' => preg_replace( "/\\s+/", " ", $sql ),
		@@ -926,33 +1099,37 @@
926	1099	if ( is_numeric( $last_row_count ) and 0 <= $last_row_count ) {
927	1100	// Prevents additional unnecessary queries.
928	1101	$rowcount = $last_row_count;
929	1102	} else {
930		- $estimate = false;
931		- if ( '1' === $row_count_estimate && '' === $where ) {
932		- // Perform row count estimate
933		- $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' );
934		- if ( isset( $countrows[0]['rowcount'] ) && 0 != $countrows[0]['rowcount'] ) {
935		- $estimate = true;
	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	+ }
936	1113	}
937		- }
938		- if ( !$estimate ) {
939	1114	if ( !$estimate ) {
940		- // (Re)Count rows.
941		- $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' );
	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	+ }
942	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	+ }
	1126	+ if ( isset( $countrows[0]['rowcount'] ) ) {
	1127	+ $rowcount = $countrows[0]['rowcount'];
	1128	+ } else {
	1129	+ $rowcount = 0;
	1130	+ }
943	1131	}
944		- if ( $wpdadb->last_error ) {
945		- // Handle SQL errors.
946		- return new \WP_Error('error', $wpdadb->last_error, array(
947		- 'status' => 420,
948		- ));
949		- }
950		- if ( isset( $countrows[0]['rowcount'] ) ) {
951		- $rowcount = $countrows[0]['rowcount'];
952		- } else {
953		- $rowcount = 0;
954		- }
955	1132	}
956	1133	// Add context node to response
957	1134	$context = array();
958	1135	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
		@@ -957,9 +1134,9 @@
957	1134	$context = array();
958	1135	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
959	1136	$context['debug'] = $debug;
960	1137	}
961		- if ( 0 < count( $media_columns ) ) {
	1138	+ if ( is_array( $media_columns ) && 0 < count( $media_columns ) ) {
962	1139	// Handle WP media library
963	1140	$media = array();
964	1141	for ($i = 0; $i < count( $dataset ); $i++) {
965	1142	$media_row = array();
		@@ -1021,8 +1198,9 @@
1021	1198	$search_value,
1022	1199	$m2m_relationship = array(),
1023	1200	$search_data_types = array()
1024	1201	) {
	1202	+ return null;
1025	1203	}
1026	1204
1027	1205	public static function add_condition( $where_lines, $operand = 'and' ) {
1028	1206	if ( 0 < count( array_filter( $where_lines ) ) ) {
		@@ -1038,13 +1216,13 @@
1038	1216	*
1039	1217	* @param string $dbs Database schema name.
1040	1218	* @param string $tbl Database table name.
1041	1219	* @param string $waa With admin actions.
1042		- * @return array\object
	1220	+ * @return array | object
1043	1221	*/
1044	1222	public function get_table_meta_data( $dbs, $tbl, $waa ) {
1045	1223	$sql_create_table = '';
1046		- if ( current_user_can( 'manage_options' ) ) {
	1224	+ if ( WPDA::current_user_is_admin() ) {
1047	1225	// Admin user has access to all resources
1048	1226	$access = array(
1049	1227	'select' => array('POST'),
1050	1228	'insert' => array('POST'),
		@@ -1055,9 +1233,10 @@
1055	1233	$wpdadb = WPDADB::get_db_connection( $dbs );
1056	1234	if ( null !== $wpdadb ) {
1057	1235	$suppress_errors = $wpdadb->suppress_errors;
1058	1236	$wpdadb->suppress_errors = true;
1059		- $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'" );
1060	1239	$sql = $wpdadb->get_results( $wpdadb->prepare( 'show create table `%1s`', array($tbl) ), 'ARRAY_N' );
1061	1240	if ( isset( $sql[0][1] ) ) {
1062	1241	$sql_create_table = $sql[0][1];
1063	1242	}
		@@ -1089,18 +1268,21 @@
1089	1268	$wp_nonce_refresh = null;
1090	1269	$connect = null;
1091	1270	global $wpdb;
1092	1271	$settings->wp = [
1093		- 'roles' => $this->get_wp_roles(),
1094		- 'users' => $this->get_wp_users(),
1095		- 'home' => admin_url( 'admin.php' ),
1096		- 'homea' => admin_url( 'admin-ajax.php' ),
1097		- 'tables' => array_values( $wpdb->tables() ),
1098		- 'date_format' => get_option( 'date_format' ),
1099		- 'time_format' => get_option( 'time_format' ),
1100		- 'alter' => $wp_nonce_alter,
1101		- 'refresh' => $wp_nonce_refresh,
1102		- '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' ),
1103	1285	];
1104	1286	if ( true === $waa ) {
1105	1287	$settings->wp['aonce'] = implode( '-', array(
1106	1288	wp_create_nonce( 'wpda-export-' . json_encode( $tbl ) ),
		@@ -1107,26 +1289,34 @@
1107	1289	// Table export
1108	1290	wp_create_nonce( 'wpda-rename-' . $tbl ),
1109	1291	) );
1110	1292	}
1111		- $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	+ }
1112	1301	}
1113	1302	return array(
1114		- 'columns' => $columns->get_table_columns(),
1115		- 'table_labels' => $columns->get_table_header_labels(),
1116		- 'form_labels' => $columns->get_table_column_headers(),
1117		- 'primary_key' => $columns->get_table_primary_key(),
1118		- 'access' => $access,
1119		- 'settings' => $settings,
1120		- 'media' => $media['media'],
1121		- 'wp_media' => $media['wp_media'],
1122		- 'table_info' => $this->get_table_info( $dbs, $tbl ),
1123		- '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,
1124	1314	);
1125	1315	}
1126	1316
1127	1317	private function get_table_access( $dbs, $tbl ) {
1128		- if ( current_user_can( 'manage_options' ) ) {
	1318	+ if ( WPDA::current_user_is_admin() ) {
1129	1319	// Check administrator rights
1130	1320	if ( is_admin() ) {
1131	1321	$access = WPDA_Dictionary_Access::check_table_access_backend( $dbs, $tbl, $done );
1132	1322	} else {
		@@ -1164,9 +1354,9 @@
1164	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'] ) ) {
1165	1355	return $table[$action]['methods'];
1166	1356	}
1167	1357	// Check authorized roles
1168		- 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'] ) ) ) {
1169	1359	return $table[$action]['methods'];
1170	1360	}
1171	1361	}
1172	1362	}
		@@ -1177,9 +1367,9 @@
1177	1367	* Check if access is grant for requested database/table.
1178	1368	*
1179	1369	* @param string $dbs Remote or local database connection string.
1180	1370	* @param string $tbl Database table name.
1181		- * @param onject $request Request object.
	1371	+ * @param object $request Request object.
1182	1372	* @param string $action Possible values: select, insert, update, delete.
1183	1373	* @return bool
1184	1374	*/
1185	1375	private function check_table_access(
		@@ -1188,9 +1378,9 @@
1188	1378	$request,
1189	1379	$action,
1190	1380	&$msg = ''
1191	1381	) {
1192		- if ( current_user_can( 'manage_options' ) ) {
	1382	+ if ( WPDA::current_user_is_admin() ) {
1193	1383	// Grant access to administrators always.
1194	1384	return true;
1195	1385	}
1196	1386	$tables = get_option( WPDA_API::WPDA_REST_API_TABLE_ACCESS );
		@@ -1204,9 +1394,9 @@
1204	1394	$msg = __( 'Unauthorized', 'wp-data-access' );
1205	1395	return false;
1206	1396	} else {
1207	1397	if ( !in_array( $request->get_method(), $tables[$dbs][$tbl][$action]['methods'] ) ) {
1208		- //phpcs:ignore - 8.1 proof
	1398	+ // phpcs:ignore -- 8.1 proof
1209	1399	$msg = __( 'Unauthorized', 'wp-data-access' );
1210	1400	return false;
1211	1401	}
1212	1402	}
		@@ -1263,9 +1453,15 @@
1263	1453	return false;
1264	1454	}
1265	1455	}
1266	1456
1267		- 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	+ ) {
1268	1464	$wpda_list_columns = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
1269	1465	$sanitized_column_values = [];
1270	1466	foreach ( $column_values as $column_name => $column_value ) {
1271	1467	$column_value = $column_values[$column_name];
		@@ -1274,9 +1470,13 @@
1274	1470	case 'text':
1275	1471	case 'mediumtext':
1276	1472	case 'longtext':
1277	1473	if ( null !== $column_value ) {
1278		- $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	+ }
1279	1479	}
1280	1480	break;
1281	1481	default:
1282	1482	if ( null !== $column_value ) {