sql.phps

<?php
$allowed = array(
	'192.168.11.109',
);
if(!in_array($_SERVER['REMOTE_ADDR'], $allowed)) {
	die('NO U!');
}
session_start();
header('X-XSS-Protection: 0');

if(!empty($_POST['query'])) {
	$query = $_POST['query'];
} elseif(!empty($_GET['query'])) {
	$query = $_GET['query'];
}

// $start getimeofday(); $end gettimeofday(); $div = number to divide by
function function_timer($start, $end, $div = 1, $format = 1) {
	$end["usec"] = ($end["usec"]+(($end["sec"] - $start["sec"])*1000000));
	if($format) {
		return number_format((($end["usec"]-$start["usec"])/$div), 0);
	} else {
		return round((($end["usec"]-$start["usec"])/$div), 0);
	}
}

$start_page = gettimeofday();

session_start();

mysql_connect($mysql_host, $mysql_user, $mysql_pass);

$res = mysql_query('SHOW DATABASES');
while($row = mysql_fetch_row($res)) {
	$databases[] = $row[0];
}

if(!empty($_GET['sqlquerydb']) && $_SESSION['sqlquerydb'] != $_GET['sqlquerydb']) {
	$_SESSION['sqlquerydb'] = $_GET['sqlquerydb'];
}
if(!in_array($_SESSION['sqlquerydb'], $databases)) {
	$_SESSION['sqlquerydb'] = 'default_dbname';
}
mysql_select_db($_SESSION['sqlquerydb']);

mysql_query('SET NAMES utf8') or die(__FILE__.':'.__LINE__.' -> '.mysql_error());
//mysql_query('SET NAMES latin1') or die(__FILE__.':'.__LINE__.' -> '.mysql_error());

?>
<!DOCTYPE html>
<html lang="en">
<head>
	<title>SQL Query Editor</title>
	<meta charset="utf-8">
	<!--<meta charset="iso-8859-15">-->
	<meta http-equiv="X-UA-Compatible" content="IE=Edge">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<script src="/js/ready.min.js"></script>
	<script src="/codemirror/lib/codemirror.js"></script>
	<script src="/codemirror/mode/sql/sql.js"></script>
	<script src="/js/list.js"></script>
	<script src="/js/list.pagination.js"></script>
	<link rel="stylesheet" href="/codemirror/lib/codemirror.css" type="text/css">
	<style type="text/css">
		table {
			/*
			font-family: Arial,sans-serif;
			font-size: 80%;
			*/
			font-family: 'DejaVu Sans Mono,Consolas';
			font-size: 12px;
			border-collapse: collapse;
		}
		tr:nth-child(odd) {
			background-color: rgb(238,238,238);
		}
		th, td {
			padding: 2px;
			border: 1px solid rgb(255, 255, 255);
		}
		th {
			background-color: rgb(204, 204, 204);
			cursor: pointer;
		}
		td {
			text-align: left;
			vertical-align: top;
			white-space: pre-wrap;
		}
		tr:nth-child(odd) {
			background-color: rgb(238,238,238);
		}
		tr:nth-child(even) {
			background-color: white;
		}
		tr:hover, td:hover {
			background-color: rgba(0,140,203,0.2) !important;
		}
		td:hover {
			background-color: rgba(0,140,203,0.2) !important;
		}
		.CodeMirror {
			border: 1px solid #ddd;
		}

		.sort {
			padding: 3px 10px 3px 3px;
		}
		.sort:hover {
		}
		.sort:focus {
		}
		.sort:after {
			width: 0;
			height: 0;
			border-left: 5px solid transparent;
			border-right: 5px solid transparent;
			border-bottom: 5px solid transparent;
			content:"";
			position: relative;
			top:-10px;
			right:-5px;
		}
		.sort.asc:after {
			width: 0;
			height: 0;
			border-left: 5px solid transparent;
			border-right: 5px solid transparent;
			border-top: 5px solid #fff;
			content:"";
			position: relative;
			top:13px;
			right:-5px;
		}
		.sort.desc:after {
			width: 0;
			height: 0;
			border-left: 5px solid transparent;
			border-right: 5px solid transparent;
			border-bottom: 5px solid #fff;
			content:"";
			position: relative;
			top:-10px;
			right:-5px;
		}

	</style>
	<script type="text/javascript">
		$(document).ready(function() {
			$('table td').hover(
				function() {
					$('table td:nth-child(' + ($(this).index() + 1) + ')').addClass('hover');
				},
				function() {
					$('table td:nth-child(' + ($(this).index() + 1) + ')').removeClass('hover');
				}
			);
		});
		domready(function() {
			var editor = CodeMirror.fromTextArea(document.getElementById('SQLQuery'), {
				lineNumbers: true,
				indentWithTabs: true,
				smartIndent: true,
				matchBrackets: true,
				autofocus: true,
				mode: "text/x-mysql",
			});
			document.getElementById('chkShowNull').checked = cook.get('shownull');
		});
		function onShowNull() {
			cook.set('shownull', document.getElementById('chkShowNull').checked ? 1 : 0, 365);
		}
		var cook = {
			/***
			* Set cookie with 2 days TTL
			* cook.set('hello', 'world', 2);
			*
			* Get cookie value
			* cook.get('hello')
			*
			* Remove cookie
			* cook.kill('hello');
			***/
			set: function(n, v, d) {
				if(d) {
					var dt = new Date();
					dt.setDate(dt.getDate() + d);
					d = '; expires=' + dt.toGMTString();
				}
				document.cookie = n + '=' + escape(v) + (d || '') + '; path=/';
			},
			get: function(n) {
				var c = document.cookie.match('(^|;)\x20*' + n + '=([^;]*)');
				return c? unescape(c[2]) : null;
			},
			kill: function(n) {
				cook.set(n, '', -1);
			}, killall: function() {
				var cookies = document.cookie.split(';'), i = cookies.length - 1;
				for(i; i > -1; --i) {
					cook.kill(cookies[i].split('=')[0]);
				}
			}
		};
	</script>
