databases[] = $db['schema_name'];
}
$this->default_database = WPDA::get_user_default_scheme();
$sql_hints = get_option( 'wpda_sql_hints' );
if ( false !== $sql_hints ) {
$this->sql_hints = $sql_hints;
}
$this->wpnonce = wp_create_nonce( 'wpda-query-builder-' . WPDA::get_current_user_id() );
$this->html();
$this->css();
$this->js();
}
protected function html() {
$title = 'Query Builder';
?>
array(),
'status' => null,
);
WPDA::sent_header( 'application/json' );
ob_start();
// Only for admins
if ( current_user_can( 'manage_options' ) && isset(
$_POST['wpda_wpnonce'],
$_POST['wpda_schemaname'],
$_POST['wpda_sqlquery'],
$_POST['wpda_sqllimit'],
$_POST['wpda_protect']
) ) {
$wpda_wpnonce = sanitize_text_field( wp_unslash( $_POST['wpda_wpnonce'] ) );
// input var okay.
$wpda_schemaname = sanitize_text_field( wp_unslash( $_POST['wpda_schemaname'] ) );
// input var okay.
$wpda_sqlquery = html_entity_decode( wp_unslash( $_POST['wpda_sqlquery'] ), ENT_QUOTES );
// input var okay.
$wpda_sqllimit = sanitize_text_field( wp_unslash( $_POST['wpda_sqllimit'] ) );
// input var okay.
$wpda_protect = sanitize_text_field( wp_unslash( $_POST['wpda_protect'] ) );
// input var okay.
// Check query
if ( $this->check_query( $wpda_protect, $wpda_schemaname, $wpda_sqlquery ) ) {
if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
// Execute query
$wpdadb = WPDADB::get_db_connection( $wpda_schemaname );
if ( null !== $wpdadb ) {
$wpdadb->suppress_errors( true );
}
$sqllines = explode( "\n", $wpda_sqlquery );
//phpcs:ignore - 8.1 proof
$sqlcmds = array();
$start_i = 0;
for ($i = 0; $i < count( $sqllines ); $i++) {
//phpcs:ignore - 8.1 proof
if ( '/' === trim( $sqllines[$i] ) ) {
$sql = '';
for ($j = $start_i; $j < $i; $j++) {
if ( '' !== trim( $sqllines[$j] ) ) {
$sql .= "{$sqllines[$j]} ";
}
}
$sqlcmds[] = $sql;
$start_i = $i + 1;
}
}
if ( count( $sqlcmds ) > 0 ) {
//phpcs:ignore - 8.1 proof
$tabs = array();
$vars = array();
$tmps = array();
// Process multiple SQL commands
for ($i = 0; $i < count( $sqlcmds ); $i++) {
//phpcs:ignore - 8.1 proof
if ( '' !== $wpda_sqllimit && 'select' === strtolower( substr( $sqlcmds[$i], 0, 6 ) ) ) {
$sqlcmds[$i] .= " limit {$wpda_sqllimit} ";
}
$columns = WPDA::get_columns_from_query( $wpda_schemaname, $sqlcmds[$i] );
// Need to reconnect when switching from local to remote and vice versa
$reconnected = false;
if ( 'use' === substr( strtolower( trim( $sqlcmds[$i] ) ), 0, 3 ) ) {
$use_cmd = explode( ' ', trim( $sqlcmds[$i] ) );
//phpcs:ignore - 8.1 proof
if ( 2 === count( $use_cmd ) && (strtolower( trim( $use_cmd[1] ) ) !== strtolower( trim( $wpda_schemaname ) ) && 'rdb:' === substr( strtolower( trim( $use_cmd[1] ) ), 0, 4 ) || 'rdb:' === substr( strtolower( trim( $wpda_schemaname ) ), 0, 4 )) ) {
$wpda_schemaname = $use_cmd[1];
$wpdadb = WPDADB::get_db_connection( $wpda_schemaname );
if ( null !== $wpdadb ) {
$wpdadb->suppress_errors( true );
}
$reconnected = true;
}
}
$exequery = true;
$var_name = null;
$wpdadb_saved = null;
switch ( strtolower( substr( trim( $sqlcmds[$i] ), 0, 7 ) ) ) {
case 'wpdavar':
$use_cmd = explode( ' ', trim( $sqlcmds[$i] ) );
//phpcs:ignore - 8.1 proof
$var_name = ( isset( $use_cmd[1] ) ? $use_cmd[1] : null );
if ( $var_name !== null ) {
$vars[$var_name] = $tabs;
$tmps[$var_name] = $this->wpdavar_table( $wpdadb, $tabs[$i - 1], $var_name );
$exequery = false;
}
break;
case 'wpdatmp':
$use_cmd = explode( ' ', trim( $sqlcmds[$i] ) );
$var_name = ( isset( $use_cmd[1] ) ? $use_cmd[1] : null );
if ( $var_name !== null ) {
$wpdadb_saved = $this->wpdatmp_table(
$wpdadb,
$tmps[$var_name],
$tmps[$var_name]['data'],
$tmps[$var_name]['table_name'],
$var_name
);
$exequery = false;
}
$var_name = null;
// reset
break;
}
if ( !$reconnected && $exequery && null !== $wpdadb ) {
$wpdadb->query( $sqlcmds[$i] );
}
if ( null === $var_name ) {
$status = ( null !== $wpdadb_saved ? $wpdadb_saved : (( null !== $wpdadb ? clone $wpdadb : null )) );
$tabs[] = array(
'cmd' => $sqlcmds[$i],
'status' => $status,
'columns' => $columns,
);
} else {
$tabs[] = array(
'cmd' => $sqlcmds[$i],
'status' => ( isset( $vars[$var_name]['status'] ) ? $vars[$var_name]['status'] : null ),
'wpdavar' => ( isset( $tmps[$var_name] ) ? $tmps[$var_name] : null ),
'columns' => null,
);
}
if ( null === $wpdadb || '' !== $wpdadb->last_error ) {
break;
}
}
$response['tabs'] = $tabs;
} else {
$response['columns'] = WPDA::get_columns_from_query( $wpda_schemaname, $wpda_sqlquery );
// Process single SQL command
if ( '' !== $wpda_sqllimit && 'select' === strtolower( substr( $wpda_sqlquery, 0, 6 ) ) ) {
$wpda_sqlquery .= " limit {$wpda_sqllimit} ";
}
if ( null !== $wpdadb ) {
$wpdadb->query( $wpda_sqlquery );
}
$response['cmd'] = $wpda_sqlquery;
$response['status'] = $wpdadb;
}
} else {
$response['status'] = 'WP Data Access error: Token expired, please refresh page';
}
} else {
$response['status'] = 'WP Data Access error: Query not allowed - WordPress tables are protected';
}
} else {
$response['status'] = 'WP Data Access error: Query execution failed';
}
ob_end_clean();
echo json_encode( $response );
}
private function wpdatmp_table(
$wpdadb,
$sql,
$data,
$table_name,
$var_name
) {
// Create temporary table
$wpdadb->query( str_ireplace( array('CREATE TABLE', $table_name), array('CREATE TEMPORARY TABLE', $var_name), $sql['create_table'] ) );
$wpdadb_created = clone $wpdadb;
// Copy data to temporary table
foreach ( $data as $resultset ) {
$column_values = array();
foreach ( $resultset as $key => $val ) {
$column_values[$key] = $val;
}
$wpdadb->insert( $var_name, $column_values );
}
return $wpdadb_created;
}
private function wpdavar_table( $wpdadb, $query, $var_name ) {
$wpdadb->query( "set sql_quote_show_create = 'ON'" );
if ( null === $query['status'] ) {
return null;
}
$explain = $wpdadb->get_results( "explain {$query['status']->last_query}", 'ARRAY_A' );
if ( '' !== $wpdadb->last_error ) {
return null;
}
$table_name = $explain[0]['table'];
$create_table = $wpdadb->get_results( "show create table `{$table_name}`", 'ARRAY_N' );
if ( '' !== $wpdadb->last_error ) {
return null;
}
$sql = str_replace( "\n", '', $create_table[0][1] );
$pos = stripos( $sql, ') ENGINE=' );
$tbl = ( false === $pos ? $sql : substr( $sql, 0, $pos + 1 ) );
return array(
'table_name' => $table_name,
'create_table' => $tbl,
'data' => $query['status']->last_result,
);
}
private function check_query( $wpda_protect, $wpda_schemaname, $wpda_sqlquery ) {
$sql_parts = explode( ' ', trim( $wpda_sqlquery ) );
//phpcs:ignore - 8.1 proof
if ( isset( $sql_parts[0] ) && isset( $sql_parts[2] ) && WPDA::is_wp_table( $sql_parts[2] ) && ('drop' === strtolower( $sql_parts[0] ) || 'alter' === strtolower( $sql_parts[0] ) || 'rename' === strtolower( $sql_parts[0] ) || 'truncate' === strtolower( $sql_parts[0] )) ) {
return false;
}
if ( isset( $sql_parts[0] ) && isset( $sql_parts[1] ) && WPDA::is_wp_table( $sql_parts[1] ) && 'truncate' === strtolower( $sql_parts[0] ) ) {
return false;
}
if ( 'false' !== $wpda_protect && isset( $sql_parts[0] ) && in_array( strtolower( $sql_parts[0] ), self::EXPLAIN_COMMANDS ) ) {
$wpdadb = WPDADB::get_db_connection( $wpda_schemaname );
$suppress = $wpdadb->suppress_errors( true );
$explain = $wpdadb->get_results( "explain {$wpda_sqlquery}", 'ARRAY_A' );
$wpdadb->suppress_errors( $suppress );
foreach ( $explain as $check ) {
if ( in_array( strtolower( $check['select_type'] ), self::EXPLAIN_PROTECTED ) && WPDA::is_wp_table( $check['table'] ) ) {
return false;
}
}
}
return true;
}
public function save() {
$response = array(
'status' => null,
);
WPDA::sent_header( 'application/json' );
// Only for admins
if ( current_user_can( 'manage_options' ) && isset(
$_POST['wpda_wpnonce'],
$_POST['wpda_schemaname'],
$_POST['wpda_sqlqueryname'],
$_POST['wpda_sqlqueryname_old'],
$_POST['wpda_sqlquery']
) ) {
$wpda_wpnonce = sanitize_text_field( wp_unslash( $_POST['wpda_wpnonce'] ) );
// input var okay.
$wpda_schemaname = sanitize_text_field( wp_unslash( $_POST['wpda_schemaname'] ) );
// input var okay.
$wpda_sqlqueryname = sanitize_text_field( wp_unslash( $_POST['wpda_sqlqueryname'] ) );
// input var okay.
$wpda_sqlqueryname_old = sanitize_text_field( wp_unslash( $_POST['wpda_sqlqueryname_old'] ) );
// input var okay.
$wpda_sqlquery = sanitize_textarea_field( wp_unslash( $_POST['wpda_sqlquery'] ) );
// input var okay.
$wpda_vqb = null;
if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
// Save query
if ( false === $this->upd_query(
$wpda_schemaname,
$wpda_sqlqueryname,
$wpda_sqlquery,
$wpda_sqlqueryname_old,
$wpda_vqb
) ) {
$response['status'] = 'Could not save query';
} else {
$response['status'] = 'Query saved';
}
} else {
$response['status'] = 'Token expired, please refresh page';
}
} else {
$response['status'] = 'Could not save query';
}
echo json_encode( $response );
}
public function open() {
$response = array(
'data' => array(),
'status' => null,
);
WPDA::sent_header( 'application/json' );
// Only for admins
if ( current_user_can( 'manage_options' ) && isset( $_POST['wpda_wpnonce'], $_POST['wpda_exclude'] ) ) {
$wpda_wpnonce = sanitize_text_field( wp_unslash( $_POST['wpda_wpnonce'] ) );
// input var okay.
$wpda_exclude = sanitize_text_field( wp_unslash( $_POST['wpda_exclude'] ) );
// input var okay.
if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
// Send list of available queries
$wpda_query_builder_data = $this->get_query_list( $wpda_exclude );
uksort( $wpda_query_builder_data, 'strnatcasecmp' );
//phpcs:ignore - 8.1 proof
$response['data'] = $wpda_query_builder_data;
} else {
$response['status'] = 'Token expired, please refresh page';
}
} else {
$response['status'] = 'Query list not available';
}
echo json_encode( $response );
}
public function delete() {
$response = array(
'status' => null,
);
WPDA::sent_header( 'application/json' );
// Only for admins
if ( current_user_can( 'manage_options' ) && isset( $_POST['wpda_wpnonce'], $_POST['wpda_sqlqueryname'] ) ) {
$wpda_wpnonce = sanitize_text_field( wp_unslash( $_POST['wpda_wpnonce'] ) );
// input var okay.
$wpda_sqlqueryname = sanitize_text_field( wp_unslash( $_POST['wpda_sqlqueryname'] ) );
// input var okay.
if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
// Save query
if ( false === $this->del_query( $wpda_sqlqueryname ) ) {
$response['status'] = 'Could not delete query';
} else {
$response['status'] = 'Query deleted';
}
} else {
$response['status'] = 'Token expired, please refresh page';
}
} else {
$response['status'] = 'Could not save query';
}
echo json_encode( $response );
}
protected function get_query_list( $exclude = '' ) {
$wpda_query_builder_data = get_user_meta( WPDA::get_current_user_id(), 'wpda_query_builder' );
if ( count( $wpda_query_builder_data ) > 0 ) {
//phpcs:ignore - 8.1 proof
$queries = $wpda_query_builder_data[0];
if ( null !== $exclude && '' !== $exclude ) {
$exclude_array = explode( ',', $exclude );
//phpcs:ignore - 8.1 proof
foreach ( $exclude_array as $exclude_item ) {
unset($queries[$exclude_item]);
}
}
return $queries;
} else {
return array();
}
}
protected function upd_query_list( $wpda_query_builder_data ) {
update_user_meta( WPDA::get_current_user_id(), 'wpda_query_builder', $wpda_query_builder_data );
}
protected function get_query( $query_name ) {
$wpda_query_builder_data = $this->get_query_list();
if ( is_array( $wpda_query_builder_data ) && isset( $wpda_query_builder_data[$query_name] ) ) {
return $wpda_query_builder_data[$query_name];
} else {
return array();
}
}
protected function upd_query(
$schema_name,
$query_name,
$query_sql,
$query_name_old,
$wpda_vqb = null
) {
$wpda_query_builder_data = $this->get_query_list();
if ( '' !== $query_name_old && $query_name !== $query_name_old ) {
unset($wpda_query_builder_data[$query_name_old]);
}
$wpda_query_builder_data[$query_name] = array(
'schema_name' => $schema_name,
'query' => $query_sql,
'is_visual' => null !== $wpda_vqb,
);
$this->upd_query_list( $wpda_query_builder_data );
}
protected function del_query( $query_name ) {
$wpda_query_builder_data = $this->get_query_list();
unset($wpda_query_builder_data[$query_name]);
$this->upd_query_list( $wpda_query_builder_data );
}
public function get_visual_query( $query_name ) {
}
protected function upd_visual_query( $query_name, $wpda_vqb ) {
}
protected function del_visual_query( $query_name ) {
}
protected function rewrite_query_name( $query_name ) {
return str_replace( ' ', '_', $query_name );
}
public function get_db_hints() {
$response = array(
'status' => 'OK',
);
WPDA::sent_header( 'application/json' );
if ( current_user_can( 'manage_options' ) && isset( $_REQUEST['wpda_wpnonce'], $_REQUEST['wpda_schemaname'] ) ) {
$wpda_wpnonce = sanitize_text_field( wp_unslash( $_REQUEST['wpda_wpnonce'] ) );
// input var okay.
$wpda_schemaname = sanitize_text_field( wp_unslash( $_REQUEST['wpda_schemaname'] ) );
// input var okay.
if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
$tables = WPDA_Dictionary_Lists::get_tables( true, $wpda_schemaname );
$hints = array();
foreach ( $tables as $table ) {
$columns = WPDA_Dictionary_Lists::get_table_columns( $table['table_name'], $wpda_schemaname );
$hint_cols = array();
foreach ( $columns as $column ) {
$hints[$table['table_name']] = $hint_cols;
$hint_cols[] = $column['column_name'];
}
}
$response['tables'] = $hints;
} else {
$response['status'] = 'Token expired, please refresh page';
}
} else {
$response['status'] = 'Invalid arguments';
}
echo json_encode( $response );
}
public function set_db_hints() {
$response = array(
'status' => 'OK',
);
WPDA::sent_header( 'application/json' );
if ( current_user_can( 'manage_options' ) && isset( $_REQUEST['wpda_wpnonce'], $_REQUEST['wpda_sqlhints'] ) ) {
$wpda_wpnonce = sanitize_text_field( wp_unslash( $_REQUEST['wpda_wpnonce'] ) );
// input var okay.
$wpda_sqlhints = sanitize_text_field( wp_unslash( $_REQUEST['wpda_sqlhints'] ) );
// input var okay.
if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
update_option( 'wpda_sql_hints', $wpda_sqlhints );
}
}
$response['hint'] = $wpda_sqlhints;
echo json_encode( $response );
}
}