Here is the solution that I came up with, with all the sensitive information changed.
If anyone has suggestions as far as hacking on some security, I'd appreciate it.
<?
php
/****************************************************
Query.
php
- This page formulates SQL queries from user input.
It gives the user a very fine grain of control over the queries and
allows him or her to see all the columns in a particular table in your
database, so I would only use it in a trusted environment. The design
could definitely stand some improvement, but I don't claim to be a
designer.
-Drew
8/10/2007
****************************************************/
require_once("functions.
php
");//All functions contained in functions.
php
.
?>
<HTML>
<head>
<script type="text/
javascript
">
var i=0;
function addAND()
{
i=i+1;
var newrow;
newrow='<TABLE border="0"><TR><TH colspan="3">AND<TR><TH>Column:<TH>Condition:<TH>Query:<TR><TD><select name="field'+i+'"><?printfields();?></select><TD><select name="condition'+i+'"><option value ="=">=</option><option value ="CONTAINS">CONTAINS</option><option value ="!=">NOT =</option><option value ="NOT CONTAIN">
OES NOT CONTAIN</option></select><TD><input type="text" name="q'+i+'"><input type="hidden" name="andor'+i+'" value="AND"></TABLE>';
document.getElementById('issues').innerHTML+=newrow;
}
function addOR()
{
i=i+1;
var newrow;
newrow='<TABLE border="0"><TR><TH colspan="3">OR<TR><TH>Column:<TH>Condition:<TH>Query:<TR><TD><select name="field'+i+'"><?printfields();?></select><TD><select name="condition'+i+'"><option value ="=">=</option><option value ="CONTAINS">CONTAINS</option><option value ="!=">NOT =</option><option value ="NOT CONTAIN">
OES NOT CONTAIN</option></select><TD><input type="text" name="q'+i+'"><input type="hidden" name="andor'+i+'" value="OR"></TABLE>';
document.getElementById('issues').innerHTML+=newrow;
}
</script>
</head>
<body>
<div id="formWrapper">
<div id="formDiv">
<form action="<?$_SERVER['
php
self'];?>" method="POST">
<div><input id="submitButton" type="submit" value="Submit Query"/> <input type="button" value="Add AND Condition" onclick="addAND();"/> <input type="button" value="Add OR Condition" onclick="addOR();"/></div>
<div id="issues"><TABLE border="0"><TR><TH>Column:<TH>Condition:<TH>Query:<TH>Sort By:<TR><TD><select name="field0"><?printfields();?></select><TD><select name="condition0"><option value ="=">=</option><option value ="CONTAINS">CONTAINS</option><option value ="!=">NOT =</option><option value ="NOT CONTAIN">
OES NOT CONTAIN</option></select><TD><input type="text" name="q0"><TD><select name="order0"><?printfields()?></select></TABLE></div>
</form>
</div>
</div>
</body>
</html>
</HTML>
<?
php
$query="SELECT * ";//Begin constructing query.
$query.="FROM TABLE ";
if(isset($_POST['q0']))//If first POST variable is set
{
for($i=0;$i<(count($_POST)/4);$i++)//Determine how many additional ANDs or ORs we have by looping through.
{
if(isset($_POST['q'.$i]))//If Nth POST variable is set.
{
$q=$_POST['q'.$i];//assign values.
$field=$_POST['field'.$i];
$condition=$_POST['condition'.$i];
$mod="WHERE";//Default value of modifier is WHERE. Changes to AND or OR for either of those.
switch($condition)//Determine operand condition.
{
case "=":
$cond="=";
$newq="'".$q."'";
$q=$newq;
break;
case "CONTAINS";
$cond="LIKE";
$newq="'%".$q."%'";
$q=$newq;
break;
case "!=";
$cond="=";
$newfield="NOT ".$field;
$field=$newfield;
$newq="'".$q."'";
$q=$newq;
break;
case "NOT CONTAIN";
$newfield="NOT ".$field;
$field=$newfield;
$cond="LIKE";
$newq="'%".$q."%'";
$q=$newq;
break;
}
if(isset($_POST['andor'.$i]))//If it's a secondary condition ORed or ANDed on.
{
$andor=$_POST['andor'.$i];
switch($andor)//Determine operand.
{
case "AND":
$mod="AND";
break;
case "OR":
$mod="OR";
break;
}
}
$query.="$mod $field ";//Construct latter part of query.
$query.="$cond $q ";
}
}
$order=$_POST['order0'];//Determine what to order on.
$query.="ORDER BY $order";
db_connect();//Connect to database.
$result = mssql_query($query);//Execute Query
$numrows = mssql_num_rows($result);
echo("
$numrows rows returned.<br /><br />");//Number of rows returned.
echo'
<form action="download.
php
" method="POST">
<input type="hidden" name="query" value="'.urlencode($query).'">
<input type="submit" value="
ownload as Spreadsheet">
</form>
';//Button to download as Excel Spreadsheet.
DataGrid($result);//Display results in data grid.
}
?>
Here is the functions.
<?
php
function db_connect()
{
$myServer = "SERVER";
$myUser = "USER";
$myPass = "PASS";
$myDB = "
B";
$s = @mssql_connect($myServer, $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");
$d = @mssql_select_db($myDB, $s)
or die("Couldn't open database $myDB");
}
function printfields()
{
db_connect();
$qq=mssql_query("SELECT * FROM TABLE");
$num=mssql_num_fields($qq);
for($i=0;$i<=$num;$i++)
{
echo('<option value ="['.@$field=mssql_field_name($qq,$i).']">'.@$field=mssql_field_name($qq,$i).'</option>');
}
}
function DataGrid($result)
{
if(mssql_num_rows($result)==0)//If the query did not return any rows...
{
echo"No data to display.";
}
else
{
echo('<TABLE border="2">');//Make a table.
$num=mssql_num_fields($result);
echo('<TR>');
for($i=0;$i<=$num;$i++)//Print each field name as a column header.
{
echo('<TH>'.@$field=mssql_field_name($result,$i));
//The @ supresses the error when we run out of field names.
}
while($row = mssql_fetch_array($result,MSSQL_ASSOC))
{
echo('<TR>');//Begin the table row.
foreach($row as $a)
{
if($a != '' && $a != 'REF!' && $a != '0' && $a != ' ')
//My data has a bunch of junk in it, you can remove this part if yours is clean.
{
echo('<TD>'.$a);//Echo each piece of data as a <TD> element.
}
else
{
echo('<TD>-');//Easier on the eyes than having all that gibberish in the table.
}
}
}
}
}
?>
