1<?php 2 3/** 4 +-----------------------------------------------------------------------+ 5 | This file is part of the Roundcube Webmail client | 6 | | 7 | Copyright (C) The Roundcube Dev Team | 8 | | 9 | Licensed under the GNU General Public License version 3 or | 10 | any later version with exceptions for skins & plugins. | 11 | See the README file for a full license statement. | 12 | | 13 | PURPOSE: | 14 | Database wrapper class that implements PHP PDO functions | 15 +-----------------------------------------------------------------------+ 16 | Author: Aleksander Machniak <alec@alec.pl> | 17 +-----------------------------------------------------------------------+ 18*/ 19 20/** 21 * Database independent query interface. 22 * This is a wrapper for the PHP PDO. 23 * 24 * @package Framework 25 * @subpackage Database 26 */ 27class rcube_db 28{ 29 /** @var string Database implementation (mysql, postgres, etc.) */ 30 public $db_provider; 31 32 protected $db_dsnw; // DSN for write operations 33 protected $db_dsnr; // DSN for read operations 34 protected $db_dsnw_array; // DSN for write operations 35 protected $db_dsnr_array; // DSN for read operations 36 protected $db_connected = false; // Already connected ? 37 protected $db_mode; // Connection mode 38 protected $db_pconn = false; // Persistent connections flag 39 protected $dbh; // Connection handle 40 protected $dbhs = []; 41 protected $table_connections = []; 42 43 protected $db_error = false; 44 protected $db_error_msg = ''; 45 protected $conn_failure = false; 46 protected $db_index = 0; 47 protected $last_result; 48 protected $tables; 49 protected $variables; 50 51 protected $options = [ 52 // column/table quotes 53 'identifier_start' => '"', 54 'identifier_end' => '"', 55 // date/time input format 56 'datetime_format' => 'Y-m-d H:i:s', 57 'table_prefix' => '', 58 ]; 59 60 const DEBUG_LINE_LENGTH = 4096; 61 const DEFAULT_QUOTE = '`'; 62 63 const TYPE_SQL = 'sql'; 64 const TYPE_INT = 'integer'; 65 const TYPE_BOOL = 'bool'; 66 const TYPE_STRING = 'string'; 67 68 69 /** 70 * Factory, returns driver-specific instance of the class 71 * 72 * @param string $db_dsnw DSN for read/write operations 73 * @param string $db_dsnr Optional DSN for read only operations 74 * @param bool $pconn Enables persistent connections 75 * 76 * @return rcube_db Object instance 77 */ 78 public static function factory($db_dsnw, $db_dsnr = '', $pconn = false) 79 { 80 $driver = strtolower(substr($db_dsnw, 0, strpos($db_dsnw, ':'))); 81 $driver_map = [ 82 'sqlite2' => 'sqlite', 83 'sybase' => 'mssql', 84 'dblib' => 'mssql', 85 'mysqli' => 'mysql', 86 'oci' => 'oracle', 87 'oci8' => 'oracle', 88 ]; 89 90 $driver = isset($driver_map[$driver]) ? $driver_map[$driver] : $driver; 91 $class = "rcube_db_$driver"; 92 93 if (!$driver || !class_exists($class)) { 94 rcube::raise_error([ 95 'code' => 600, 'type' => 'db', 96 'line' => __LINE__, 'file' => __FILE__, 97 'message' => "Configuration error. Unsupported database driver: $driver" 98 ], 99 true, true 100 ); 101 } 102 103 return new $class($db_dsnw, $db_dsnr, $pconn); 104 } 105 106 /** 107 * Object constructor 108 * 109 * @param string $db_dsnw DSN for read/write operations 110 * @param string $db_dsnr Optional DSN for read only operations 111 * @param bool $pconn Enables persistent connections 112 */ 113 public function __construct($db_dsnw, $db_dsnr = '', $pconn = false) 114 { 115 if (empty($db_dsnr)) { 116 $db_dsnr = $db_dsnw; 117 } 118 119 $this->db_dsnw = $db_dsnw; 120 $this->db_dsnr = $db_dsnr; 121 $this->db_pconn = $pconn; 122 123 $this->db_dsnw_array = self::parse_dsn($db_dsnw); 124 $this->db_dsnr_array = self::parse_dsn($db_dsnr); 125 126 $config = rcube::get_instance()->config; 127 128 $this->options['table_prefix'] = $config->get('db_prefix'); 129 $this->options['dsnw_noread'] = $config->get('db_dsnw_noread', false); 130 $this->options['table_dsn_map'] = array_map([$this, 'table_name'], $config->get('db_table_dsn', [])); 131 } 132 133 /** 134 * Connect to specific database 135 * 136 * @param array $dsn DSN for DB connections 137 * @param string $mode Connection mode (r|w) 138 */ 139 protected function dsn_connect($dsn, $mode) 140 { 141 $this->db_error = false; 142 $this->db_error_msg = null; 143 144 // return existing handle 145 if (!empty($this->dbhs[$mode])) { 146 $this->dbh = $this->dbhs[$mode]; 147 $this->db_mode = $mode; 148 149 return $this->dbh; 150 } 151 152 // connect to database 153 if ($dbh = $this->conn_create($dsn)) { 154 $this->dbhs[$mode] = $dbh; 155 $this->db_mode = $mode; 156 $this->db_connected = true; 157 } 158 } 159 160 /** 161 * Create PDO connection 162 */ 163 protected function conn_create($dsn) 164 { 165 // Get database specific connection options 166 $dsn_string = $this->dsn_string($dsn); 167 $dsn_options = $this->dsn_options($dsn); 168 169 // Connect 170 try { 171 // with this check we skip fatal error on PDO object creation 172 if (!class_exists('PDO', false)) { 173 throw new Exception('PDO extension not loaded. See http://php.net/manual/en/intro.pdo.php'); 174 } 175 176 $this->conn_prepare($dsn); 177 178 $username = isset($dsn['username']) ? $dsn['username'] : null; 179 $password = isset($dsn['password']) ? $dsn['password'] : null; 180 181 $this->dbh = new PDO($dsn_string, $username, $password, $dsn_options); 182 183 // don't throw exceptions or warnings 184 $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); 185 186 $this->conn_configure($dsn, $this->dbh); 187 } 188 catch (Exception $e) { 189 $this->db_error = true; 190 $this->db_error_msg = $e->getMessage(); 191 192 rcube::raise_error([ 193 'code' => 500, 'type' => 'db', 194 'line' => __LINE__, 'file' => __FILE__, 195 'message' => $this->db_error_msg 196 ], 197 true, false 198 ); 199 200 return null; 201 } 202 203 return $this->dbh; 204 } 205 206 /** 207 * Driver-specific preparation of database connection 208 * 209 * @param array $dsn DSN for DB connections 210 */ 211 protected function conn_prepare($dsn) 212 { 213 } 214 215 /** 216 * Driver-specific configuration of database connection 217 * 218 * @param array $dsn DSN for DB connections 219 * @param PDO $dbh Connection handler 220 */ 221 protected function conn_configure($dsn, $dbh) 222 { 223 } 224 225 /** 226 * Connect to appropriate database depending on the operation 227 * 228 * @param string $mode Connection mode (r|w) 229 * @param bool $force Enforce using the given mode 230 */ 231 public function db_connect($mode, $force = false) 232 { 233 // previous connection failed, don't attempt to connect again 234 if ($this->conn_failure) { 235 return; 236 } 237 238 // no replication 239 if ($this->db_dsnw == $this->db_dsnr) { 240 $mode = 'w'; 241 } 242 243 // Already connected 244 if ($this->db_connected) { 245 // connected to db with the same or "higher" mode (if allowed) 246 if ($this->db_mode == $mode || $this->db_mode == 'w' && !$force && empty($this->options['dsnw_noread'])) { 247 return; 248 } 249 } 250 251 $dsn = ($mode == 'r') ? $this->db_dsnr_array : $this->db_dsnw_array; 252 $this->dsn_connect($dsn, $mode); 253 254 // use write-master when read-only fails 255 if (!$this->db_connected && $mode == 'r' && $this->is_replicated()) { 256 $this->dsn_connect($this->db_dsnw_array, 'w'); 257 } 258 259 $this->conn_failure = !$this->db_connected; 260 } 261 262 /** 263 * Analyze the given SQL statement and select the appropriate connection to use 264 */ 265 protected function dsn_select($query) 266 { 267 // no replication 268 if ($this->db_dsnw == $this->db_dsnr) { 269 return 'w'; 270 } 271 272 // Read or write ? 273 $mode = preg_match('/^(select|show|set)/i', $query) ? 'r' : 'w'; 274 275 $start = '[' . $this->options['identifier_start'] . self::DEFAULT_QUOTE . ']'; 276 $end = '[' . $this->options['identifier_end'] . self::DEFAULT_QUOTE . ']'; 277 $regex = '/(?:^|\s)(from|update|into|join)\s+'.$start.'?([a-z0-9._]+)'.$end.'?\s+/i'; 278 279 // find tables involved in this query 280 if (preg_match_all($regex, $query, $matches, PREG_SET_ORDER)) { 281 foreach ($matches as $m) { 282 $table = $m[2]; 283 284 // always use direct mapping 285 if (!empty($this->options['table_dsn_map'][$table])) { 286 $mode = $this->options['table_dsn_map'][$table]; 287 break; // primary table rules 288 } 289 else if ($mode == 'r') { 290 // connected to db with the same or "higher" mode for this table 291 $db_mode = $this->table_connections[$table]; 292 if ($db_mode == 'w' && empty($this->options['dsnw_noread'])) { 293 $mode = $db_mode; 294 } 295 } 296 } 297 298 // remember mode chosen (for primary table) 299 $table = $matches[0][2]; 300 $this->table_connections[$table] = $mode; 301 } 302 303 return $mode; 304 } 305 306 /** 307 * Activate/deactivate debug mode 308 * 309 * @param bool $dbg True if SQL queries should be logged 310 */ 311 public function set_debug($dbg = true) 312 { 313 $this->options['debug_mode'] = $dbg; 314 } 315 316 /** 317 * Writes debug information/query to 'sql' log file 318 * 319 * @param string $query SQL query 320 */ 321 protected function debug($query) 322 { 323 if (!empty($this->options['debug_mode'])) { 324 if (($len = strlen($query)) > self::DEBUG_LINE_LENGTH) { 325 $diff = $len - self::DEBUG_LINE_LENGTH; 326 $query = substr($query, 0, self::DEBUG_LINE_LENGTH) 327 . "... [truncated $diff bytes]"; 328 } 329 330 rcube::write_log('sql', '[' . (++$this->db_index) . '] ' . $query . ';'); 331 } 332 } 333 334 /** 335 * Getter for error state 336 * 337 * @param mixed $result Optional query result 338 * 339 * @return string Error message 340 */ 341 public function is_error($result = null) 342 { 343 if ($result !== null) { 344 return $result === false ? $this->db_error_msg : null; 345 } 346 347 return $this->db_error ? $this->db_error_msg : null; 348 } 349 350 /** 351 * Connection state checker 352 * 353 * @return bool True if in connected state 354 */ 355 public function is_connected() 356 { 357 return !is_object($this->dbh) ? false : $this->db_connected; 358 } 359 360 /** 361 * Is database replication configured? 362 * 363 * @return bool Returns true if dsnw != dsnr 364 */ 365 public function is_replicated() 366 { 367 return !empty($this->db_dsnr) && $this->db_dsnw != $this->db_dsnr; 368 } 369 370 /** 371 * Get database runtime variables 372 * 373 * @param string $varname Variable name 374 * @param mixed $default Default value if variable is not set 375 * 376 * @return mixed Variable value or default 377 */ 378 public function get_variable($varname, $default = null) 379 { 380 // to be implemented by driver class 381 return rcube::get_instance()->config->get('db_' . $varname, $default); 382 } 383 384 /** 385 * Execute a SQL query 386 * 387 * @param string SQL query to execute 388 * @param mixed Values to be inserted in query 389 * 390 * @return PDOStatement|false Query handle or False on error 391 */ 392 public function query() 393 { 394 $params = func_get_args(); 395 $query = array_shift($params); 396 397 // Support one argument of type array, instead of n arguments 398 if (count($params) == 1 && is_array($params[0])) { 399 $params = $params[0]; 400 } 401 402 return $this->_query($query, 0, 0, $params); 403 } 404 405 /** 406 * Execute a SQL query with limits 407 * 408 * @param string SQL query to execute 409 * @param int Offset for LIMIT statement 410 * @param int Number of rows for LIMIT statement 411 * @param mixed Values to be inserted in query 412 * 413 * @return PDOStatement|false Query handle or False on error 414 */ 415 public function limitquery() 416 { 417 $params = func_get_args(); 418 $query = array_shift($params); 419 $offset = array_shift($params); 420 $numrows = array_shift($params); 421 422 return $this->_query($query, $offset, $numrows, $params); 423 } 424 425 /** 426 * Execute a SQL query with limits 427 * 428 * @param string $query SQL query to execute 429 * @param int $offset Offset for LIMIT statement 430 * @param int $numrows Number of rows for LIMIT statement 431 * @param array $params Values to be inserted in query 432 * 433 * @return PDOStatement|false Query handle or False on error 434 */ 435 protected function _query($query, $offset, $numrows, $params) 436 { 437 $query = ltrim($query); 438 439 $this->db_connect($this->dsn_select($query), true); 440 441 // check connection before proceeding 442 if (!$this->is_connected()) { 443 return $this->last_result = false; 444 } 445 446 if ($numrows || $offset) { 447 $query = $this->set_limit($query, $numrows, $offset); 448 } 449 450 // replace self::DEFAULT_QUOTE with driver-specific quoting 451 $query = $this->query_parse($query); 452 453 // Because in Roundcube we mostly use queries that are 454 // executed only once, we will not use prepared queries 455 $pos = 0; 456 $idx = 0; 457 458 if (count($params)) { 459 while ($pos = strpos($query, '?', $pos)) { 460 if (isset($query[$pos+1]) && $query[$pos+1] == '?') { // skip escaped '?' 461 $pos += 2; 462 } 463 else { 464 $val = $this->quote($params[$idx++]); 465 unset($params[$idx-1]); 466 $query = substr_replace($query, $val, $pos, 1); 467 $pos += strlen($val); 468 } 469 } 470 } 471 472 $query = rtrim($query, " \t\n\r\0\x0B;"); 473 474 // replace escaped '?' and quotes back to normal, see self::quote() 475 $query = str_replace( 476 ['??', self::DEFAULT_QUOTE.self::DEFAULT_QUOTE], 477 ['?', self::DEFAULT_QUOTE], 478 $query 479 ); 480 481 // log query 482 $this->debug($query); 483 484 return $this->query_execute($query); 485 } 486 487 /** 488 * Query execution 489 */ 490 protected function query_execute($query) 491 { 492 // destroy reference to previous result, required for SQLite driver (#1488874) 493 $this->last_result = null; 494 $this->db_error_msg = null; 495 496 // send query 497 $result = $this->dbh->query($query); 498 499 if ($result === false) { 500 $result = $this->handle_error($query); 501 } 502 503 return $this->last_result = $result; 504 } 505 506 /** 507 * Parse SQL query and replace identifier quoting 508 * 509 * @param string $query SQL query 510 * 511 * @return string SQL query 512 */ 513 protected function query_parse($query) 514 { 515 $start = $this->options['identifier_start']; 516 $end = $this->options['identifier_end']; 517 $quote = self::DEFAULT_QUOTE; 518 519 if ($start == $quote) { 520 return $query; 521 } 522 523 $pos = 0; 524 $in = false; 525 526 while ($pos = strpos($query, $quote, $pos)) { 527 if (isset($query[$pos+1]) && $query[$pos+1] == $quote) { // skip escaped quote 528 $pos += 2; 529 } 530 else { 531 if ($in) { 532 $q = $end; 533 $in = false; 534 } 535 else { 536 $q = $start; 537 $in = true; 538 } 539 540 $query = substr_replace($query, $q, $pos, 1); 541 $pos++; 542 } 543 } 544 545 return $query; 546 } 547 548 /** 549 * Helper method to handle DB errors. 550 * This by default logs the error but could be overridden by a driver implementation 551 * 552 * @param string $query Query that triggered the error 553 * 554 * @return mixed Result to be stored and returned 555 */ 556 protected function handle_error($query) 557 { 558 $error = $this->dbh->errorInfo(); 559 560 if (empty($this->options['ignore_key_errors']) || !in_array($error[0], ['23000', '23505'])) { 561 $this->db_error = true; 562 $this->db_error_msg = sprintf('[%s] %s', $error[1], $error[2]); 563 564 if (empty($this->options['ignore_errors'])) { 565 rcube::raise_error([ 566 'code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__, 567 'message' => $this->db_error_msg . " (SQL Query: $query)" 568 ], true, false); 569 } 570 } 571 572 return false; 573 } 574 575 /** 576 * INSERT ... ON DUPLICATE KEY UPDATE (or equivalent). 577 * When not supported by the engine we do UPDATE and INSERT. 578 * 579 * @param string $table Table name (should be already passed via table_name() with quoting) 580 * @param array $keys Hash array (column => value) of the unique constraint 581 * @param array $columns List of columns to update 582 * @param array $values List of values to update (number of elements 583 * should be the same as in $columns) 584 * 585 * @return PDOStatement|bool Query handle or False on error 586 * @todo Multi-insert support 587 */ 588 public function insert_or_update($table, $keys, $columns, $values) 589 { 590 $columns = array_map(function($i) { return "`$i`"; }, $columns); 591 $sets = array_map(function($i) { return "$i = ?"; }, $columns); 592 $where = $keys; 593 594 array_walk($where, function(&$val, $key) { 595 $val = $this->quote_identifier($key) . " = " . $this->quote($val); 596 }); 597 598 // First try UPDATE 599 $result = $this->query("UPDATE $table SET " . implode(", ", $sets) 600 . " WHERE " . implode(" AND ", $where), $values); 601 602 // if UPDATE fails use INSERT 603 if ($result && !$this->affected_rows($result)) { 604 $cols = implode(', ', array_map(function($i) { return "`$i`"; }, array_keys($keys))); 605 $cols .= ', ' . implode(', ', $columns); 606 $vals = implode(', ', array_map(function($i) { return $this->quote($i); }, $keys)); 607 $vals .= ', ' . rtrim(str_repeat('?, ', count($columns)), ', '); 608 609 $result = $this->query("INSERT INTO $table ($cols) VALUES ($vals)", $values); 610 } 611 612 return $result; 613 } 614 615 /** 616 * Get number of affected rows for the last query 617 * 618 * @param mixed $result Optional query handle 619 * 620 * @return int Number of (matching) rows 621 */ 622 public function affected_rows($result = null) 623 { 624 if ($result || ($result === null && ($result = $this->last_result))) { 625 if ($result !== true) { 626 return $result->rowCount(); 627 } 628 } 629 630 return 0; 631 } 632 633 /** 634 * Get number of rows for a SQL query 635 * If no query handle is specified, the last query will be taken as reference 636 * 637 * @param mixed $result Optional query handle 638 * 639 * @return mixed Number of rows or false on failure 640 * @deprecated This method shows very poor performance and should be avoided. 641 */ 642 public function num_rows($result = null) 643 { 644 if (($result || ($result === null && ($result = $this->last_result))) && $result !== true) { 645 // repeat query with SELECT COUNT(*) ... 646 if (preg_match('/^SELECT\s+(?:ALL\s+|DISTINCT\s+)?(?:.*?)\s+FROM\s+(.*)$/ims', $result->queryString, $m)) { 647 $query = $this->dbh->query('SELECT COUNT(*) FROM ' . $m[1], PDO::FETCH_NUM); 648 return $query ? intval($query->fetchColumn(0)) : false; 649 } 650 else { 651 $num = count($result->fetchAll()); 652 $result->execute(); // re-execute query because there's no seek(0) 653 return $num; 654 } 655 } 656 657 return false; 658 } 659 660 /** 661 * Get last inserted record ID 662 * 663 * @param string $table Table name (to find the incremented sequence) 664 * 665 * @return mixed ID or false on failure 666 */ 667 public function insert_id($table = '') 668 { 669 if (!$this->db_connected || $this->db_mode == 'r') { 670 return false; 671 } 672 673 if ($table) { 674 // resolve table name 675 $table = $this->table_name($table); 676 } 677 678 return $this->dbh->lastInsertId($table); 679 } 680 681 /** 682 * Get an associative array for one row 683 * If no query handle is specified, the last query will be taken as reference 684 * 685 * @param mixed $result Optional query handle 686 * 687 * @return array|false Array with col values or false on failure 688 */ 689 public function fetch_assoc($result = null) 690 { 691 return $this->_fetch_row($result, PDO::FETCH_ASSOC); 692 } 693 694 /** 695 * Get an index array for one row 696 * If no query handle is specified, the last query will be taken as reference 697 * 698 * @param mixed $result Optional query handle 699 * 700 * @return array|false Array with col values or false on failure 701 */ 702 public function fetch_array($result = null) 703 { 704 return $this->_fetch_row($result, PDO::FETCH_NUM); 705 } 706 707 /** 708 * Get col values for a result row 709 * 710 * @param mixed $result Optional query handle 711 * @param int $mode Fetch mode identifier 712 * 713 * @return array|false Array with col values or false on failure 714 */ 715 protected function _fetch_row($result, $mode) 716 { 717 if ($result || ($result === null && ($result = $this->last_result))) { 718 if ($result !== true) { 719 return $result->fetch($mode); 720 } 721 } 722 723 return false; 724 } 725 726 /** 727 * Adds LIMIT,OFFSET clauses to the query 728 * 729 * @param string $query SQL query 730 * @param int $limit Number of rows 731 * @param int $offset Offset 732 * 733 * @return string SQL query 734 */ 735 protected function set_limit($query, $limit = 0, $offset = 0) 736 { 737 if ($limit) { 738 $query .= ' LIMIT ' . intval($limit); 739 } 740 741 if ($offset) { 742 $query .= ' OFFSET ' . intval($offset); 743 } 744 745 return $query; 746 } 747 748 /** 749 * Returns list of tables in a database 750 * 751 * @return array List of all tables of the current database 752 */ 753 public function list_tables() 754 { 755 // get tables if not cached 756 if ($this->tables === null) { 757 $q = $this->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES" 758 . " WHERE TABLE_TYPE = 'BASE TABLE'" 759 . " ORDER BY TABLE_NAME" 760 ); 761 762 $this->tables = $q ? $q->fetchAll(PDO::FETCH_COLUMN, 0) : []; 763 } 764 765 return $this->tables; 766 } 767 768 /** 769 * Returns list of columns in database table 770 * 771 * @param string $table Table name 772 * 773 * @return array List of table cols 774 */ 775 public function list_cols($table) 776 { 777 $q = $this->query('SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?', $table); 778 779 if ($q) { 780 return $q->fetchAll(PDO::FETCH_COLUMN, 0); 781 } 782 783 return []; 784 } 785 786 /** 787 * Start transaction 788 * 789 * @return bool True on success, False on failure 790 */ 791 public function startTransaction() 792 { 793 $this->db_connect('w', true); 794 795 // check connection before proceeding 796 if (!$this->is_connected()) { 797 return $this->last_result = false; 798 } 799 800 $this->debug('BEGIN TRANSACTION'); 801 802 return $this->last_result = $this->dbh->beginTransaction(); 803 } 804 805 /** 806 * Commit transaction 807 * 808 * @return bool True on success, False on failure 809 */ 810 public function endTransaction() 811 { 812 $this->db_connect('w', true); 813 814 // check connection before proceeding 815 if (!$this->is_connected()) { 816 return $this->last_result = false; 817 } 818 819 $this->debug('COMMIT TRANSACTION'); 820 821 return $this->last_result = $this->dbh->commit(); 822 } 823 824 /** 825 * Rollback transaction 826 * 827 * @return bool True on success, False on failure 828 */ 829 public function rollbackTransaction() 830 { 831 $this->db_connect('w', true); 832 833 // check connection before proceeding 834 if (!$this->is_connected()) { 835 return $this->last_result = false; 836 } 837 838 $this->debug('ROLLBACK TRANSACTION'); 839 840 return $this->last_result = $this->dbh->rollBack(); 841 } 842 843 /** 844 * Release resources related to the last query result. 845 * When we know we don't need to access the last query result we can destroy it 846 * and release memory. Useful especially if the query returned big chunk of data. 847 */ 848 public function reset() 849 { 850 $this->last_result = null; 851 } 852 853 /** 854 * Terminate database connection. 855 */ 856 public function closeConnection() 857 { 858 $this->db_connected = false; 859 $this->db_index = 0; 860 861 // release statement and connection resources 862 $this->last_result = null; 863 $this->dbh = null; 864 $this->dbhs = []; 865 } 866 867 /** 868 * Formats input so it can be safely used in a query 869 * 870 * @param mixed $input Value to quote 871 * @param string $type Type of data (integer, bool, ident) 872 * 873 * @return string Quoted/converted string for use in query 874 */ 875 public function quote($input, $type = null) 876 { 877 if ($input instanceof rcube_db_param) { 878 return (string) $input; 879 } 880 881 // handle int directly for better performance 882 if ($type == 'integer' || $type == 'int') { 883 return intval($input); 884 } 885 886 if (is_null($input)) { 887 return 'NULL'; 888 } 889 890 if ($input instanceof DateTime) { 891 return $this->quote($input->format($this->options['datetime_format'])); 892 } 893 894 if ($type == 'ident') { 895 return $this->quote_identifier($input); 896 } 897 898 // create DB handle if not available 899 if (!$this->dbh) { 900 $this->db_connect('r'); 901 } 902 903 if ($this->dbh) { 904 $map = [ 905 'bool' => PDO::PARAM_BOOL, 906 'integer' => PDO::PARAM_INT, 907 ]; 908 909 $type = isset($map[$type]) ? $map[$type] : PDO::PARAM_STR; 910 911 return strtr($this->dbh->quote($input, $type), 912 // escape ? and ` 913 ['?' => '??', self::DEFAULT_QUOTE => self::DEFAULT_QUOTE.self::DEFAULT_QUOTE] 914 ); 915 } 916 917 return 'NULL'; 918 } 919 920 /** 921 * Escapes a string so it can be safely used in a query 922 * 923 * @param string $str A string to escape 924 * 925 * @return string Escaped string for use in a query 926 */ 927 public function escape($str) 928 { 929 if (is_null($str)) { 930 return 'NULL'; 931 } 932 933 return substr($this->quote($str), 1, -1); 934 } 935 936 /** 937 * Quotes a string so it can be safely used as a table or column name 938 * 939 * @param string $str Value to quote 940 * 941 * @return string Quoted string for use in query 942 * @deprecated Replaced by rcube_db::quote_identifier 943 * @see rcube_db::quote_identifier 944 */ 945 public function quoteIdentifier($str) 946 { 947 return $this->quote_identifier($str); 948 } 949 950 /** 951 * Escapes a string so it can be safely used in a query 952 * 953 * @param string $str A string to escape 954 * 955 * @return string Escaped string for use in a query 956 * @deprecated Replaced by rcube_db::escape 957 * @see rcube_db::escape 958 */ 959 public function escapeSimple($str) 960 { 961 return $this->escape($str); 962 } 963 964 /** 965 * Quotes a string so it can be safely used as a table or column name 966 * 967 * @param string $str Value to quote 968 * 969 * @return string Quoted string for use in query 970 */ 971 public function quote_identifier($str) 972 { 973 $start = $this->options['identifier_start']; 974 $end = $this->options['identifier_end']; 975 $name = []; 976 977 foreach (explode('.', $str) as $elem) { 978 $elem = str_replace([$start, $end], '', $elem); 979 $name[] = $start . $elem . $end; 980 } 981 982 return implode('.', $name); 983 } 984 985 /** 986 * Create query parameter object 987 * 988 * @param mixed $value Parameter value 989 * @param string $type Parameter type (one of rcube_db::TYPE_* constants) 990 */ 991 public function param($value, $type = null) 992 { 993 return new rcube_db_param($this, $value, $type); 994 } 995 996 /** 997 * Return SQL function for current time and date 998 * 999 * @param int $interval Optional interval (in seconds) to add/subtract 1000 * 1001 * @return string SQL function to use in query 1002 */ 1003 public function now($interval = 0) 1004 { 1005 $result = 'now()'; 1006 1007 if ($interval) { 1008 $result .= ' ' . ($interval > 0 ? '+' : '-') . ' INTERVAL ' 1009 . ($interval > 0 ? intval($interval) : intval($interval) * -1) 1010 . ' SECOND'; 1011 } 1012 1013 return $result; 1014 } 1015 1016 /** 1017 * Return list of elements for use with SQL's IN clause 1018 * 1019 * @param array $arr Input array 1020 * @param string $type Type of data (integer, bool, ident) 1021 * 1022 * @return string Comma-separated list of quoted values for use in query 1023 */ 1024 public function array2list($arr, $type = null) 1025 { 1026 if (!is_array($arr)) { 1027 return $this->quote($arr, $type); 1028 } 1029 1030 foreach ($arr as $idx => $item) { 1031 $arr[$idx] = $this->quote($item, $type); 1032 } 1033 1034 return implode(',', $arr); 1035 } 1036 1037 /** 1038 * Return SQL statement to convert a field value into a unix timestamp 1039 * 1040 * This method is deprecated and should not be used anymore due to limitations 1041 * of timestamp functions in Mysql (year 2038 problem) 1042 * 1043 * @param string $field Field name 1044 * 1045 * @return string SQL statement to use in query 1046 * @deprecated 1047 */ 1048 public function unixtimestamp($field) 1049 { 1050 return "UNIX_TIMESTAMP($field)"; 1051 } 1052 1053 /** 1054 * Return SQL statement to convert from a unix timestamp 1055 * 1056 * @param int $timestamp Unix timestamp 1057 * 1058 * @return string Date string in db-specific format 1059 * @deprecated 1060 */ 1061 public function fromunixtime($timestamp) 1062 { 1063 return $this->quote(date($this->options['datetime_format'], $timestamp)); 1064 } 1065 1066 /** 1067 * Return SQL statement for case insensitive LIKE 1068 * 1069 * @param string $column Field name 1070 * @param string $value Search value 1071 * 1072 * @return string SQL statement to use in query 1073 */ 1074 public function ilike($column, $value) 1075 { 1076 return $this->quote_identifier($column).' LIKE '.$this->quote($value); 1077 } 1078 1079 /** 1080 * Abstract SQL statement for value concatenation 1081 * 1082 * @return string SQL statement to be used in query 1083 */ 1084 public function concat(/* col1, col2, ... */) 1085 { 1086 $args = func_get_args(); 1087 if (!empty($args) && is_array($args[0])) { 1088 $args = $args[0]; 1089 } 1090 1091 return '(' . implode(' || ', $args) . ')'; 1092 } 1093 1094 /** 1095 * Encodes non-UTF-8 characters in string/array/object (recursive) 1096 * 1097 * @param mixed $input Data to fix 1098 * @param bool $serialized Enable serialization 1099 * 1100 * @return mixed Properly UTF-8 encoded data 1101 */ 1102 public static function encode($input, $serialized = false) 1103 { 1104 // use Base64 encoding to workaround issues with invalid 1105 // or null characters in serialized string (#1489142) 1106 if ($serialized) { 1107 return base64_encode(serialize($input)); 1108 } 1109 1110 if (is_object($input)) { 1111 foreach (get_object_vars($input) as $idx => $value) { 1112 $input->$idx = self::encode($value); 1113 } 1114 1115 return $input; 1116 } 1117 else if (is_array($input)) { 1118 foreach ($input as $idx => $value) { 1119 $input[$idx] = self::encode($value); 1120 } 1121 1122 return $input; 1123 } 1124 1125 return utf8_encode($input); 1126 } 1127 1128 /** 1129 * Decodes encoded UTF-8 string/object/array (recursive) 1130 * 1131 * @param mixed $input Input data 1132 * @param bool $serialized Enable serialization 1133 * 1134 * @return mixed Decoded data 1135 */ 1136 public static function decode($input, $serialized = false) 1137 { 1138 // use Base64 encoding to workaround issues with invalid 1139 // or null characters in serialized string (#1489142) 1140 if ($serialized) { 1141 // Keep backward compatibility where base64 wasn't used 1142 if (strpos(substr($input, 0, 16), ':') !== false) { 1143 return self::decode(@unserialize($input)); 1144 } 1145 1146 return @unserialize(base64_decode($input)); 1147 } 1148 1149 if (is_object($input)) { 1150 foreach (get_object_vars($input) as $idx => $value) { 1151 $input->$idx = self::decode($value); 1152 } 1153 1154 return $input; 1155 } 1156 else if (is_array($input)) { 1157 foreach ($input as $idx => $value) { 1158 $input[$idx] = self::decode($value); 1159 } 1160 1161 return $input; 1162 } 1163 1164 return utf8_decode($input); 1165 } 1166 1167 /** 1168 * Return correct name for a specific database table 1169 * 1170 * @param string $table Table name 1171 * @param bool $quoted Quote table identifier 1172 * 1173 * @return string Translated table name 1174 */ 1175 public function table_name($table, $quoted = false) 1176 { 1177 // let plugins alter the table name (#1489837) 1178 $plugin = rcube::get_instance()->plugins->exec_hook('db_table_name', ['table' => $table]); 1179 $table = $plugin['table']; 1180 1181 // add prefix to the table name if configured 1182 if (($prefix = $this->options['table_prefix']) && strpos($table, $prefix) !== 0) { 1183 $table = $prefix . $table; 1184 } 1185 1186 if ($quoted) { 1187 $table = $this->quote_identifier($table); 1188 } 1189 1190 return $table; 1191 } 1192 1193 /** 1194 * Set class option value 1195 * 1196 * @param string $name Option name 1197 * @param mixed $value Option value 1198 */ 1199 public function set_option($name, $value) 1200 { 1201 $this->options[$name] = $value; 1202 } 1203 1204 /** 1205 * Set DSN connection to be used for the given table 1206 * 1207 * @param string $table Table name 1208 * @param string $mode DSN connection ('r' or 'w') to be used 1209 */ 1210 public function set_table_dsn($table, $mode) 1211 { 1212 $this->options['table_dsn_map'][$this->table_name($table)] = $mode; 1213 } 1214 1215 /** 1216 * MDB2 DSN string parser 1217 * 1218 * @param string $sequence Sequence name 1219 * 1220 * @return array DSN parameters 1221 */ 1222 public static function parse_dsn($dsn) 1223 { 1224 if (empty($dsn)) { 1225 return null; 1226 } 1227 1228 // Find phptype and dbsyntax 1229 if (($pos = strpos($dsn, '://')) !== false) { 1230 $str = substr($dsn, 0, $pos); 1231 $dsn = substr($dsn, $pos + 3); 1232 } 1233 else { 1234 $str = $dsn; 1235 $dsn = null; 1236 } 1237 1238 // Get phptype and dbsyntax 1239 // $str => phptype(dbsyntax) 1240 if (preg_match('|^(.+?)\((.*?)\)$|', $str, $arr)) { 1241 $parsed['phptype'] = $arr[1]; 1242 $parsed['dbsyntax'] = !$arr[2] ? $arr[1] : $arr[2]; 1243 } 1244 else { 1245 $parsed['phptype'] = $str; 1246 $parsed['dbsyntax'] = $str; 1247 } 1248 1249 if (empty($dsn)) { 1250 return $parsed; 1251 } 1252 1253 // Get (if found): username and password 1254 // $dsn => username:password@protocol+hostspec/database 1255 if (($at = strrpos($dsn,'@')) !== false) { 1256 $str = substr($dsn, 0, $at); 1257 $dsn = substr($dsn, $at + 1); 1258 if (($pos = strpos($str, ':')) !== false) { 1259 $parsed['username'] = rawurldecode(substr($str, 0, $pos)); 1260 $parsed['password'] = rawurldecode(substr($str, $pos + 1)); 1261 } 1262 else { 1263 $parsed['username'] = rawurldecode($str); 1264 } 1265 } 1266 1267 // Find protocol and hostspec 1268 // $dsn => proto(proto_opts)/database 1269 if (preg_match('|^([^(]+)\((.*?)\)/?(.*?)$|', $dsn, $match)) { 1270 $proto = $match[1]; 1271 $proto_opts = $match[2] ? $match[2] : false; 1272 $dsn = $match[3]; 1273 } 1274 // $dsn => protocol+hostspec/database (old format) 1275 else { 1276 if (strpos($dsn, '+') !== false) { 1277 list($proto, $dsn) = explode('+', $dsn, 2); 1278 } 1279 if ( strpos($dsn, '//') === 0 1280 && strpos($dsn, '/', 2) !== false 1281 && $parsed['phptype'] == 'oci8' 1282 ) { 1283 // Oracle's "Easy Connect" syntax: 1284 // "username/password@[//]host[:port][/service_name]" 1285 // e.g. "scott/tiger@//mymachine:1521/oracle" 1286 $proto_opts = $dsn; 1287 $pos = strrpos($proto_opts, '/'); 1288 $dsn = substr($proto_opts, $pos + 1); 1289 $proto_opts = substr($proto_opts, 0, $pos); 1290 } 1291 else if (strpos($dsn, '/') !== false) { 1292 list($proto_opts, $dsn) = explode('/', $dsn, 2); 1293 } 1294 else { 1295 $proto_opts = $dsn; 1296 $dsn = null; 1297 } 1298 } 1299 1300 // process the different protocol options 1301 $parsed['protocol'] = !empty($proto) ? $proto : 'tcp'; 1302 $proto_opts = rawurldecode($proto_opts); 1303 if (strpos($proto_opts, ':') !== false) { 1304 list($proto_opts, $parsed['port']) = explode(':', $proto_opts); 1305 } 1306 if ($parsed['protocol'] == 'tcp') { 1307 $parsed['hostspec'] = $proto_opts; 1308 } 1309 else if ($parsed['protocol'] == 'unix') { 1310 $parsed['socket'] = $proto_opts; 1311 } 1312 1313 // Get database if any 1314 // $dsn => database 1315 if ($dsn) { 1316 // /database 1317 if (($pos = strpos($dsn, '?')) === false) { 1318 $parsed['database'] = rawurldecode($dsn); 1319 } 1320 else { 1321 // /database?param1=value1¶m2=value2 1322 $parsed['database'] = rawurldecode(substr($dsn, 0, $pos)); 1323 $dsn = substr($dsn, $pos + 1); 1324 1325 if (strpos($dsn, '&') !== false) { 1326 $opts = explode('&', $dsn); 1327 } 1328 else { // database?param1=value1 1329 $opts = [$dsn]; 1330 } 1331 foreach ($opts as $opt) { 1332 list($key, $value) = explode('=', $opt); 1333 if (!array_key_exists($key, $parsed) || false === $parsed[$key]) { 1334 // don't allow params overwrite 1335 $parsed[$key] = rawurldecode($value); 1336 } 1337 } 1338 } 1339 1340 // remove problematic suffix (#7034) 1341 $parsed['database'] = preg_replace('/;.*$/', '', $parsed['database']); 1342 1343 // Resolve relative path to the sqlite database file 1344 // so for example it works with Roundcube Installer 1345 if (!empty($parsed['phptype']) && !empty($parsed['database']) 1346 && stripos($parsed['phptype'], 'sqlite') === 0 1347 && $parsed['database'][0] != '/' 1348 && strpos($parsed['database'], ':') === false 1349 ) { 1350 $parsed['database'] = INSTALL_PATH . $parsed['database']; 1351 } 1352 } 1353 1354 return $parsed; 1355 } 1356 1357 /** 1358 * Returns PDO DSN string from DSN array 1359 * 1360 * @param array $dsn DSN parameters 1361 * 1362 * @return string DSN string 1363 */ 1364 protected function dsn_string($dsn) 1365 { 1366 $params = []; 1367 $result = $dsn['phptype'] . ':'; 1368 1369 if (isset($dsn['hostspec'])) { 1370 $params[] = 'host=' . $dsn['hostspec']; 1371 } 1372 1373 if (isset($dsn['port'])) { 1374 $params[] = 'port=' . $dsn['port']; 1375 } 1376 1377 if (isset($dsn['database'])) { 1378 $params[] = 'dbname=' . $dsn['database']; 1379 } 1380 1381 if (!empty($params)) { 1382 $result .= implode(';', $params); 1383 } 1384 1385 return $result; 1386 } 1387 1388 /** 1389 * Returns driver-specific connection options 1390 * 1391 * @param array $dsn DSN parameters 1392 * 1393 * @return array Connection options 1394 */ 1395 protected function dsn_options($dsn) 1396 { 1397 $result = []; 1398 1399 if ($this->db_pconn) { 1400 $result[PDO::ATTR_PERSISTENT] = true; 1401 } 1402 1403 if (!empty($dsn['prefetch'])) { 1404 $result[PDO::ATTR_PREFETCH] = (int) $dsn['prefetch']; 1405 } 1406 1407 if (!empty($dsn['timeout'])) { 1408 $result[PDO::ATTR_TIMEOUT] = (int) $dsn['timeout']; 1409 } 1410 1411 return $result; 1412 } 1413 1414 /** 1415 * Execute the given SQL script 1416 * 1417 * @param string $sql SQL queries to execute 1418 * 1419 * @return boolean True on success, False on error 1420 */ 1421 public function exec_script($sql) 1422 { 1423 $sql = $this->fix_table_names($sql); 1424 $buff = ''; 1425 $exec = ''; 1426 1427 foreach (explode("\n", $sql) as $line) { 1428 $trimmed = trim($line); 1429 if ($trimmed == '' || preg_match('/^--/', $trimmed)) { 1430 continue; 1431 } 1432 1433 if ($trimmed == 'GO') { 1434 $exec = $buff; 1435 } 1436 else if ($trimmed[strlen($trimmed)-1] == ';') { 1437 $exec = $buff . substr(rtrim($line), 0, -1); 1438 } 1439 1440 if ($exec) { 1441 $this->query($exec); 1442 $buff = ''; 1443 $exec = ''; 1444 if ($this->db_error) { 1445 break; 1446 } 1447 } 1448 else { 1449 $buff .= $line . "\n"; 1450 } 1451 } 1452 1453 return !$this->db_error; 1454 } 1455 1456 /** 1457 * Parse SQL file and fix table names according to table prefix 1458 */ 1459 protected function fix_table_names($sql) 1460 { 1461 if (!$this->options['table_prefix']) { 1462 return $sql; 1463 } 1464 1465 $sql = preg_replace_callback( 1466 '/((TABLE|TRUNCATE( TABLE)?|(?<!ON )UPDATE|INSERT INTO|FROM' 1467 . '| ON(?! (DELETE|UPDATE))|REFERENCES|CONSTRAINT|FOREIGN KEY|INDEX|UNIQUE( INDEX)?)' 1468 . '\s+(IF (NOT )?EXISTS )?[`"]*)([^`"\( \r\n]+)/', 1469 [$this, 'fix_table_names_callback'], 1470 $sql 1471 ); 1472 1473 return $sql; 1474 } 1475 1476 /** 1477 * Preg_replace callback for fix_table_names() 1478 */ 1479 protected function fix_table_names_callback($matches) 1480 { 1481 $prefix = $this->options['table_prefix']; 1482 1483 // Schema prefix (ends with a dot) 1484 if ($prefix[strlen($prefix)-1] === '.') { 1485 // These can't have a schema prefix 1486 if (preg_match('/(CONSTRAINT|UNIQUE|INDEX)[\s\t`"]*$/', $matches[1])) { 1487 $prefix = ''; 1488 } 1489 else { 1490 // check if the identifier is quoted, then quote the prefix 1491 $last = $matches[1][strlen($matches[1])-1]; 1492 1493 if ($last === '`' || $last === '"') { 1494 $prefix = substr($prefix, 0, -1) . $last . '.' . $last; 1495 } 1496 } 1497 } 1498 1499 return $matches[1] . $prefix . $matches[count($matches)-1]; 1500 } 1501} 1502