1<?php
2
3	/**
4	 * Process an arbitrary SQL query - tricky!  The main problem is that
5	 * unless we implement a full SQL parser, there's no way of knowing
6	 * how many SQL statements have been strung together with semi-colons
7	 * @param $_SESSION['sqlquery'] The SQL query string to execute
8	 *
9	 * $Id: sql.php,v 1.43 2008/01/10 20:19:27 xzilla Exp $
10	 */
11
12	 global $lang;
13
14	// Prevent timeouts on large exports (non-safe mode only)
15	if (!ini_get('safe_mode')) set_time_limit(0);
16
17	// Include application functions
18	include_once('./libraries/lib.inc.php');
19
20	/**
21	 * This is a callback function to display the result of each separate query
22	 * @param ADORecordSet $rs The recordset returned by the script execetor
23	 */
24	function sqlCallback($query, $rs, $lineno) {
25		global $data, $misc, $lang, $_connection;
26		// Check if $rs is false, if so then there was a fatal error
27		if ($rs === false) {
28			echo htmlspecialchars($_FILES['script']['name']), ':', $lineno, ': ', nl2br(htmlspecialchars($_connection->getLastError())), "<br/>\n";
29		}
30		else {
31			// Print query results
32			switch (pg_result_status($rs)) {
33				case PGSQL_TUPLES_OK:
34					// If rows returned, then display the results
35					$num_fields = pg_numfields($rs);
36					echo "<p><table>\n<tr>";
37					for ($k = 0; $k < $num_fields; $k++) {
38						echo "<th class=\"data\">", $misc->printVal(pg_fieldname($rs, $k)), "</th>";
39					}
40
41					$i = 0;
42					$row = pg_fetch_row($rs);
43					while ($row !== false) {
44						$id = (($i % 2) == 0 ? '1' : '2');
45						echo "<tr class=\"data{$id}\">\n";
46						foreach ($row as $k => $v) {
47							echo "<td style=\"white-space:nowrap;\">", $misc->printVal($v, pg_fieldtype($rs, $k), array('null' => true)), "</td>";
48						}
49						echo "</tr>\n";
50						$row = pg_fetch_row($rs);
51						$i++;
52					};
53					echo "</table><br/>\n";
54					echo $i, " {$lang['strrows']}</p>\n";
55					break;
56				case PGSQL_COMMAND_OK:
57					// If we have the command completion tag
58					if (version_compare(phpversion(), '4.3', '>=')) {
59						echo htmlspecialchars(pg_result_status($rs, PGSQL_STATUS_STRING)), "<br/>\n";
60					}
61					// Otherwise if any rows have been affected
62					elseif ($data->conn->Affected_Rows() > 0) {
63						echo $data->conn->Affected_Rows(), " {$lang['strrowsaff']}<br/>\n";
64					}
65					// Otherwise output nothing...
66					break;
67				case PGSQL_EMPTY_QUERY:
68					break;
69				default:
70					break;
71			}
72		}
73	}
74
75	// We need to store the query in a session for editing purposes
76	// We avoid GPC vars to avoid truncating long queries
77	if (isset($_REQUEST['subject']) && $_REQUEST['subject'] == 'history') {
78		// Or maybe we came from the history popup
79		$_SESSION['sqlquery'] = $_SESSION['history'][$_REQUEST['server']][$_REQUEST['database']][$_GET['queryid']]['query'];
80	}
81	elseif (isset($_POST['query'])) {
82		// Or maybe we came from an sql form
83		$_SESSION['sqlquery'] = $_POST['query'];
84	}
85	else {
86		echo "could not find the query!!";
87	}
88
89	// Pagination maybe set by a get link that has it as FALSE,
90	// if that's the case, unset the variable.
91
92	if (isset($_REQUEST['paginate']) && $_REQUEST['paginate'] == 'f') {
93		unset($_REQUEST['paginate']);
94		unset($_POST['paginate']);
95		unset($_GET['paginate']);
96	}
97	// Check to see if pagination has been specified. In that case, send to display
98	// script for pagination
99	/* if a file is given or the request is an explain, do not paginate */
100	if (isset($_REQUEST['paginate']) && !(isset($_FILES['script']) && $_FILES['script']['size'] > 0)
101			&& (preg_match('/^\s*explain/i', $_SESSION['sqlquery']) == 0)) {
102		include('./display.php');
103		exit;
104	}
105
106	$subject = isset($_REQUEST['subject'])? $_REQUEST['subject'] : '';
107	$misc->printHeader($lang['strqueryresults']);
108	$misc->printBody();
109	$misc->printTrail('database');
110	$misc->printTitle($lang['strqueryresults']);
111
112	// Set the schema search path
113	if (isset($_REQUEST['search_path'])) {
114		if ($data->setSearchPath(array_map('trim',explode(',',$_REQUEST['search_path']))) != 0) {
115			$misc->printFooter();
116			exit;
117		}
118	}
119
120	// May as well try to time the query
121	if (function_exists('microtime')) {
122		list($usec, $sec) = explode(' ', microtime());
123		$start_time = ((float)$usec + (float)$sec);
124	}
125	else $start_time = null;
126	// Execute the query.  If it's a script upload, special handling is necessary
127	if (isset($_FILES['script']) && $_FILES['script']['size'] > 0)
128		$data->executeScript('script', 'sqlCallback');
129	else {
130		// Set fetch mode to NUM so that duplicate field names are properly returned
131		$data->conn->setFetchMode(ADODB_FETCH_NUM);
132		$rs = $data->conn->Execute($_SESSION['sqlquery']);
133
134		// $rs will only be an object if there is no error
135		if (is_object($rs)) {
136			// Request was run, saving it in history
137			if(!isset($_REQUEST['nohistory']))
138				$misc->saveScriptHistory($_SESSION['sqlquery']);
139
140			// Now, depending on what happened do various things
141
142			// First, if rows returned, then display the results
143			if ($rs->recordCount() > 0) {
144				echo "<table>\n<tr>";
145				foreach ($rs->fields as $k => $v) {
146					$finfo = $rs->fetchField($k);
147					echo "<th class=\"data\">", $misc->printVal($finfo->name), "</th>";
148				}
149                                echo "</tr>\n";
150				$i = 0;
151				while (!$rs->EOF) {
152					$id = (($i % 2) == 0 ? '1' : '2');
153					echo "<tr class=\"data{$id}\">\n";
154					foreach ($rs->fields as $k => $v) {
155						$finfo = $rs->fetchField($k);
156						echo "<td style=\"white-space:nowrap;\">", $misc->printVal($v, $finfo->type, array('null' => true)), "</td>";
157					}
158					echo "</tr>\n";
159					$rs->moveNext();
160					$i++;
161				}
162				echo "</table>\n";
163				echo "<p>", $rs->recordCount(), " {$lang['strrows']}</p>\n";
164			}
165			// Otherwise if any rows have been affected
166			elseif ($data->conn->Affected_Rows() > 0) {
167				echo "<p>", $data->conn->Affected_Rows(), " {$lang['strrowsaff']}</p>\n";
168			}
169			// Otherwise nodata to print
170			else echo '<p>', $lang['strnodata'], "</p>\n";
171		}
172	}
173
174	// May as well try to time the query
175	if ($start_time !== null) {
176		list($usec, $sec) = explode(' ', microtime());
177		$end_time = ((float)$usec + (float)$sec);
178		// Get duration in milliseconds, round to 3dp's
179		$duration = number_format(($end_time - $start_time) * 1000, 3);
180	}
181	else $duration = null;
182
183	// Reload the browser as we may have made schema changes
184	$_reload_browser = true;
185
186	// Display duration if we know it
187	if ($duration !== null) {
188		echo "<p>", sprintf($lang['strruntime'], $duration), "</p>\n";
189	}
190
191	echo "<p>{$lang['strsqlexecuted']}</p>\n";
192
193	$navlinks = array();
194	$fields = array(
195		'server' => $_REQUEST['server'],
196		'database' => $_REQUEST['database'],
197	);
198
199	if(isset($_REQUEST['schema']))
200		$fields['schema'] = $_REQUEST['schema'];
201
202	// Return
203	if (isset($_REQUEST['return'])) {
204		$urlvars = $misc->getSubjectParams($_REQUEST['return']);
205		$navlinks['back'] = array (
206			'attr'=> array (
207				'href' => array (
208					'url' => $urlvars['url'],
209					'urlvars' => $urlvars['params']
210				)
211			),
212			'content' => $lang['strback']
213		);
214	}
215
216	// Edit
217	$navlinks['alter'] = array (
218		'attr'=> array (
219			'href' => array (
220				'url' => 'database.php',
221				'urlvars' => array_merge($fields, array (
222					'action' => 'sql',
223				))
224			)
225		),
226		'content' => $lang['streditsql']
227	);
228
229	// Create view and download
230	if (isset($_SESSION['sqlquery']) && isset($rs) && is_object($rs) && $rs->recordCount() > 0) {
231		// Report views don't set a schema, so we need to disable create view in that case
232		if (isset($_REQUEST['schema'])) {
233			$navlinks['createview'] = array (
234				'attr'=> array (
235					'href' => array (
236						'url' => 'views.php',
237						'urlvars' => array_merge($fields, array (
238							'action' => 'create'
239						))
240					)
241				),
242				'content' => $lang['strcreateview']
243			);
244		}
245
246		if (isset($_REQUEST['search_path']))
247			$fields['search_path'] = $_REQUEST['search_path'];
248
249		$navlinks['download'] = array (
250			'attr'=> array (
251				'href' => array (
252					'url' => 'dataexport.php',
253					'urlvars' => $fields
254				)
255			),
256			'content' => $lang['strdownload']
257		);
258	}
259
260	$misc->printNavLinks($navlinks, 'sql-form', get_defined_vars());
261
262	$misc->printFooter();
263?>
264