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: