PageRenderTime 63ms CodeModel.GetById 32ms RepoModel.GetById 0ms app.codeStats 0ms

/etc/apps/webmail/program/lib/Roundcube/rcube_db.php

https://github.com/raiman264/zpanelx
PHP | 1132 lines | 592 code | 144 blank | 396 comment | 132 complexity | b845fb952683713de5d81d745b3e7816 MD5 | raw file
Possible License(s): GPL-2.0, LGPL-3.0, LGPL-2.1, CC-BY-SA-4.0, GPL-3.0
  1. <?php
  2. /**
  3. +-----------------------------------------------------------------------+
  4. | This file is part of the Roundcube Webmail client |
  5. | Copyright (C) 2005-2012, The Roundcube Dev Team |
  6. | |
  7. | Licensed under the GNU General Public License version 3 or |
  8. | any later version with exceptions for skins & plugins. |
  9. | See the README file for a full license statement. |
  10. | |
  11. | PURPOSE: |
  12. | Database wrapper class that implements PHP PDO functions |
  13. +-----------------------------------------------------------------------+
  14. | Author: Aleksander Machniak <alec@alec.pl> |
  15. +-----------------------------------------------------------------------+
  16. */
  17. /**
  18. * Database independent query interface.
  19. * This is a wrapper for the PHP PDO.
  20. *
  21. * @package Framework
  22. * @subpackage Database
  23. */
  24. class rcube_db
  25. {
  26. public $db_provider;
  27. protected $db_dsnw; // DSN for write operations
  28. protected $db_dsnr; // DSN for read operations
  29. protected $db_connected = false; // Already connected ?
  30. protected $db_mode; // Connection mode
  31. protected $db_table_dsn_map = array();
  32. protected $dbh; // Connection handle
  33. protected $dbhs = array();
  34. protected $table_connections = array();
  35. protected $db_error = false;
  36. protected $db_error_msg = '';
  37. protected $conn_failure = false;
  38. protected $db_index = 0;
  39. protected $last_result;
  40. protected $tables;
  41. protected $variables;
  42. protected $options = array(
  43. // column/table quotes
  44. 'identifier_start' => '"',
  45. 'identifier_end' => '"',
  46. );
  47. const DEBUG_LINE_LENGTH = 4096;
  48. /**
  49. * Factory, returns driver-specific instance of the class
  50. *
  51. * @param string $db_dsnw DSN for read/write operations
  52. * @param string $db_dsnr Optional DSN for read only operations
  53. * @param bool $pconn Enables persistent connections
  54. *
  55. * @return rcube_db Object instance
  56. */
  57. public static function factory($db_dsnw, $db_dsnr = '', $pconn = false)
  58. {
  59. $driver = strtolower(substr($db_dsnw, 0, strpos($db_dsnw, ':')));
  60. $driver_map = array(
  61. 'sqlite2' => 'sqlite',
  62. 'sybase' => 'mssql',
  63. 'dblib' => 'mssql',
  64. 'mysqli' => 'mysql',
  65. );
  66. $driver = isset($driver_map[$driver]) ? $driver_map[$driver] : $driver;
  67. $class = "rcube_db_$driver";
  68. if (!$driver || !class_exists($class)) {
  69. rcube::raise_error(array('code' => 600, 'type' => 'db',
  70. 'line' => __LINE__, 'file' => __FILE__,
  71. 'message' => "Configuration error. Unsupported database driver: $driver"),
  72. true, true);
  73. }
  74. return new $class($db_dsnw, $db_dsnr, $pconn);
  75. }
  76. /**
  77. * Object constructor
  78. *
  79. * @param string $db_dsnw DSN for read/write operations
  80. * @param string $db_dsnr Optional DSN for read only operations
  81. * @param bool $pconn Enables persistent connections
  82. */
  83. public function __construct($db_dsnw, $db_dsnr = '', $pconn = false)
  84. {
  85. if (empty($db_dsnr)) {
  86. $db_dsnr = $db_dsnw;
  87. }
  88. $this->db_dsnw = $db_dsnw;
  89. $this->db_dsnr = $db_dsnr;
  90. $this->db_pconn = $pconn;
  91. $this->db_dsnw_noread = rcube::get_instance()->config->get('db_dsnw_noread', false);
  92. $this->db_dsnw_array = self::parse_dsn($db_dsnw);
  93. $this->db_dsnr_array = self::parse_dsn($db_dsnr);
  94. $this->db_table_dsn_map = array_map(array($this, 'table_name'), rcube::get_instance()->config->get('db_table_dsn', array()));
  95. }
  96. /**
  97. * Connect to specific database
  98. *
  99. * @param array $dsn DSN for DB connections
  100. * @param string $mode Connection mode (r|w)
  101. */
  102. protected function dsn_connect($dsn, $mode)
  103. {
  104. $this->db_error = false;
  105. $this->db_error_msg = null;
  106. // return existing handle
  107. if ($this->dbhs[$mode]) {
  108. $this->dbh = $this->dbhs[$mode];
  109. $this->db_mode = $mode;
  110. return $this->dbh;
  111. }
  112. // Get database specific connection options
  113. $dsn_string = $this->dsn_string($dsn);
  114. $dsn_options = $this->dsn_options($dsn);
  115. if ($this->db_pconn) {
  116. $dsn_options[PDO::ATTR_PERSISTENT] = true;
  117. }
  118. // Connect
  119. try {
  120. // with this check we skip fatal error on PDO object creation
  121. if (!class_exists('PDO', false)) {
  122. throw new Exception('PDO extension not loaded. See http://php.net/manual/en/intro.pdo.php');
  123. }
  124. $this->conn_prepare($dsn);
  125. $dbh = new PDO($dsn_string, $dsn['username'], $dsn['password'], $dsn_options);
  126. // don't throw exceptions or warnings
  127. $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
  128. }
  129. catch (Exception $e) {
  130. $this->db_error = true;
  131. $this->db_error_msg = $e->getMessage();
  132. rcube::raise_error(array('code' => 500, 'type' => 'db',
  133. 'line' => __LINE__, 'file' => __FILE__,
  134. 'message' => $this->db_error_msg), true, false);
  135. return null;
  136. }
  137. $this->dbh = $dbh;
  138. $this->dbhs[$mode] = $dbh;
  139. $this->db_mode = $mode;
  140. $this->db_connected = true;
  141. $this->conn_configure($dsn, $dbh);
  142. }
  143. /**
  144. * Driver-specific preparation of database connection
  145. *
  146. * @param array $dsn DSN for DB connections
  147. */
  148. protected function conn_prepare($dsn)
  149. {
  150. }
  151. /**
  152. * Driver-specific configuration of database connection
  153. *
  154. * @param array $dsn DSN for DB connections
  155. * @param PDO $dbh Connection handler
  156. */
  157. protected function conn_configure($dsn, $dbh)
  158. {
  159. }
  160. /**
  161. * Connect to appropriate database depending on the operation
  162. *
  163. * @param string $mode Connection mode (r|w)
  164. * @param boolean $force Enforce using the given mode
  165. */
  166. public function db_connect($mode, $force = false)
  167. {
  168. // previous connection failed, don't attempt to connect again
  169. if ($this->conn_failure) {
  170. return;
  171. }
  172. // no replication
  173. if ($this->db_dsnw == $this->db_dsnr) {
  174. $mode = 'w';
  175. }
  176. // Already connected
  177. if ($this->db_connected) {
  178. // connected to db with the same or "higher" mode (if allowed)
  179. if ($this->db_mode == $mode || $this->db_mode == 'w' && !$force && !$this->db_dsnw_noread) {
  180. return;
  181. }
  182. }
  183. $dsn = ($mode == 'r') ? $this->db_dsnr_array : $this->db_dsnw_array;
  184. $this->dsn_connect($dsn, $mode);
  185. // use write-master when read-only fails
  186. if (!$this->db_connected && $mode == 'r' && $this->is_replicated()) {
  187. $this->dsn_connect($this->db_dsnw_array, 'w');
  188. }
  189. $this->conn_failure = !$this->db_connected;
  190. }
  191. /**
  192. * Analyze the given SQL statement and select the appropriate connection to use
  193. */
  194. protected function dsn_select($query)
  195. {
  196. // no replication
  197. if ($this->db_dsnw == $this->db_dsnr) {
  198. return 'w';
  199. }
  200. // Read or write ?
  201. $mode = preg_match('/^(select|show|set)/i', $query) ? 'r' : 'w';
  202. // find tables involved in this query
  203. if (preg_match_all('/(?:^|\s)(from|update|into|join)\s+'.$this->options['identifier_start'].'?([a-z0-9._]+)'.$this->options['identifier_end'].'?\s+/i', $query, $matches, PREG_SET_ORDER)) {
  204. foreach ($matches as $m) {
  205. $table = $m[2];
  206. // always use direct mapping
  207. if ($this->db_table_dsn_map[$table]) {
  208. $mode = $this->db_table_dsn_map[$table];
  209. break; // primary table rules
  210. }
  211. else if ($mode == 'r') {
  212. // connected to db with the same or "higher" mode for this table
  213. $db_mode = $this->table_connections[$table];
  214. if ($db_mode == 'w' && !$this->db_dsnw_noread) {
  215. $mode = $db_mode;
  216. }
  217. }
  218. }
  219. // remember mode chosen (for primary table)
  220. $table = $matches[0][2];
  221. $this->table_connections[$table] = $mode;
  222. }
  223. return $mode;
  224. }
  225. /**
  226. * Activate/deactivate debug mode
  227. *
  228. * @param boolean $dbg True if SQL queries should be logged
  229. */
  230. public function set_debug($dbg = true)
  231. {
  232. $this->options['debug_mode'] = $dbg;
  233. }
  234. /**
  235. * Writes debug information/query to 'sql' log file
  236. *
  237. * @param string $query SQL query
  238. */
  239. protected function debug($query)
  240. {
  241. if ($this->options['debug_mode']) {
  242. if (($len = strlen($query)) > self::DEBUG_LINE_LENGTH) {
  243. $diff = $len - self::DEBUG_LINE_LENGTH;
  244. $query = substr($query, 0, self::DEBUG_LINE_LENGTH)
  245. . "... [truncated $diff bytes]";
  246. }
  247. rcube::write_log('sql', '[' . (++$this->db_index) . '] ' . $query . ';');
  248. }
  249. }
  250. /**
  251. * Getter for error state
  252. *
  253. * @param mixed $result Optional query result
  254. *
  255. * @return string Error message
  256. */
  257. public function is_error($result = null)
  258. {
  259. if ($result !== null) {
  260. return $result === false ? $this->db_error_msg : null;
  261. }
  262. return $this->db_error ? $this->db_error_msg : null;
  263. }
  264. /**
  265. * Connection state checker
  266. *
  267. * @return boolean True if in connected state
  268. */
  269. public function is_connected()
  270. {
  271. return !is_object($this->dbh) ? false : $this->db_connected;
  272. }
  273. /**
  274. * Is database replication configured?
  275. *
  276. * @return bool Returns true if dsnw != dsnr
  277. */
  278. public function is_replicated()
  279. {
  280. return !empty($this->db_dsnr) && $this->db_dsnw != $this->db_dsnr;
  281. }
  282. /**
  283. * Get database runtime variables
  284. *
  285. * @param string $varname Variable name
  286. * @param mixed $default Default value if variable is not set
  287. *
  288. * @return mixed Variable value or default
  289. */
  290. public function get_variable($varname, $default = null)
  291. {
  292. // to be implemented by driver class
  293. return $default;
  294. }
  295. /**
  296. * Execute a SQL query
  297. *
  298. * @param string SQL query to execute
  299. * @param mixed Values to be inserted in query
  300. *
  301. * @return number Query handle identifier
  302. */
  303. public function query()
  304. {
  305. $params = func_get_args();
  306. $query = array_shift($params);
  307. // Support one argument of type array, instead of n arguments
  308. if (count($params) == 1 && is_array($params[0])) {
  309. $params = $params[0];
  310. }
  311. return $this->_query($query, 0, 0, $params);
  312. }
  313. /**
  314. * Execute a SQL query with limits
  315. *
  316. * @param string SQL query to execute
  317. * @param int Offset for LIMIT statement
  318. * @param int Number of rows for LIMIT statement
  319. * @param mixed Values to be inserted in query
  320. *
  321. * @return PDOStatement|bool Query handle or False on error
  322. */
  323. public function limitquery()
  324. {
  325. $params = func_get_args();
  326. $query = array_shift($params);
  327. $offset = array_shift($params);
  328. $numrows = array_shift($params);
  329. return $this->_query($query, $offset, $numrows, $params);
  330. }
  331. /**
  332. * Execute a SQL query with limits
  333. *
  334. * @param string $query SQL query to execute
  335. * @param int $offset Offset for LIMIT statement
  336. * @param int $numrows Number of rows for LIMIT statement
  337. * @param array $params Values to be inserted in query
  338. *
  339. * @return PDOStatement|bool Query handle or False on error
  340. */
  341. protected function _query($query, $offset, $numrows, $params)
  342. {
  343. $query = ltrim($query);
  344. $this->db_connect($this->dsn_select($query), true);
  345. // check connection before proceeding
  346. if (!$this->is_connected()) {
  347. return $this->last_result = false;
  348. }
  349. if ($numrows || $offset) {
  350. $query = $this->set_limit($query, $numrows, $offset);
  351. }
  352. // Because in Roundcube we mostly use queries that are
  353. // executed only once, we will not use prepared queries
  354. $pos = 0;
  355. $idx = 0;
  356. if (count($params)) {
  357. while ($pos = strpos($query, '?', $pos)) {
  358. if ($query[$pos+1] == '?') { // skip escaped '?'
  359. $pos += 2;
  360. }
  361. else {
  362. $val = $this->quote($params[$idx++]);
  363. unset($params[$idx-1]);
  364. $query = substr_replace($query, $val, $pos, 1);
  365. $pos += strlen($val);
  366. }
  367. }
  368. }
  369. // replace escaped '?' back to normal, see self::quote()
  370. $query = str_replace('??', '?', $query);
  371. $query = rtrim($query, " \t\n\r\0\x0B;");
  372. $this->debug($query);
  373. // destroy reference to previous result, required for SQLite driver (#1488874)
  374. $this->last_result = null;
  375. $this->db_error_msg = null;
  376. // send query
  377. $result = $this->dbh->query($query);
  378. if ($result === false) {
  379. $result = $this->handle_error($query);
  380. }
  381. $this->last_result = $result;
  382. return $result;
  383. }
  384. /**
  385. * Helper method to handle DB errors.
  386. * This by default logs the error but could be overriden by a driver implementation
  387. *
  388. * @param string Query that triggered the error
  389. * @return mixed Result to be stored and returned
  390. */
  391. protected function handle_error($query)
  392. {
  393. $error = $this->dbh->errorInfo();
  394. if (empty($this->options['ignore_key_errors']) || !in_array($error[0], array('23000', '23505'))) {
  395. $this->db_error = true;
  396. $this->db_error_msg = sprintf('[%s] %s', $error[1], $error[2]);
  397. rcube::raise_error(array('code' => 500, 'type' => 'db',
  398. 'line' => __LINE__, 'file' => __FILE__,
  399. 'message' => $this->db_error_msg . " (SQL Query: $query)"
  400. ), true, false);
  401. }
  402. return false;
  403. }
  404. /**
  405. * Get number of affected rows for the last query
  406. *
  407. * @param mixed $result Optional query handle
  408. *
  409. * @return int Number of (matching) rows
  410. */
  411. public function affected_rows($result = null)
  412. {
  413. if ($result || ($result === null && ($result = $this->last_result))) {
  414. return $result->rowCount();
  415. }
  416. return 0;
  417. }
  418. /**
  419. * Get number of rows for a SQL query
  420. * If no query handle is specified, the last query will be taken as reference
  421. *
  422. * @param mixed $result Optional query handle
  423. * @return mixed Number of rows or false on failure
  424. * @deprecated This method shows very poor performance and should be avoided.
  425. */
  426. public function num_rows($result = null)
  427. {
  428. if ($result || ($result === null && ($result = $this->last_result))) {
  429. // repeat query with SELECT COUNT(*) ...
  430. if (preg_match('/^SELECT\s+(?:ALL\s+|DISTINCT\s+)?(?:.*?)\s+FROM\s+(.*)$/ims', $result->queryString, $m)) {
  431. $query = $this->dbh->query('SELECT COUNT(*) FROM ' . $m[1], PDO::FETCH_NUM);
  432. return $query ? intval($query->fetchColumn(0)) : false;
  433. }
  434. else {
  435. $num = count($result->fetchAll());
  436. $result->execute(); // re-execute query because there's no seek(0)
  437. return $num;
  438. }
  439. }
  440. return false;
  441. }
  442. /**
  443. * Get last inserted record ID
  444. *
  445. * @param string $table Table name (to find the incremented sequence)
  446. *
  447. * @return mixed ID or false on failure
  448. */
  449. public function insert_id($table = '')
  450. {
  451. if (!$this->db_connected || $this->db_mode == 'r') {
  452. return false;
  453. }
  454. if ($table) {
  455. // resolve table name
  456. $table = $this->table_name($table);
  457. }
  458. $id = $this->dbh->lastInsertId($table);
  459. return $id;
  460. }
  461. /**
  462. * Get an associative array for one row
  463. * If no query handle is specified, the last query will be taken as reference
  464. *
  465. * @param mixed $result Optional query handle
  466. *
  467. * @return mixed Array with col values or false on failure
  468. */
  469. public function fetch_assoc($result = null)
  470. {
  471. return $this->_fetch_row($result, PDO::FETCH_ASSOC);
  472. }
  473. /**
  474. * Get an index array for one row
  475. * If no query handle is specified, the last query will be taken as reference
  476. *
  477. * @param mixed $result Optional query handle
  478. *
  479. * @return mixed Array with col values or false on failure
  480. */
  481. public function fetch_array($result = null)
  482. {
  483. return $this->_fetch_row($result, PDO::FETCH_NUM);
  484. }
  485. /**
  486. * Get col values for a result row
  487. *
  488. * @param mixed $result Optional query handle
  489. * @param int $mode Fetch mode identifier
  490. *
  491. * @return mixed Array with col values or false on failure
  492. */
  493. protected function _fetch_row($result, $mode)
  494. {
  495. if ($result || ($result === null && ($result = $this->last_result))) {
  496. return $result->fetch($mode);
  497. }
  498. return false;
  499. }
  500. /**
  501. * Adds LIMIT,OFFSET clauses to the query
  502. *
  503. * @param string $query SQL query
  504. * @param int $limit Number of rows
  505. * @param int $offset Offset
  506. *
  507. * @return string SQL query
  508. */
  509. protected function set_limit($query, $limit = 0, $offset = 0)
  510. {
  511. if ($limit) {
  512. $query .= ' LIMIT ' . intval($limit);
  513. }
  514. if ($offset) {
  515. $query .= ' OFFSET ' . intval($offset);
  516. }
  517. return $query;
  518. }
  519. /**
  520. * Returns list of tables in a database
  521. *
  522. * @return array List of all tables of the current database
  523. */
  524. public function list_tables()
  525. {
  526. // get tables if not cached
  527. if ($this->tables === null) {
  528. $q = $this->query('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME');
  529. if ($q) {
  530. $this->tables = $q->fetchAll(PDO::FETCH_COLUMN, 0);
  531. }
  532. else {
  533. $this->tables = array();
  534. }
  535. }
  536. return $this->tables;
  537. }
  538. /**
  539. * Returns list of columns in database table
  540. *
  541. * @param string $table Table name
  542. *
  543. * @return array List of table cols
  544. */
  545. public function list_cols($table)
  546. {
  547. $q = $this->query('SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?',
  548. array($table));
  549. if ($q) {
  550. return $q->fetchAll(PDO::FETCH_COLUMN, 0);
  551. }
  552. return array();
  553. }
  554. /**
  555. * Formats input so it can be safely used in a query
  556. *
  557. * @param mixed $input Value to quote
  558. * @param string $type Type of data (integer, bool, ident)
  559. *
  560. * @return string Quoted/converted string for use in query
  561. */
  562. public function quote($input, $type = null)
  563. {
  564. // handle int directly for better performance
  565. if ($type == 'integer' || $type == 'int') {
  566. return intval($input);
  567. }
  568. if (is_null($input)) {
  569. return 'NULL';
  570. }
  571. if ($type == 'ident') {
  572. return $this->quote_identifier($input);
  573. }
  574. // create DB handle if not available
  575. if (!$this->dbh) {
  576. $this->db_connect('r');
  577. }
  578. if ($this->dbh) {
  579. $map = array(
  580. 'bool' => PDO::PARAM_BOOL,
  581. 'integer' => PDO::PARAM_INT,
  582. );
  583. $type = isset($map[$type]) ? $map[$type] : PDO::PARAM_STR;
  584. return strtr($this->dbh->quote($input, $type), array('?' => '??')); // escape ?
  585. }
  586. return 'NULL';
  587. }
  588. /**
  589. * Escapes a string so it can be safely used in a query
  590. *
  591. * @param string $str A string to escape
  592. *
  593. * @return string Escaped string for use in a query
  594. */
  595. public function escape($str)
  596. {
  597. if (is_null($str)) {
  598. return 'NULL';
  599. }
  600. return substr($this->quote($str), 1, -1);
  601. }
  602. /**
  603. * Quotes a string so it can be safely used as a table or column name
  604. *
  605. * @param string $str Value to quote
  606. *
  607. * @return string Quoted string for use in query
  608. * @deprecated Replaced by rcube_db::quote_identifier
  609. * @see rcube_db::quote_identifier
  610. */
  611. public function quoteIdentifier($str)
  612. {
  613. return $this->quote_identifier($str);
  614. }
  615. /**
  616. * Escapes a string so it can be safely used in a query
  617. *
  618. * @param string $str A string to escape
  619. *
  620. * @return string Escaped string for use in a query
  621. * @deprecated Replaced by rcube_db::escape
  622. * @see rcube_db::escape
  623. */
  624. public function escapeSimple($str)
  625. {
  626. return $this->escape($str);
  627. }
  628. /**
  629. * Quotes a string so it can be safely used as a table or column name
  630. *
  631. * @param string $str Value to quote
  632. *
  633. * @return string Quoted string for use in query
  634. */
  635. public function quote_identifier($str)
  636. {
  637. $start = $this->options['identifier_start'];
  638. $end = $this->options['identifier_end'];
  639. $name = array();
  640. foreach (explode('.', $str) as $elem) {
  641. $elem = str_replace(array($start, $end), '', $elem);
  642. $name[] = $start . $elem . $end;
  643. }
  644. return implode($name, '.');
  645. }
  646. /**
  647. * Return SQL function for current time and date
  648. *
  649. * @param int $interval Optional interval (in seconds) to add/subtract
  650. *
  651. * @return string SQL function to use in query
  652. */
  653. public function now($interval = 0)
  654. {
  655. if ($interval) {
  656. $add = ' ' . ($interval > 0 ? '+' : '-') . ' INTERVAL ';
  657. $add .= $interval > 0 ? intval($interval) : intval($interval) * -1;
  658. $add .= ' SECOND';
  659. }
  660. return "now()" . $add;
  661. }
  662. /**
  663. * Return list of elements for use with SQL's IN clause
  664. *
  665. * @param array $arr Input array
  666. * @param string $type Type of data (integer, bool, ident)
  667. *
  668. * @return string Comma-separated list of quoted values for use in query
  669. */
  670. public function array2list($arr, $type = null)
  671. {
  672. if (!is_array($arr)) {
  673. return $this->quote($arr, $type);
  674. }
  675. foreach ($arr as $idx => $item) {
  676. $arr[$idx] = $this->quote($item, $type);
  677. }
  678. return implode(',', $arr);
  679. }
  680. /**
  681. * Return SQL statement to convert a field value into a unix timestamp
  682. *
  683. * This method is deprecated and should not be used anymore due to limitations
  684. * of timestamp functions in Mysql (year 2038 problem)
  685. *
  686. * @param string $field Field name
  687. *
  688. * @return string SQL statement to use in query
  689. * @deprecated
  690. */
  691. public function unixtimestamp($field)
  692. {
  693. return "UNIX_TIMESTAMP($field)";
  694. }
  695. /**
  696. * Return SQL statement to convert from a unix timestamp
  697. *
  698. * @param int $timestamp Unix timestamp
  699. *
  700. * @return string Date string in db-specific format
  701. */
  702. public function fromunixtime($timestamp)
  703. {
  704. return date("'Y-m-d H:i:s'", $timestamp);
  705. }
  706. /**
  707. * Return SQL statement for case insensitive LIKE
  708. *
  709. * @param string $column Field name
  710. * @param string $value Search value
  711. *
  712. * @return string SQL statement to use in query
  713. */
  714. public function ilike($column, $value)
  715. {
  716. return $this->quote_identifier($column).' LIKE '.$this->quote($value);
  717. }
  718. /**
  719. * Abstract SQL statement for value concatenation
  720. *
  721. * @return string SQL statement to be used in query
  722. */
  723. public function concat(/* col1, col2, ... */)
  724. {
  725. $args = func_get_args();
  726. if (is_array($args[0])) {
  727. $args = $args[0];
  728. }
  729. return '(' . join(' || ', $args) . ')';
  730. }
  731. /**
  732. * Encodes non-UTF-8 characters in string/array/object (recursive)
  733. *
  734. * @param mixed $input Data to fix
  735. * @param bool $serialized Enable serialization
  736. *
  737. * @return mixed Properly UTF-8 encoded data
  738. */
  739. public static function encode($input, $serialized = false)
  740. {
  741. // use Base64 encoding to workaround issues with invalid
  742. // or null characters in serialized string (#1489142)
  743. if ($serialized) {
  744. return base64_encode(serialize($input));
  745. }
  746. if (is_object($input)) {
  747. foreach (get_object_vars($input) as $idx => $value) {
  748. $input->$idx = self::encode($value);
  749. }
  750. return $input;
  751. }
  752. else if (is_array($input)) {
  753. foreach ($input as $idx => $value) {
  754. $input[$idx] = self::encode($value);
  755. }
  756. return $input;
  757. }
  758. return utf8_encode($input);
  759. }
  760. /**
  761. * Decodes encoded UTF-8 string/object/array (recursive)
  762. *
  763. * @param mixed $input Input data
  764. * @param bool $serialized Enable serialization
  765. *
  766. * @return mixed Decoded data
  767. */
  768. public static function decode($input, $serialized = false)
  769. {
  770. // use Base64 encoding to workaround issues with invalid
  771. // or null characters in serialized string (#1489142)
  772. if ($serialized) {
  773. // Keep backward compatybility where base64 wasn't used
  774. if (strpos(substr($input, 0, 16), ':') !== false) {
  775. return self::decode(@unserialize($input));
  776. }
  777. return @unserialize(base64_decode($input));
  778. }
  779. if (is_object($input)) {
  780. foreach (get_object_vars($input) as $idx => $value) {
  781. $input->$idx = self::decode($value);
  782. }
  783. return $input;
  784. }
  785. else if (is_array($input)) {
  786. foreach ($input as $idx => $value) {
  787. $input[$idx] = self::decode($value);
  788. }
  789. return $input;
  790. }
  791. return utf8_decode($input);
  792. }
  793. /**
  794. * Return correct name for a specific database table
  795. *
  796. * @param string $table Table name
  797. *
  798. * @return string Translated table name
  799. */
  800. public function table_name($table)
  801. {
  802. static $rcube;
  803. if (!$rcube) {
  804. $rcube = rcube::get_instance();
  805. }
  806. // add prefix to the table name if configured
  807. if (($prefix = $rcube->config->get('db_prefix')) && strpos($table, $prefix) !== 0) {
  808. return $prefix . $table;
  809. }
  810. return $table;
  811. }
  812. /**
  813. * Set class option value
  814. *
  815. * @param string $name Option name
  816. * @param mixed $value Option value
  817. */
  818. public function set_option($name, $value)
  819. {
  820. $this->options[$name] = $value;
  821. }
  822. /**
  823. * Set DSN connection to be used for the given table
  824. *
  825. * @param string Table name
  826. * @param string DSN connection ('r' or 'w') to be used
  827. */
  828. public function set_table_dsn($table, $mode)
  829. {
  830. $this->db_table_dsn_map[$this->table_name($table)] = $mode;
  831. }
  832. /**
  833. * MDB2 DSN string parser
  834. *
  835. * @param string $sequence Secuence name
  836. *
  837. * @return array DSN parameters
  838. */
  839. public static function parse_dsn($dsn)
  840. {
  841. if (empty($dsn)) {
  842. return null;
  843. }
  844. // Find phptype and dbsyntax
  845. if (($pos = strpos($dsn, '://')) !== false) {
  846. $str = substr($dsn, 0, $pos);
  847. $dsn = substr($dsn, $pos + 3);
  848. }
  849. else {
  850. $str = $dsn;
  851. $dsn = null;
  852. }
  853. // Get phptype and dbsyntax
  854. // $str => phptype(dbsyntax)
  855. if (preg_match('|^(.+?)\((.*?)\)$|', $str, $arr)) {
  856. $parsed['phptype'] = $arr[1];
  857. $parsed['dbsyntax'] = !$arr[2] ? $arr[1] : $arr[2];
  858. }
  859. else {
  860. $parsed['phptype'] = $str;
  861. $parsed['dbsyntax'] = $str;
  862. }
  863. if (empty($dsn)) {
  864. return $parsed;
  865. }
  866. // Get (if found): username and password
  867. // $dsn => username:password@protocol+hostspec/database
  868. if (($at = strrpos($dsn,'@')) !== false) {
  869. $str = substr($dsn, 0, $at);
  870. $dsn = substr($dsn, $at + 1);
  871. if (($pos = strpos($str, ':')) !== false) {
  872. $parsed['username'] = rawurldecode(substr($str, 0, $pos));
  873. $parsed['password'] = rawurldecode(substr($str, $pos + 1));
  874. }
  875. else {
  876. $parsed['username'] = rawurldecode($str);
  877. }
  878. }
  879. // Find protocol and hostspec
  880. // $dsn => proto(proto_opts)/database
  881. if (preg_match('|^([^(]+)\((.*?)\)/?(.*?)$|', $dsn, $match)) {
  882. $proto = $match[1];
  883. $proto_opts = $match[2] ? $match[2] : false;
  884. $dsn = $match[3];
  885. }
  886. // $dsn => protocol+hostspec/database (old format)
  887. else {
  888. if (strpos($dsn, '+') !== false) {
  889. list($proto, $dsn) = explode('+', $dsn, 2);
  890. }
  891. if ( strpos($dsn, '//') === 0
  892. && strpos($dsn, '/', 2) !== false
  893. && $parsed['phptype'] == 'oci8'
  894. ) {
  895. //oracle's "Easy Connect" syntax:
  896. //"username/password@[//]host[:port][/service_name]"
  897. //e.g. "scott/tiger@//mymachine:1521/oracle"
  898. $proto_opts = $dsn;
  899. $pos = strrpos($proto_opts, '/');
  900. $dsn = substr($proto_opts, $pos + 1);
  901. $proto_opts = substr($proto_opts, 0, $pos);
  902. }
  903. else if (strpos($dsn, '/') !== false) {
  904. list($proto_opts, $dsn) = explode('/', $dsn, 2);
  905. }
  906. else {
  907. $proto_opts = $dsn;
  908. $dsn = null;
  909. }
  910. }
  911. // process the different protocol options
  912. $parsed['protocol'] = (!empty($proto)) ? $proto : 'tcp';
  913. $proto_opts = rawurldecode($proto_opts);
  914. if (strpos($proto_opts, ':') !== false) {
  915. list($proto_opts, $parsed['port']) = explode(':', $proto_opts);
  916. }
  917. if ($parsed['protocol'] == 'tcp') {
  918. $parsed['hostspec'] = $proto_opts;
  919. }
  920. else if ($parsed['protocol'] == 'unix') {
  921. $parsed['socket'] = $proto_opts;
  922. }
  923. // Get dabase if any
  924. // $dsn => database
  925. if ($dsn) {
  926. // /database
  927. if (($pos = strpos($dsn, '?')) === false) {
  928. $parsed['database'] = rawurldecode($dsn);
  929. // /database?param1=value1&param2=value2
  930. }
  931. else {
  932. $parsed['database'] = rawurldecode(substr($dsn, 0, $pos));
  933. $dsn = substr($dsn, $pos + 1);
  934. if (strpos($dsn, '&') !== false) {
  935. $opts = explode('&', $dsn);
  936. }
  937. else { // database?param1=value1
  938. $opts = array($dsn);
  939. }
  940. foreach ($opts as $opt) {
  941. list($key, $value) = explode('=', $opt);
  942. if (!array_key_exists($key, $parsed) || false === $parsed[$key]) {
  943. // don't allow params overwrite
  944. $parsed[$key] = rawurldecode($value);
  945. }
  946. }
  947. }
  948. }
  949. return $parsed;
  950. }
  951. /**
  952. * Returns PDO DSN string from DSN array
  953. *
  954. * @param array $dsn DSN parameters
  955. *
  956. * @return string DSN string
  957. */
  958. protected function dsn_string($dsn)
  959. {
  960. $params = array();
  961. $result = $dsn['phptype'] . ':';
  962. if ($dsn['hostspec']) {
  963. $params[] = 'host=' . $dsn['hostspec'];
  964. }
  965. if ($dsn['port']) {
  966. $params[] = 'port=' . $dsn['port'];
  967. }
  968. if ($dsn['database']) {
  969. $params[] = 'dbname=' . $dsn['database'];
  970. }
  971. if (!empty($params)) {
  972. $result .= implode(';', $params);
  973. }
  974. return $result;
  975. }
  976. /**
  977. * Returns driver-specific connection options
  978. *
  979. * @param array $dsn DSN parameters
  980. *
  981. * @return array Connection options
  982. */
  983. protected function dsn_options($dsn)
  984. {
  985. $result = array();
  986. return $result;
  987. }
  988. }