1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70: 71: 72: 73: 74: 75: 76: 77: 78: 79: 80: 81: 82: 83: 84: 85: 86: 87: 88: 89: 90: 91: 92: 93: 94: 95: 96: 97: 98: 99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 123: 124: 125: 126: 127: 128: 129: 130: 131: 132: 133: 134: 135: 136: 137: 138: 139: 140: 141: 142: 143: 144: 145: 146: 147: 148: 149: 150: 151: 152: 153: 154: 155: 156: 157: 158: 159: 160: 161: 162: 163: 164: 165: 166: 167: 168: 169: 170: 171: 172: 173: 174: 175: 176: 177: 178: 179: 180: 181: 182: 183: 184: 185: 186: 187: 188: 189: 190: 191: 192: 193: 194: 195: 196: 197: 198: 199: 200: 201: 202: 203: 204: 205: 206: 207: 208: 209: 210: 211: 212: 213: 214: 215: 216: 217: 218: 219: 220: 221: 222: 223: 224: 225: 226: 227: 228: 229: 230: 231: 232: 233: 234: 235: 236: 237: 238: 239: 240: 241: 242: 243: 244: 245: 246: 247: 248: 249: 250: 251: 252: 253: 254: 255: 256: 257: 258: 259: 260: 261: 262: 263: 264: 265: 266: 267: 268: 269: 270: 271: 272: 273: 274: 275: 276: 277: 278: 279: 280: 281: 282: 283: 284: 285: 286: 287: 288: 289: 290: 291: 292: 293: 294: 295: 296: 297: 298: 299: 300: 301: 302: 303: 304: 305: 306: 307: 308: 309: 310: 311: 312: 313: 314: 315: 316: 317: 318: 319: 320: 321: 322: 323: 324: 325: 326: 327: 328: 329: 330: 331: 332: 333: 334: 335: 336: 337: 338: 339: 340: 341: 342: 343: 344: 345: 346: 347: 348: 349: 350: 351: 352: 353: 354: 355: 356: 357: 358: 359: 360: 361: 362: 363: 364: 365: 366: 367: 368: 369: 370: 371: 372: 373: 374: 375: 376: 377: 378: 379: 380: 381: 382: 383: 384: 385: 386: 387: 388: 389: 390: 391: 392: 393: 394: 395: 396: 397: 398: 399: 400: 401: 402: 403: 404: 405: 406: 407: 408: 409: 410: 411: 412: 413: 414: 415: 416: 417: 418: 419: 420: 421: 422: 423: 424: 425: 426: 427: 428: 429: 430: 431: 432: 433: 434: 435: 436: 437: 438: 439: 440: 441: 442: 443: 444: 445: 446: 447: 448: 449: 450: 451: 452: 453: 454: 455: 456: 457: 458: 459: 460: 461: 462: 463: 464: 465: 466: 467: 468: 469: 470: 471: 472: 473: 474: 475: 476: 477: 478: 479: 480: 481: 482: 483: 484: 485: 486: 487: 488: 489: 490: 491: 492: 493: 494: 495: 496: 497: 498: 499: 500: 501: 502: 503: 504: 505: 506: 507: 508: 509: 510: 511: 512: 513: 514: 515: 516: 517: 518: 519: 520: 521: 522: 523: 524: 525: 526: 527: 528: 529: 530: 531: 532: 533: 534: 535: 536: 537: 538: 539: 540: 541: 542: 543: 544: 545: 546: 547: 548: 549: 550: 551: 552: 553: 554: 555: 556: 557: 558: 559: 560: 561: 562: 563: 564: 565: 566: 567: 568: 569: 570: 571: 572: 573: 574: 575: 576: 577: 578: 579: 580: 581: 582: 583: 584: 585: 586: 587: 588: 589: 590: 591: 592: 593: 594: 595: 596: 597: 598: 599: 600: 601: 602: 603: 604: 605: 606: 607: 608: 609: 610: 611: 612: 613: 614: 615: 616: 617: 618: 619: 620: 621: 622: 623: 624: 625: 626: 627: 628: 629: 630: 631: 632: 633: 634: 635: 636: 637: 638: 639: 640: 641: 642: 643: 644: 645: 646: 647: 648: 649: <?php
/**
* PostgreSQL query class and associated functions
*
* This subpackage provides some functions that are useful around database
* activity and a PgQuery class to simplify handling of database queries.
*
* The class is intended to be a very lightweight wrapper with no pretentions
* towards database independence, but it does include some features that have
* proved useful in developing and debugging web-based applications:
* - All queries are timed, and an expected time can be provided.
* - Parameters replaced into the SQL will be escaped correctly in order to
* minimise the chances of SQL injection errors.
* - Queries which fail, or which exceed their expected execution time, will
* be logged for potential further analysis.
* - Debug logging of queries may be enabled globally, or restricted to
* particular sets of queries.
* - Simple syntax for iterating through a result set.
*
* The database should be connected in a variable $dbconn before
* PgQuery.php is included. If not already connected, PgQuery will attempt to
* connect to the database, successively applying connection parameters from
* the array in $c->pg_connect.
*
* We will die if the database is not currently connected and we fail to find
* a working connection.
*
* @package awl
* @subpackage PgQuery
* @author Andrew McMillan <andrew@mcmillan.net.nz>
* @copyright Catalyst IT Ltd, Morphoss Ltd <http://www.morphoss.com/>
* @license http://gnu.org/copyleft/gpl.html GNU GPL v2 or later
*/
if ( ! function_exists('pg_Connect') ) {
echo <<<EOERRMSG
<html>
<head>
<title>PostgreSQL Support Not Present</title>
</head>
<body>
<h1>PostgreSQL Support Not Present</h1>
<h3>PHP is not configured to support the PostgreSQL database</h3>
<p>You need to ensure that the PostgreSQL support module is installed, and then to configure
it in your php.ini file by adding a line like this:</p>
<pre>
extension=pgsql.so
</pre>
</body>
</html>
EOERRMSG;
exit;
}
require_once("AWLUtilities.php");
/**
* Connect to the database defined in the $c->dbconn[] array
*/
function connect_configured_database() {
global $c, $dbconn;
if ( isset($dbconn) ) return;
/**
* Attempt to connect to the configured connect strings
*/
$dbconn = false;
dbg_error_log('pgquery', 'Attempting to connect to database');
if ( isset($c->pg_connect) && is_array($c->pg_connect) ) {
foreach( $c->pg_connect AS $k => $v ) {
if ( !$dbconn ) {
if ( $dbconn = ((isset($c->use_persistent) && $c->use_persistent) ? pg_pConnect($v) : pg_Connect($v) ) ) break;
}
}
}
if ( ! $dbconn ) {
echo <<<EOERRMSG
<html><head><title>Database Connection Failure</title></head><body>
<h1>Database Error</h1>
<h3>Could not connect to PostgreSQL database</h3>
</body>
</html>
EOERRMSG;
if ( isset($c->pg_connect) && is_array($c->pg_connect) ) {
dbg_error_log("ERROR", "Failed to connect to database" );
}
exit;
}
if ( isset($c->db_schema) && $c->db_schema != '' ) {
$result = pg_exec( $dbconn, "SET Search_path TO ".$c->db_schema.",public;" );
$row = pg_fetch_array($result, 0);
}
$result = pg_exec( $dbconn, "SELECT version()" );
$row = pg_fetch_array($result, 0);
$c->found_dbversion = preg_replace( '/^PostgreSQL (\d+\.\d+)\..*$/i', '$1', $row[0]);
}
/**
* @global resource $dbconn
* @name $dbconn
* The database connection.
*/
$dbconn = null;
if ( !function_exists('duration') ) {
/**
* A duration (in decimal seconds) between two times which are the result of calls to microtime()
*
* This simple function is used by the PgQuery class because the
* microtime function doesn't return a decimal time, so a simple
* subtraction is not sufficient.
*
* @param microtime $t1 start time
* @param microtime $t2 end time
* @return double difference
*/
function duration( $t1, $t2 ) {
list ( $ms1, $s1 ) = explode ( " ", $t1 ); // Format times - by spliting seconds and microseconds
list ( $ms2, $s2 ) = explode ( " ", $t2 );
$s1 = $s2 - $s1;
$s1 = $s1 + ( $ms2 -$ms1 );
return $s1; // Return duration of time
}
}
/**
* Quote the given string (depending on its type) so that it can be used
* safely in a PostgreSQL query without fear of SQL injection errors.
*
* Although this function effectively achieves a similar goal to the pg_escape_string()
* function, it is needed for older versions of PHP (< 4.2.0) and older versions
* of PostgreSQL (< 7.2.0), however. PgQuery does not attempt to use the newer
* pg_escape_string() function at this stage.
*
* This function is outside the PgQuery class because it is sometimes
* desirable to quote values for SQL command strings in circumstances
* where there is no benefit to using the class.
*
* @param mixed $str Data to be converted to a string suitable for including as a value in SQL.
* @return string NULL, TRUE, FALSE, a plain number, or the original string quoted and with ' and \ characters escaped
*/
function qpg($str = null) {
global $c;
switch (strtolower(gettype($str))) {
case 'null':
$rv = 'NULL';
break;
case 'integer':
case 'double' :
return $str;
case 'boolean':
$rv = $str ? 'TRUE' : 'FALSE';
break;
case 'string':
default:
$str = str_replace("'", "''", $str);
//PostgreSQL treats a backslash as an escape character.
$str = str_replace('\\', '\\\\', $str);
$rv = "'$str'";
if ( !isset($c->found_dbversion) || $c->found_dbversion > 8.0 ) $rv = 'E'.$rv;
}
return $rv;
}
/**
* Clean a string of many suspicious characters
*
* While this is a fairly aggressive approach, it applies in many circumstances
* where various strings should not contain things that might screw up (e.g.)
* filesystem semantics. Although not strictly a PgQuery function it's here
* for the time being until I invent a new "generally useful functions" include.
*
* @param string $unclean The dirty filthy string needing washing.
* @return string The pristine uncontaminated string we can safely use for Just About Anything(tm).
*/
function clean_string( $unclean, $type = 'full' ) {
if ( ! isset($unclean) ) return null;
if ( is_array($unclean) ) {
$result = array();
foreach( $unclean AS $k => $v ) {
$result[$k] = clean_string( $v, $type );
}
return $result;
}
if ( $type != 'basic' ) $cleaned = strtolower($unclean); else $cleaned = &$unclean;
$cleaned = preg_replace( "/['\"!\\\\()\[\]|*\/{}&%@~;:?<>]/", '', $cleaned ); //"// Stupid Bluefish Syntax highlighting...
dbg_error_log( "PgQuery", "clean_string: Cleaned string from <<%s>> to <<%s>>", $unclean, $cleaned );
return $cleaned;
}
/**
* Replaces PostgreSQL query with escaped parameters in preparation
* for execution.
*
* The function takes a variable number of arguments, the first is the
* SQL string, with replaceable '?' characters (a la DBI). The subsequent
* parameters being the values to replace into the SQL string.
*
* The values passed to the routine are analyzed for type, and quoted if
* they appear to need quoting. This can go wrong for (e.g.) NULL or
* other special SQL values which are not straightforwardly identifiable
* as needing quoting (or not). In such cases the parameter can be forced
* to be inserted unquoted by passing it as "array( 'plain' => $param )".
*
* This function is outside the PgQuery class because it is sometimes
* desirable to build SQL command strings in circumstances where there
* is no benefit to using the class.
*
* @param string The query string with replacable '?' characters.
* @param mixed The values to replace into the SQL string.
* @return The built query string
*/
function awl_replace_sql_args() {
$argc = func_num_args(); //number of arguments passed to the function
$qry = func_get_arg(0); //first argument
$args = func_get_args(); //all argument in an array
if ( is_array($qry) ) {
$qry = $args[0][0];
$args = $args[0];
$argc = count($args);
}
// building query string by replacing ? with
// escaped parameters
$parts = explode( '?', $qry );
$querystring = $parts[0];
$z = min( count($parts), $argc );
for( $i = 1; $i < $z; $i++ ) {
$arg = $args[$i];
if ( !isset($arg) ) {
$querystring .= 'NULL';
}
elseif ( is_array($arg) && $arg['plain'] != '' ) {
// We abuse this, but people should access it through the PgQuery::Plain($v) function
$querystring .= $arg['plain'];
}
else {
$querystring .= qpg($arg); //parameter
}
$querystring .= $parts[$i]; //extras eg. ","
}
if ( isset($parts[$z]) ) $querystring .= $parts[$z]; //puts last part on the end
return $querystring;
}
/**
* The PgQuery Class.
*
* This class builds and executes PostgreSQL Queries and traverses the
* set of results returned from the query.
*
* <b>Example usage</b>
* <code>
* $sql = "SELECT * FROM mytable WHERE mytype = ?";
* $qry = new PgQuery( $sql, $myunsanitisedtype );
* if ( $qry->Exec("typeselect", __line__, __file__ )
* && $qry->rows() > 0 )
* {
* while( $row = $qry->Fetch() ) {
* do_something_with($row);
* }
* }
* </code>
*
* @package awl
*/
class PgQuery
{
/**#@+
* @access private
*/
/**
* holds the connection to the database
* should be internal
* @var resource
*/
var $connection;
/**
* stores a query string
* should be read-only
* @var string
*/
var $querystring;
/**
* stores a resource result
* should be internal
* @var resource
*/
var $result;
/**
* number of current row
* should be internal, or at least read-only
* @var int
*/
var $rownum = -1;
/**
* Where we called this query from so we can find it in our code!
* Debugging may also be selectively enabled for a $location.
* @var string
*/
var $location;
/**
* The row most recently fetched by a call to Fetch() or FetchBackwards
* which will either be an array or an object (depending on the Fetch call).
* @var mixed
*/
var $object;
/**#@-*/
/**#@+
* @access public
*/
/**
* number of rows from pg_numrows - for fetching result
* should be read-only
* @var int
*/
var $rows;
/**
* The PostgreSQL error message, if the query fails.
* Should be read-only, although any successful Exec should clear it
* @var string
*/
var $errorstring;
/**
* Stores the query execution time - used to deal with long queries.
* should be read-only
* @var string
*/
var $execution_time;
/**
* How long the query should take before a warning is issued.
*
* This is writable, but a method to set it might be a better interface.
* The default is 0.3 seconds.
* @var double
*/
var $query_time_warning = 0.3;
/**#@-*/
/**
* Constructor
* @param string The query string with replacable '?' characters.
* @param mixed The values to replace into the SQL string.
* @return The PgQuery object
*/
function __construct() {
global $dbconn;
$this->result = 0;
$this->rows = 0;
$this->execution_time = 0;
$this->rownum = -1;
$this->connection = $dbconn;
$argc = func_num_args();
if ( 1 < $argc ) {
$this->querystring = awl_replace_sql_args( func_get_args() );
}
else {
// If we are only called with a single argument, we do
// nothing special with any question marks.
$this->querystring = func_get_arg(0);
}
return $this;
}
/**
* Use a different database connection for this query
* @param resource $new_connection The database connection to use.
*/
function SetConnection( $new_connection ) {
$this->connection = $new_connection;
}
/**
* Log error, optionally with file and line location of the caller.
*
* This function should not really be used outside of PgQuery. For a more
* useful generic logging interface consider calling dbg_error_log(...);
*
* @param string $locn A string identifying the calling location.
* @param string $tag A tag string, e.g. identifying the type of event.
* @param string $string The information to be logged.
* @param int $line The line number where the logged event occurred.
* @param string $file The file name where the logged event occurred.
*/
function _log_error( $locn, $tag, $string, $line = 0, $file = "") {
// replace more than one space with one space
$string = preg_replace('/\s+/', ' ', $string);
if ( ($tag == 'QF' || $tag == 'SQ') && ( $line != 0 && $file != "" ) ) {
dbg_error_log( "LOG-$locn", " Query: %s: Error in '%s' on line %d", $tag, $file, $line );
}
while( strlen( $string ) > 0 ) {
dbg_error_log( "LOG-$locn", " Query: %s: %s", $tag, substr( $string, 0, 240) );
$string = substr( "$string", 240 );
}
}
/**
* Provide a rows() method for forward compatibility with AwlQuery.
*/
function rows() {
return $this->rows;
}
/**
* Quote the given string so it can be safely used within string delimiters
* in a query.
*
* @see qpg()
* which is where this is really done.
*
* @param mixed $str Data to be converted to a string suitable for including as a value in SQL.
* @return string NULL, TRUE, FALSE, a plain number, or the original string quoted and with ' and \ characters escaped
*/
function quote($str = null) {
return qpg($str);
}
/**
* Convert a string which has already been quoted and escaped for PostgreSQL
* into a magic array so that it will be inserted unmodified into the SQL
* string. Use with care!
*
* @param string $field The value which has alread been quoted and escaped.
* @return array An array with the value associated with a key of 'plain'
*/
function Plain( $field ) {
// Abuse the array type to extend our ability to avoid \\ and ' replacement
$rv = array( 'plain' => $field );
return $rv;
}
/**
* Execute the query, logging any debugging.
*
* <b>Example</b>
* So that you can nicely enable/disable the queries for a particular class, you
* could use some of PHPs magic constants in your call.
* <code>
* $qry->Exec(__CLASS__, __LINE__, __FILE__);
* </code>
*
*
* @param string $location The name of the location for enabling debugging or just
* to help our children find the source of a problem.
* @param int $line The line number where Exec was called
* @param string $file The file where Exec was called
* @return resource The actual result of the query (FWIW)
*/
function Exec( $location = '', $line = 0, $file = '' ) {
global $debuggroups, $c, $dbconn;
if ( !isset($this->connection) ) {
if ( !isset($dbconn) ) {
connect_configured_database();
}
$this->connection = $dbconn;
}
$this->location = trim($location);
if ( $this->location == "" ) $this->location = substr($_SERVER['PHP_SELF'],1);
if ( isset($debuggroups['querystring']) || isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) {
$this->_log_error( $this->location, 'DBGQ', $this->querystring, $line, $file );
}
$t1 = microtime(); // get start time
$this->result = @pg_exec( $this->connection, $this->querystring ); // execute the query
$this->rows = ($this->result ? pg_numrows($this->result) : -1); // number of rows returned
$t2 = microtime(); // get end time
$i_took = duration( $t1, $t2 ); // calculate difference
$c->total_query_time += $i_took;
$this->execution_time = sprintf( "%2.06lf", $i_took);
if ( !$this->result ) {
// query simply failed
$this->errorstring = @pg_errormessage(); // returns database error message
$this->_log_error( $this->location, 'QF', $this->querystring, $line, $file );
$this->_log_error( $this->location, 'QF', $this->errorstring, $line, $file );
}
elseif ( $this->execution_time > $this->query_time_warning ) {
// if execution time is too long
$this->_log_error( $this->location, 'SQ', "Took: $this->execution_time for $this->querystring", $line, $file ); // SQ == Slow Query :-)
}
elseif ( isset($debuggroups[$this->location]) || isset($c->dbg[strtolower($this->location)]) || isset($c->dbg['ALL']) ) {
// query successful, but we're debugging and want to know how long it took anyway
$this->_log_error( $this->location, 'DBGQ', "Took: $this->execution_time for $this->querystring to find $this->rows rows.", $line, $file );
}
return $this->result;
}
/**
* Fetch the next row from the query results
* @param boolean $as_array True if thing to be returned is array
* @return mixed query row
*/
function Fetch($as_array = false) {
global $c, $debuggroups;
if ( ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 2 )
|| (isset($c) && is_object($c) && ( isset($c->dbg[strtolower($this->location)]) && isset($c->dbg[strtolower($this->location)]) )
|| isset($c->dbg['ALL']) ) ) {
$this->_log_error( $this->location, "Fetch", "$this->result Rows: $this->rows, Rownum: $this->rownum");
}
if ( ! $this->result ) return false; // no results
if ( ($this->rownum + 1) >= $this->rows ) return false; // reached the end of results
$this->rownum++;
if ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 1 ) {
$this->_log_error( $this->location, "Fetch", "Fetching row $this->rownum" );
}
if ( $as_array )
{
$this->object = pg_fetch_array($this->result, $this->rownum);
}
else
{
$this->object = pg_fetch_object($this->result, $this->rownum);
}
return $this->object;
}
/**
* Set row counter back one
*
* In the case that you may like to fetch the same row twice, for example
* if your SQL returns some columns that are the same for each row, and you
* want to display them cleanly before displaying the other data repeatedly
* for each row.
*
* <b>Example</b>
* <code>
* $master_row = $qry->Fetch();
* $qry->UnFetch();
* do_something_first($master_row);
* while( $row = $qry->Fetch() ) {
* do_something_repeatedly($row);
* }
* </code>
*/
function UnFetch() {
global $debuggroups;
$this->rownum--;
if ( $this->rownum < -1 ) $this->rownum = -1;
}
/**
* Fetch backwards from the result resource
* @param boolean $as_array True if thing to be returned is array (default: <b>False</b>
* @return mixed query row
*/
function FetchBackwards($as_array = false) {
global $debuggroups;
if ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 2 ) {
$this->_log_error( $this->location, "FetchBackwards", "$this->result Rows: $this->rows, Rownum: $this->rownum");
}
if ( ! $this->result ) return false;
if ( ($this->rownum - 1) == -1 ) return false;
if ( $this->rownum == -1 ) $this->rownum = $this->rows;
$this->rownum--;
if ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 1 ) {
$this->_log_error( $this->location, "Fetch", "Fetching row $this->rownum" );
}
if ( $as_array )
{
$this->object = pg_fetch_array($this->result, $this->rownum);
}
else
{
$this->object = pg_fetch_object($this->result, $this->rownum);
}
return $this->object;
}
/**
* Build an option list from the query.
* @param string $current Default selection of drop down box (optional)
* @param string $location for debugging purposes
* @param array $parameters an array further parameters, including 'maxwidth' => 20 to set a maximum width
* @return string Select box HTML
*/
function BuildOptionList( $current = '', $location = 'options', $parameters = false ) {
global $debuggroups;
$result = '';
$translate = false;
if ( isset($maxwidth) ) unset($maxwidth);
if ( is_array($parameters) ) {
if ( isset($parameters['maxwidth']) ) $maxwidth = max(4,intval($parameters['maxwidth']));
if ( isset($parameters['translate']) ) $translate = true;
}
// The query may already have been executed
if ( $this->rows > 0 || $this->Exec($location) ) {
$this->rownum = -1;
while( $row = $this->Fetch(true) )
{
if (is_array($current)) {
$selected = ( ( in_array($row[0],$current,true) || in_array($row[1],$current,true)) ? ' selected="selected"' : '' );
}
else {
$selected = ( ( "$row[0]" == "$current" || "$row[1]" == "$current" ) ? ' selected="selected"' : '' );
}
$display_value = $row[1];
if ( isset($translate) ) $display_value = translate( $display_value );
if ( isset($maxwidth) ) $display_value = substr( $display_value, 0, $maxwidth);
$nextrow = "<option value=\"".htmlspecialchars($row[0])."\"$selected>".htmlspecialchars($display_value)."</option>";
$result .= $nextrow;
}
}
return $result;
}
}