Overview

Packages

  • awl
    • AuthPlugin
    • AwlDatabase
    • Browser
    • classEditor
    • DataEntry
    • DataUpdate
    • EMail
    • iCalendar
    • MenuSet
    • PgQuery
    • Session
    • Translation
    • User
    • Utilities
    • Validation
    • vCalendar
    • vComponent
    • XMLDocument
    • XMLElement
  • None
  • PHP

Classes

  • PgQuery

Functions

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