</head>
<body>

<form action="<?=$_SERVER['PHP_SELF']?>" method="GET">
	<select name="sqlquerydb">
		<?php
		foreach($databases as $database) {
			echo '<option value="'.urlencode($database).'"'.($_SESSION['sqlquerydb'] == $database ? ' selected' : '').'>'.htmlentities($database).'</option>'.PHP_EOL;
		}
		?>
	</select>
	<label><input type="checkbox" name="shownull" id="chkShowNull" onchange="onShowNull()"<?=($_COOKIE['shownull'] ? ' checked' : '')?>> Show null</label>
	<p>SQL Query:<br />
		<!-- onFocus="this.select()" -->
		<textarea name="query" id="SQLQuery"><?=htmlspecialchars(stripslashes($query, ENT_QUOTES | ENT_IGNORE, 'UTF-8'))?></textarea>
	</p>
	<p><input type="submit" name="submitquery" value="Submit Query (Alt-S)" accesskey="S" /></p>
</form>
<?php

if (isset($_GET['submitquery'])) {
	if (get_magic_quotes_gpc()) {
		$_GET['query'] = stripslashes($_GET['query']);
	}
	require 'sql-formatter/lib/SqlFormatter.php';
	echo '<p><strong>Query:</strong><br />'.SqlFormatter::format($_GET['query']).'</p>';
	//echo '<p><strong>Query:</strong><br />'.SqlFormatter::format(utf8_encode($_REQUEST['query'])).'</p>';
	$start = gettimeofday();
	$result = mysql_query($_GET['query']);
	$time = function_timer($start, gettimeofday(), 1);
	if(!$result) {
		echo sprintf('<p><b>Query Failed:</b>%s (%s microseconds)</p>', mysql_error(), $time).PHP_EOL;
	} else {
	?>
		<?php if(!@mysql_num_rows($result)) {
			echo sprintf('<p><b>Query OK:</b> %s rows affected. (%s microseconds)</p>', mysql_affected_rows(), $time).PHP_EOL;
		} else {
			$num = mysql_num_fields($result);
			for ($x=0; $x<$num; $x++) {
				//$cols[] = mysql_field_name($result, $x);
				$cols[] = htmlentities(mysql_field_name($result, $x), ENT_COMPAT | ENT_HTML401);
			}
			echo '<script type="text/javascript">domready(function() { var options = { valueNames: ["'.implode('","', $cols).'"], page: 20000 }; var jsList = new List("jsList", options); });</script>';
			?>
			<p><b>Result Set:</b> <?=mysql_affected_rows()?> rows returned. (<?=$time?> microseconds)</p>
			<div id="jsList">
			<table border="1">
				<thead>
					<tr>
						<?php
						$num = mysql_num_fields($result);
						for ($i=0; $i<$num; $i++) {
						?>
							<th class="sort" data-sort="<?=$cols[$i]?>"><?=htmlentities(mysql_field_name($result, $i), ENT_COMPAT | ENT_HTML401)?></th>
						<?php
						}
						?>
					</tr>
				</thead>
				<tbody class="list">
					<?php while ($row = mysql_fetch_row($result)) { ?>
						<tr>
							<?php
							$num = mysql_num_fields($result);
							for ($i=0; $i<$num; $i++) {
							?>
								<td class="<?=$cols[$i]?>"><?=($row[$i] == null && $_COOKIE['shownull'] ? '<i>NULL</i>' : $row[$i]).(strlen($row[$i])==0?'&nbsp;':'')//str_replace("\n", "<br>", $row[$i])?></td>
							<?php
							}
							?>
						</tr>
					<?php } ?>
				</tbody>
			</table>
			<ul class="pagination"></ul>
			</div>
		<?php }
	}
}

$time_page = function_timer($start_page, gettimeofday(), 1);

?>
<p>Page time: <?=$time_page?> microseconds</p>
<br><br>

</body>
</html>
<!-- vim: set ts=4 -->