<?php
function MyHeader ($menutitle, $querydesc) {
echo("<?xml version=\"1.0\"?>
<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\"
\"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">
<html xmlns=\"http://www.w3.org/1999/xhtml\">
<head>
<title>$menutitle </title>
</head>
<body bgcolor=\"#ffeb10\">
<div class=\"main\">
<h3>$menutitle </h3>
<h2>$querydesc </h2>
<p/>
<hr width=\"30%\" align=\"left\"/>
");
}
function Dbconnect() {
// database connection
$GLOBALS['dbl'] = mysql_connect($GLOBALS['opt']['hn'],$GLOBALS['opt']['un'],$GLOBALS['opt']['pw'])
or pma_errpage("Could not connect to MySQL");
if (!isset($GLOBALS['opt']['db'])) {
pma_errpage("No Database defined");
}
if (!isset($GLOBALS['tb'])) {
pma_errpage("No Table defined");
}
}
// NOTE from gfdos: not sure WHY this is marked *fixme*. These fields don't match anything else in this release -- must have been "WIP" when 1.0.10 was released
// *fixme* Query table Fields should be :
// query_ID query_name query_desc query_level query_owner query_state query_headers query_query query_mod
// get our query SQL Value
function GetQuery ($queryId) {
// defang web data to be a positive integer
$qID = abs(intval($queryId));
// $query = "Select query_name, query_desc, query_level, query_owner, query_state, query_headers, query_query, query_mod from queries where query_ID=$qID";
$query = "Select query_name, query_desc, query_level, query_headers, query_query from queries where query_ID=$qID";
pma_debug ("QueryID = $queryId");
$res = mysql_db_query($GLOBALS['opt']['db'],$query);
if (!$res) {
pma_errpage("Query table select " . mysql_errno().": ".mysql_error()." Bad Query Number or DB error");
}
//list($GLOBALS['qry']['name'], $GLOBALS['qry']['desc'], $GLOBALS['qry']['level'], $GLOBALS['qry']['owner'], $GLOBALS['qry']['state'], $GLOBALS['qry']['headers'], $GLOBALS['qry']['query'], $GLOBALS['qry']['mod']) = mysql_fetch_row($res);
list($GLOBALS['qry']['name'], $GLOBALS['qry']['desc'], $GLOBALS['qry']['level'], $GLOBALS['qry']['headers'], $GLOBALS['qry']['query']) = mysql_fetch_row($res);
}
// totally stolen, messed up and adapted for this limited use.
// original author Erol Ozcan (eozcan AT superonline.com)
// psxlsgen class on phpclasses.upperdesign.com
// free registration required to download there.
$xls['xls_data'] = ""; // where generated xls be stored
$xls['crow'] = 0; // current row number
$xls['ccol'] = 0; // current column number
function xlsStart() {
// begin of the excel file header
$GLOBALS['xls']['xls_data'] = pack( "ssssss", 0x809, 0x08, 0x00,0x10, 0x0, 0x0 );
}
// end of the excel file
function xlsEnd() {
$GLOBALS['xls']['xls_data'] .= pack( "ss", 0x0A, 0x00 );
return;
}
// write a value of type number/text into row, col
// if both row and col are 0 then just add to where you were in the sheet
function xlsInsert( $value, $type, $row=0, $col=0 ) {
$colinc = 0;
if (!$row && !$col) {
$row=$GLOBALS['xls']['crow'];
$col=$GLOBALS['xls']['ccol'];
$colinc = 1;
}
if ($type=='number') {
$GLOBALS['xls']['xls_data'] .= pack( "sssss", 0x0203, 14, $row, $col, 0x00 );
$GLOBALS['xls']['xls_data'] .= pack( "d", $value );
} else if ($type=='text') {
$len = strlen( $value );
$GLOBALS['xls']['xls_data'] .= pack( "s*", 0x0204, 8 + $len, $row, $col, 0x00, $len );
$GLOBALS['xls']['xls_data'] .= $value;
}
if ($colinc == 1) {
$GLOBALS['xls']['ccol']++;
}
return;
}
// Change position of row,col
function xlsChangePos( $newrow, $newcol ) {
$GLOBALS['xls']['crow'] = $newrow;
$GLOBALS['xls']['ccol'] = $newcol;
return;
}
// new line (first column, next row)
function xlsNewLine() {
$GLOBALS['xls']['ccol'] = 0;
$GLOBALS['xls']['crow']++;
return;
}
// send generated xls as stream file
function xlsSendFile() {
xlsEnd();
header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" );
header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" );
header ( "Cache-Control: no-cache, must-revalidate" );
header ( "Pragma: no-cache" );
header ( "Content-type: application/x-msexcel" );
header ( "Content-Disposition: attachment; filename=report.xls" );
header ( "Content-Description: PHP Generated XLS Data" );
print $GLOBALS['xls']['xls_data'];
}
function ExecuteQueryRTF() {
// the header(/footer) file you can usually fake up using MS Word and "save as.."
// choose rtf as export format for a layount, put some easy to recognize
// keywords in the document and search for "}}" and insert a line break after there.
// Mangle until satisfied and use in your query, substituting your data for your keywords.
// Be prepared for some amount of frustration while you work things out, and some
// surprises at how much supposedly private data is in there.... Go Go M$
$headerfile = "header1.rtf";
// $footerfile = "footer1.rtf";
// you don't want your password file acessible, do you ?
// comment out the next line if you set your footer file to something that's there.
unset($footerfile);
$res = mysql_db_query($GLOBALS['opt']['db'],$GLOBALS['qry']['queryfixed']);
if (!$res) {
pma_errpage ("Query select " . mysql_errno().": ".mysql_error()." Bad Query or DB error");
}
header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" );
header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" );
header ( "Cache-Control: no-cache, must-revalidate" );
header ( "Pragma: no-cache" );
header ( "Content-Type: application/rtf" );
header ( "Content-Disposition: attachment; filename=report.doc" );
header ( "Content-Description: PHP Generated Data" );
if (file_exists($headerfile)) {
$fhead = fopen($headerfile,"r");
fpassthru($fhead);
}
echo "Report for : ".$GLOBALS['auth_user']."\\tab \\tab \\tab \\tab " . date('Y-m-d') . "\\par }{";
while($row = mysql_fetch_row($res)) {
echo "\\par ";
while (list ($key, $val) = each ($row)) {
echo "$val\\tab ";
}
}
echo "\\par \\par --------------------- \\par End Report \\par }} ";
if (file_exists($footerfile)) {
$ffoot = fopen($footerfile,"r");
fpassthru($ffoot);
}
}
function ExecuteQueryXLS() {
$truncs = 0;
$res = mysql_db_query($GLOBALS['opt']['db'],$GLOBALS['qry']['queryfixed']);
if (!$res) {
pma_errpage ("Query select " . mysql_errno().": ".mysql_error()." Bad Query or DB error");
}
xlsStart();
xlsInsert( "Report for : ".$GLOBALS['auth_user']." ". date('Y-m-d'), 'text' );
xlsNewLine();
while($row = mysql_fetch_row($res)) {
while (list ($key, $val) = each ($row)) {
// this is slightly iffy, but it seems to work well
// make sure a number is a number
$dtype=doubleval($val);
$stype=strval($val);
if ($stype==strval($dtype)) {
xlsInsert($val,'number');
} else {
if (strlen($val) > 255 ) {
$val = substr($val, 1, 255);
$truncs++;
}
xlsInsert($val,'text');
}
}
xlsNewLine();
}
if ($truncs) {
xlsInsert($truncs . " Field(s) were truncated !!",'text');
xlsNewLine();
}
xlsSendFile();
}
function ExecuteQueryHTML() {
$res = mysql_db_query($GLOBALS['opt']['db'],$GLOBALS['qry']['queryfixed']);
if (!$res) {
pma_errpage ("Query select " . mysql_errno().": ".mysql_error()." Bad Query or DB error");
}
echo "<table border=\"1\" cellpadding=\"1\" cellspacing=\"0\" summary=\"'.$tb.'\">\n";
while($row = mysql_fetch_row($res)) {
echo "<tr>";
while (list ($key, $val) = each ($row)) {
echo "<td>$val</td>";
}
echo "</tr>\n";
}
echo "</table>\n</div>\n";
echo "</body>\n</html>\n";
}
function DisplayReportOptions() {
echo ("
<p/>
<a href=\"".$_SERVER['PHP_SELF']."?query=".$_GET['query']."&type=doc\">Download this query as RTF</a>
<a href=\"".$_SERVER['PHP_SELF']."?query=".$_GET['query']."&type=xls\">Download this query as Excel file</a>
<hr width=\"30%\" align=\"left\"/>
<p/>
");
}
function DisplayPermissionError() {
pma_errpage ("You are not allowed to execute this query");
}
//-----------------MAIN------------
// Make this user READ-ONLY !! MySQL host name, user name, password, database, and table to edit
include 'dbqryaccess.php';
include 'pma-debuginc.php';
$tb='queries';
$debug=0;
$auth = '999';
Dbconnect();
// we can get a bunch of fields that are used in queries, don't make a function of this
if ($auth >= 1) {
$auth_level = 0;
include 'auth.inc.php';
if ((!$auth_level) || ($auth_level < $auth)) {
Say_No_acces_then_die();
}
}
//--queryid is a web var
GetQuery($_GET['query']);
// any vars you want to use in the query eval should be defined here.
// user accounts file can be
$user = $GLOBALS['auth_user'];
$evstr = '$qry["queryfixed"]' . " = \"" . $qry['query'] . "\";";
// this eval is security sensitive, since it uses unchecked database results
// Smart users with access to the query table are capable of messing you up here.
// Apart from that the queries are not checked for DELETE etc.
// You should create a separate db user for this do-query.php perhaps, without
// change/delete permissions
// You will have to change the dbaccess.php include to use a separate include
// with those auth settings.
// make sure there are no "", in the strings, those are not escaped at this point
// and will cause your query to fail, use ' instead.
eval($evstr);
pma_debug ($qry['query']);
pma_debug ($qry["queryfixed"]);
if ($auth >= $qry['level'] ) {
if ($_GET['type']=="doc") {
ExecuteQueryRTF();
} else if ($_GET['type']=="xls") {
ExecuteQueryXLS();
} else {
MyHeader("Query ".$_GET['query']." executed on " . date('Y-m-d H:m'),$qry["querydesc"]);
debug_on();
DisplayReportOptions();
ExecuteQueryHTML();
}
} else {
MyHeader("Report access error");
DisplayPermissionError();
}
?>