← All changes | classes/Visualizer/Source/Query.php +140 -46 3.1.3 → 4.0.8 View file →

		@@ -35,26 +35,55 @@
35	35	*/
36	36	protected $_query;
37	37
38	38	/**
39		- * The error message.
	39	+ * The chart id.
40	40	*
41	41	* @access protected
42		- * @var string
	42	+ * @var int
43	43	*/
44		- protected $_error;
	44	+ protected $_chart_id;
45	45
46	46	/**
	47	+ * Any additional parameters (e.g. for connecting to a remote db).
	48	+ *
	49	+ * @access protected
	50	+ * @var array
	51	+ */
	52	+ protected $_params;
	53	+
	54	+ /**
47	55	* Constructor.
48	56	*
49	57	* @access public
50	58	* @param string $query The query.
	59	+ * @param int $chart_id The chart id.
	60	+ * @param array $params Any additional parameters (e.g. for connecting to a remote db).
51	61	*/
52		- public function __construct( $query = null ) {
53		- $this->_query = $query;
	62	+ public function __construct( $query = null, $chart_id = null, $params = null ) {
	63	+ $this->_query = $this->strip_sql_comments( $query );
	64	+ $this->_chart_id = $chart_id;
	65	+ $this->_params = $params;
54	66	}
55	67
56	68	/**
	69	+ * Strips SQL comments from the query.
	70	+ *
	71	+ * @param string $query The query.
	72	+ *
	73	+ * @return string
	74	+ */
	75	+ private function strip_sql_comments( $query = '' ) {
	76	+ if ( empty( $query ) ) {
	77	+ return $query;
	78	+ }
	79	+
	80	+ // Regex https://regex101.com/r/xd5Vrg/1
	81	+ $sql_comments_regex = '@(--[^\r\n]*)|(\#[^\r\n]*)|(/\*[\w\W]*?(?=\*/)\*/)@ms';
	82	+ return trim( preg_replace( $sql_comments_regex, '', $query ) );
	83	+ }
	84	+
	85	+ /**
57	86	* Fetches information from source, parses it and builds series and data arrays.
58	87	*
59	88	* @param bool $as_html Should the result be fetched as an HTML table or as an object.
60	89	* @param bool $results_as_numeric_array Should the result be fetched as ARRAY_N instead of ARRAY_A.
		@@ -66,52 +95,128 @@
66	95	if ( empty( $this->_query ) ) {
67	96	return false;
68	97	}
69	98
	99	+ // only select queries allowed. must start with SELECT keyword.
	100	+ if ( ! preg_match( '/^(\bselect\b)\s/i', $this->_query ) ) {
	101	+ $this->_error = __( 'Only SELECT queries are allowed', 'visualizer' );
	102	+ return false;
	103	+ }
	104	+
	105	+ // if previous check passed, check for disallowed query parts to prevent subqueries and other harmful queries.
	106	+ $disallow_query_parts = array(
	107	+ 'CREATE',
	108	+ 'ALTER',
	109	+ 'TRUNCATE',
	110	+ 'DROP',
	111	+
	112	+ 'INSERT',
	113	+ 'DELETE',
	114	+ 'UPDATE',
	115	+ 'REPLACE',
	116	+
	117	+ 'RENAME',
	118	+ 'COMMIT',
	119	+ 'ROLLBACK',
	120	+ 'MERGE',
	121	+ 'CALL',
	122	+ 'EXPLAIN',
	123	+ 'LOCK',
	124	+ 'GRANT',
	125	+ 'REVOKE',
	126	+ 'SAVEPOINT',
	127	+ 'TRANSACTION',
	128	+ 'SET',
	129	+ );
	130	+ $disallow_regex = implode(
	131	+ '|',
	132	+ array_map(
	133	+ function ( $value ) {
	134	+ return '\b' . $value . '\b';
	135	+ }, $disallow_query_parts
	136	+ )
	137	+ );
	138	+
	139	+ if ( preg_match( '/(' . $disallow_regex . ')/i', $this->_query) !== 0 ) {
	140	+ $this->_error = __( 'Only SELECT queries are allowed', 'visualizer' );
	141	+ return false;
	142	+ }
	143	+
70	144	// impose a limit if no limit clause is provided.
71	145	if ( strpos( strtolower( $this->_query ), ' limit ' ) === false ) {
72		- $this->_query .= ' LIMIT ' . apply_filters( 'visualizer_sql_query_limit', 1000 );
	146	+ $this->_query .= ' LIMIT ' . apply_filters( 'visualizer_sql_query_limit', 1000, $this->_chart_id );
73	147	}
74	148
75		- global $wpdb;
76		- $wpdb->hide_errors();
77		- // @codingStandardsIgnoreStart
78		- $rows = $wpdb->get_results( $this->_query, $results_as_numeric_array ? ARRAY_N : ARRAY_A );
79		- do_action( 'themeisle_log_event', Visualizer_Plugin::NAME, sprintf( 'Firing query %s to get results %s with error %s', $this->_query, print_r( $rows, true ), print_r( $wpdb->last_error, true ) ), 'debug', __FILE__, __LINE__ );
80		- // @codingStandardsIgnoreEnd
81		- $wpdb->show_errors();
	149	+ $this->_query = apply_filters( 'visualizer_db_query', $this->_query, $this->_chart_id, $this->_params );
82	150
83		- if ( $raw_results ) {
84		- return $rows;
	151	+ $results = array();
	152	+ $headers = array();
	153	+
	154	+ // short circuit results for remote dbs.
	155	+ $remote_results = apply_filters( 'visualizer_db_query_execute', false, $this->_query, $as_html, $results_as_numeric_array, $raw_results, $this->_chart_id, $this->_params );
	156	+ if ( false !== $remote_results ) {
	157	+ $error = $remote_results['error'];
	158	+ if ( empty( $error ) ) {
	159	+ $results = $remote_results['results'];
	160	+ $headers = $remote_results['headers'];
	161	+ }
	162	+
	163	+ $this->_error = $error;
	164	+
	165	+ if ( $raw_results ) {
	166	+ return $results;
	167	+ }
85	168	}
86	169
87		- if ( $rows ) {
88		- $results = array();
89		- $headers = array();
	170	+ if ( ! ( $results && $headers ) ) {
	171	+ global $wpdb;
	172	+ $wpdb->hide_errors();
	173	+ // @codingStandardsIgnoreStart
	174	+ $rows = $wpdb->get_results( $this->_query, $results_as_numeric_array ? ARRAY_N : ARRAY_A );
	175	+ // @codingStandardsIgnoreEnd
	176	+ $wpdb->show_errors();
	177	+
	178	+ if ( $raw_results ) {
	179	+ return $rows;
	180	+ }
	181	+
	182	+ if ( $wpdb->last_error ) {
	183	+ $this->_error = $wpdb->last_error;
	184	+ return array();
	185	+ }
	186	+
90	187	if ( $rows ) {
91		- $row_num = 0;
92		- foreach ( $rows as $row ) {
93		- $result = array();
94		- $col_num = 0;
95		- foreach ( $row as $k => $v ) {
96		- $result[] = $v;
97		- if ( 0 === $row_num ) {
98		- $headers[] = array( 'type' => $this->get_col_type( $col_num++ ), 'label' => $k );
	188	+ $results = array();
	189	+ $headers = array();
	190	+ if ( $rows ) {
	191	+ $row_num = 0;
	192	+ foreach ( $rows as $row ) {
	193	+ $result = array();
	194	+ $col_num = 0;
	195	+ foreach ( $row as $k => $v ) {
	196	+ $result[] = $v;
	197	+ if ( 0 === $row_num ) {
	198	+ $headers[] = array( 'type' => $this->get_col_type( $col_num++ ), 'label' => $k );
	199	+ }
99	200	}
	201	+ $results[] = $result;
	202	+ ++$row_num;
100	203	}
101		- $results[] = $result;
102		- $row_num++;
103	204	}
	205	+
	206	+ $this->_error = $wpdb->last_error;
104	207	}
	208	+ }
	209	+ // Query log.
	210	+ do_action( 'themeisle_log_event', Visualizer_Plugin::NAME, sprintf( 'Firing query %s to get results %s with error %s', $this->_query, print_r( $rows, true ), print_r( $wpdb->last_error, true ) ), 'debug', __FILE__, __LINE__ );
105	211
106		- if ( $as_html ) {
107		- return $this->html( $headers, $results );
108		- }
109		- return $this->object( $headers, $results );
	212	+ if ( $as_html ) {
	213	+ $results = $this->html( $headers, $results );
	214	+ } else {
	215	+ $results = $this->object( $headers, $results );
110	216	}
111	217
112		- $this->_error = $wpdb->last_error;
113		- return null;
	218	+ return apply_filters( 'visualizer_db_query_results', $results, $headers, $as_html, $results_as_numeric_array, $raw_results, $this->_query, $this->_chart_id, $this->_params );
114	219	}
115	220
116	221	/**
117	222	* Get the data type of the column.
		@@ -173,20 +278,9 @@
173	278	foreach ( $results as $row ) {
174	279	$data[] = $this->_normalizeData( $row );
175	280	}
176	281	$this->_data = $data;
177		-
178		- return true;
179		- }
180		-
181		- /**
182		- * Returns the error, if any.
183		- *
184		- * @access public
185		- * @return string
186		- */
187		- public function get_error() {
188		- return $this->_error;
	282	+ return $this->_data;
189	283	}
190	284
191	285	/**
192	286	* Returns the final query.