PluginProbe
WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards / 5.5.84
WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards v5.5.84
5.5.84 5.5.83 5.5.82 5.5.81 5.5.80 5.5.79 5.5.77 5.5.76 5.5.75 5.5.73 5.5.72 5.5.22 5.5.23 5.5.29 5.5.3 5.5.31 5.5.32 5.5.34 5.5.35 5.5.36 5.5.37 5.5.4 5.5.40 5.5.41 5.5.42 All 160 releases
← All changes | WPDataAccess/Query_Builder/WPDA_Query_Builder.php +273 -516 5.5.22 → 5.5.84 View file →
@@ -5,8 +5,12 @@
5 5 use WPDataAccess\Connection\WPDADB;
6 6 use WPDataAccess\Data_Dictionary\WPDA_Dictionary_Lists;
7 7 use WPDataAccess\WPDA;
8 8 class WPDA_Query_Builder {
9 + const QUERY_BUILDER_OPTIONS = 'wpda_query_builder';
10 +
11 + const QUERY_BUILDER_HINTS = 'wpda_sql_hints';
12 +
9 13 const EXPLAIN_COMMANDS = array(
10 14 'select',
11 15 'delete',
12 16 'insert',
@@ -28,377 +32,8 @@
28 32 protected $wpnonce = '';
29 33
30 34 protected $sql_hints = 'off';
31 35
32 - public function show() {
33 - $dbs = WPDA_Dictionary_Lists::get_db_schemas();
34 - foreach ( $dbs as $db ) {
35 - $this->databases[] = $db['schema_name'];
36 - }
37 - $this->default_database = WPDA::get_user_default_scheme();
38 - $sql_hints = get_option( 'wpda_sql_hints' );
39 - if ( false !== $sql_hints ) {
40 - $this->sql_hints = $sql_hints;
41 - }
42 - $this->wpnonce = wp_create_nonce( 'wpda-query-builder-' . WPDA::get_current_user_id() );
43 - $this->html();
44 - $this->css();
45 - $this->js();
46 - }
47 -
48 - protected function html() {
49 - $title = 'Query Builder';
50 - ?>
51 - <div class="wrap">
52 - <span style="float:right">
53 - <label style="font-weight:normal"><input type="checkbox" id="wpda_sql_hints" <?php
54 - echo ( 'on' === $this->sql_hints ? 'checked' : '' );
55 - ?>> Enable SQL hints</label>
56 - </span>
57 - <h1 class="wp-heading-inline">
58 - <?php
59 - echo esc_attr( $title );
60 - ?>
61 - </h1>
62 - <div id="wpda_query_builder_open" style="display: none">
63 - <fieldset class="wpda_fieldset" style="position:relative">
64 - <legend>
65 - Queries stored for user <?php
66 - echo esc_attr( WPDA::get_current_user_login() );
67 - ?>
68 - </legend>
69 - <label>
70 - Select
71 - </label>
72 - <select id="wpda_query_builder_open_select"></select>
73 - <a id="wpda_query_builder_open_open"
74 - href="javascript:void(0)"
75 - onclick="tabOpen()"
76 - class="wpda_tooltip button button-primary"
77 - title="Open query in new tab">
78 - <i class="fas fa-folder-open wpda_icon_on_button"></i> Open selected</a>
79 - <a id="wpda_query_builder_open_openall"
80 - href="javascript:void(0)"
81 - onclick="tabOpenAll()"
82 - class="wpda_tooltip button button-secondary"
83 - title="Open all stored queries in new tabs">
84 - <i class="fas fa-folder-open wpda_icon_on_button"></i> Open all</a>
85 - <a id="wpda_query_builder_open_delete"
86 - href="javascript:void(0)"
87 - onclick="deleteQuery()"
88 - class="wpda_tooltip button button-secondary"
89 - title="Delete query">
90 - <i class="fas fa-trash wpda_icon_on_button"></i> Delete selected</a>
91 - <a href="javascript:void(0)"
92 - onclick="closeQuery()"
93 - class="wpda_tooltip button button-secondary"
94 - title="Close panel">
95 - <i class="fas fa-times-circle wpda_icon_on_button"></i> Cancel</a>
96 - </fieldset>
97 - </div>
98 - <div id="wpda_query_builder">
99 - <nav class="nav-tab-wrapper">
100 - </nav>
101 - </div>
102 - </div>
103 - <?php
104 - }
105 -
106 - protected function css() {
107 - // Overwrite default style
108 - ?>
109 - <style>
110 - .ui-corner-all, .ui-corner-top, .ui-corner-right, .ui-corner-tr, .ui-corner-left, .ui-corner-tl {
111 - border-radius: 0 !important;
112 - }
113 - .ui-widget.ui-widget-content {
114 - border: none;
115 - }
116 - .wpda_query_builder_tabs .ui-widget-content {
117 - background: none;
118 - color: inherit;
119 - }
120 - /*.ui-widget-header {*/
121 - /* font-weight: normal;*/
122 - /* background: none;*/
123 - /* border: none;*/
124 - /* border-bottom: 1px solid #c3c4c7;*/
125 - /*}*/
126 - .ui-tabs .ui-tabs-nav li.ui-tabs-active .ui-tabs-anchor, .ui-tabs .ui-tabs-nav li.ui-state-disabled .ui-tabs-anchor, .ui-tabs .ui-tabs-nav li.ui-tabs-loading .ui-tabs-anchor {
127 - cursor: pointer;
128 - }
129 - .ui-state-default, .ui-widget-content .ui-state-default, .ui-widget-header .ui-state-default, .ui-button, html .ui-button.ui-state-disabled:hover, html .ui-button.ui-state-disabled:active {
130 - border: 1px solid #c3c4c7;
131 - font-weight: bold;
132 - color: #000 !important;
133 - background: #dcdcde !important;
134 - }
135 - .ui-state-default:hover {
136 - background: #f0f0f1 !important;
137 - }
138 - .ui-state-active, .ui-widget-content .ui-state-active, .ui-widget-header .ui-state-active, a.ui-button:active, .ui-button:active, .ui-button.ui-state-active:hover {
139 - border: 1px solid #c3c4c7;
140 - border-bottom: none;
141 - font-weight: bold;
142 - color: #000 !important;
143 - background: #f0f0f1 !important;
144 - }
145 - .ui-tabs-anchor {
146 - color: #50575e !important;
147 - }
148 - .ui-tabs-anchor span.dashicons {
149 - padding-left: 5px;
150 - }
151 - .ui-tabs .ui-tabs-panel {
152 - padding: 0;
153 - font-family: inherit;
154 - font-size: inherit;
155 - }
156 - .ui-tabs .ui-tabs-nav li {
157 - margin-left: 4px;
158 - }
159 - .ui-tabs .ui-tabs-nav .ui-tabs-anchor {
160 - line-height: 20px;
161 - }
162 - </style>
163 - <?php
164 - }
165 -
166 - protected function js() {
167 - ?>
168 - <script type="application/javascript">
169 - var wpda_default_database = '<?php
170 - echo esc_attr( $this->default_database );
171 - ?>';
172 - var wpda_databases = '';
173 - <?php
174 - global $wpdb;
175 - $database_options = '';
176 - foreach ( $this->databases as $database ) {
177 - if ( $wpdb->dbname === $database ) {
178 - $database_printed = "WordPress database ({$database})";
179 - } else {
180 - $database_printed = $database;
181 - }
182 - $database_options .= '<option value="' . $database . '">' . $database_printed . '</option>';
183 - }
184 - echo "wpda_databases = '{$database_options}';";
185 - // phpcs:ignore WordPress.Security.EscapeOutput
186 - ?>
187 - var wpda_home_url = "<?php
188 - echo admin_url( 'admin.php' );
189 - // phpcs:ignore WordPress.Security.EscapeOutput
190 - ?>";
191 - var wpda_wpnonce = "<?php
192 - echo esc_attr( $this->wpnonce );
193 - ?>";
194 - var wpda_loader_url = "<?php
195 - echo plugins_url( '../../assets/images/loading.gif', __FILE__ );
196 - // phpcs:ignore WordPress.Security.EscapeOutput
197 - ?>";
198 - var vqbInstalled = false;
199 - <?php
200 - ?>
201 -
202 - function getColumns(schema, table, activeIndex, callback, rebuild = false) {
203 - const url = location.pathname + '?action=wpda_get_table_widget_info';
204 - const data = {
205 - wpdaschema_name: schema,
206 - table_name: table,
207 - wpda_wpnonce: '<?php
208 - echo esc_attr( wp_create_nonce( 'wpda-getdata-access-' . WPDA::get_current_user_login() ) );
209 - ?>'
210 - };
211 - jQuery.post(
212 - url,
213 - data,
214 - function (data) {
215 - callback(schema, table, activeIndex, JSON.parse(data), rebuild);
216 - }
217 - );
218 - }
219 -
220 - jQuery(
221 - function() {
222 - tabNew();
223 - jQuery('#wpda_query_builder nav').sortable();
224 - jQuery('.wpda_tooltip').tooltip();
225 - jQuery("#wpda_sql_hints").on('change', function() {
226 - jQuery.ajax({
227 - method: 'POST',
228 - url: wpda_home_url + "?action=wpda_query_builder_set_db_hints",
229 - data: {
230 - wpda_wpnonce: wpda_wpnonce,
231 - wpda_sqlhints: jQuery(this).is(":checked") ? 'on' : 'off'
232 - }
233 - }).done(
234 - function (msg) {}
235 - ).fail(
236 - function (msg) {}
237 - );
238 -
239 - });
240 - }
241 - );
242 - </script>
243 - <?php
244 - }
245 -
246 - public function execute() {
247 - $response = array(
248 - 'tabs' => array(),
249 - 'status' => null,
250 - );
251 - WPDA::sent_header( 'application/json' );
252 - ob_start();
253 - // Only for admins
254 - if ( current_user_can( 'manage_options' ) && isset(
255 - $_POST['wpda_wpnonce'],
256 - $_POST['wpda_schemaname'],
257 - $_POST['wpda_sqlquery'],
258 - $_POST['wpda_sqllimit'],
259 - $_POST['wpda_protect']
260 - ) ) {
261 - $wpda_wpnonce = sanitize_text_field( wp_unslash( $_POST['wpda_wpnonce'] ) );
262 - // input var okay.
263 - $wpda_schemaname = sanitize_text_field( wp_unslash( $_POST['wpda_schemaname'] ) );
264 - // input var okay.
265 - $wpda_sqlquery = html_entity_decode( wp_unslash( $_POST['wpda_sqlquery'] ), ENT_QUOTES );
266 - // input var okay.
267 - $wpda_sqllimit = sanitize_text_field( wp_unslash( $_POST['wpda_sqllimit'] ) );
268 - // input var okay.
269 - $wpda_protect = sanitize_text_field( wp_unslash( $_POST['wpda_protect'] ) );
270 - // input var okay.
271 - // Check query
272 - if ( $this->check_query( $wpda_protect, $wpda_schemaname, $wpda_sqlquery ) ) {
273 - if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
274 - // Execute query
275 - $wpdadb = WPDADB::get_db_connection( $wpda_schemaname );
276 - if ( null !== $wpdadb ) {
277 - $wpdadb->suppress_errors( true );
278 - }
279 - $sqllines = explode( "\n", $wpda_sqlquery );
280 - //phpcs:ignore - 8.1 proof
281 - $sqlcmds = array();
282 - $start_i = 0;
283 - for ($i = 0; $i < count( $sqllines ); $i++) {
284 - //phpcs:ignore - 8.1 proof
285 - if ( '/' === trim( $sqllines[$i] ) ) {
286 - $sql = '';
287 - for ($j = $start_i; $j < $i; $j++) {
288 - if ( '' !== trim( $sqllines[$j] ) ) {
289 - $sql .= "{$sqllines[$j]} ";
290 - }
291 - }
292 - $sqlcmds[] = $sql;
293 - $start_i = $i + 1;
294 - }
295 - }
296 - if ( count( $sqlcmds ) > 0 ) {
297 - //phpcs:ignore - 8.1 proof
298 - $tabs = array();
299 - $vars = array();
300 - $tmps = array();
301 - // Process multiple SQL commands
302 - for ($i = 0; $i < count( $sqlcmds ); $i++) {
303 - //phpcs:ignore - 8.1 proof
304 - if ( '' !== $wpda_sqllimit && 'select' === strtolower( substr( $sqlcmds[$i], 0, 6 ) ) ) {
305 - $sqlcmds[$i] .= " limit {$wpda_sqllimit} ";
306 - }
307 - $columns = WPDA::get_columns_from_query( $wpda_schemaname, $sqlcmds[$i] );
308 - // Need to reconnect when switching from local to remote and vice versa
309 - $reconnected = false;
310 - if ( 'use' === substr( strtolower( trim( $sqlcmds[$i] ) ), 0, 3 ) ) {
311 - $use_cmd = explode( ' ', trim( $sqlcmds[$i] ) );
312 - //phpcs:ignore - 8.1 proof
313 - 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 )) ) {
314 - $wpda_schemaname = $use_cmd[1];
315 - $wpdadb = WPDADB::get_db_connection( $wpda_schemaname );
316 - if ( null !== $wpdadb ) {
317 - $wpdadb->suppress_errors( true );
318 - }
319 - $reconnected = true;
320 - }
321 - }
322 - $exequery = true;
323 - $var_name = null;
324 - $wpdadb_saved = null;
325 - switch ( strtolower( substr( trim( $sqlcmds[$i] ), 0, 7 ) ) ) {
326 - case 'wpdavar':
327 - $use_cmd = explode( ' ', trim( $sqlcmds[$i] ) );
328 - //phpcs:ignore - 8.1 proof
329 - $var_name = ( isset( $use_cmd[1] ) ? $use_cmd[1] : null );
330 - if ( $var_name !== null ) {
331 - $vars[$var_name] = $tabs;
332 - $tmps[$var_name] = $this->wpdavar_table( $wpdadb, $tabs[$i - 1], $var_name );
333 - $exequery = false;
334 - }
335 - break;
336 - case 'wpdatmp':
337 - $use_cmd = explode( ' ', trim( $sqlcmds[$i] ) );
338 - $var_name = ( isset( $use_cmd[1] ) ? $use_cmd[1] : null );
339 - if ( $var_name !== null ) {
340 - $wpdadb_saved = $this->wpdatmp_table(
341 - $wpdadb,
342 - $tmps[$var_name],
343 - $tmps[$var_name]['data'],
344 - $tmps[$var_name]['table_name'],
345 - $var_name
346 - );
347 - $exequery = false;
348 - }
349 - $var_name = null;
350 - // reset
351 - break;
352 - }
353 - if ( !$reconnected && $exequery && null !== $wpdadb ) {
354 - $wpdadb->query( $sqlcmds[$i] );
355 - }
356 - if ( null === $var_name ) {
357 - $status = ( null !== $wpdadb_saved ? $wpdadb_saved : (( null !== $wpdadb ? clone $wpdadb : null )) );
358 - $tabs[] = array(
359 - 'cmd' => $sqlcmds[$i],
360 - 'status' => $status,
361 - 'columns' => $columns,
362 - );
363 - } else {
364 - $tabs[] = array(
365 - 'cmd' => $sqlcmds[$i],
366 - 'status' => ( isset( $vars[$var_name]['status'] ) ? $vars[$var_name]['status'] : null ),
367 - 'wpdavar' => ( isset( $tmps[$var_name] ) ? $tmps[$var_name] : null ),
368 - 'columns' => null,
369 - );
370 - }
371 - if ( null === $wpdadb || '' !== $wpdadb->last_error ) {
372 - break;
373 - }
374 - }
375 - $response['tabs'] = $tabs;
376 - } else {
377 - $response['columns'] = WPDA::get_columns_from_query( $wpda_schemaname, $wpda_sqlquery );
378 - // Process single SQL command
379 - if ( '' !== $wpda_sqllimit && 'select' === strtolower( substr( $wpda_sqlquery, 0, 6 ) ) ) {
380 - $wpda_sqlquery .= " limit {$wpda_sqllimit} ";
381 - }
382 - if ( null !== $wpdadb ) {
383 - $wpdadb->query( $wpda_sqlquery );
384 - }
385 - $response['cmd'] = $wpda_sqlquery;
386 - $response['status'] = $wpdadb;
387 - }
388 - } else {
389 - $response['status'] = '<strong>WP Data Access error:</strong> Token expired, please refresh page';
390 - }
391 - } else {
392 - $response['status'] = '<strong>WP Data Access error:</strong> Query not allowed - WordPress tables are protected';
393 - }
394 - } else {
395 - $response['status'] = '<strong>WP Data Access error:</strong> Query execution failed';
396 - }
397 - ob_end_clean();
398 - echo json_encode( $response );
399 - }
400 -
401 36 private function wpdatmp_table(
402 37 $wpdadb,
403 38 $sql,
404 39 $data,
@@ -442,11 +77,11 @@
442 77 'data' => $query['status']->last_result,
443 78 );
444 79 }
445 80
446 - private function check_query( $wpda_protect, $wpda_schemaname, $wpda_sqlquery ) {
81 + public function check_query( $wpda_protect, $wpda_schemaname, $wpda_sqlquery ) {
447 82 $sql_parts = explode( ' ', trim( $wpda_sqlquery ) );
448 - //phpcs:ignore - 8.1 proof
83 + // phpcs:ignore -- 8.1 proof
449 84 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] )) ) {
450 85 return false;
451 86 }
452 87 if ( isset( $sql_parts[0] ) && isset( $sql_parts[1] ) && WPDA::is_wp_table( $sql_parts[1] ) && 'truncate' === strtolower( $sql_parts[0] ) ) {
@@ -465,116 +100,34 @@
465 100 }
466 101 return true;
467 102 }
468 103
469 - public function save() {
470 - $response = array(
471 - 'status' => null,
472 - );
473 - WPDA::sent_header( 'application/json' );
474 - // Only for admins
475 - if ( current_user_can( 'manage_options' ) && isset(
476 - $_POST['wpda_wpnonce'],
477 - $_POST['wpda_schemaname'],
478 - $_POST['wpda_sqlqueryname'],
479 - $_POST['wpda_sqlqueryname_old'],
480 - $_POST['wpda_sqlquery']
481 - ) ) {
482 - $wpda_wpnonce = sanitize_text_field( wp_unslash( $_POST['wpda_wpnonce'] ) );
483 - // input var okay.
484 - $wpda_schemaname = sanitize_text_field( wp_unslash( $_POST['wpda_schemaname'] ) );
485 - // input var okay.
486 - $wpda_sqlqueryname = sanitize_text_field( wp_unslash( $_POST['wpda_sqlqueryname'] ) );
487 - // input var okay.
488 - $wpda_sqlqueryname_old = sanitize_text_field( wp_unslash( $_POST['wpda_sqlqueryname_old'] ) );
489 - // input var okay.
490 - $wpda_sqlquery = sanitize_textarea_field( wp_unslash( $_POST['wpda_sqlquery'] ) );
491 - // input var okay.
492 - $wpda_vqb = null;
493 - if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
494 - // Save query
495 - if ( false === $this->upd_query(
496 - $wpda_schemaname,
497 - $wpda_sqlqueryname,
498 - $wpda_sqlquery,
499 - $wpda_sqlqueryname_old,
500 - $wpda_vqb
501 - ) ) {
502 - $response['status'] = 'Could not save query';
503 - } else {
504 - $response['status'] = 'Query saved';
104 + public function get_query_list( $exclude = '', $user_id = null ) {
105 + $wpda_query_builder_data = get_user_meta( ( null === $user_id ? WPDA::get_current_user_id() : $user_id ), self::QUERY_BUILDER_OPTIONS );
106 + if ( is_array( $wpda_query_builder_data ) && count( $wpda_query_builder_data ) > 0 ) {
107 + // phpcs:ignore -- 8.1 proof
108 + $queries = $wpda_query_builder_data[0];
109 + if ( null !== $exclude && '' !== $exclude ) {
110 + $exclude_array = explode( ',', $exclude );
111 + // phpcs:ignore -- 8.1 proof
112 + foreach ( $exclude_array as $exclude_item ) {
113 + unset($queries[$exclude_item]);
505 114 }
506 - } else {
507 - $response['status'] = 'Token expired, please refresh page';
508 115 }
116 + return $queries;
509 117 } else {
510 - $response['status'] = 'Could not save query';
118 + return array();
511 119 }
512 - echo json_encode( $response );
513 120 }
514 121
515 - public function open() {
516 - $response = array(
517 - 'data' => array(),
518 - 'status' => null,
519 - );
520 - WPDA::sent_header( 'application/json' );
521 - // Only for admins
522 - if ( current_user_can( 'manage_options' ) && isset( $_POST['wpda_wpnonce'], $_POST['wpda_exclude'] ) ) {
523 - $wpda_wpnonce = sanitize_text_field( wp_unslash( $_POST['wpda_wpnonce'] ) );
524 - // input var okay.
525 - $wpda_exclude = sanitize_text_field( wp_unslash( $_POST['wpda_exclude'] ) );
526 - // input var okay.
527 - if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
528 - // Send list of available queries
529 - $wpda_query_builder_data = $this->get_query_list( $wpda_exclude );
530 - uksort( $wpda_query_builder_data, 'strnatcasecmp' );
531 - //phpcs:ignore - 8.1 proof
532 - $response['data'] = $wpda_query_builder_data;
533 - } else {
534 - $response['status'] = 'Token expired, please refresh page';
535 - }
536 - } else {
537 - $response['status'] = 'Query list not available';
538 - }
539 - echo json_encode( $response );
540 - }
541 -
542 - public function delete() {
543 - $response = array(
544 - 'status' => null,
545 - );
546 - WPDA::sent_header( 'application/json' );
547 - // Only for admins
548 - if ( current_user_can( 'manage_options' ) && isset( $_POST['wpda_wpnonce'], $_POST['wpda_sqlqueryname'] ) ) {
549 - $wpda_wpnonce = sanitize_text_field( wp_unslash( $_POST['wpda_wpnonce'] ) );
550 - // input var okay.
551 - $wpda_sqlqueryname = sanitize_text_field( wp_unslash( $_POST['wpda_sqlqueryname'] ) );
552 - // input var okay.
553 - if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
554 - // Save query
555 - if ( false === $this->del_query( $wpda_sqlqueryname ) ) {
556 - $response['status'] = 'Could not delete query';
557 - } else {
558 - $response['status'] = 'Query deleted';
559 - }
560 - } else {
561 - $response['status'] = 'Token expired, please refresh page';
562 - }
563 - } else {
564 - $response['status'] = 'Could not save query';
565 - }
566 - echo json_encode( $response );
567 - }
568 -
569 - protected function get_query_list( $exclude = '' ) {
570 - $wpda_query_builder_data = get_user_meta( WPDA::get_current_user_id(), 'wpda_query_builder' );
571 - if ( count( $wpda_query_builder_data ) > 0 ) {
572 - //phpcs:ignore - 8.1 proof
573 - $queries = $wpda_query_builder_data[0];
122 + public function get_query_list_global( $exclude = '' ) {
123 + $wpda_query_builder_data = get_option( self::QUERY_BUILDER_OPTIONS );
124 + if ( is_array( $wpda_query_builder_data ) && count( $wpda_query_builder_data ) > 0 ) {
125 + // phpcs:ignore -- 8.1 proof
126 + $queries = $wpda_query_builder_data;
574 127 if ( null !== $exclude && '' !== $exclude ) {
575 128 $exclude_array = explode( ',', $exclude );
576 - //phpcs:ignore - 8.1 proof
129 + // phpcs:ignore -- 8.1 proof
577 130 foreach ( $exclude_array as $exclude_item ) {
578 131 unset($queries[$exclude_item]);
579 132 }
580 133 }
@@ -583,14 +136,22 @@
583 136 return array();
584 137 }
585 138 }
586 139
587 - protected function upd_query_list( $wpda_query_builder_data ) {
588 - update_user_meta( WPDA::get_current_user_id(), 'wpda_query_builder', $wpda_query_builder_data );
140 + protected function update_query_list( $wpda_query_builder_data ) {
141 + update_user_meta( WPDA::get_current_user_id(), self::QUERY_BUILDER_OPTIONS, $wpda_query_builder_data );
589 142 }
590 143
591 - protected function get_query( $query_name ) {
592 - $wpda_query_builder_data = $this->get_query_list();
144 + protected function update_query_list_global( $wpda_query_builder_data ) {
145 + update_option( self::QUERY_BUILDER_OPTIONS, $wpda_query_builder_data );
146 + }
147 +
148 + public function get_query( $query_name, $user_id = null ) {
149 + if ( null === $user_id ) {
150 + $wpda_query_builder_data = $this->get_query_list();
151 + } else {
152 + $wpda_query_builder_data = $this->get_query_list( '', $user_id );
153 + }
593 154 if ( is_array( $wpda_query_builder_data ) && isset( $wpda_query_builder_data[$query_name] ) ) {
594 155 return $wpda_query_builder_data[$query_name];
595 156 } else {
596 157 return array();
@@ -596,14 +157,24 @@
596 157 return array();
597 158 }
598 159 }
599 160
600 - protected function upd_query(
161 + public function get_query_global( $query_name ) {
162 + $wpda_query_builder_data = $this->get_query_list_global();
163 + if ( is_array( $wpda_query_builder_data ) && isset( $wpda_query_builder_data[$query_name] ) ) {
164 + return $wpda_query_builder_data[$query_name];
165 + } else {
166 + return array();
167 + }
168 + }
169 +
170 + public function update_query_old(
601 171 $schema_name,
602 172 $query_name,
603 173 $query_sql,
604 174 $query_name_old,
605 - $wpda_vqb = null
175 + $wpda_vqb = null,
176 + $params = null
606 177 ) {
607 178 $wpda_query_builder_data = $this->get_query_list();
608 179 if ( '' !== $query_name_old && $query_name !== $query_name_old ) {
609 180 unset($wpda_query_builder_data[$query_name_old]);
@@ -611,77 +182,263 @@
611 182 $wpda_query_builder_data[$query_name] = array(
612 183 'schema_name' => $schema_name,
613 184 'query' => $query_sql,
614 185 'is_visual' => null !== $wpda_vqb,
186 + 'params' => $params,
615 187 );
616 - $this->upd_query_list( $wpda_query_builder_data );
188 + $this->update_query_list( $wpda_query_builder_data );
617 189 }
618 190
619 - protected function del_query( $query_name ) {
191 + public function update_query(
192 + $schema_name,
193 + $query_name,
194 + $query_sql,
195 + $query_name_old,
196 + $is_visual,
197 + $vqb,
198 + $params
199 + ) {
620 200 $wpda_query_builder_data = $this->get_query_list();
201 + if ( '' !== $query_name_old && $query_name !== $query_name_old ) {
202 + unset($wpda_query_builder_data[$query_name_old]);
203 + }
204 + $wpda_query_builder_data[$query_name] = array(
205 + 'schema_name' => $schema_name,
206 + 'query' => $query_sql,
207 + 'is_visual' => $is_visual,
208 + 'params' => $params,
209 + );
210 + if ( $vqb !== "" ) {
211 + $wpda_query_builder_data[$query_name]['vqb'] = $vqb;
212 + }
213 + $this->update_query_list( $wpda_query_builder_data );
214 + }
215 +
216 + public function update_query_global(
217 + $schema_name,
218 + $query_name,
219 + $query_sql,
220 + $query_name_old,
221 + $is_visual,
222 + $vqb,
223 + $params
224 + ) {
225 + $wpda_query_builder_data = $this->get_query_list_global();
226 + if ( '' !== $query_name_old && $query_name !== $query_name_old ) {
227 + unset($wpda_query_builder_data[$query_name_old]);
228 + }
229 + $wpda_query_builder_data[$query_name] = array(
230 + 'schema_name' => $schema_name,
231 + 'query' => $query_sql,
232 + 'is_visual' => $is_visual,
233 + 'params' => $params,
234 + );
235 + if ( $vqb !== "" ) {
236 + $wpda_query_builder_data[$query_name]['vqb'] = $vqb;
237 + }
238 + $this->update_query_list_global( $wpda_query_builder_data );
239 + }
240 +
241 + public function delete_query( $query_name ) {
242 + $wpda_query_builder_data = $this->get_query_list();
621 243 unset($wpda_query_builder_data[$query_name]);
622 - $this->upd_query_list( $wpda_query_builder_data );
244 + $this->update_query_list( $wpda_query_builder_data );
245 + $this->del_visual_query( $query_name );
623 246 }
624 247
248 + public function delete_query_global( $query_name ) {
249 + $wpda_query_builder_data = $this->get_query_list_global();
250 + unset($wpda_query_builder_data[$query_name]);
251 + $this->update_query_list_global( $wpda_query_builder_data );
252 + $this->del_visual_query_global( $query_name );
253 + }
254 +
625 255 public function get_visual_query( $query_name ) {
256 + return get_user_meta( WPDA::get_current_user_id(), 'wpda_query_builder_' . $this->rewrite_query_name( $query_name ), true );
626 257 }
627 258
628 - protected function upd_visual_query( $query_name, $wpda_vqb ) {
259 + public function get_visual_query_global( $query_name ) {
260 + return get_option( 'wpda_query_builder_' . $this->rewrite_query_name( $query_name ), true );
629 261 }
630 262
263 + public function upd_visual_query( $query_name, $wpda_vqb ) {
264 + update_user_meta( WPDA::get_current_user_id(), 'wpda_query_builder_' . $this->rewrite_query_name( $query_name ), $wpda_vqb );
265 + }
266 +
267 + public function upd_visual_query_global( $query_name, $wpda_vqb ) {
268 + update_option( 'wpda_query_builder_' . $this->rewrite_query_name( $query_name ), $wpda_vqb );
269 + }
270 +
631 271 protected function del_visual_query( $query_name ) {
272 + delete_user_meta( WPDA::get_current_user_id(), 'wpda_query_builder_' . $this->rewrite_query_name( $query_name ) );
632 273 }
633 274
275 + protected function del_visual_query_global( $query_name ) {
276 + delete_option( 'wpda_query_builder_' . $this->rewrite_query_name( $query_name ) );
277 + }
278 +
634 279 protected function rewrite_query_name( $query_name ) {
635 280 return str_replace( ' ', '_', $query_name );
636 281 }
637 282
638 - public function get_db_hints() {
283 + private function substitute_sql_params( $wpdadb, $query, $params ) {
284 + }
285 +
286 + public function execute_query(
287 + $dbs,
288 + $query,
289 + $limit,
290 + $protect,
291 + $params
292 + ) {
639 293 $response = array(
640 - 'status' => 'OK',
294 + 'tabs' => array(),
295 + 'status' => null,
641 296 );
642 - WPDA::sent_header( 'application/json' );
643 - if ( current_user_can( 'manage_options' ) && isset( $_REQUEST['wpda_wpnonce'], $_REQUEST['wpda_schemaname'] ) ) {
644 - $wpda_wpnonce = sanitize_text_field( wp_unslash( $_REQUEST['wpda_wpnonce'] ) );
645 - // input var okay.
646 - $wpda_schemaname = sanitize_text_field( wp_unslash( $_REQUEST['wpda_schemaname'] ) );
647 - // input var okay.
648 - if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
649 - $tables = WPDA_Dictionary_Lists::get_tables( true, $wpda_schemaname );
650 - $hints = array();
651 - foreach ( $tables as $table ) {
652 - $columns = WPDA_Dictionary_Lists::get_table_columns( $table['table_name'], $wpda_schemaname );
653 - $hint_cols = array();
654 - foreach ( $columns as $column ) {
655 - $hints[$table['table_name']] = $hint_cols;
656 - $hint_cols[] = $column['column_name'];
297 + if ( $this->check_query( $protect, $dbs, $query ) ) {
298 + // Execute query
299 + $wpdadb = WPDADB::get_db_connection( $dbs );
300 + if ( null !== $wpdadb ) {
301 + $wpdadb->suppress_errors( true );
302 + }
303 + $sqllines = explode( "\n", $query );
304 + // phpcs:ignore -- 8.1 proof
305 + $sqlcmds = array();
306 + $start_i = 0;
307 + for ($i = 0; $i < count( $sqllines ); $i++) {
308 + // phpcs:ignore -- 8.1 proof
309 + if ( '/' === trim( $sqllines[$i] ) ) {
310 + $sql = '';
311 + for ($j = $start_i; $j < $i; $j++) {
312 + if ( '' !== trim( $sqllines[$j] ) ) {
313 + $sql .= "{$sqllines[$j]} ";
314 + }
657 315 }
316 + $sqlcmds[] = $sql;
317 + $start_i = $i + 1;
658 318 }
659 - $response['tables'] = $hints;
319 + }
320 + if ( count( $sqlcmds ) > 0 ) {
321 + // phpcs:ignore -- 8.1 proof
322 + $tabs = array();
323 + $vars = array();
324 + $tmps = array();
325 + // Process multiple SQL commands
326 + for ($i = 0; $i < count( $sqlcmds ); $i++) {
327 + // phpcs:ignore -- 8.1 proof
328 + if ( '' !== $limit && 'select' === strtolower( substr( $sqlcmds[$i], 0, 6 ) ) ) {
329 + $sqlcmds[$i] .= " limit {$limit} ";
330 + }
331 + $columns = WPDA::get_columns_from_query( $dbs, $sqlcmds[$i] );
332 + // Need to reconnect when switching from local to remote and vice versa
333 + $reconnected = false;
334 + if ( 'use' === substr( strtolower( trim( $sqlcmds[$i] ) ), 0, 3 ) ) {
335 + $use_cmd = explode( ' ', trim( $sqlcmds[$i] ) );
336 + // phpcs:ignore -- 8.1 proof
337 + if ( 2 === count( $use_cmd ) && (strtolower( trim( $use_cmd[1] ) ) !== strtolower( trim( $dbs ) ) && 'rdb:' === substr( strtolower( trim( $use_cmd[1] ) ), 0, 4 ) || 'rdb:' === substr( strtolower( trim( $dbs ) ), 0, 4 )) ) {
338 + $dbs = $use_cmd[1];
339 + $wpdadb = WPDADB::get_db_connection( $dbs );
340 + if ( null !== $wpdadb ) {
341 + $wpdadb->suppress_errors( true );
342 + }
343 + $reconnected = true;
344 + }
345 + }
346 + $exequery = true;
347 + $var_name = null;
348 + $wpdadb_saved = null;
349 + switch ( strtolower( substr( trim( $sqlcmds[$i] ), 0, 7 ) ) ) {
350 + case 'wpdavar':
351 + $use_cmd = explode( ' ', trim( $sqlcmds[$i] ) );
352 + // phpcs:ignore -- 8.1 proof
353 + $var_name = ( isset( $use_cmd[1] ) ? $use_cmd[1] : null );
354 + if ( $var_name !== null ) {
355 + $vars[$var_name] = $tabs;
356 + $tmps[$var_name] = $this->wpdavar_table( $wpdadb, $tabs[$i - 1], $var_name );
357 + $exequery = false;
358 + }
359 + break;
360 + case 'wpdatmp':
361 + $use_cmd = explode( ' ', trim( $sqlcmds[$i] ) );
362 + $var_name = ( isset( $use_cmd[1] ) ? $use_cmd[1] : null );
363 + if ( $var_name !== null ) {
364 + $wpdadb_saved = $this->wpdatmp_table(
365 + $wpdadb,
366 + $tmps[$var_name],
367 + $tmps[$var_name]['data'],
368 + $tmps[$var_name]['table_name'],
369 + $var_name
370 + );
371 + $exequery = false;
372 + }
373 + $var_name = null;
374 + // reset
375 + break;
376 + }
377 + if ( !$reconnected && $exequery && null !== $wpdadb ) {
378 + $wpdadb->query( $sqlcmds[$i] );
379 + }
380 + if ( null === $var_name ) {
381 + $status = ( null !== $wpdadb_saved ? $wpdadb_saved : (( null !== $wpdadb ? clone $wpdadb : null )) );
382 + $tabs[] = array(
383 + 'cmd' => $sqlcmds[$i],
384 + 'status' => $status,
385 + 'columns' => $columns,
386 + );
387 + } else {
388 + $tabs[] = array(
389 + 'cmd' => $sqlcmds[$i],
390 + 'status' => ( isset( $vars[$var_name]['status'] ) ? $vars[$var_name]['status'] : null ),
391 + 'wpdavar' => ( isset( $tmps[$var_name] ) ? $tmps[$var_name] : null ),
392 + 'columns' => null,
393 + );
394 + }
395 + if ( null === $wpdadb || '' !== $wpdadb->last_error ) {
396 + break;
397 + }
398 + }
399 + $response['tabs'] = $tabs;
660 400 } else {
661 - $response['status'] = 'Token expired, please refresh page';
401 + $response['columns'] = WPDA::get_columns_from_query( $dbs, $query );
402 + // Process single SQL command
403 + if ( '' !== $limit && 'select' === strtolower( substr( $query, 0, 6 ) ) ) {
404 + $query .= " limit {$limit} ";
405 + }
406 + if ( null !== $wpdadb ) {
407 + $wpdadb->query( $query );
408 + }
409 + $response['cmd'] = $query;
410 + $response['status'] = clone $wpdadb;
662 411 }
663 412 } else {
664 - $response['status'] = 'Invalid arguments';
413 + $response['status'] = '<strong>WP Data Access error:</strong> Query not allowed - WordPress tables are protected';
665 414 }
666 - echo json_encode( $response );
415 + return $response;
667 416 }
668 417
669 - public function set_db_hints() {
670 - $response = array(
671 - 'status' => 'OK',
672 - );
673 - WPDA::sent_header( 'application/json' );
674 - if ( current_user_can( 'manage_options' ) && isset( $_REQUEST['wpda_wpnonce'], $_REQUEST['wpda_sqlhints'] ) ) {
675 - $wpda_wpnonce = sanitize_text_field( wp_unslash( $_REQUEST['wpda_wpnonce'] ) );
676 - // input var okay.
677 - $wpda_sqlhints = sanitize_text_field( wp_unslash( $_REQUEST['wpda_sqlhints'] ) );
678 - // input var okay.
679 - if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
680 - update_option( 'wpda_sql_hints', $wpda_sqlhints );
418 + public function add_query( $query_name, $query ) {
419 + $wpda_query_builder_data = $this->get_query_list();
420 + $wpda_query_builder_data[$query_name] = $query;
421 + $this->update_query_list( $wpda_query_builder_data );
422 + }
423 +
424 + public function add_query_global( $query_name, $query ) {
425 + $wpda_query_builder_data = $this->get_query_list_global();
426 + $wpda_query_builder_data[$query_name] = $query;
427 + $this->update_query_list_global( $wpda_query_builder_data );
428 + }
429 +
430 + public function get_hints( $dbs ) {
431 + $tables = WPDA_Dictionary_Lists::get_tables( true, $dbs );
432 + $hints = array();
433 + foreach ( $tables as $table ) {
434 + $columns = WPDA_Dictionary_Lists::get_table_columns( $table['table_name'], $dbs );
435 + $hint_cols = array();
436 + foreach ( $columns as $column ) {
437 + $hint_cols[] = $column['column_name'];
681 438 }
439 + $hints[$table['table_name']] = $hint_cols;
682 440 }
683 - $response['hint'] = $wpda_sqlhints;
684 - echo json_encode( $response );
441 + return $hints;
685 442 }
686 443
687 444 }