| 1 |
<?php // phpcs:ignore Standard.Category.SniffName.ErrorCode |
| 2 |
/** |
| 3 |
* JSON REST API |
| 4 |
*/ |
| 5 |
|
| 6 |
namespace WPDataAccess\API { |
| 7 |
|
| 8 |
use WPDataAccess\Connection\WPDADB; |
| 9 |
use WPDataAccess\Data_Dictionary\WPDA_List_Columns_Cache; |
| 10 |
use WPDataAccess\WPDA; |
| 11 |
|
| 12 |
/** |
| 13 |
* JSON REST API main class |
| 14 |
*/ |
| 15 |
class WPDA_API { |
| 16 |
|
| 17 |
const WPDA_API_VERSION = '/v1'; |
| 18 |
const WPDA_REST_API = 'wpda_rest_api'; |
| 19 |
const WPDA_PER_PAGE = 10; |
| 20 |
|
| 21 |
/** |
| 22 |
* Tables accessible through REST API (default = none) |
| 23 |
* |
| 24 |
* @var array |
| 25 |
*/ |
| 26 |
private $tables = array(); |
| 27 |
|
| 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 |
} |
| 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 |
) |
| 54 |
); |
| 55 |
} |
| 56 |
|
| 57 |
/** |
| 58 |
* Get list from table |
| 59 |
* |
| 60 |
* Supports: searching, ordering and pagination through URL arguments |
| 61 |
* |
| 62 |
* @return \WP_Error|\WP_REST_Response |
| 63 |
*/ |
| 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 |
} |
| 90 |
|
| 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 |
} |
| 98 |
|
| 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 ); |
| 102 |
|
| 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 |
} |
| 111 |
|
| 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 |
} |
| 118 |
|
| 119 |
return new \WP_Error( 'query', __( 'Forbidden', 'wp-data-access' ), array( 'status' => 403 ) ); |
| 120 |
} |
| 121 |
|
| 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 ); |
| 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 |
} |
| 212 |
} |
| 213 |
|
| 214 |
} |
| 215 |
|
| 216 |
} |
| 217 |
|
