1<?php 2/** 3* @package awl 4* @subpackage AwlDatabase 5* @author Andrew McMillan <andrew@morphoss.com> 6* @copyright Morphoss Ltd 7* @license http://gnu.org/copyleft/gpl.html GNU GPL v3 or later 8* @compatibility Requires PHP 5.1 or later 9*/ 10 11require_once('AwlDatabase.php'); 12 13/** 14* Database query class and associated functions 15* 16* This subpackage provides some functions that are useful around database 17* activity and an AwlQuery class to simplify handling of database queries. 18* 19* The class is intended to be a very lightweight wrapper with no pretentions 20* towards database independence, but it does include some features that have 21* proved useful in developing and debugging web-based applications: 22* - All queries are timed, and an expected time can be provided. 23* - Parameters replaced into the SQL will be escaped correctly in order to 24* minimise the chances of SQL injection errors. 25* - Queries which fail, or which exceed their expected execution time, will 26* be logged for potential further analysis. 27* - Debug logging of queries may be enabled globally, or restricted to 28* particular sets of queries. 29* - Simple syntax for iterating through a result set. 30* 31* This class is intended as a transitional mechanism for moving from the 32* PostgreSQL-specific Pg Query class to something which uses PDO in a more 33* replaceable manner. 34* 35*/ 36 37/** 38* Connect to the database defined in the $c->db_connect[] (or $c->pg_connect) arrays 39*/ 40function _awl_connect_configured_database() { 41 global $c, $_awl_dbconn; 42 43 /** 44 * Attempt to connect to the configured connect strings 45 */ 46 $_awl_dbconn = false; 47 48 if ( isset($c->db_connect) ) { 49 $connection_strings = $c->db_connect; 50 } 51 elseif ( isset($c->pg_connect) ) { 52 $connection_strings = $c->pg_connect; 53 } 54 55 foreach( $connection_strings AS $k => $v ) { 56 $dbuser = null; 57 $dbpass = null; 58 if ( is_array($v) ) { 59 $dsn = $v['dsn']; 60 if ( isset($v['dbuser']) ) $dbuser = $v['dbuser']; 61 if ( isset($v['dbpass']) ) $dbpass = $v['dbpass']; 62 } 63 elseif ( preg_match( '/^(\S+:)?(.*)( user=(\S+))?( password=(\S+))?$/', $v, $matches ) ) { 64 $dsn = $matches[2]; 65 if ( isset($matches[1]) && $matches[1] != '' ) { 66 $dsn = $matches[1] . $dsn; 67 } 68 else { 69 $dsn = 'pgsql:' . $dsn; 70 } 71 if ( isset($matches[4]) && $matches[4] != '' ) $dbuser = $matches[4]; 72 if ( isset($matches[6]) && $matches[6] != '' ) $dbpass = $matches[6]; 73 } 74 if ( $_awl_dbconn = new AwlDatabase( $dsn, $dbuser, $dbpass, (isset($c->use_persistent) && $c->use_persistent ? array(PDO::ATTR_PERSISTENT => true) : null) ) ) break; 75 } 76 77 if ( ! $_awl_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 database</h3> 82 </body> 83 </html> 84EOERRMSG; 85 exit; 86 } 87 88 if ( isset($c->db_schema) && $c->db_schema != '' ) { 89 $_awl_dbconn->SetSearchPath( $c->db_schema . ',public' ); 90 } 91 92 $c->_awl_dbversion = $_awl_dbconn->GetVersion(); 93} 94 95 96/** 97* The AwlQuery Class. 98* 99* This class builds and executes SQL Queries and traverses the 100* set of results returned from the query. 101* 102* <b>Example usage</b> 103* <code> 104* $sql = "SELECT * FROM mytable WHERE mytype = ?"; 105* $qry = new AwlQuery( $sql, $myunsanitisedtype ); 106* if ( $qry->Exec("typeselect", __line__, __file__ ) 107* && $qry->rows > 0 ) 108* { 109* while( $row = $qry->Fetch() ) { 110* do_something_with($row); 111* } 112* } 113* </code> 114* 115* @package awl 116*/ 117class AwlQuery 118{ 119 /**#@+ 120 * @access private 121 */ 122 /** 123 * Our database connection, normally copied from a global one 124 * @var resource 125 */ 126 protected $connection; 127 128 /** 129 * The original query string 130 * @var string 131 */ 132 protected $querystring; 133 134 /** 135 * The actual query string, after we've replaced parameters in it 136 * @var string 137 */ 138 protected $bound_querystring; 139 140 /** 141 * The current array of bound parameters 142 * @var array 143 */ 144 protected $bound_parameters; 145 146 /** 147 * The PDO statement handle, or null if we don't have one yet. 148 * @var string 149 */ 150 protected $sth; 151 152 /** 153 * Result of the last execution 154 * @var resource 155 */ 156 protected $result; 157 158 /** 159 * number of current row - use accessor to get/set 160 * @var int 161 */ 162 protected $rownum = null; 163 164 /** 165 * number of rows from pg_numrows - use accessor to get value 166 * @var int 167 */ 168 protected $rows; 169 170 /** 171 * The Database error information, if the query fails. 172 * @var string 173 */ 174 protected $error_info; 175 176 /** 177 * Stores the query execution time - used to deal with long queries. 178 * should be read-only 179 * @var string 180 */ 181 protected $execution_time; 182 183 /**#@-*/ 184 185 /**#@+ 186 * @access public 187 */ 188 /** 189 * Where we called this query from so we can find it in our code! 190 * Debugging may also be selectively enabled for a $location. 191 * @var string 192 */ 193 public $location; 194 195 /** 196 * How long the query should take before a warning is issued. 197 * 198 * This is writable, but a method to set it might be a better interface. 199 * The default is 5 seconds, which can be overridden by setting a value 200 * for $c->default_query_warning_threshold 201 * @var double 202 */ 203 public $query_time_warning = 5; 204 /**#@-*/ 205 206 207 /** 208 * Constructor 209 * @param string The query string in PDO syntax with replacable '?' characters or bindable parameters. 210 * @param mixed The values to replace into the SQL string. 211 * @return The AwlQuery object 212 */ 213 function __construct() { 214 global $_awl_dbconn, $c; 215 $this->rows = null; 216 $this->execution_time = 0; 217 $this->error_info = null; 218 if ( isset($c->default_query_warning_threshold) ) { 219 $this->query_time_warning = $c->default_query_warning_threshold; 220 } 221 222 $this->rownum = -1; 223 if ( isset($_awl_dbconn) ) $this->connection = $_awl_dbconn; 224 else $this->connection = null; 225 226 $argc = func_num_args(); 227 $args = func_get_args(); 228 229 $this->querystring = array_shift($args); 230 if ( 1 < $argc ) { 231 if ( is_array($args[0]) ) 232 $this->bound_parameters = $args[0]; 233 else 234 $this->bound_parameters = $args; 235// print_r( $this->bound_parameters ); 236 } 237 238 return $this; 239 } 240 241 242 /** 243 * Use a different database connection for this query 244 * @param resource $new_connection The database connection to use. 245 */ 246 function SetConnection( $new_connection, $options = null ) { 247 if ( is_string($new_connection) || is_array($new_connection) ) { 248 $dbuser = null; 249 $dbpass = null; 250 if ( is_array($new_connection) ) { 251 $dsn = $new_connection['dsn']; 252 if ( isset($new_connection['dbuser']) ) $dbuser = $new_connection['dbuser']; 253 if ( isset($new_connection['dbpass']) ) $dbpass = $new_connection['dbpass']; 254 } 255 elseif ( preg_match( '/^(\S+:)?(.*)( user=(\S+))?( password=(\S+))?$/', $new_connection, $matches ) ) { 256 $dsn = $matches[2]; 257 if ( isset($matches[1]) && $matches[1] != '' ) { 258 $dsn = $matches[1] . $dsn; 259 } 260 else { 261 $dsn = 'pgsql:' . $dsn; 262 } 263 if ( isset($matches[4]) && $matches[4] != '' ) $dbuser = $matches[4]; 264 if ( isset($matches[6]) && $matches[6] != '' ) $dbpass = $matches[6]; 265 } 266 if ( ! $new_connection = new AwlDatabase( $dsn, $dbuser, $dbpass, $options ) ) return; 267 } 268 $this->connection = $new_connection; 269 return $new_connection; 270 } 271 272 273 274 /** 275 * Get the current database connection for this query 276 */ 277 function GetConnection() { 278 return $this->connection; 279 } 280 281 282 /** 283 * Log query, optionally with file and line location of the caller. 284 * 285 * This function should not really be used outside of AwlQuery. For a more 286 * useful generic logging interface consider calling dbg_error_log(...); 287 * 288 * @param string $locn A string identifying the calling location. 289 * @param string $tag A tag string, e.g. identifying the type of event. 290 * @param string $string The information to be logged. 291 * @param int $line The line number where the logged event occurred. 292 * @param string $file The file name where the logged event occurred. 293 */ 294 function _log_query( $locn, $tag, $string, $line = 0, $file = "") { 295 // replace more than one space with one space 296 $string = preg_replace('/\s+/', ' ', $string); 297 298 if ( ($tag == 'QF' || $tag == 'SQ') && ( $line != 0 && $file != "" ) ) { 299 dbg_error_log( "LOG-$locn", " Query: %s: %s in '%s' on line %d", ($tag == 'QF' ? 'Error' : 'Possible slow query'), $tag, $file, $line ); 300 } 301 302 while( strlen( $string ) > 0 ) { 303 dbg_error_log( "LOG-$locn", " Query: %s: %s", $tag, substr( $string, 0, 240) ); 304 $string = substr( "$string", 240 ); 305 } 306 } 307 308 309 /** 310 * Quote the given string so it can be safely used within string delimiters 311 * in a query. To be avoided, in general. 312 * 313 * @param mixed $str Data to be converted to a string suitable for including as a value in SQL. 314 * @return string NULL, TRUE, FALSE, a plain number, or the original string quoted and with ' and \ characters escaped 315 */ 316 public static function quote($str = null) { 317 global $_awl_dbconn; 318 if ( !isset($_awl_dbconn) ) { 319 _awl_connect_configured_database(); 320 } 321 return $_awl_dbconn->Quote($str); 322 } 323 324 325 /** 326 * Bind some parameters. This can be called in three ways: 327 * 1) As Bind(':key','value), when using named parameters 328 * 2) As Bind('value'), when using ? placeholders 329 * 3) As Bind(array()), to overwrite the existing bound parameters. The array may 330 * be ':name' => 'value' pairs or ordinal values, depending on whether the SQL 331 * is using ':name' or '?' style placeholders. 332 * 333 * @param mixed $args See details above. 334 */ 335 function Bind() { 336 $argc = func_num_args(); 337 $args = func_get_args(); 338 339 if ( $argc == 1 ) { 340 if ( gettype($args[0]) == 'array' ) { 341 $this->bound_parameters = $args[0]; 342 } 343 else { 344 $this->bound_parameters[] = $args[0]; 345 } 346 } 347 else { 348 $this->bound_parameters[$args[0]] = $args[1]; 349 } 350 } 351 352 353 /** 354 * Tell the database to prepare the query that we will execute 355 */ 356 function Prepare() { 357 global $c; 358 359 if ( isset($this->sth) ) return; // Already prepared 360 if ( isset($c->expand_pdo_parameters) && $c->expand_pdo_parameters ) return; // No-op if we're expanding internally 361 362 if ( !isset($this->connection) ) { 363 _awl_connect_configured_database(); 364 $this->connection = $GLOBALS['_awl_dbconn']; 365 } 366 367 $this->sth = $this->connection->prepare( $this->querystring ); 368 369 if ( ! $this->sth ) { 370 $this->error_info = $this->connection->errorInfo(); 371 } 372 else $this->error_info = null; 373 } 374 375 /** 376 * Tell the database to execute the query 377 */ 378 function Execute() { 379 global $c; 380 381 if ( !isset($this->connection) ) { 382 _awl_connect_configured_database(); 383 $this->connection = $GLOBALS['_awl_dbconn']; 384 } 385 if ( !is_object($this->connection) ) throw new Exception('Database not connected.'); 386 387 if ( isset($c->expand_pdo_parameters) && $c->expand_pdo_parameters ) { 388 $this->bound_querystring = $this->querystring; 389 if ( isset($this->bound_parameters) ) { 390 $this->bound_querystring = $this->connection->ReplaceParameters($this->querystring,$this->bound_parameters); 391// printf( "\n=============================================================== OQ\n%s\n", $this->querystring); 392// printf( "\n=============================================================== QQ\n%s\n", $this->bound_querystring); 393// print_r( $this->bound_parameters ); 394 } 395 $t1 = microtime(true); // get start time 396 $execute_result = $this->sth = $this->connection->query($this->bound_querystring); 397 } 398 else { 399 $t1 = microtime(true); // get start time 400 $execute_result = $this->sth = $this->connection->prepare($this->querystring); 401 if ( $this->sth ) $execute_result = $this->sth->execute($this->bound_parameters); 402// printf( "\n=============================================================== OQ\n%s\n", $this->querystring); 403// print_r( $this->bound_parameters ); 404 } 405 $this->bound_querystring = null; 406 407 if ( $execute_result === false ) { 408 $this->error_info = $this->connection->errorInfo(); 409 return false; 410 } 411 $this->rows = $this->sth->rowCount(); 412 413 $i_took = microtime(true) - $t1; 414 $c->total_query_time += $i_took; 415 $this->execution_time = sprintf( "%2.06lf", $i_took); 416 417 $this->error_info = null; 418 return true; 419 } 420 421 422 /** 423 * Return the query string we are planning to execute 424 */ 425 function QueryString() { 426 return $this->querystring; 427 } 428 429 430 /** 431 * Return the parameters we are planning to substitute into the query string 432 */ 433 function Parameters() { 434 return $this->bound_parameters; 435 } 436 437 438 /** 439 * Return the count of rows retrieved/affected 440 */ 441 function rows() { 442 return $this->rows; 443 } 444 445 446 /** 447 * Return the current rownum in the retrieved set 448 */ 449 function rownum() { 450 return $this->rownum; 451 } 452 453 454 /** 455 * Returns the current state of a transaction, indicating if we have begun a transaction, whether the transaction 456 * has failed, or if we are not in a transaction. 457 * @return int 0 = not started, 1 = in progress, -1 = error pending rollback/commit 458 */ 459 function TransactionState() { 460 global $_awl_dbconn; 461 if ( !isset($this->connection) ) { 462 if ( !isset($_awl_dbconn) ) _awl_connect_configured_database(); 463 $this->connection = $_awl_dbconn; 464 } 465 return $this->connection->TransactionState(); 466 } 467 468 469 /** 470 * Wrap the parent DB class Begin() so we can $qry->Begin() sometime before we $qry->Exec() 471 */ 472 public function Begin() { 473 global $_awl_dbconn; 474 if ( !isset($this->connection) ) { 475 if ( !isset($_awl_dbconn) ) _awl_connect_configured_database(); 476 $this->connection = $_awl_dbconn; 477 } 478 return $this->connection->Begin(); 479 } 480 481 482 /** 483 * Wrap the parent DB class Commit() so we can $qry->Commit() sometime after we $qry->Exec() 484 */ 485 public function Commit() { 486 if ( !isset($this->connection) ) { 487 trigger_error("Cannot commit a transaction without an active statement.", E_USER_ERROR); 488 } 489 return $this->connection->Commit(); 490 } 491 492 493 /** 494 * Wrap the parent DB class Rollback() so we can $qry->Rollback() sometime after we $qry->Exec() 495 */ 496 public function Rollback() { 497 if ( !isset($this->connection) ) { 498 trigger_error("Cannot rollback a transaction without an active statement.", E_USER_ERROR); 499 } 500 return $this->connection->Rollback(); 501 } 502 503 504 /** 505 * Simple SetSql() class which will reset the object with the querystring from the first argument. 506 * @param string The query string in PDO syntax with replacable '?' characters or bindable parameters. 507 */ 508 public function SetSql( $sql ) { 509 $this->rows = null; 510 $this->execution_time = 0; 511 $this->error_info = null; 512 $this->rownum = -1; 513 $this->bound_parameters = null; 514 $this->bound_querystring = null; 515 $this->sth = null; 516 517 $this->querystring = $sql; 518 } 519 520 521 /** 522 * Simple QDo() class which will re-use this query for whatever was passed in, and execute it 523 * returning the result of the Exec() call. We can't call it Do() since that's a reserved word... 524 * @param string The query string in PDO syntax with replacable '?' characters or bindable parameters. 525 * @param mixed The values to replace into the SQL string. 526 * @return boolean Success (true) or Failure (false) 527 */ 528 public function QDo() { 529 $argc = func_num_args(); 530 $args = func_get_args(); 531 532 $this->SetSql( array_shift($args) ); 533 if ( 1 < $argc ) { 534 if ( is_array($args[0]) ) 535 $this->bound_parameters = $args[0]; 536 else 537 $this->bound_parameters = $args; 538 } 539 540 return $this->Exec(); 541 } 542 543 544 /** 545 * Execute the query, logging any debugging. 546 * 547 * <b>Example</b> 548 * So that you can nicely enable/disable the queries for a particular class, you 549 * could use some of PHPs magic constants in your call. 550 * <code> 551 * $qry->Exec(__CLASS__, __LINE__, __FILE__); 552 * </code> 553 * 554 * 555 * @param string $location The name of the location for enabling debugging or just 556 * to help our children find the source of a problem. 557 * @param int $line The line number where Exec was called 558 * @param string $file The file where Exec was called 559 * @return boolean Success (true) or Failure (false) 560 */ 561 function Exec( $location = null, $line = null, $file = null ) { 562 global $c; 563 if ( isset($location) ) $this->location = trim($location); 564 if ( !isset($this->location) || $this->location == "" ) $this->location = substr($_SERVER['PHP_SELF'],1); 565 566 if ( isset($line) ) $this->location_line = intval($line); 567 else if ( isset($this->location_line) ) $line = $this->location_line; 568 569 if ( isset($file) ) $this->location_file = trim($file); 570 else if ( isset($this->location_file) ) $file = $this->location_file; 571 572 if ( isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) { 573 $this->_log_query( $this->location, 'DBGQ', $this->querystring, $line, $file ); 574 if ( isset($this->bound_parameters) && !isset($this->sth) ) { 575 foreach( $this->bound_parameters AS $k => $v ) { 576 $this->_log_query( $this->location, 'DBGQ', sprintf(' "%s" => "%s"', $k, $v), $line, $file ); 577 } 578 } 579 } 580 581 if ( isset($this->bound_parameters) ) { 582 $this->Prepare(); 583 } 584 585 $success = $this->Execute(); 586 587 if ( ! $success ) { 588 // query failed 589 $this->errorstring = sprintf( 'SQL error "%s" - %s"', $this->error_info[0], (isset($this->error_info[2]) ? $this->error_info[2] : '')); 590 if ( isset($c->dbg['print_query_errors']) && $c->dbg['print_query_errors'] ) { 591 printf( "\n=====================\n" ); 592 printf( "%s[%d] QF: %s\n", $file, $line, $this->errorstring); 593 printf( "%s\n", $this->querystring ); 594 if ( isset($this->bound_parameters) ) { 595 foreach( $this->bound_parameters AS $k => $v ) { 596 printf( " %-18s \t=> '%s'\n", "'$k'", $v ); 597 } 598 } 599 printf( ".....................\n" ); 600 } 601 $this->_log_query( $this->location, 'QF', $this->errorstring, $line, $file ); 602 $this->_log_query( $this->location, 'QF', $this->querystring, $line, $file ); 603 if ( isset($this->bound_parameters) && ! ( isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) ) { 604 foreach( $this->bound_parameters AS $k => $v ) { 605 dbg_error_log( 'LOG-'.$this->location, ' Query: QF: "%s" => "%s"', $k, $v); 606 } 607 } 608 } 609 elseif ( $this->execution_time > $this->query_time_warning ) { 610 // if execution time is too long 611 $this->_log_query( $this->location, 'SQ', "Took: $this->execution_time for $this->querystring", $line, $file ); // SQ == Slow Query :-) 612 } 613 elseif ( isset($c->dbg['querystring']) || isset($c->dbg[strtolower($this->location)]) || isset($c->dbg['ALL']) ) { 614 // query successful, but we're debugging and want to know how long it took anyway 615 $this->_log_query( $this->location, 'DBGQ', "Took: $this->execution_time to find $this->rows rows.", $line, $file ); 616 } 617 618 return $success; 619 } 620 621 622 /** 623 * Fetch the next row from the query results 624 * @param boolean $as_array True if thing to be returned is array 625 * @return mixed query row 626 */ 627 function Fetch($as_array = false) { 628 629 if ( ! $this->sth || $this->rows == 0 ) return false; // no results 630 if ( $this->rownum == null ) $this->rownum = -1; 631 if ( ($this->rownum + 1) >= $this->rows ) return false; // reached the end of results 632 633 $this->rownum++; 634 $row = $this->sth->fetch( ($as_array ? PDO::FETCH_NUM : PDO::FETCH_OBJ) ); 635 636 return $row; 637 } 638 639 640 /** 641 * Get any error information from the last query 642 */ 643 function getErrorInfo() { 644 return $this->error_info; 645 } 646 647 648 /** 649 * Set the slow query threshold for this query (and subsequent queries using this object). The 650 * overall default is $c->slow_query_threshold, or 5 seconds if that is not set. 651 * @param double $new_threshold The new threshold for slow queries to be logged 652 * @return double The old threshold. 653 */ 654 function SetSlowQueryThreshold( $new_threshold ) { 655 $old = $this->query_time_warning; 656 $this->query_time_warning = $new_threshold; 657 return $oldval; 658 } 659 660} 661 662