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 +292 -521 5.5.3 → 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,388 +32,34 @@
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] = count( $tabs );
332 - $tmps[$var_name] = $this->get_create_table( $wpdadb, count( $tabs ) );
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->create_tmp_table( $wpdadb, $tmps[$var_name], $vars[$var_name] );
341 - $exequery = false;
342 - }
343 - $var_name = null;
344 - // reset
345 - break;
346 - }
347 - if ( !$reconnected && $exequery && null !== $wpdadb ) {
348 - $wpdadb->query( $sqlcmds[$i] );
349 - }
350 - if ( null === $var_name ) {
351 - $status = ( null !== $wpdadb_saved ? $wpdadb_saved : (( null !== $wpdadb ? clone $wpdadb : null )) );
352 - $tabs[] = array(
353 - 'cmd' => $sqlcmds[$i],
354 - 'status' => $status,
355 - 'columns' => $columns,
356 - );
357 - } else {
358 - $tabs[] = array(
359 - 'cmd' => $sqlcmds[$i],
360 - 'status' => ( isset( $vars[$var_name]['status'] ) ? $vars[$var_name]['status'] : null ),
361 - 'wpdavar' => ( isset( $tmps[$var_name] ) ? $tmps[$var_name] : null ),
362 - 'columns' => null,
363 - );
364 - }
365 - if ( null === $wpdadb || '' !== $wpdadb->last_error ) {
366 - break;
367 - }
368 - }
369 - $response['tabs'] = $tabs;
370 - } else {
371 - $response['columns'] = WPDA::get_columns_from_query( $wpda_schemaname, $wpda_sqlquery );
372 - // Process single SQL command
373 - if ( '' !== $wpda_sqllimit && 'select' === strtolower( substr( $wpda_sqlquery, 0, 6 ) ) ) {
374 - $wpda_sqlquery .= " limit {$wpda_sqllimit} ";
375 - }
376 - if ( null !== $wpdadb ) {
377 - $wpdadb->query( $wpda_sqlquery );
378 - }
379 - $response['cmd'] = $wpda_sqlquery;
380 - $response['status'] = $wpdadb;
381 - }
382 - } else {
383 - $response['status'] = '<strong>WP Data Access error:</strong> Token expired, please refresh page';
384 - }
385 - } else {
386 - $response['status'] = '<strong>WP Data Access error:</strong> Query not allowed - WordPress tables are protected';
387 - }
388 - } else {
389 - $response['status'] = '<strong>WP Data Access error:</strong> Query execution failed';
390 - }
391 - ob_end_clean();
392 - echo json_encode( $response );
393 - }
394 -
395 - private function create_tmp_table( $wpdadb, $sql, $data ) {
36 + private function wpdatmp_table(
37 + $wpdadb,
38 + $sql,
39 + $data,
40 + $table_name,
41 + $var_name
42 + ) {
396 43 // Create temporary table
397 - $wpdadb->query( str_ireplace( 'CREATE TABLE', 'CREATE TEMPORARY TABLE', $sql['create_table'] ) );
44 + $wpdadb->query( str_ireplace( array('CREATE TABLE', $table_name), array('CREATE TEMPORARY TABLE', $var_name), $sql['create_table'] ) );
398 45 $wpdadb_created = clone $wpdadb;
399 46 // Copy data to temporary table
400 - foreach ( $data['status']->last_result as $resultset ) {
47 + foreach ( $data as $resultset ) {
401 48 $column_values = array();
402 49 foreach ( $resultset as $key => $val ) {
403 50 $column_values[$key] = $val;
404 51 }
405 - $wpdadb->insert( $sql['table_name'], $column_values );
52 + $wpdadb->insert( $var_name, $column_values );
406 53 }
407 54 return $wpdadb_created;
408 55 }
409 56
410 - private function get_create_table( $wpdadb, $query ) {
57 + private function wpdavar_table( $wpdadb, $query, $var_name ) {
411 58 $wpdadb->query( "set sql_quote_show_create = 'ON'" );
59 + if ( null === $query['status'] ) {
60 + return null;
61 + }
412 62 $explain = $wpdadb->get_results( "explain {$query['status']->last_query}", 'ARRAY_A' );
413 63 if ( '' !== $wpdadb->last_error ) {
414 64 return null;
415 65 }
@@ -419,20 +69,19 @@
419 69 return null;
420 70 }
421 71 $sql = str_replace( "\n", '', $create_table[0][1] );
422 72 $pos = stripos( $sql, ') ENGINE=' );
423 - return ( false === $pos ? array(
73 + $tbl = ( false === $pos ? $sql : substr( $sql, 0, $pos + 1 ) );
74 + return array(
424 75 'table_name' => $table_name,
425 - 'create_table' => $sql,
426 - ) : array(
427 - 'table_name' => $table_name,
428 - 'create_table' => substr( $sql, 0, $pos + 1 ),
429 - ) );
76 + 'create_table' => $tbl,
77 + 'data' => $query['status']->last_result,
78 + );
430 79 }
431 80
432 - private function check_query( $wpda_protect, $wpda_schemaname, $wpda_sqlquery ) {
81 + public function check_query( $wpda_protect, $wpda_schemaname, $wpda_sqlquery ) {
433 82 $sql_parts = explode( ' ', trim( $wpda_sqlquery ) );
434 - //phpcs:ignore - 8.1 proof
83 + // phpcs:ignore -- 8.1 proof
435 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] )) ) {
436 85 return false;
437 86 }
438 87 if ( isset( $sql_parts[0] ) && isset( $sql_parts[1] ) && WPDA::is_wp_table( $sql_parts[1] ) && 'truncate' === strtolower( $sql_parts[0] ) ) {
@@ -451,116 +100,34 @@
451 100 }
452 101 return true;
453 102 }
454 103
455 - public function save() {
456 - $response = array(
457 - 'status' => null,
458 - );
459 - WPDA::sent_header( 'application/json' );
460 - // Only for admins
461 - if ( current_user_can( 'manage_options' ) && isset(
462 - $_POST['wpda_wpnonce'],
463 - $_POST['wpda_schemaname'],
464 - $_POST['wpda_sqlqueryname'],
465 - $_POST['wpda_sqlqueryname_old'],
466 - $_POST['wpda_sqlquery']
467 - ) ) {
468 - $wpda_wpnonce = sanitize_text_field( wp_unslash( $_POST['wpda_wpnonce'] ) );
469 - // input var okay.
470 - $wpda_schemaname = sanitize_text_field( wp_unslash( $_POST['wpda_schemaname'] ) );
471 - // input var okay.
472 - $wpda_sqlqueryname = sanitize_text_field( wp_unslash( $_POST['wpda_sqlqueryname'] ) );
473 - // input var okay.
474 - $wpda_sqlqueryname_old = sanitize_text_field( wp_unslash( $_POST['wpda_sqlqueryname_old'] ) );
475 - // input var okay.
476 - $wpda_sqlquery = sanitize_textarea_field( wp_unslash( $_POST['wpda_sqlquery'] ) );
477 - // input var okay.
478 - $wpda_vqb = null;
479 - if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
480 - // Save query
481 - if ( false === $this->upd_query(
482 - $wpda_schemaname,
483 - $wpda_sqlqueryname,
484 - $wpda_sqlquery,
485 - $wpda_sqlqueryname_old,
486 - $wpda_vqb
487 - ) ) {
488 - $response['status'] = 'Could not save query';
489 - } else {
490 - $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]);
491 114 }
492 - } else {
493 - $response['status'] = 'Token expired, please refresh page';
494 115 }
116 + return $queries;
495 117 } else {
496 - $response['status'] = 'Could not save query';
118 + return array();
497 119 }
498 - echo json_encode( $response );
499 120 }
500 121
501 - public function open() {
502 - $response = array(
503 - 'data' => array(),
504 - 'status' => null,
505 - );
506 - WPDA::sent_header( 'application/json' );
507 - // Only for admins
508 - if ( current_user_can( 'manage_options' ) && isset( $_POST['wpda_wpnonce'], $_POST['wpda_exclude'] ) ) {
509 - $wpda_wpnonce = sanitize_text_field( wp_unslash( $_POST['wpda_wpnonce'] ) );
510 - // input var okay.
511 - $wpda_exclude = sanitize_text_field( wp_unslash( $_POST['wpda_exclude'] ) );
512 - // input var okay.
513 - if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
514 - // Send list of available queries
515 - $wpda_query_builder_data = $this->get_query_list( $wpda_exclude );
516 - uksort( $wpda_query_builder_data, 'strnatcasecmp' );
517 - //phpcs:ignore - 8.1 proof
518 - $response['data'] = $wpda_query_builder_data;
519 - } else {
520 - $response['status'] = 'Token expired, please refresh page';
521 - }
522 - } else {
523 - $response['status'] = 'Query list not available';
524 - }
525 - echo json_encode( $response );
526 - }
527 -
528 - public function delete() {
529 - $response = array(
530 - 'status' => null,
531 - );
532 - WPDA::sent_header( 'application/json' );
533 - // Only for admins
534 - if ( current_user_can( 'manage_options' ) && isset( $_POST['wpda_wpnonce'], $_POST['wpda_sqlqueryname'] ) ) {
535 - $wpda_wpnonce = sanitize_text_field( wp_unslash( $_POST['wpda_wpnonce'] ) );
536 - // input var okay.
537 - $wpda_sqlqueryname = sanitize_text_field( wp_unslash( $_POST['wpda_sqlqueryname'] ) );
538 - // input var okay.
539 - if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
540 - // Save query
541 - if ( false === $this->del_query( $wpda_sqlqueryname ) ) {
542 - $response['status'] = 'Could not delete query';
543 - } else {
544 - $response['status'] = 'Query deleted';
545 - }
546 - } else {
547 - $response['status'] = 'Token expired, please refresh page';
548 - }
549 - } else {
550 - $response['status'] = 'Could not save query';
551 - }
552 - echo json_encode( $response );
553 - }
554 -
555 - protected function get_query_list( $exclude = '' ) {
556 - $wpda_query_builder_data = get_user_meta( WPDA::get_current_user_id(), 'wpda_query_builder' );
557 - if ( count( $wpda_query_builder_data ) > 0 ) {
558 - //phpcs:ignore - 8.1 proof
559 - $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;
560 127 if ( null !== $exclude && '' !== $exclude ) {
561 128 $exclude_array = explode( ',', $exclude );
562 - //phpcs:ignore - 8.1 proof
129 + // phpcs:ignore -- 8.1 proof
563 130 foreach ( $exclude_array as $exclude_item ) {
564 131 unset($queries[$exclude_item]);
565 132 }
566 133 }
@@ -569,14 +136,22 @@
569 136 return array();
570 137 }
571 138 }
572 139
573 - protected function upd_query_list( $wpda_query_builder_data ) {
574 - 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 );
575 142 }
576 143
577 - protected function get_query( $query_name ) {
578 - $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 + }
579 154 if ( is_array( $wpda_query_builder_data ) && isset( $wpda_query_builder_data[$query_name] ) ) {
580 155 return $wpda_query_builder_data[$query_name];
581 156 } else {
582 157 return array();
@@ -582,14 +157,24 @@
582 157 return array();
583 158 }
584 159 }
585 160
586 - 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(
587 171 $schema_name,
588 172 $query_name,
589 173 $query_sql,
590 174 $query_name_old,
591 - $wpda_vqb = null
175 + $wpda_vqb = null,
176 + $params = null
592 177 ) {
593 178 $wpda_query_builder_data = $this->get_query_list();
594 179 if ( '' !== $query_name_old && $query_name !== $query_name_old ) {
595 180 unset($wpda_query_builder_data[$query_name_old]);
@@ -597,77 +182,263 @@
597 182 $wpda_query_builder_data[$query_name] = array(
598 183 'schema_name' => $schema_name,
599 184 'query' => $query_sql,
600 185 'is_visual' => null !== $wpda_vqb,
186 + 'params' => $params,
601 187 );
602 - $this->upd_query_list( $wpda_query_builder_data );
188 + $this->update_query_list( $wpda_query_builder_data );
603 189 }
604 190
605 - 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 + ) {
606 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();
607 243 unset($wpda_query_builder_data[$query_name]);
608 - $this->upd_query_list( $wpda_query_builder_data );
244 + $this->update_query_list( $wpda_query_builder_data );
245 + $this->del_visual_query( $query_name );
609 246 }
610 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 +
611 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 );
612 257 }
613 258
614 - 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 );
615 261 }
616 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 +
617 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 ) );
618 273 }
619 274
275 + protected function del_visual_query_global( $query_name ) {
276 + delete_option( 'wpda_query_builder_' . $this->rewrite_query_name( $query_name ) );
277 + }
278 +
620 279 protected function rewrite_query_name( $query_name ) {
621 280 return str_replace( ' ', '_', $query_name );
622 281 }
623 282
624 - 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 + ) {
625 293 $response = array(
626 - 'status' => 'OK',
294 + 'tabs' => array(),
295 + 'status' => null,
627 296 );
628 - WPDA::sent_header( 'application/json' );
629 - if ( current_user_can( 'manage_options' ) && isset( $_REQUEST['wpda_wpnonce'], $_REQUEST['wpda_schemaname'] ) ) {
630 - $wpda_wpnonce = sanitize_text_field( wp_unslash( $_REQUEST['wpda_wpnonce'] ) );
631 - // input var okay.
632 - $wpda_schemaname = sanitize_text_field( wp_unslash( $_REQUEST['wpda_schemaname'] ) );
633 - // input var okay.
634 - if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
635 - $tables = WPDA_Dictionary_Lists::get_tables( true, $wpda_schemaname );
636 - $hints = array();
637 - foreach ( $tables as $table ) {
638 - $columns = WPDA_Dictionary_Lists::get_table_columns( $table['table_name'], $wpda_schemaname );
639 - $hint_cols = array();
640 - foreach ( $columns as $column ) {
641 - $hints[$table['table_name']] = $hint_cols;
642 - $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 + }
643 315 }
316 + $sqlcmds[] = $sql;
317 + $start_i = $i + 1;
644 318 }
645 - $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;
646 400 } else {
647 - $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;
648 411 }
649 412 } else {
650 - $response['status'] = 'Invalid arguments';
413 + $response['status'] = '<strong>WP Data Access error:</strong> Query not allowed - WordPress tables are protected';
651 414 }
652 - echo json_encode( $response );
415 + return $response;
653 416 }
654 417
655 - public function set_db_hints() {
656 - $response = array(
657 - 'status' => 'OK',
658 - );
659 - WPDA::sent_header( 'application/json' );
660 - if ( current_user_can( 'manage_options' ) && isset( $_REQUEST['wpda_wpnonce'], $_REQUEST['wpda_sqlhints'] ) ) {
661 - $wpda_wpnonce = sanitize_text_field( wp_unslash( $_REQUEST['wpda_wpnonce'] ) );
662 - // input var okay.
663 - $wpda_sqlhints = sanitize_text_field( wp_unslash( $_REQUEST['wpda_sqlhints'] ) );
664 - // input var okay.
665 - if ( wp_verify_nonce( $wpda_wpnonce, 'wpda-query-builder-' . WPDA::get_current_user_id() ) ) {
666 - 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'];
667 438 }
439 + $hints[$table['table_name']] = $hint_cols;
668 440 }
669 - $response['hint'] = $wpda_sqlhints;
670 - echo json_encode( $response );
441 + return $hints;
671 442 }
672 443
673 444 }