<?php
namespace WPDataAccess\API;
use stdClass;
use WPDataAccess\Connection\WPDADB;
use WPDataAccess\Data_Dictionary\WPDA_Dictionary_Access;
use WPDataAccess\Data_Dictionary\WPDA_List_Columns_Cache;
use WPDataAccess\Plugin_Table_Models\WPDA_Table_Settings_Model;
use WPDataAccess\Utilities\WPDA_WP_Media;
use WPDataAccess\WPDA;
class WPDA_Table extends WPDA_API_Core {
const WPDA_SEARCH_MODES = array(
'contains',
'startsWith',
'endsWith',
'equals',
'notEquals',
'empty',
'notEmpty',
'between',
'betweenInclusive',
'greaterThan',
'greaterThanOrEqualTo',
'lessThan',
'lessThanOrEqualTo'
);
public function register_rest_routes() {
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/meta', array(
'methods' => array('POST'),
'callback' => array($this, 'table_meta'),
'permission_callback' => '__return_true',
'args' => array(
'dbs' => $this->get_param( 'dbs' ),
'tbl' => $this->get_param( 'tbl' ),
'waa' => array(
'required' => false,
'type' => 'boolean',
'description' => __( 'With admin actions (to support table exports)', 'wp-data-access' ),
),
),
) );
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/select', array(
'methods' => array('GET', 'POST'),
'callback' => array($this, 'table_select'),
'permission_callback' => '__return_true',
'args' => array(
'dbs' => $this->get_param( 'dbs' ),
'tbl' => $this->get_param( 'tbl' ),
'col' => $this->get_param( 'cols' ),
'page_index' => $this->get_param( 'page_index' ),
'page_size' => $this->get_param( 'page_size' ),
'search' => $this->get_param( 'search' ),
'search_columns' => $this->get_param( 'search_columns' ),
'search_column_fns' => $this->get_param( 'search_column_fns' ),
'sorting' => $this->get_param( 'sorting' ),
'row_count' => $this->get_param( 'row_count' ),
'row_count_estimate' => $this->get_param( 'row_count_estimate' ),
'media' => $this->get_param( 'media' ),
),
) );
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/get', array(
'methods' => array('GET', 'POST'),
'callback' => array($this, 'table_get'),
'permission_callback' => '__return_true',
'args' => array(
'dbs' => $this->get_param( 'dbs' ),
'tbl' => $this->get_param( 'tbl' ),
'key' => $this->get_param( 'key' ),
'media' => $this->get_param( 'media' ),
),
) );
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/insert', array(
'methods' => array('GET', 'POST'),
'callback' => array($this, 'table_insert'),
'permission_callback' => '__return_true',
'args' => array(
'dbs' => $this->get_param( 'dbs' ),
'tbl' => $this->get_param( 'tbl' ),
'val' => $this->get_param( 'val' ),
),
) );
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/update', array(
'methods' => array('GET', 'POST'),
'callback' => array($this, 'table_update'),
'permission_callback' => '__return_true',
'args' => array(
'dbs' => $this->get_param( 'dbs' ),
'tbl' => $this->get_param( 'tbl' ),
'key' => $this->get_param( 'key' ),
'val' => $this->get_param( 'val' ),
),
) );
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/delete', array(
'methods' => array('GET', 'POST'),
'callback' => array($this, 'table_delete'),
'permission_callback' => '__return_true',
'args' => array(
'dbs' => $this->get_param( 'dbs' ),
'tbl' => $this->get_param( 'tbl' ),
'key' => $this->get_param( 'key' ),
),
) );
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/lov', array(
'methods' => array('GET', 'POST'),
'callback' => array($this, 'table_lov'),
'permission_callback' => '__return_true',
'args' => array(
'dbs' => $this->get_param( 'dbs' ),
'tbl' => $this->get_param( 'tbl' ),
'col' => $this->get_param( 'col' ),
),
) );
}
/**
* Get table meta info.
*
* @param WP_REST_Request $request Rest API request.
* @return \WP_Error|\WP_REST_Response
*/
public function table_meta( $request ) {
$dbs = $request->get_param( 'dbs' );
$tbl = $request->get_param( 'tbl' );
$waa = $request->get_param( 'waa' );
if ( $this->check_table_access(
$dbs,
$tbl,
$request,
'select',
$msg
) ) {
return $this->WPDA_Rest_Response( '', $this->get_table_meta_data( $dbs, $tbl, $waa ) );
} else {
if ( 'rest_cookie_invalid_nonce' === $msg ) {
return $this->invalid_nonce();
} else {
return new \WP_Error('error', $msg, array(
'status' => 401,
));
}
}
}
/**
* Database table query using the full primary key. Must return exactly one row.
*
* @param WP_REST_Request $request Rest API request.
* @return \WP_Error|\WP_REST_Response
*/
public function table_get( $request ) {
$dbs = $request->get_param( 'dbs' );
$tbl = $request->get_param( 'tbl' );
$key = $request->get_param( 'key' );
$media = $request->get_param( 'media' );
if ( $this->check_table_access(
$dbs,
$tbl,
$request,
'select',
$msg
) ) {
return $this->get(
$dbs,
$tbl,
$key,
$media
);
} else {
if ( 'rest_cookie_invalid_nonce' === $msg ) {
return $this->invalid_nonce();
} else {
return new \WP_Error('error', $msg, array(
'status' => 401,
));
}
}
}
/**
* Insert one row.
*
* @param WP_REST_Request $request Rest API request.
* @return \WP_Error|\WP_REST_Response
*/
public function table_insert( $request ) {
$dbs = $request->get_param( 'dbs' );
$tbl = $request->get_param( 'tbl' );
$val = $request->get_param( 'val' );
if ( $this->check_table_access(
$dbs,
$tbl,
$request,
'insert',
$msg
) ) {
return $this->insert( $dbs, $tbl, $val );
} else {
if ( 'rest_cookie_invalid_nonce' === $msg ) {
return $this->invalid_nonce();
} else {
return new \WP_Error('error', $msg, array(
'status' => 401,
));
}
}
}
/**
* Update uses primary key. Must return exactly one row.
*
* @param WP_REST_Request $request Rest API request.
* @return \WP_Error|\WP_REST_Response
*/
public function table_update( $request ) {
$dbs = $request->get_param( 'dbs' );
$tbl = $request->get_param( 'tbl' );
$key = $request->get_param( 'key' );
$val = $request->get_param( 'val' );
if ( $this->check_table_access(
$dbs,
$tbl,
$request,
'update',
$msg
) ) {
return $this->update(
$dbs,
$tbl,
$key,
$val
);
} else {
if ( 'rest_cookie_invalid_nonce' === $msg ) {
return $this->invalid_nonce();
} else {
return new \WP_Error('error', $msg, array(
'status' => 401,
));
}
}
}
/**
* Delete uses primary key. Must return exactly one row.
*
* @param WP_REST_Request $request Rest API request.
* @return \WP_Error|\WP_REST_Response
*/
public function table_delete( $request ) {
$dbs = $request->get_param( 'dbs' );
$tbl = $request->get_param( 'tbl' );
$key = $request->get_param( 'key' );
if ( $this->check_table_access(
$dbs,
$tbl,
$request,
'delete',
$msg
) ) {
return $this->delete( $dbs, $tbl, $key );
} else {
if ( 'rest_cookie_invalid_nonce' === $msg ) {
return $this->invalid_nonce();
} else {
return new \WP_Error('error', $msg, array(
'status' => 401,
));
}
}
}
/**
* Database table query to populate a list of values for a specific table/column.
*
* @param WP_REST_Request $request Rest API request.
* @return \WP_Error|\WP_REST_Response
*/
public function table_lov( $request ) {
}
/**
* Database table query.
*
* Supports: searching, ordering and pagination.
*
* @param WP_REST_Request $request Rest API request.
* @return \WP_Error|\WP_REST_Response
*/
public function table_select( $request ) {
$dbs = $request->get_param( 'dbs' );
$tbl = $request->get_param( 'tbl' );
$col = $request->get_param( 'col' );
$page_index = $request->get_param( 'page_index' );
$page_size = $request->get_param( 'page_size' );
$search = $request->get_param( 'search' );
$search_columns = $request->get_param( 'search_columns' );
$search_column_fns = $request->get_param( 'search_column_fns' );
$sorting = $request->get_param( 'sorting' );
$row_count = $request->get_param( 'row_count' );
$row_count_estimate = $request->get_param( 'row_count_estimate' );
$media = $request->get_param( 'media' );
if ( $this->check_table_access(
$dbs,
$tbl,
$request,
'select',
$msg
) ) {
return $this->select(
$dbs,
$tbl,
$col,
$page_index,
$page_size,
$search,
$search_columns,
$search_column_fns,
$sorting,
$row_count,
$row_count_estimate,
$media
);
} else {
if ( 'rest_cookie_invalid_nonce' === $msg ) {
return $this->invalid_nonce();
} else {
return new \WP_Error('error', $msg, array(
'status' => 401,
));
}
}
}
/**
* Perform query and return result as JSON response.
*
* @param string $dbs Schema name (database).
* @param string $tbl Table Name.
* @param array $column_name Column name.
* @param array $search Global search.
* @param array $search_columns Column filters.
* @param array $search_column_fns Column filter fns.
* @return \WP_Error|\WP_REST_Response
*/
public function lov(
$dbs,
$tbl,
$column_name,
$cascade = false,
$default_where = '',
$search = '',
$column_names = array(),
$search_columns = array(),
$search_column_fns = array(),
$lookups = array(),
$md = array(),
$m2m_relationship = array(),
$search_data_types = array()
) {
}
public function lookup(
$dbs,
$tbl,
$column_key,
$column_value,
$column_dynamic_values,
$default_where,
$cascade = false,
$cascade_table = '',
$cascade_column = '',
$cascade_where = '',
$search = '',
$column_names = array(),
$search_columns = array(),
$search_column_fns = array(),
$lookups = array(),
$md = array(),
$m2m_relationship = array(),
$search_data_types = array()
) {
$wpdadb = WPDADB::get_db_connection( $dbs );
if ( null === $wpdadb ) {
// Error connecting.
return new \WP_Error('error', "Error connecting to database {$dbs}", array(
'status' => 420,
));
} else {
// Connected, perform queries.
$suppress = $wpdadb->suppress_errors( true );
$subquery = '';
$where = '';
if ( '' !== trim( $default_where ) ) {
if ( 'where' !== strtolower( substr( trim( $default_where ), 0, 5 ) ) ) {
$where = "where {$default_where}";
} else {
$where = $default_where;
}
}
$dynamic_where = array();
if ( is_array( $column_dynamic_values ) && 0 < count( $column_dynamic_values ) ) {
foreach ( $column_dynamic_values as $key => $value ) {
$dynamic_where[] = $wpdadb->prepare( " `{$key}` = %s ", $value );
}
$where .= (( '' === $where ? ' where ' : ' and ' )) . ' (' . implode( ' and ', $dynamic_where ) . ') ';
}
if ( strpos( $column_value, ',' ) !== false ) {
$columns = explode( ',', $column_value );
$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) );
} else {
$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) );
}
$sql .= " {$where} order by 2 ";
// $where already sanitized
$dataset = $wpdadb->get_results( $sql, 'OBJECT' );
$wpdadb->suppress_errors( $suppress );
// Send response.
if ( '' === $wpdadb->last_error ) {
// Prepare debug info.
if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
$debug = array(
'debug' => array(
'sql' => preg_replace( "/\\s+/", " ", $sql ),
'where' => $where ?? '',
),
);
} else {
$debug = null;
}
// Add context node to response.
$context = array();
if ( isset( $debug['debug'] ) && 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
$context['debug'] = $debug['debug'];
}
return $this->WPDA_Rest_Response( '', $dataset, $context );
} else {
return new \WP_Error('error', $wpdadb->last_error, array(
'status' => 420,
));
}
}
}
/**
* Perform query and return result as JSON response.
*
* @param string $dbs Schema name (database).
* @param string $tbl Table Name.
* @param array $primary Primary (key|value pairs.
* @param array $media_columns Media columns.
* @param array $column_names Just a plain array containing the column names.
* @return \WP_Error|\WP_REST_Response
*/
public function get(
$dbs,
$tbl,
$primary_key,
$media_columns,
$column_names = array(),
$default_where = ''
) {
$wpdadb = WPDADB::get_db_connection( $dbs );
if ( null === $wpdadb ) {
// Error connecting.
return new \WP_Error('error', "Error connecting to database {$dbs}", array(
'status' => 420,
));
} else {
// Connected, perform queries.
$suppress = $wpdadb->suppress_errors( true );
$where = '';
foreach ( $primary_key as $primary_key_column => $primary_key_value ) {
$where = ( '' === $where ? ' where ' : $where . ' and ' );
$where .= $wpdadb->prepare( " `%1s` = %s ", array($primary_key_column, $primary_key_value) );
}
if ( '' !== $default_where ) {
if ( '' === $where ) {
$where = $default_where;
} else {
$where .= " and {$default_where} ";
}
}
$selected_columns = '*';
if ( 0 < count( $column_names ) ) {
$selected_columns = '`' . implode( '`,`', array_map( function ( $column_name ) {
return WPDA::remove_backticks( $column_name );
}, $column_names ) ) . '`';
}
$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' );
// Prepare debug info.
if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
$debug = array(
'debug' => array(
'where' => $where,
),
);
} else {
$debug = null;
}
$wpdadb->suppress_errors( $suppress );
// Send response.
if ( 0 === count( $dataset ) ) {
return new \WP_Error('error', "No data found", array(
'status' => 420,
));
} elseif ( 1 === count( $dataset ) ) {
$media = array();
if ( 0 < count( $media_columns ) ) {
foreach ( $media_columns as $media_column_name => $media_column_type ) {
if ( isset( $dataset[0][$media_column_name] ) ) {
if ( in_array( $media_column_type, [
'WP-Image',
'WP-Attachment',
'WP-Audio',
'WP-Video'
] ) ) {
$media[$media_column_name] = WPDA_WP_Media::get_media_url( $dataset[0][$media_column_name] );
}
}
}
}
$context = array();
$context['media'] = $media;
if ( isset( $debug['debug'] ) && 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
$context['debug'] = $debug['debug'];
}
return $this->WPDA_Rest_Response( '', $dataset, $context );
} else {
return new \WP_Error('error', "Invalid arguments", array(
'status' => 420,
));
}
}
}
public function insert( $dbs, $tbl, $column_values ) {
$wpdadb = WPDADB::get_db_connection( $dbs );
if ( null === $wpdadb ) {
// Error connecting.
return new \WP_Error('error', "Error connecting to database {$dbs}", array(
'status' => 420,
));
} else {
// Sanitize column names and values.
$sanitized_column_values = self::sanitize_column_values( $dbs, $tbl, $column_values );
if ( false === $sanitized_column_values ) {
return new \WP_Error('error', "Invalid arguments", array(
'status' => 420,
));
}
// Insert row.
$rows_inserted = $wpdadb->insert( $tbl, $sanitized_column_values );
// Send response.
if ( 1 === $rows_inserted ) {
return $this->WPDA_Rest_Response( __( 'Row successfully inserted', 'wp-data-access' ), null, array(
'insert_id' => $wpdadb->insert_id,
) );
} else {
if ( '' !== $wpdadb->last_error ) {
return new \WP_Error('error', $wpdadb->last_error, array(
'status' => 420,
));
} else {
return new \WP_Error('error', 'Insert failed', array(
'status' => 420,
));
}
}
}
}
public function update(
$dbs,
$tbl,
$primary_key,
$column_values,
$column_names = array()
) {
$wpdadb = WPDADB::get_db_connection( $dbs );
if ( null === $wpdadb ) {
// Error connecting.
return new \WP_Error('error', "Error connecting to database {$dbs}", array(
'status' => 420,
));
} else {
// Sanitize column names and values.
$sanitized_column_values = self::sanitize_column_values( $dbs, $tbl, $column_values );
if ( false === $sanitized_column_values ) {
return new \WP_Error('error', "Invalid arguments", array(
'status' => 420,
));
}
// Update row.
$rows_inserted = $wpdadb->update( $tbl, $sanitized_column_values, $primary_key );
// Send response.
if ( 0 === $rows_inserted ) {
return $this->WPDA_Rest_Response_Info( 'Nothing to update' );
} elseif ( 1 === $rows_inserted ) {
$context = null;
if ( 0 < count( $column_names ) ) {
// Return updated values
$updated_row = $this->get(
$dbs,
$tbl,
$primary_key,
$column_names
);
if ( isset( $updated_row->data['data'][0] ) ) {
$updated_values = $updated_row->data['data'][0];
$updated_context = array();
foreach ( $updated_values as $key => $value ) {
if ( !isset( $column_values[$key] ) ) {
$updated_context[$key] = $value;
}
}
if ( 0 < count( $updated_context ) ) {
$context = array(
'updated' => $updated_context,
);
}
}
}
return $this->WPDA_Rest_Response( __( 'Row successfully updated', 'wp-data-access' ), null, $context );
} else {
if ( '' !== $wpdadb->last_error ) {
return new \WP_Error('error', $wpdadb->last_error, array(
'status' => 420,
));
} else {
return new \WP_Error('error', 'Update failed', array(
'status' => 420,
));
}
}
}
}
public function delete( $dbs, $tbl, $primary_key ) {
$wpdadb = WPDADB::get_db_connection( $dbs );
if ( null === $wpdadb ) {
// Error connecting.
return new \WP_Error('error', "Error connecting to database {$dbs}", array(
'status' => 420,
));
} else {
// Delete row.
$rows_deleted = $wpdadb->delete( $tbl, $primary_key );
// Send response.
if ( 0 === $rows_deleted ) {
return $this->WPDA_Rest_Response_Info( __( 'No data found', 'wp-data-access' ) );
} elseif ( 1 === $rows_deleted ) {
return $this->WPDA_Rest_Response( __( 'Row successfully deleted', 'wp-data-access' ) );
} else {
if ( '' !== $wpdadb->last_error ) {
return new \WP_Error('error', $wpdadb->last_error, array(
'status' => 420,
));
} else {
return new \WP_Error('error', 'Delete failed', array(
'status' => 420,
));
}
}
}
}
private function generate_lookup_condition(
$wpdadb,
$lookups,
$column_name,
$search_values,
$search_column_fns,
$filter_mode = null,
$filter_key = false
) {
$lookup = $lookups[$column_name];
$lookup_table = $lookup['tbl'];
$lookup_key = $lookup['key'];
$lookup_columns = explode( ',', $lookup['value'] );
$lookup_where = array();
if ( $filter_key ) {
$filter_columns = array($lookup_key);
} else {
$filter_columns = $lookup_columns;
}
foreach ( $filter_columns as $lookup_column ) {
foreach ( $search_values as $search_value ) {
$lookup_where[] = $this->add_filter(
$wpdadb,
$lookup_column,
( $filter_mode !== null ? $filter_mode : $search_column_fns[$column_name] ),
$search_value
);
}
}
if ( 0 < count( $lookup_where ) ) {
return $wpdadb->prepare( ' `%1s` in ( select `%1s` from `%1s` where (' . implode( ' or ', $lookup_where ) . ') ) ', array(
$column_name,
$lookup_key,
$lookup_table,
$lookup_columns[0],
"%{$search_values[0]}%"
) );
} else {
return null;
}
}
public static function remove_where_from_sql( $sql ) {
if ( 'where' === substr( trim( $sql ), 0, 5 ) ) {
$pos = strpos( $sql, 'where' );
if ( false !== $pos ) {
$sql = substr_replace(
$sql,
'',
$pos,
5
);
}
}
return $sql;
}
private function get_md( $md, $wpdadb, $m2m_relationship ) {
}
private function get_global_filter(
$wpdadb,
$search,
$column_names,
$lookups,
$m2m_relationship
) {
$where_global = array();
if ( null !== $search && "" !== $search ) {
foreach ( $column_names as $column_name => $queryable ) {
if ( $queryable ) {
if ( isset( $lookups[$column_name] ) ) {
// Perform look search.
$condition = $this->generate_lookup_condition(
$wpdadb,
$lookups,
$column_name,
array($search),
array(),
'contains'
);
if ( null !== $condition ) {
$where_global[] = $condition;
}
} else {
$where_global[] = $wpdadb->prepare( " `%1s` like '%s' ", array($this->convert_column_name( $m2m_relationship, $column_name ), '%' . esc_sql( $search ) . '%') );
}
}
}
}
return $where_global;
}
private function get_column_filters(
$wpdadb,
$search_columns,
$search_column_fns,
$lookups,
$m2m_relationship,
$search_data_types
) {
}
private function get_where(
$wpdadb,
$default_where,
$md,
$m2m_relationship,
$search,
$column_names,
$lookups,
$search_columns,
$search_column_fns,
$search_data_types
) {
// Default where.
if ( '' !== trim( $default_where ) && 'where' !== strtolower( substr( trim( $default_where ), 0, 5 ) ) ) {
$where = "where {$default_where}";
} else {
$where = $default_where;
}
// Global filter.
$where_global = $this->get_global_filter(
$wpdadb,
$search,
$column_names,
$lookups,
$m2m_relationship
);
if ( 0 < count( $where_global ) ) {
$where .= (( '' === trim( $where ) ? ' where ' : ' and ' )) . $this->add_condition( $where_global, 'or' );
}
return $where;
}
/**
* Perform query and return result as JSON response.
*
* @param string $dbs Schema name (database).
* @param string $tbl Table Name.
* @param string $column_names Column Names.
* @param string $page_index Page number.
* @param string $page_size Rows per page.
* @param string $search Filter.
* @param string $search_columns Column search filters.
* @param string $search_column_fns Column search filter modes.
* @param string $Sorting Order by.
* @param integer $last_row_count Row count previous request.
* @param string $row_count_estimate Indicates if row count estimate should be used.
* @param string $media_columns Media columns.
* @param string $default_where Defaul where clause
* @param string $default_orderby Defaul order by clause
* @return \WP_Error|\WP_REST_Response
*/
public function select(
$dbs,
$tbl,
$column_names,
$page_index,
$page_size,
$search,
$search_columns,
$search_column_fns,
$sorting,
$last_row_count,
$row_count_estimate,
$media_columns,
$default_where = '',
$default_orderby = '',
$lookups = array(),
$md = array(),
$m2m_relationship = array(),
$search_data_types = array()
) {
$wpdadb = WPDADB::get_db_connection( $dbs );
if ( null === $wpdadb ) {
// Error connecting.
return new \WP_Error('error', "Error connecting to database {$dbs}", array(
'status' => 420,
));
} else {
$suppress = $wpdadb->suppress_errors( true );
// Build where clause.
$where = $this->get_where(
$wpdadb,
$default_where,
$md,
$m2m_relationship,
$search,
$column_names,
$lookups,
$search_columns,
$search_column_fns,
$search_data_types
);
// Build order by.
$sqlorder = '';
if ( is_array( $sorting ) && 0 < count( $sorting ) ) {
foreach ( $sorting as $sort ) {
if ( '' === $sqlorder ) {
$sqlorder = 'order by ';
} else {
$sqlorder .= ',';
}
$sqlorder .= '`' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` ' . (( $sort['desc'] ? 'desc' : 'asc' ));
}
}
if ( '' === $sqlorder && '' !== trim( $default_orderby ) ) {
$sqlorder = $default_orderby;
}
// Add pagination.
if ( !is_numeric( $page_size ) ) {
$page_size = 10;
}
$offset = $page_index * $page_size;
// Calculate offset.
if ( !is_numeric( $offset ) ) {
$offset = 0;
}
// Prepare query.
$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";
$sql_tables = array($tbl);
// Prepare debug info.
if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
$debug = array(
'debug' => array(
'sql' => preg_replace( "/\\s+/", " ", $sql ),
'where' => $where,
'order by' => $sqlorder,
),
);
} else {
$debug = null;
}
// Perform query.
$dataset = $wpdadb->get_results( $wpdadb->prepare( $sql . (( 0 < $page_size ? " limit {$page_size} offset {$offset} " : '' )), $sql_tables ), 'ARRAY_A' );
if ( $wpdadb->last_error ) {
// Handle SQL errors.
return new \WP_Error('error', $wpdadb->last_error, array(
'status' => 420,
'debug' => ( isset( $debug['debug'] ) ? $debug : null ),
));
}
if ( is_numeric( $last_row_count ) and 0 <= $last_row_count ) {
// Prevents an extra query.
$rowcount = $last_row_count;
} else {
$estimate = false;
if ( '1' === $row_count_estimate && '' === $where ) {
// Perform row count estimate
$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' );
if ( isset( $countrows[0]['rowcount'] ) ) {
$estimate = true;
}
}
if ( !$estimate ) {
if ( !$estimate ) {
// (Re)Count rows.
$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' );
}
}
if ( $wpdadb->last_error ) {
// Handle SQL errors.
return new \WP_Error('error', $wpdadb->last_error, array(
'status' => 420,
));
}
if ( isset( $countrows[0]['rowcount'] ) ) {
$rowcount = $countrows[0]['rowcount'];
} else {
$rowcount = 0;
}
}
// Add context node to response
$context = array();
if ( isset( $debug['debug'] ) && 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
$context['debug'] = $debug['debug'];
}
if ( 0 < count( $media_columns ) ) {
// Handle WP media library
$media = array();
for ($i = 0; $i < count( $dataset ); $i++) {
$media_row = array();
foreach ( $media_columns as $media_column_name => $media_column_type ) {
if ( isset( $dataset[$i][$media_column_name] ) ) {
$media_row[$media_column_name] = WPDA_WP_Media::get_media_url( $dataset[$i][$media_column_name] );
}
}
$media[] = $media_row;
}
// Add media to context node
$context['media'] = $media;
}
$wpdadb->suppress_errors( $suppress );
// Send response.
$response = $this->WPDA_Rest_Response(
'',
$dataset,
$context,
array(
'rowCount' => $rowcount,
)
);
$response->header( 'X-WP-Total', $rowcount );
// Total rows for this query.
if ( 0 < $page_size ) {
$pagecount = floor( $rowcount / $page_size );
if ( $pagecount != $rowcount / $page_size ) {
// phpcs:ignore WordPress.PHP.StrictComparisons
$pagecount++;
}
} else {
// Prevent division by zero
$pagecount = 0;
}
$response->header( 'X-WP-TotalPages', $pagecount );
// Total pages for this query.
return $response;
}
}
private function convert_column_name( $m2m_relationship, $column_name ) {
if ( 0 < count( $m2m_relationship ) ) {
// Map join table and relation table columns.
if ( 'd.' === substr( $column_name, 0, 2 ) ) {
return 'd`.`' . $this->sanitize_db_identifier( substr( $column_name, 2 ) );
} else {
return 'm`.`' . $this->sanitize_db_identifier( $column_name );
}
} else {
// Return plaing column name.
return $this->sanitize_db_identifier( $column_name );
}
}
private function map_columns( $prefix, $column_names ) {
return implode( ",", array_map( function ( $v ) use($prefix) {
$c = $this->sanitize_db_identifier( $v );
$r = ( 'd' === $prefix ? "d.{$c}" : $c );
return "`{$prefix}`.`{$c}` as \"{$r}\"";
}, array_keys( $column_names ) ) );
}
public function add_filter(
$wpdadb,
$search_column,
$search_column_fns,
$search_value,
$m2m_relationship = array(),
$search_data_types = array()
) {
}
public static function add_condition( $where_lines, $operand = 'and' ) {
if ( 0 < count( array_filter( $where_lines ) ) ) {
// Apply all searches.
return ' ( (' . implode( ") {$operand} (", array_filter( $where_lines ) ) . ') ) ';
} else {
return "";
}
}
/**
* Get table meta data.
*
* @param string $dbs Database schema name.
* @param string $tbl Database table name.
* @param string $waa With admin actions.
* @return array\object
*/
public function get_table_meta_data( $dbs, $tbl, $waa ) {
$sql_create_table = '';
if ( current_user_can( 'manage_options' ) ) {
// Admin user has access to all resources
$access = array(
'select' => array('POST'),
'insert' => array('POST'),
'update' => array('POST'),
'delete' => array('POST'),
);
// Get create table script
$wpdadb = WPDADB::get_db_connection( $dbs );
if ( null !== $wpdadb ) {
$suppress_errors = $wpdadb->suppress_errors;
$wpdadb->suppress_errors = true;
$wpdadb->query( "SET sql_mode = 'NO_TABLE_OPTIONS'" );
$sql = $wpdadb->get_results( $wpdadb->prepare( 'show create table `%1s`', array($tbl) ), 'ARRAY_N' );
if ( isset( $sql[0][1] ) ) {
$sql_create_table = $sql[0][1];
}
$wpdadb->suppress_errors = $suppress_errors;
}
} else {
$access = $this->get_table_access( $dbs, $tbl );
}
$settings = new stdClass();
if ( null !== $access ) {
$columns = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
$settings_db = WPDA_Table_Settings_Model::query( $tbl, $dbs );
if ( isset( $settings_db[0]['wpda_table_settings'] ) ) {
$settings = json_decode( $settings_db[0]['wpda_table_settings'] );
// Remove old settings from response.
unset($settings->form_labels);
unset($settings->list_labels);
unset($settings->custom_settings);
unset($settings->search_settings);
}
$settings->ui = WPDA_Settings::get_admin_settings( $dbs, $tbl );
$rest_api = get_option( WPDA_API::WPDA_REST_API_TABLE_ACCESS );
if ( isset( $rest_api[$dbs][$tbl] ) ) {
$settings->rest_api = $rest_api[$dbs][$tbl];
}
$settings->env = $this->get_env();
$wp_nonce_action_alter = "wpda-alter-{$tbl}";
$wp_nonce_alter = wp_create_nonce( $wp_nonce_action_alter );
$wp_nonce_refresh = null;
$connect = null;
global $wpdb;
$settings->wp = [
'roles' => $this->get_wp_roles(),
'users' => $this->get_wp_users(),
'home' => admin_url( 'admin.php' ),
'homea' => admin_url( 'admin-ajax.php' ),
'tables' => array_values( $wpdb->tables() ),
'date_format' => get_option( 'date_format' ),
'time_format' => get_option( 'time_format' ),
'alter' => $wp_nonce_alter,
'refresh' => $wp_nonce_refresh,
'connect' => $connect,
];
if ( true === $waa ) {
$settings->wp['aonce'] = implode( '-', array(
wp_create_nonce( 'wpda-export-' . json_encode( $tbl ) ),
// Table export
wp_create_nonce( 'wpda-rename-' . $tbl ),
) );
}
$media = $this->get_media( $dbs, $tbl, $columns->get_table_columns() );
}
return array(
'columns' => $columns->get_table_columns(),
'table_labels' => $columns->get_table_header_labels(),
'form_labels' => $columns->get_table_column_headers(),
'primary_key' => $columns->get_table_primary_key(),
'access' => $access,
'settings' => $settings,
'media' => $media['media'],
'wp_media' => $media['wp_media'],
'table_info' => $this->get_table_info( $dbs, $tbl ),
'create' => $sql_create_table,
);
}
private function get_table_access( $dbs, $tbl ) {
if ( current_user_can( 'manage_options' ) ) {
// Check administrator rights
if ( is_admin() ) {
$access = WPDA_Dictionary_Access::check_table_access_backend( $dbs, $tbl, $done );
} else {
$access = WPDA_Dictionary_Access::check_table_access_frontend( $dbs, $tbl, $done );
}
if ( $access ) {
// Administrator access granted
return array(
'select' => array('POST'),
'insert' => array('POST'),
'update' => array('POST'),
'delete' => array('POST'),
);
}
}
$tables = get_option( WPDA_API::WPDA_REST_API_TABLE_ACCESS );
if ( false !== $tables && isset( $tables[$dbs][$tbl] ) && is_array( $tables[$dbs][$tbl] ) ) {
$table = $tables[$dbs][$tbl];
$table_access = new \stdClass();
$table_access->select = $this->get_table_access_action( $table, 'select' );
$table_access->insert = $this->get_table_access_action( $table, 'insert' );
$table_access->update = $this->get_table_access_action( $table, 'update' );
$table_access->delete = $this->get_table_access_action( $table, 'delete' );
return $table_access;
}
return false;
}
private function get_table_access_action( $table, $action ) {
if ( isset( $table[$action]['authorization'], $table[$action]['methods'] ) && is_array( $table[$action]['methods'] ) && 0 < count( $table[$action]['methods'] ) ) {
if ( 'anonymous' === $table[$action]['authorization'] ) {
return $table[$action]['methods'];
} else {
// Check authorized users
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'] ) ) {
return $table[$action]['methods'];
}
// Check authorized roles
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'] ) ) ) {
return $table[$action]['methods'];
}
}
}
return array();
}
/**
* Check if access is grant for requested database/table.
*
* @param string $dbs Remote or local database connection string.
* @param string $tbl Database table name.
* @param onject $request Request object.
* @param string $action Possible values: select, insert, update, delete.
* @return bool
*/
private function check_table_access(
$dbs,
$tbl,
$request,
$action,
&$msg = ''
) {
if ( current_user_can( 'manage_options' ) ) {
// Grant access to administrators always.
return true;
}
$tables = get_option( WPDA_API::WPDA_REST_API_TABLE_ACCESS );
if ( false === $tables ) {
// No tables.
$msg = __( 'Unauthorized', 'wp-data-access' );
return false;
}
if ( !(isset( $tables[$dbs][$tbl][$action]['methods'] ) && is_array( $tables[$dbs][$tbl][$action]['methods'] )) ) {
// No methods.
$msg = __( 'Unauthorized', 'wp-data-access' );
return false;
} else {
if ( !in_array( $request->get_method(), $tables[$dbs][$tbl][$action]['methods'] ) ) {
//phpcs:ignore - 8.1 proof
$msg = __( 'Unauthorized', 'wp-data-access' );
return false;
}
}
if ( !isset( $tables[$dbs][$tbl][$action]['authorization'] ) ) {
// No authorization.
$msg = __( 'Unauthorized', 'wp-data-access' );
return false;
} else {
if ( 'anonymous' === $tables[$dbs][$tbl][$action]['authorization'] ) {
// Access granted to all users.
return true;
}
}
global $wp_rest_auth_cookie;
if ( true !== $wp_rest_auth_cookie ) {
// No anonymous access.
$msg = __( 'Unauthorized', 'wp-data-access' );
return false;
} else {
if ( 'authorized' !== $tables[$dbs][$tbl][$action]['authorization'] ) {
// Authorization check.
$msg = __( 'Unauthorized', 'wp-data-access' );
return false;
}
// Authorized access requires a valid nonce.
if ( !wp_verify_nonce( $request->get_header( 'X-WP-Nonce' ), 'wp_rest' ) ) {
$msg = 'rest_cookie_invalid_nonce';
return false;
}
if ( !(isset( $tables[$dbs][$tbl][$action]['authorized_users'] ) && is_array( $tables[$dbs][$tbl][$action]['authorized_users'] )) ) {
// No users.
$msg = __( 'Unauthorized', 'wp-data-access' );
return false;
} else {
$requesting_user_login = $this->get_user_login();
if ( 0 < count( $tables[$dbs][$tbl][$action]['authorized_users'] ) && in_array( $requesting_user_login, $tables[$dbs][$tbl][$action]['authorized_users'] ) ) {
return true;
}
}
if ( !(isset( $tables[$dbs][$tbl][$action]['authorized_roles'] ) && is_array( $tables[$dbs][$tbl][$action]['authorized_roles'] )) ) {
// No roles.
$msg = __( 'Unauthorized', 'wp-data-access' );
return false;
} else {
$requesting_user_roles = $this->get_user_roles();
if ( false === $requesting_user_roles ) {
$requesting_user_roles = array();
}
if ( 0 < count( $tables[$dbs][$tbl][$action]['authorized_roles'] ) && 0 < count( array_intersect( $requesting_user_roles, $tables[$dbs][$tbl][$action]['authorized_roles'] ) ) ) {
return true;
}
}
$msg = __( 'Unauthorized', 'wp-data-access' );
return false;
}
}
private function sanitize_column_values( $dbs, $tbl, $column_values ) {
$wpda_list_columns = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
$sanitized_column_values = [];
foreach ( $column_values as $column_name => $column_value ) {
$column_value = $column_values[$column_name];
switch ( $wpda_list_columns->get_column_data_type( $column_name ) ) {
case 'tinytext':
case 'text':
case 'mediumtext':
case 'longtext':
if ( null !== $column_value ) {
$column_value = wp_kses_post( $column_value );
}
break;
default:
if ( null !== $column_value ) {
$column_value = sanitize_text_field( $column_value );
}
}
$sanitized_column_values[$this->sanitize_db_identifier( $column_name )] = $column_value;
}
return $sanitized_column_values;
}
}