1<?php 2# MantisBT - A PHP based bugtracking system 3 4# MantisBT is free software: you can redistribute it and/or modify 5# it under the terms of the GNU General Public License as published by 6# the Free Software Foundation, either version 2 of the License, or 7# (at your option) any later version. 8# 9# MantisBT is distributed in the hope that it will be useful, 10# but WITHOUT ANY WARRANTY; without even the implied warranty of 11# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12# GNU General Public License for more details. 13# 14# You should have received a copy of the GNU General Public License 15# along with MantisBT. If not, see <http://www.gnu.org/licenses/>. 16 17/** 18 * DbQuery class. 19 * @copyright Copyright 2017 MantisBT Team - mantisbt-dev@lists.sourceforge.net 20 * @link http://www.mantisbt.org 21 * @package MantisBT 22 * @subpackage classes 23 * 24 * @uses config_api.php 25 * @uses constant_inc.php 26 * @uses database_api.php 27 * @uses logging_api.php 28 */ 29 30require_api( 'config_api.php' ); 31require_api( 'constant_inc.php' ); 32require_api( 'database_api.php' ); 33require_api( 'logging_api.php' ); 34 35 36/** 37 * Database Query abstraction class. 38 * 39 * Allows building a query and fetching data, providing compatible functionality 40 * for all supported databases. Hides the underlaying details from the ADOdb layer. 41 * 42 * Supports inline parameters with ":label" syntax, and anonymous parameters 43 * through the param() method. 44 * Supports binding arrays which will be later expanded to individual parameters. 45 * 46 * The internal processing steps for query string are: 47 * 1) $query_string: stores the user input string, containing parameter tokens for all 48 * supported formats: 49 * - ":{string}" For labeled parameters. Binded values are stored in $query_bind_array 50 * - "${int}" For anonymous parameters. Binded values are stored in $query_autobind_array 51 * - "${string}{int}" For special constructs, eg: $in0 for late binding IN clauses 52 * 2) $expanded_query_string: stores the query string after expansion of special constructs 53 * into standard "${int}" parameters 54 * 3) $db_query_string: stores the query string after all parameters have been renamed, 55 * reordered and converted to the specific database parameter syntax expected by ADOdb. 56 * Binded values are stored in $db_param_array 57 * 58 * The steps for query string processing are performed by: 59 * - process_expand_params(): converts query string (1) into (2) 60 * - process_bind_params(): converts query string(2) into (3) 61 * - process_sql_syntax(): modifies query string (3) performing some general and database 62 * specific modifications. 63 * 64 * Execution of the query is supported by: 65 * - db_execute(): performs the low level, actual execution from ADOdb 66 * - execute(): performs all query processing steps and then calls db_execute(). 67 */ 68 69class DbQuery { 70 71 /** 72 * Stores the user input for query string. 73 * @var string 74 */ 75 protected $query_string; 76 77 /** 78 * Stores the query string with "late binding" placeholders expanded into final 79 * sql syntax and parameters. 80 * @var string 81 */ 82 protected $expanded_query_string = null; 83 84 /** 85 * Array of values binded to labeled parameters, indexed by label names. 86 * @var array 87 */ 88 protected $query_bind_array; 89 90 /** 91 * Array of values binded to anonymous parameters, indexed by numerical index. 92 * @var array 93 */ 94 protected $query_autobind_array = array(); 95 96 /** 97 * Counter for assigning numerical indexes to anonymous parameters. 98 * @var integer 99 */ 100 protected $query_autobind_count = 0; 101 102 /** 103 * Stores the final query string to be passed to ADOdb. 104 * @var string 105 */ 106 protected $db_query_string; 107 108 /** 109 * Array of values for parameters included in the final query string for ADOdb, 110 * indexed as needed for the ADOdb driver 111 * @var array 112 */ 113 protected $db_param_array; 114 115 /** 116 * Stores the adodb result set for the query after it has been executed, or false if the query failed. 117 * @var IteratorAggregate|boolean 118 */ 119 protected $db_result = null; 120 121 /** 122 * Stores execution time of the query (microseconds). 123 * @var integer 124 */ 125 protected $db_query_time = null; 126 127 /** 128 * Stores the latest fetched array fromthe result set. 129 * @var array 130 */ 131 protected $current_row = null; 132 133 /** 134 * Stores row limit value for query execution. 135 * Number of rows to be returned from the query result 136 * Use -1 to disable this option. 137 * @var integer 138 */ 139 protected $select_limit = -1; 140 141 /** 142 * Stores the offset value for query execution. 143 * The resultset starts at this position from the query result 144 * Use -1 to disable this option. 145 * @var integer 146 */ 147 protected $select_offset = -1; 148 149 /** 150 * Array to manage late binding for IN constructs 151 * @var array 152 */ 153 protected $late_binding_in_clause = array(); 154 155 protected static $oracle_in_limit = 1000; # this could be a constant 156 157 158 /** 159 * Construct a new query object. 160 * Optional parameters are the query string, and an array of values to be 161 * binded to labeled parameters 162 * @param string $p_query_string Query string 163 * @param array $p_bind_array Bind values 164 * @return void 165 */ 166 public function __construct( $p_query_string = null, array $p_bind_array = null ) { 167 # Itialization 168 if( null === $p_query_string ) { 169 $this->query_string = ''; 170 } else { 171 $this->query_string = $p_query_string; 172 } 173 if( null === $p_bind_array ) { 174 $this->query_bind_array = array(); 175 } else { 176 $this->query_bind_array = $p_bind_array; 177 } 178 } 179 180 /** 181 * Set or replaces current query string 182 * @param string $p_query_string Query string 183 * @return void 184 */ 185 public function sql( $p_query_string ) { 186 $this->query_string = $p_query_string; 187 } 188 189 /** 190 * Append to current query string 191 * @param string $p_query_string Query string 192 * @return void 193 */ 194 public function append_sql( $p_query_string ) { 195 $this->query_string .= $p_query_string; 196 } 197 198 /** 199 * Creates a string containing a parameter that can be appended to the query string 200 * The provided value is binded to the parameter and stored for use at execution time 201 * The parameters created by this method are anonymous parameters, so they can't be 202 * accessed later to have values modified or rebinded. 203 * The format of the token created is: "$n", where "n" is an incremental integer 204 * @param mixed $p_value Value to bind for this parameter 205 * @return string Token string 206 */ 207 public function param( $p_value ) { 208 $t_new_id = $this->query_autobind_count++; 209 $this->query_autobind_array[$t_new_id] = $p_value; 210 $t_par = '$' . $t_new_id; 211 return $t_par; 212 } 213 214 /** 215 * Sets the "limit" value. This value is used for all subsequent query executions 216 * Use -1 to disable this option 217 * @param integer $p_limit Number of rows to limit 218 * @return void 219 */ 220 public function set_limit( $p_limit = -1 ) { 221 $this->select_limit = $p_limit; 222 } 223 224 /** 225 * Sets the "offset" value. This value is used for all subsequent query executions 226 * Use -1 to disable this option 227 * @param integer $p_offset Number of rows to offset 228 * @return void 229 */ 230 public function set_offset( $p_offset = -1 ) { 231 $this->select_offset = $p_offset; 232 } 233 234 /** 235 * Executes the query, performing all preprocess and binding steps. 236 * A bind array can provided, which will be added to current bindings. 237 * Limit and offset options can be provided, which will affect only to this execution. 238 * @param array $p_bind_array Array for binding values 239 * @param integer $p_limit Limit value 240 * @param integer $p_offset Offset value 241 * @return IteratorAggregate|boolean ADOdb result set or false if the query failed. 242 */ 243 public function execute( array $p_bind_array = null, $p_limit = null, $p_offset = null ) { 244 # For backwards compatibility with legacy code still relying on DB API, 245 # we need to save the parameters count before binding otherwise it will 246 # be reset after query execution, which will cause issues on RDBMS with 247 # numbered params (e.g. PostgreSQL). 248 db_param_push(); 249 250 # bind values if provided 251 if( null !== $p_bind_array ) { 252 $this->bind_values( $p_bind_array ); 253 } 254 255 # preprocess parameters 256 $this->process_expand_params(); 257 $this->process_bind_params(); 258 $this->process_sql_syntax(); 259 260 $t_result = $this->db_execute($p_limit, $p_offset); 261 db_param_pop(); 262 return $t_result; 263 } 264 265 /** 266 * Call ADOdb execution of sql string. 267 * At this point all preprocessing and value binding has been performed. 268 * @param integer $p_limit Limit value 269 * @param integer $p_offset Offset value 270 * @return IteratorAggregate|boolean ADOdb result set or false if the query failed. 271 */ 272 protected function db_execute( $p_limit = null, $p_offset = null ) { 273 global $g_db; 274 275 # get limit and offset 276 if( null !== $p_limit ) { 277 $t_limit = $p_limit; 278 } else { 279 $t_limit = $this->select_limit; 280 } 281 if( null !== $p_offset ) { 282 $t_offset = $p_offset; 283 } else { 284 $t_offset = $this->select_offset; 285 } 286 287 $t_start = microtime( true ); 288 289 if( ( $t_limit != -1 ) || ( $t_offset != -1 ) ) { 290 $this->db_result = $g_db->SelectLimit( $this->db_query_string, $t_limit, $t_offset, $this->db_param_array ); 291 } else { 292 $this->db_result = $g_db->Execute( $this->db_query_string, $this->db_param_array ); 293 } 294 295 $this->db_query_time = number_format( microtime( true ) - $t_start, 4 ); 296 297 $this->log_query(); 298 299 if( !$this->db_result ) { 300 db_error( $this->db_query_string ); 301 trigger_error( ERROR_DB_QUERY_FAILED, ERROR ); 302 $this->db_result = false; 303 } 304 $this->current_row = null; 305 return $this->db_result; 306 } 307 308 /** 309 * Logs data from latest execution 310 * @return void 311 */ 312 protected function log_query() { 313 global $g_db_log_queries, $g_queries_array; 314 if( ON == $g_db_log_queries ) { 315 $t_query_text = db_format_query_log_msg( $this->db_query_string, $this->db_param_array ); 316 log_event( LOG_DATABASE, array( $t_query_text, $this->db_query_time ) ); 317 } else { 318 # If not logging the queries the actual text is not needed 319 $t_query_text = ''; 320 } 321 array_push( $g_queries_array, array( $t_query_text, $this->db_query_time ) ); 322 } 323 324 /** 325 * Bind values for labeled parameters. 326 * An array indexed by label names must be provided. 327 * @param array $p_values_array Array of values 328 * @return void 329 */ 330 public function bind_values( array $p_values_array ) { 331 $this->query_bind_array = $p_values_array + $this->query_bind_array; 332 } 333 334 /** 335 * Binds values to a labeled parameters 336 * When using: 337 * bind(string, value) binds one value to one parameter 338 * bind(array) is an alias for method "bind_values(array)" 339 * @param mixed $p_label_or_values A labeled parameter name, or array for value bindings 340 * @param mixed $p_value A value to bind 341 * @return void 342 */ 343 public function bind( $p_label_or_values, $p_value = null ) { 344 if( is_array( $p_label_or_values ) ) { 345 # is a values array 346 $this->bind_values( $p_label_or_values ); 347 } else { 348 # is a label string 349 $this->query_bind_array[$p_label_or_values] = $p_value; 350 } 351 } 352 353 /** 354 * Performs some specific preprocessing on sql string 355 * - replace table names from abbreviated syntax 356 * - replaces boolean values to fix some db driver inconsistencies 357 * - process oracle syntax fixes for compatibility 358 * @return void 359 */ 360 protected function process_sql_syntax() { 361 global $g_db; 362 363 static $s_prefix; 364 static $s_suffix; 365 if( $s_prefix === null ) { 366 # Determine table prefix and suffixes including trailing and leading '_' 367 $s_prefix = trim( config_get_global( 'db_table_prefix' ) ); 368 $s_suffix = trim( config_get_global( 'db_table_suffix' ) ); 369 370 if( !empty( $s_prefix ) && '_' != substr( $s_prefix, -1 ) ) { 371 $s_prefix .= '_'; 372 } 373 if( !empty( $s_suffix ) && '_' != substr( $s_suffix, 0, 1 ) ) { 374 $s_suffix = '_' . $s_suffix; 375 } 376 } 377 378 $this->db_query_string = strtr( $this->db_query_string, 379 array( '{' => $s_prefix, '}' => $s_suffix ) 380 ); 381 382 # check parameters for special treatment of boolean types 383 # use the native values provided by the ADOdb driver 384 foreach( $this->db_param_array as $t_key => $t_value ) { 385 if( $t_value === false ) { 386 $this->db_param_array[$t_key] = $g_db->false; 387 } elseif( $t_value === true ) { 388 $this->db_param_array[$t_key] = $g_db->true; 389 } 390 } 391 392 if( db_is_oracle() ) { 393 $this->process_sql_syntax_oracle(); 394 } 395 } 396 397 /** 398 * Process current query string converting all internal parameter placeholders 399 * to final ADOdb parameter syntax. 400 * Will convert all labeled ":xxx", and anonymous "$n" parameters, and build 401 * a values array suitable for ADOdb. 402 * @param integer $p_counter_start 403 * @return integer Number of parameters created 404 */ 405 protected function process_bind_params( $p_counter_start = 0) { 406 global $g_db; 407 408 # shortcut, if no values are binded, skip parameter replacement 409 if( empty( $this->query_autobind_array ) && empty( $this->query_bind_array ) ) { 410 $this->db_query_string = $this->query_string; 411 $this->db_param_array = array(); 412 } 413 414 $t_query_string = $this->expanded_query_string; 415 416 $t_new_query = ''; 417 $t_new_binds = array(); 418 $t_par_index = $p_counter_start; 419 $t_par_count = 0; 420 $t_parts = preg_split( '/(:[a-z0-9_]+)|(\$[0-9]+)/mi', $t_query_string, -1, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE ); 421 foreach( $t_parts as $t_part ) { 422 423 $t_first = substr( $t_part, 0, 1 ); 424 425 if( $t_first === '$' || $t_first === ':' ) { 426 427 $t_label = substr( $t_part, 1 ); 428 switch( $t_first ) { 429 case '$': 430 $t_value = $this->query_autobind_array[(int)$t_label]; 431 break; 432 case ':': 433 $t_value = $this->query_bind_array[$t_label]; 434 break; 435 } 436 437 if( is_array( $t_value ) ) { 438 $t_params_for_array = array(); 439 foreach( $t_value as $t_array_item ) { 440 $t_params_for_array[] = $g_db->Param( $t_par_index ); 441 $t_new_binds[$t_par_index] = $t_array_item; 442 $t_par_count++; 443 $t_par_index++; 444 } 445 $t_new_query .= '(' . implode( ',', $t_params_for_array ) . ')'; 446 } elseif( $t_value instanceof DbQuery ) { 447 # preprocess subquery object 448 $t_value->process_expand_params(); 449 $t_sub_params = $t_value->process_bind_params( $t_par_index ); 450 $t_par_index += $t_sub_params; 451 $t_par_count += $t_sub_params; 452 # append subquery 453 $t_new_binds = $t_new_binds + $t_value->db_param_array; 454 $t_new_query .= '(' . $t_value->db_query_string . ')'; 455 } else { 456 $t_new_query .= $g_db->Param( $t_par_index ); 457 $t_new_binds[$t_par_index] = $t_value; 458 $t_par_count++; 459 $t_par_index++; 460 } 461 462 continue; 463 } 464 465 # default 466 $t_new_query .= $t_part; 467 } 468 469 $this->db_query_string = $t_new_query; 470 $this->db_param_array = $t_new_binds; 471 472 return $t_par_count; 473 } 474 475 /** 476 * Builds an alternative IN expression to work around Oracle limits. 477 * (X, field) IN ( (X,V1), (X,V2), ... ) where X is a constant value 478 * is equivalent to: field IN (V1,V2,..) 479 * @param string $p_alias 480 * @param array $p_values 481 * @return string 482 */ 483 protected function helper_in_oracle_fix( $p_alias, array $p_values ) { 484 $t_in_tuples = array(); 485 foreach( $p_values as $t_value ) { 486 $t_in_tuples[] = '(1,' . $this->param( $t_value ) . ')'; 487 } 488 $t_sql = '(1,' . $p_alias . ') IN (' . implode( ',', $t_in_tuples ) . ')'; 489 return $t_sql; 490 } 491 492 /** 493 * Process query string to expand late binding constructs 494 * @return void 495 */ 496 protected function process_expand_params() { 497 # original query_string should not be modified to allow for rebinding 498 $this->expanded_query_string = $this->query_string; 499 500 if( !empty( $this->late_binding_in_clause ) ) { 501 $this->process_expand_params_in(); 502 } 503 } 504 505 /** 506 * Process query string to expand late binding constructs for IN clauses 507 * @return void 508 */ 509 protected function process_expand_params_in() { 510 $t_new_query = ''; 511 $t_parts = preg_split( '/(\$in[0-9]+)/m', $this->expanded_query_string, -1, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE ); 512 foreach( $t_parts as $t_part ) { 513 $t_is_token = substr( $t_part, 0, 3 ) === '$in'; 514 if( $t_is_token ) { 515 $t_index = (int)substr( $t_part, 3 ); 516 $t_label = $this->late_binding_in_clause[$t_index]['label']; 517 $t_alias = $this->late_binding_in_clause[$t_index]['alias']; 518 $t_values = $this->query_bind_array[$t_label]; 519 if( count( $t_values ) > self::$oracle_in_limit ) { 520 $t_new_query .= $this->helper_in_oracle_fix( $t_alias, $t_values ); 521 } elseif( count( $t_values ) == 1 ) { 522 $t_new_query .= $t_alias . ' = ' . $this->param( reset( $t_values ) ); 523 } else { 524 $t_new_query .= $t_alias . ' IN ' . $this->param( $t_values ); 525 } 526 continue; 527 } 528 529 $t_new_query .= $t_part; 530 } 531 $this->expanded_query_string = $t_new_query; 532 } 533 534 /** 535 * Creates a string construction for an IN expression, providing: 536 * - alias: is the name of the column as a valid identifier in the final sql query 537 * - a label, or an array of values 538 * If a label is provided, the values must be binded as separate calls to binding methods. 539 * 540 * The returned string would replace the sql part for: "alias IN (x,x,x)" 541 * 542 * To work around Oracle limit of 1000 elements in IN clauses, using a label 543 * is recommended in situations that this number may be reached. 544 * This construct will then automatically deal with a compatible syntax fix. 545 * Using a label in this scenario is needed because the fix must be implemented 546 * with a late binding of the array at execution time (this also allows correctly 547 * rebinding the elements for the IN clause) 548 * 549 * @param string $p_alias A valid sql column identifier 550 * @param mixed $p_label_or_values Label or values array 551 * @return string Constructed string to be added to query 552 */ 553 public function sql_in( $p_alias, $p_label_or_values ) { 554 if( is_array( $p_label_or_values ) ) { 555 if( count( $p_label_or_values ) > self::$oracle_in_limit ) { 556 $t_sql = $this->helper_in_oracle_fix( $p_alias, $p_label_or_values ); 557 } elseif( count( $p_label_or_values ) == 1 ) { 558 $t_sql = $p_alias . ' = ' . $this->param( reset( $p_label_or_values ) ); 559 } else { 560 $t_sql = $p_alias . ' IN ' . $this->param( $p_label_or_values ); 561 } 562 } else { 563 # is a label 564 # create placeholder for late binding 565 $t_new_index = count( $this->late_binding_in_clause ); 566 $this->late_binding_in_clause[$t_new_index] = array(); 567 $this->late_binding_in_clause[$t_new_index]['alias'] = $p_alias; 568 $this->late_binding_in_clause[$t_new_index]['label'] = $p_label_or_values; 569 $t_sql = '$in' . $t_new_index; 570 } 571 572 return $t_sql; 573 } 574 575 protected function process_sql_syntax_oracle() { 576 # Remove "AS" keyword, because not supported with table aliasing 577 # - Do not remove text literal within "'" quotes 578 # - Will remove all "AS", except when it's part of a "CAST(x AS y)" expression 579 # To do so, we will assume that the "AS" following a "CAST", is safe to be kept. 580 # Using a counter for "CAST" appearances to allow nesting: CAST(CAST(x AS y) AS z) 581 582 $t_query = $this->db_query_string; 583 584 # split the string by the relevant delimiters. The delimiters will be part of the split array 585 $t_parts = preg_split("/(')|( AS )|(CAST\s*\()/mi", $t_query, -1, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE); 586 $t_is_literal = false; 587 $t_cast = 0; 588 $t_new_query = ''; 589 foreach( $t_parts as $t_part ) { 590 # if quotes, switch literal flag 591 if( $t_part == '\'' ) { 592 $t_is_literal = !$t_is_literal; 593 $t_new_query .= $t_part; 594 continue; 595 } 596 # if this part is litereal, do not change 597 if( $t_is_literal ) { 598 $t_new_query .= $t_part; 599 continue; 600 } else { 601 # if there is "CAST" delimiter, flag the counter 602 if( preg_match( '/^CAST\s*\($/i', $t_part ) ) { 603 $t_cast++; 604 $t_new_query .= $t_part; 605 continue; 606 } 607 # if there is "AS" 608 if( strcasecmp( $t_part, ' AS ' ) == 0 ) { 609 # if there's a previous CAST, keep the AS 610 if( $t_cast > 0 ) { 611 $t_cast--; 612 $t_new_query .= $t_part; 613 } else { 614 # otherwise, remove the " AS ", replace by a space 615 $t_new_query .= ' '; 616 } 617 continue; 618 } 619 $t_new_query .= $t_part; 620 continue; 621 } 622 } 623 $t_query = $t_new_query; 624 625 # Remove null bind variables in insert statements for default values support 626 if( is_array( $this->db_param_array ) ) { 627 preg_match( '/^[\s\n\r]*insert[\s\n\r]+(into){0,1}[\s\n\r]+(?P<table>[a-z0-9_]+)[\s\n\r]*\([\s\n\r]*[\s\n\r]*(?P<fields>[a-z0-9_,\s\n\r]+)[\s\n\r]*\)[\s\n\r]*values[\s\n\r]*\([\s\n\r]*(?P<values>[:a-z0-9_,\s\n\r]+)\)/i', $t_query, $t_matches ); 628 629 if( isset( $t_matches['values'] ) ) { #if statement is a INSERT INTO ... (...) VALUES(...) 630 # iterates non-empty bind variables 631 $i = 0; 632 $t_fields_left = $t_matches['fields']; 633 $t_values_left = $t_matches['values']; 634 635 //for( $t_arr_index = 0; $t_arr_index < count( $this->db_param_array ); $t_arr_index++ ) { 636 foreach( $this->db_param_array as $t_arr_index => $t_arr_value ) { 637 # inserting fieldname search 638 if( preg_match( '/^[\s\n\r]*([a-z0-9_]+)[\s\n\r]*,{0,1}([\d\D]*)\z/i', $t_fields_left, $t_fieldmatch ) ) { 639 $t_fields_left = $t_fieldmatch[2]; 640 $t_fields_arr[$i] = $t_fieldmatch[1]; 641 } 642 # inserting bindvar name search 643 if( preg_match( '/^[\s\n\r]*(:[a-z0-9_]+)[\s\n\r]*,{0,1}([\d\D]*)\z/i', $t_values_left, $t_valuematch ) ) { 644 $t_values_left = $t_valuematch[2]; 645 $t_values_arr[$i] = $t_valuematch[1]; 646 } 647 # skip unsetting if bind array value not empty 648 //if( $this->db_param_array[$t_arr_index] !== '' ) { 649 if( $t_arr_value !== '' ) { 650 $i++; 651 } else { 652 unset( $t_fields_arr[$i] ); 653 unset( $t_values_arr[$i] ); 654 unset( $this->db_param_array[$t_arr_index] ); 655 /* 656 $t_arr_index--; 657 # Shift array and unset bind array element 658 for( $n = $i + 1; $n < count( $this->db_param_array ); $n++ ) { 659 $this->db_param_array[$n-1] = $this->db_param_array[$n]; 660 } 661 unset( $t_fields_arr[$i] ); 662 unset( $t_values_arr[$i] ); 663 unset( $this->db_param_array[count( $this->db_param_array ) - 1] ); 664 */ 665 } 666 } 667 668 # Combine statement from arrays 669 $t_query = 'INSERT INTO ' . $t_matches['table'] . ' (' . implode( ',', $t_fields_arr ) . ')' 670 . ' VALUES (' . implode( ',', $t_values_arr ) . ')'; 671 /* 672 $t_query = 'INSERT INTO ' . $t_matches['table'] . ' (' . $t_fields_arr[0]; 673 for( $i = 1; $i < count( $this->db_param_array ); $i++ ) { 674 $t_query = $t_query . ', ' . $t_fields_arr[$i]; 675 } 676 $t_query = $t_query . ') values (' . $t_values_arr[0]; 677 for( $i = 1; $i < count( $this->db_param_array ); $i++ ) { 678 $t_query = $t_query . ', ' . $t_values_arr[$i]; 679 } 680 $t_query = $t_query . ')'; 681 */ 682 } else { 683 # if input statement is NOT a INSERT INTO (...) VALUES(...) 684 685 # "IS NULL" adoptation here 686 $t_set_where_template_str = substr( md5( uniqid( rand(), true ) ), 0, 50 ); 687 $t_removed_set_where = ''; 688 689 # Find and remove temporarily "SET var1=:bind1, var2=:bind2 WHERE" part 690 preg_match( '/^(?P<before_set_where>.*)(?P<set_where>[\s\n\r]*set[\s\n\r]+[\s\n\ra-z0-9_\.=,:\']+)(?P<after_set_where>where[\d\D]*)$/i', $t_query, $t_matches ); 691 $t_set_where_stmt = isset( $t_matches['after_set_where'] ); 692 693 if( $t_set_where_stmt ) { 694 $t_removed_set_where = $t_matches['set_where']; 695 # Now work with statement without "SET ... WHERE" part 696 $t_templated_query = $t_matches['before_set_where'] . $t_set_where_template_str . $t_matches['after_set_where']; 697 } else { 698 $t_templated_query = $t_query; 699 } 700 701 # Replace "var1=''" by "var1 IS NULL" 702 while( preg_match( '/^(?P<before_empty_literal>[\d\D]*[\s\n\r(]+([a-z0-9_]*[\s\n\r]*\.){0,1}[\s\n\r]*[a-z0-9_]+)[\s\n\r]*=[\s\n\r]*\'\'(?P<after_empty_literal>[\s\n\r]*[\d\D]*\z)/i', $t_templated_query, $t_matches ) > 0 ) { 703 $t_templated_query = $t_matches['before_empty_literal'] . ' IS NULL ' . $t_matches['after_empty_literal']; 704 } 705 # Replace "var1!=''" and "var1<>''" by "var1 IS NOT NULL" 706 while( preg_match( '/^(?P<before_empty_literal>[\d\D]*[\s\n\r(]+([a-z0-9_]*[\s\n\r]*\.){0,1}[\s\n\r]*[a-z0-9_]+)[\s\n\r]*(![\s\n\r]*=|<[\s\n\r]*>)[\s\n\r]*\'\'(?P<after_empty_literal>[\s\n\r]*[\d\D]*\z)/i', $t_templated_query, $t_matches ) > 0 ) { 707 $t_templated_query = $t_matches['before_empty_literal'] . ' IS NOT NULL ' . $t_matches['after_empty_literal']; 708 } 709 710 $t_query = $t_templated_query; 711 # Process input bind variable array to replace "WHERE fld=:12" 712 # by "WHERE fld IS NULL" if :12 is empty 713 while( preg_match( '/^(?P<before_var>[\d\D]*[\s\n\r(]+)(?P<var_name>([a-z0-9_]*[\s\n\r]*\.){0,1}[\s\n\r]*[a-z0-9_]+)(?P<dividers>[\s\n\r]*=[\s\n\r]*:)(?P<bind_name>[0-9]+)(?P<after_var>[\s\n\r]*[\d\D]*\z)/i', $t_templated_query, $t_matches ) > 0 ) { 714 $t_bind_num = $t_matches['bind_name']; 715 716 $t_search_substr = $t_matches['before_var'] . $t_matches['var_name'] . $t_matches['dividers'] . $t_matches['bind_name'] . $t_matches['after_var']; 717 $t_replace_substr = $t_matches['before_var'] . $t_matches['var_name'] . '=:' . $t_matches['bind_name']. $t_matches['after_var']; 718 719 if( $this->db_param_array[$t_bind_num] === '' ) { 720 unset( $this->db_param_array[$t_bind_num] ); 721 /* 722 for( $n = $t_bind_num + 1; $n < count( $this->db_param_array ); $n++ ) { 723 $this->db_param_array[$n - 1] = $this->db_param_array[$n]; 724 } 725 unset( $this->db_param_array[count( $this->db_param_array ) - 1] ); 726 */ 727 $t_replace_substr = $t_matches['before_var'] . $t_matches['var_name'] . ' IS NULL ' . $t_matches['after_var']; 728 } 729 $t_query = str_replace( $t_search_substr, $t_replace_substr, $t_query ); 730 731 $t_templated_query = $t_matches['before_var'] . $t_matches['after_var']; 732 } 733 734 if( $t_set_where_stmt ) { 735 # Put temporarily removed "SET ... WHERE" part back 736 $t_query = str_replace( $t_set_where_template_str, $t_removed_set_where, $t_query ); 737 # Find and remove temporary "SET var1=:bind1, var2=:bind2 WHERE" part again 738 preg_match( '/^(?P<before_set_where>.*)(?P<set_where>[\s\n\r]*set[\s\n\r]+[\s\n\ra-z0-9_\.=,:\']+)(?P<after_set_where>where[\d\D]*)$/i', $t_query, $t_matches ); 739 $t_removed_set_where = $t_matches['set_where']; 740 $t_query = $t_matches['before_set_where'] . $t_set_where_template_str . $t_matches['after_set_where']; 741 742 #Replace "SET fld1=:1" to "SET fld1=DEFAULT" if bind array value is empty 743 $t_removed_set_where_parsing = $t_removed_set_where; 744 745 while( preg_match( '/^(?P<before_var>[\d\D]*[\s\n\r,]+)(?P<var_name>([a-z0-9_]*[\s\n\r]*\.){0,1}[\s\n\r]*[a-z0-9_]+)(?P<dividers>[\s\n\r]*=[\s\n\r]*:)(?P<bind_name>[0-9]+)(?P<after_var>[,\s\n\r]*[\d\D]*\z)/i', $t_removed_set_where_parsing, $t_matches ) > 0 ) { 746 $t_bind_num = $t_matches['bind_name']; 747 $t_search_substr = $t_matches['before_var'] . $t_matches['var_name'] . $t_matches['dividers'] . $t_matches['bind_name'] ; 748 $t_replace_substr = $t_matches['before_var'] . $t_matches['var_name'] . $t_matches['dividers'] . $t_matches['bind_name'] ; 749 750 if( $this->db_param_array[$t_bind_num] === '' ) { 751 unset( $this->db_param_array[$t_bind_num] ); 752 /* 753 for( $n = $t_bind_num + 1; $n < count( $this->db_param_array ); $n++ ) { 754 $this->db_param_array[$n - 1] = $this->db_param_array[$n]; 755 } 756 unset( $this->db_param_array[count( $this->db_param_array ) - 1] ); 757 */ 758 $t_replace_substr = $t_matches['before_var'] . $t_matches['var_name'] . '=DEFAULT '; 759 } 760 $t_removed_set_where = str_replace( $t_search_substr, $t_replace_substr, $t_removed_set_where ); 761 $t_removed_set_where_parsing = $t_matches['before_var'] . $t_matches['after_var']; 762 } 763 $t_query = str_replace( $t_set_where_template_str, $t_removed_set_where, $t_query ); 764 } 765 } 766 } 767 768 $this->db_query_string = $t_query; 769 } 770 771 /** 772 * Compatibility method to support execution of legacy query syntax through db_query(...) 773 * @param string $p_query Query string 774 * @param array $p_arr_parms Values array for parameters 775 * @param integer $p_limit Query limit 776 * @param integer $p_offset Query offset 777 * @param boolean $p_pop_param Set to false to leave the parameters on the stack 778 * @return IteratorAggregate|boolean ADOdb result set or false if the query failed 779 */ 780 public static function compat_db_query( $p_query, array $p_arr_parms = null, $p_limit = -1, $p_offset = -1, $p_pop_param = true ) { 781 global $g_db_param; 782 783 if( !is_array( $p_arr_parms ) ) { 784 $p_arr_parms = array(); 785 } 786 787 $t_query = new DbQuery(); 788 $t_query->db_query_string = $p_query; 789 $t_query->db_param_array = $p_arr_parms; 790 791 $t_query->process_sql_syntax(); 792 793 # Pushing params to safeguard the ADOdb parameter count (required for pgsql) 794 $g_db_param->push(); 795 796 $t_query->db_execute( $p_limit, $p_offset ); 797 798 # Restore ADOdb parameter count 799 $g_db_param->pop(); 800 801 if( $p_pop_param && !empty( $p_arr_parms ) ) { 802 $g_db_param->pop(); 803 } 804 805 return $t_query->db_result; 806 } 807 808 /** 809 * Returns next row of values from current resultset, or false if empty or 810 * the pointer has reached the end. 811 * This method will execute current query if it hasn't been executed yet. 812 * @return array|boolean Next row from result 813 */ 814 public function fetch() { 815 if( null === $this->db_result ) { 816 $this->execute(); 817 } 818 if( !$this->db_result ) { 819 return false; 820 } 821 $this->current_row = db_fetch_array( $this->db_result ); 822 return $this->current_row; 823 } 824 825 /** 826 * Returns all rows as an array 827 * @return array|boolean Array with all rows from the result, false if result is empty. 828 */ 829 public function fetch_all() { 830 if( null === $this->db_result ) { 831 $this->execute(); 832 } 833 if( !$this->db_result ) { 834 return false; 835 } 836 $t_all_rows = array(); 837 while( $t_row = db_fetch_array( $this->db_result ) ) { 838 $t_all_rows[] = $t_row; 839 } 840 return $t_all_rows; 841 } 842 843 /** 844 * Returns one value from current row from resultset 845 * Provided parameter can be a name of the column referenced in the query, 846 * or a numerical index (zero-based) 847 * Without column parameters,first column value will be returned. 848 * 849 * Current row is the latest one fetched, if none was fetched previously, an 850 * automatic fetch() is performed for first row 851 * 852 * @param integer|string $p_index_or_name Column name or numeric index 853 * @return string|boolean Value, or false if end of result or index is not valid 854 */ 855 public function value( $p_index_or_name = 0) { 856 if( !$this->current_row ) { 857 $this->fetch(); 858 } 859 if( is_numeric( $p_index_or_name ) ) { 860 if( count( $this->current_row ) > $p_index_or_name ) { 861 # get the element at that numerical position 862 $t_keys = array_keys( $this->current_row ); 863 $t_value = $this->current_row[$t_keys[$p_index_or_name]]; 864 } else { 865 $t_value = false; 866 } 867 } else { 868 if( isset( $this->current_row[$p_index_or_name] ) ) { 869 # get the value by column name 870 $t_value = $this->current_row[$p_index_or_name]; 871 } else { 872 $t_value = false; 873 } 874 } 875 return $t_value; 876 } 877 878 /** 879 * Alias for value() 880 * @param integer|string $p_index_or_name Column name or numeric index 881 */ 882 public function field( $p_index_or_name = 0) { 883 return $this->value( $p_index_or_name ); 884 } 885 886 /** 887 * Creates a string construction for a case-insensitive LIKE expression 888 * This is an alias for sql_like() with the force_ci parameter set to true. 889 * @param string $p_alias A valid sql column identifier 890 * @param string $p_pattern Pattern string 891 * @param string $p_escape Escape character 892 * @return string Constructed string to be added to query 893 */ 894 public function sql_ilike( $p_alias, $p_pattern, $p_escape = null ) { 895 return $this->sql_like( $p_alias, $p_pattern, $p_escape, true ); 896 } 897 898 /** 899 * Creates a string construction for a LIKE expression, providing: 900 * - alias: is the name of the column as a valid identifier in the final sql query 901 * - value: is the string used as pattern for the like expression. 902 * - escape: optionally, a character used as escape character in the pattern string 903 * Optionally, the expression can be forced to be case insensitive, otherwise the default 904 * behaviour from the database is used. 905 * 906 * The returned string would replace the sql part for: "alias LIKE 'xxx'" 907 * 908 * For portability reasons, the supported wildcards are '%' and '_'. Other special tokens 909 * are automatically escaped: 910 * - [] syntax in mssql are treated as literal characters. 911 * - \ as default escape char in mysql is treated as a literal character. 912 * Note that the pattern string uses c-style escaping, so a "\" character must be written as "\\" 913 * 914 * The pattern string must be prepared by the caller, with proper wildcards and character escaping. 915 * 916 * @param string $p_alias A valid sql column identifier 917 * @param string $p_pattern Pattern string 918 * @param string $p_escape Escape character 919 * @param boolean $p_force_ci If true, force a case-insensitive expression 920 * @return string Constructed string to be added to query 921 */ 922 public function sql_like( $p_alias, $p_pattern, $p_escape = null, $p_force_ci = false ) { 923 # for mssql replace "[" as this is a special non portable token 924 if( db_is_mssql() && strpos( $p_pattern, '[' ) !== false ) { 925 if( null === $p_escape = null ) { 926 $p_escape = '\\'; 927 } 928 $p_pattern = str_replace( '[', $p_escape . '[', $p_pattern ); 929 } 930 931 # for mysql replace "\\" if this char is nor already a explicit escape char 932 # because mysql uses \ as default escape char if ESCAPE caluse is not used 933 if( db_is_mysql() && $p_escape != '\\' && strpos( $p_pattern, '\\' ) !== false ) { 934 if( null === $p_escape = null ) { 935 $p_escape = '\\'; 936 } 937 $p_pattern = str_replace( '\\', $p_escape . '\\', $p_pattern ); 938 } 939 940 $t_expr = $p_alias; 941 $t_operator = 'LIKE'; 942 $t_pattern = $p_pattern; 943 944 # Here we assume that by default: 945 # mysql, mssql: have case-insensitive collations 946 # pgsql, oracle: have case-sensitive collations 947 # Otherwise, a more complicated discovery should be implemented. 948 if( $p_force_ci ) { 949 global $g_db_functional_type; 950 switch( $g_db_functional_type ) { 951 case DB_TYPE_PGSQL: 952 $t_operator = 'ILIKE'; 953 break; 954 case DB_TYPE_ORACLE: 955 $t_expr = 'upper(' . $t_expr . ')'; 956 $t_pattern = strtoupper( $t_pattern ); 957 break; 958 } 959 } 960 961 $t_sql = $t_expr . ' ' . $t_operator . ' ' . $this->param( $t_pattern ); 962 if( null !== $p_escape ) { 963 $t_sql .= ' ESCAPE ' . $this->param( $p_escape ); 964 } 965 return $t_sql; 966 } 967} 968