Location: PHPKode > scripts > phpMyAccess > phpmyaccess-1.5.3/phpmyaccess/do-query.php
<?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>&nbsp;&nbsp;
   <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();
}


?>
Return current item: phpMyAccess