← All changes | WPDataAccess/API/WPDA_API.php +183 -46 5.5.3 → 5.2 View file →

		@@ -1,78 +1,215 @@
1	1	<?php // phpcs:ignore Standard.Category.SniffName.ErrorCode
2		-
3	2	/**
4		- * JSON REST API.
	3	+ * JSON REST API
5	4	*/
6	5
7	6	namespace WPDataAccess\API {
8	7
	8	+ use WPDataAccess\Connection\WPDADB;
	9	+ use WPDataAccess\Data_Dictionary\WPDA_List_Columns_Cache;
9	10	use WPDataAccess\WPDA;
10	11
11	12	/**
12		- * JSON REST API main class.
	13	+ * JSON REST API main class
13	14	*/
14	15	class WPDA_API {
15	16
16		- const WPDA_NAMESPACE = 'wpda';
17		- const WPDA_REST_API_TABLE_ACCESS = 'wpda_rest_api_table_access';
	17	+ const WPDA_API_VERSION = '/v1';
	18	+ const WPDA_REST_API = 'wpda_rest_api';
	19	+ const WPDA_PER_PAGE = 10;
18	20
19		- public function hide() {
	21	+ /**
	22	+ * Tables accessible through REST API (default = none)
	23	+ *
	24	+ * @var array
	25	+ */
	26	+ private $tables = array();
20	27
21		- add_filter(
22		- 'rest_authentication_errors',
23		- function ( $access ) {
24		- $rest_route = isset( $GLOBALS['wp']->query_vars['rest_route'] )
25		- ? untrailingslashit( $GLOBALS['wp']->query_vars['rest_route'] )
26		- : '';
	28	+ /**
	29	+ * Constructor
	30	+ *
	31	+ * Loads accessible tables and privileges
	32	+ */
	33	+ public function __construct() {
	34	+ $this->tables = get_option( self::WPDA_REST_API );
	35	+ if ( false === $this->tables ) {
	36	+ $this->tables = array();
	37	+ }
	38	+ }
27	39
28		- if ( '/' . self::WPDA_NAMESPACE === $rest_route && ! current_user_can( 'manage_options' ) ) {
29		- return new \WP_Error(
30		- 'rest_cannot_access',
31		- __( 'Only authenticated admin users can access the REST API.', 'wp-data-access' ),
32		- array(
33		- 'status' => rest_authorization_required_code(),
34		- )
35		- );
36		- }
37		-
38		- return $access;
39		- }
	40	+ /**
	41	+ * Register routes
	42	+ *
	43	+ * @return void
	44	+ */
	45	+ public function init() {
	46	+ register_rest_route(
	47	+ 'wpda' . self::WPDA_API_VERSION,
	48	+ 'lst',
	49	+ array(
	50	+ 'methods' => 'GET,POST',
	51	+ 'callback' => array( $this, 'lst' ),
	52	+ 'permission_callback' => '__return_true',
	53	+ )
40	54	);
41		-
42	55	}
43	56
44	57	/**
45		- * Register routes.
	58	+ * Get list from table
46	59	*
47		- * @return void
	60	+ * Supports: searching, ordering and pagination through URL arguments
	61	+ *
	62	+ * @return \WP_Error|\WP_REST_Response
48	63	*/
49		- public function init() {
	64	+ public function lst() {
	65	+ if ( isset( $_SERVER['REQUEST_METHOD'] ) && 'POST' === $_SERVER['REQUEST_METHOD'] ) {
	66	+ $schema_name = isset( $_POST['schema_name'] ) ? sanitize_text_field( wp_unslash( $_POST['schema_name'] ) ) : null; // phpcs:ignore WordPress.Security.NonceVerification
	67	+ $table_name = isset( $_POST['table_name'] ) ? sanitize_text_field( wp_unslash( $_POST['table_name'] ) ) : null; // phpcs:ignore WordPress.Security.NonceVerification
	68	+ $page = isset( $_POST['page'] ) ? sanitize_text_field( wp_unslash( $_POST['page'] ) ) : 1; // phpcs:ignore WordPress.Security.NonceVerification
	69	+ $per_page = isset( $_POST['per_page'] ) ? sanitize_text_field( wp_unslash( $_POST['per_page'] ) ) : self::WPDA_PER_PAGE; // phpcs:ignore WordPress.Security.NonceVerification
	70	+ $order = isset( $_POST['order'] ) ? sanitize_text_field( wp_unslash( $_POST['order'] ) ) : null; // phpcs:ignore WordPress.Security.NonceVerification
	71	+ $orderby = isset( $_POST['orderby'] ) ? sanitize_text_field( wp_unslash( $_POST['orderby'] ) ) : null; // phpcs:ignore WordPress.Security.NonceVerification
	72	+ $search = isset( $_POST['search'] ) ? sanitize_text_field( wp_unslash( $_POST['search'] ) ) : null; // phpcs:ignore WordPress.Security.NonceVerification
	73	+ } elseif ( isset( $_SERVER['REQUEST_METHOD'] ) && 'GET' === $_SERVER['REQUEST_METHOD'] ) {
	74	+ $schema_name = isset( $_GET['schema_name'] ) ? sanitize_text_field( wp_unslash( $_GET['schema_name'] ) ) : null; // phpcs:ignore WordPress.Security.NonceVerification
	75	+ $table_name = isset( $_GET['table_name'] ) ? sanitize_text_field( wp_unslash( $_GET['table_name'] ) ) : null; // phpcs:ignore WordPress.Security.NonceVerification
	76	+ $page = isset( $_GET['page'] ) ? sanitize_text_field( wp_unslash( $_GET['page'] ) ) : 1; // phpcs:ignore WordPress.Security.NonceVerification
	77	+ $per_page = isset( $_GET['per_page'] ) ? sanitize_text_field( wp_unslash( $_GET['per_page'] ) ) : self::WPDA_PER_PAGE; // phpcs:ignore WordPress.Security.NonceVerification
	78	+ $order = isset( $_GET['order'] ) ? sanitize_text_field( wp_unslash( $_GET['order'] ) ) : null; // phpcs:ignore WordPress.Security.NonceVerification
	79	+ $orderby = isset( $_GET['orderby'] ) ? sanitize_text_field( wp_unslash( $_GET['orderby'] ) ) : null; // phpcs:ignore WordPress.Security.NonceVerification
	80	+ $search = isset( $_GET['search'] ) ? sanitize_text_field( wp_unslash( $_GET['search'] ) ) : null; // phpcs:ignore WordPress.Security.NonceVerification
	81	+ } else {
	82	+ $schema_name = null;
	83	+ $table_name = null;
	84	+ $page = null;
	85	+ $per_page = null;
	86	+ $order = null;
	87	+ $orderby = null;
	88	+ $search = null;
	89	+ }
50	90
51		- // Plugin
52		- $plugin = new WPDA_Plugin();
53		- $plugin->register_rest_routes();
	91	+ if (
	92	+ null === $schema_name ||
	93	+ null === $table_name ||
	94	+ ! isset( $this->tables[ $schema_name ][ $table_name ] )
	95	+ ) {
	96	+ return new \WP_Error( 'query', __( 'Invalid arguments', 'wp-data-access' ), array( 'status' => 404 ) );
	97	+ }
54	98
55		- // Apps
56		- $apps = new WPDA_Apps();
57		- $apps->register_rest_routes();
	99	+ // Remove backticks from names and add them to query to prevent SQL injection.
	100	+ $schema_name = str_replace( '`', '', $schema_name );
	101	+ $table_name = str_replace( '`', '', $table_name );
58	102
59		- // Data Explorer
60		- $tree = new WPDA_Tree();
61		- $tree->register_rest_routes();
	103	+ global $wp_rest_auth_cookie;
	104	+ if ( true !== $wp_rest_auth_cookie ) {
	105	+ // Anonymous call.
	106	+ $requesting_user = 'anonymous';
	107	+ } else {
	108	+ // Authorized user (get user id from : WPDA::get_current_user_id()).
	109	+ $requesting_user = 'authorized';
	110	+ }
62	111
63		- // Data Tables and Data Forms
64		- $tables = new WPDA_Table();
65		- $tables->register_rest_routes();
	112	+ if (
	113	+ isset( $this->tables[ $schema_name ][ $table_name ][ $requesting_user ]['get'] ) &&
	114	+ true === $this->tables[ $schema_name ][ $table_name ][ $requesting_user ]['get']
	115	+ ) {
	116	+ return $this->query( $schema_name, $table_name, $page, $per_page, $order, $orderby, $search );
	117	+ }
66	118
67		- // Admin actions
68		- $actions = new WPDA_Actions();
69		- $actions->register_rest_routes();
	119	+ return new \WP_Error( 'query', __( 'Forbidden', 'wp-data-access' ), array( 'status' => 403 ) );
	120	+ }
70	121
71		- // Settings
72		- $settings = new WPDA_Settings();
73		- $settings->register_rest_routes();
	122	+ /**
	123	+ * Perform query and return result as JSON response
	124	+ *
	125	+ * @param string $schema_name Schema name (database).
	126	+ * @param string $table_name Table Name.
	127	+ * @param string $page Page number.
	128	+ * @param string $per_page Rows per page.
	129	+ * @param string $order Sorting columns.
	130	+ * @param string $orderby Ascending (default) or descending.
	131	+ * @param string $search Filter.
	132	+ * @return \WP_Error|\WP_REST_Response
	133	+ */
	134	+ private function query( $schema_name, $table_name, $page, $per_page, $order, $orderby, $search ) {
	135	+ $wpdadb = WPDADB::get_db_connection( $schema_name );
	136	+ if ( null !== $wpdadb ) {
	137	+ // Connected, perform queries.
	138	+ $suppress = $wpdadb->suppress_errors( true );
	139	+ $where = '';
	140	+ if ( null !== $search ) {
	141	+ // Add search filter.
	142	+ $wpda_list_columns = WPDA_List_Columns_Cache::get_list_columns( $schema_name, $table_name );
	143	+ $where = WPDA::construct_where_clause(
	144	+ $schema_name,
	145	+ $table_name,
	146	+ $wpda_list_columns->get_searchable_table_columns(),
	147	+ $search
	148	+ );
	149	+ if ( '' !== $where ) {
	150	+ $where = " where {$where} ";
	151	+ }
	152	+ }
	153	+ $sqlorder = '';
	154	+ if ( null !== $orderby ) {
	155	+ // Add order by.
	156	+ $_orderby = explode( ',', $orderby );
	157	+ $_order = explode( ',', $order );
	158	+ for ( $i = 0; $i < count( $_orderby ); $i++ ) { // phpcs:ignore Generic.CodeAnalysis.ForLoopWithTestFunctionCall, Squiz.PHP.DisallowSizeFunctionsInLoops
	159	+ if ( '' === $sqlorder ) {
	160	+ $sqlorder = 'order by ';
	161	+ } else {
	162	+ $sqlorder .= ',';
	163	+ }
	164	+ if ( isset( $_order[ $i ] ) ) {
	165	+ $sqlorder .= sanitize_sql_orderby( "{$_orderby[ $i ]} {$_order[ $i ]}" );
	166	+ } else {
	167	+ $sqlorder .= sanitize_sql_orderby( $_orderby[ $i ] );
	168	+ }
	169	+ }
	170	+ }
	171	+ if ( ! is_numeric( $per_page ) ) {
	172	+ $per_page = 10;
	173	+ }
	174	+ $offset = ( $page - 1 ) * $per_page; // Calculate offset.
	175	+ if ( ! is_numeric( $offset ) ) {
	176	+ $offset = 0;
	177	+ }
	178	+ // Query.
	179	+ $rows = $wpdadb->get_results(
	180	+ "select * from `{$table_name}` {$where} {$sqlorder} limit {$per_page} offset {$offset}",
	181	+ 'ARRAY_A'
	182	+ );
	183	+ if ( $wpdadb->last_error ) {
	184	+ // Handle SQL errors.
	185	+ return new \WP_Error( 'query', __( 'Unprocessable Entity', 'wp-data-access' ) . $where, array( 'status' => 422 ) );
	186	+ }
	187	+ // Count rows.
	188	+ $countrows = $wpdadb->get_results(
	189	+ "select count(1) as rowcount from `{$table_name}`",
	190	+ 'ARRAY_A'
	191	+ );
	192	+ if ( $wpdadb->last_error ) {
	193	+ // Handle SQL errors.
	194	+ return new \WP_Error( 'query', __( 'Unprocessable Entity', 'wp-data-access' ), array( 'status' => 422 ) );
	195	+ }
	196	+ $rowcount = isset( $countrows[0]['rowcount'] ) ? $countrows[0]['rowcount'] : 0;
	197	+ $pagecount = floor( $rowcount / $per_page );
	198	+ if ( $pagecount != $rowcount / $per_page ) { // phpcs:ignore WordPress.PHP.StrictComparisons
	199	+ $pagecount++;
	200	+ }
	201	+ $wpdadb->suppress_errors( $suppress );
74	202
	203	+ // Send response.
	204	+ $response = new \WP_REST_Response( $rows, 200 );
	205	+ $response->header( 'X-WP-Total', $rowcount ); // total rows for this query.
	206	+ $response->header( 'X-WP-TotalPages', $pagecount ); // pages for this query.
	207	+ return $response;
	208	+ } else {
	209	+ // Error connecting, return error.
	210	+ return new \WP_Error( 'query', __( 'Unprocessable Entity', 'wp-data-access' ), array( 'status' => 422 ) );
	211	+ }
75	212	}
76	213
77	214	}
78	215