Mercurial > hg > rc2

comparison program/lib/Roundcube/rcube_db.php @ 0:4681f974d28b

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