diff --git a/lib/SQL.php b/lib/SQL.php index 102f59d..796f106 100644 --- a/lib/SQL.php +++ b/lib/SQL.php @@ -1,585 +1,590 @@ | +--------------------------------------------------------------------------+ | Author: Aleksander Machniak | | Author: Jeroen van Meeuwen | +--------------------------------------------------------------------------+ */ class SQL { static private $instance = array(); private $name; private $conn = false; private $conn_tried = false; protected $sql_uri; protected $last_result; protected $db_error; protected $db_error_msg; protected $options = array( // column/table quotes 'identifier_start' => '"', 'identifier_end' => '"', ); const DEFAULT_QUOTE = '`'; /** * This implements the 'singleton' design pattern * * @return SQL The one and only instance associated with $_conn */ static function get_instance($conn_name = 'kolab_wap') { if (!array_key_exists($conn_name, self::$instance)) { self::$instance[$conn_name] = SQL::factory($conn_name); } return self::$instance[$conn_name]; } /** * Class constructor */ public function __construct($conn_name, $conn_dsn) { $this->name = $conn_name; $this->sql_uri = $conn_dsn; } /** * Factory, returns driver-specific instance of the class * * @return SQL Object instance */ public static function factory($conn_name = 'kolab_wap') { $conf = Conf::get_instance(); $dsn = $conf->get($conn_name, 'sql_uri'); + if (empty($dsn)) { + Log::error("Configuration error. Empty DSN ($conn_name/sql_uri)"); + exit; + } + $driver = strtolower(substr($dsn, 0, strpos($dsn, ':'))); $driver_map = array( 'sqlite2' => 'sqlite', 'sybase' => 'mssql', 'dblib' => 'mssql', 'mysqli' => 'mysql', 'oci' => 'oracle', 'oci8' => 'oracle', ); $driver = isset($driver_map[$driver]) ? $driver_map[$driver] : $driver; $class = "SQL_$driver"; if (!$driver || !class_exists($class)) { Log::error("Configuration error. Unsupported database driver: $driver"); exit; } return new $class($conn_name, $dsn); } /** * Connects to database */ protected function connect() { if (!$this->conn && !$this->conn_tried) { $passwd_regex = '|^([a-zA-Z0-9]+://[^:]+:)[^@]+|'; Log::debug("SQL: Connecting to " . preg_replace($passwd_regex, '\\1***', $this->sql_uri)); $this->conn_tried = true; $dsn = self::parse_dsn($this->sql_uri); // Get database specific connection options $dsn_string = $this->dsn_string($dsn); $dsn_options = $this->dsn_options($dsn); // Connect try { // with this check we skip fatal error on PDO object creation if (!class_exists('PDO', false)) { throw new Exception('PDO extension not loaded. See http://php.net/manual/en/intro.pdo.php'); } $this->conn_prepare(); $this->conn = new PDO($dsn_string, $dsn['username'], $dsn['password'], $dsn_options); // don't throw exceptions or warnings $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); $this->conn_configure(); } catch (Exception $e) { Log::error('DB Error: ' . $e->getMessage()); } } return $this->conn; } /** * Driver-specific preparation of database connection */ protected function conn_prepare() { } /** * Driver-specific configuration of database connection */ protected function conn_configure() { } /** * Execute a SQL query with limits * * @param string $query SQL query to execute * @param array $params Values to be inserted in query * @param int $offset Offset for LIMIT statement * @param int $numrows Number of rows for LIMIT statement * * @return PDOStatement|bool Query handle or False on error */ public function query($query, $params = array(), $offset = null, $numrows = null) { if (!$this->connect()) { return $this->last_result = false; } $query = ltrim($query); if ($numrows || $offset) { $query = $this->set_limit($query, $numrows, $offset); } // replace self::DEFAULT_QUOTE with driver-specific quoting $query = $this->query_parse($query); $pos = 0; $idx = 0; if (count($params)) { while ($pos = strpos($query, '?', $pos)) { if ($query[$pos+1] == '?') { // skip escaped '?' $pos += 2; } else { $val = $this->quote($params[$idx++]); unset($params[$idx-1]); $query = substr_replace($query, $val, $pos, 1); $pos += strlen($val); } } } // replace escaped '?' back to normal, see self::quote() $query = str_replace('??', '?', $query); $query = rtrim($query, " \t\n\r\0\x0B;"); Log::debug('SQL: ' . $query); // destroy reference to previous result, required for SQLite driver $this->last_result = null; $this->db_error_msg = null; // send query $result = $this->conn->query($query); if ($result === false) { $result = $this->handle_error($query); } $this->last_result = $result; return $result; } /** * Parse SQL query and replace identifier quoting * * @param string $query SQL query * * @return string SQL query */ protected function query_parse($query) { $start = $this->options['identifier_start']; $end = $this->options['identifier_end']; $quote = self::DEFAULT_QUOTE; if ($start == $quote) { return $query; } $pos = 0; $in = false; while ($pos = strpos($query, $quote, $pos)) { if ($query[$pos+1] == $quote) { // skip escaped quote $pos += 2; } else { if ($in) { $q = $end; $in = false; } else { $q = $start; $in = true; } $query = substr_replace($query, $q, $pos, 1); $pos++; } } // replace escaped quote back to normal, see self::quote() $query = str_replace($quote.$quote, $quote, $query); return $query; } /** * Helper method to handle DB errors. * This by default logs the error but could be overriden by a driver implementation * * @param string Query that triggered the error * @return mixed Result to be stored and returned */ protected function handle_error($query) { $error = $this->conn->errorInfo(); if (empty($this->options['ignore_key_errors']) || !in_array($error[0], array('23000', '23505'))) { $this->db_error = true; $this->db_error_msg = sprintf('[%s] %s', $error[1], $error[2]); Log::error($this->db_error_msg . " (SQL Query: $query)"); } return false; } /** * Adds LIMIT,OFFSET clauses to the query * * @param string $query SQL query * @param int $limit Number of rows * @param int $offset Offset * * @return string SQL query */ protected function set_limit($query, $limit = 0, $offset = 0) { if ($limit) { $query .= ' LIMIT ' . intval($limit); } if ($offset) { $query .= ' OFFSET ' . intval($offset); } return $query; } /** * Returns a record from query result */ public function fetch_assoc($result = null) { return $this->fetch_row($result, PDO::FETCH_ASSOC); } /** * Returns a record from query result */ public function fetch_array($result = null) { return $this->fetch_row($result, PDO::FETCH_NUM); } /** * Get col values for a result row * * @param mixed $result Optional query handle * @param int $mode Fetch mode identifier * * @return mixed Array with col values or false on failure */ protected function fetch_row($result, $mode) { if ($result || ($result === null && ($result = $this->last_result))) { return $result->fetch($mode); } return false; } public function affected_rows($result = null) { if ($result || ($result === null && ($result = $this->last_result))) { return $result->rowCount(); } return 0; } /** * Returns ID of last inserted record */ public function insert_id($table = null) { if (!$this->connect()) { return null; } return $this->conn->lastInsertId($table); } /** * Formats input so it can be safely used in a query * * @param mixed $input Value to quote * @param string $type Type of data (integer, bool, ident) * * @return string Quoted/converted string for use in query */ public function quote($input, $type = null) { // handle int directly for better performance if ($type == 'integer' || $type == 'int') { return intval($input); } if (is_null($input)) { return 'NULL'; } if ($type == 'ident') { return $this->quote_identifier($input); } // create DB handle if not available if (!$this->connect()) { return 'NULL'; } $map = array( 'bool' => PDO::PARAM_BOOL, 'integer' => PDO::PARAM_INT, ); $type = isset($map[$type]) ? $map[$type] : PDO::PARAM_STR; return strtr($this->conn->quote($input, $type), array( '?' => '??', self::DEFAULT_QUOTE => self::DEFAULT_QUOTE.self::DEFAULT_QUOTE )); } /** * Quotes a string so it can be safely used as a table or column name * * @param string $str Value to quote * * @return string Quoted string for use in query */ public function quote_identifier($str) { $start = $this->options['identifier_start']; $end = $this->options['identifier_end']; $name = array(); foreach (explode('.', $str) as $elem) { $elem = str_replace(array($start, $end), '', $elem); $name[] = $start . $elem . $end; } return implode($name, '.'); } /** * MDB2 DSN string parser * * @param string $sequence Secuence name * * @return array DSN parameters */ public static function parse_dsn($dsn) { if (empty($dsn)) { return null; } // Find phptype and dbsyntax if (($pos = strpos($dsn, '://')) !== false) { $str = substr($dsn, 0, $pos); $dsn = substr($dsn, $pos + 3); } else { $str = $dsn; $dsn = null; } // Get phptype and dbsyntax // $str => phptype(dbsyntax) if (preg_match('|^(.+?)\((.*?)\)$|', $str, $arr)) { $parsed['phptype'] = $arr[1]; $parsed['dbsyntax'] = !$arr[2] ? $arr[1] : $arr[2]; } else { $parsed['phptype'] = $str; $parsed['dbsyntax'] = $str; } if (empty($dsn)) { return $parsed; } // Get (if found): username and password // $dsn => username:password@protocol+hostspec/database if (($at = strrpos($dsn,'@')) !== false) { $str = substr($dsn, 0, $at); $dsn = substr($dsn, $at + 1); if (($pos = strpos($str, ':')) !== false) { $parsed['username'] = rawurldecode(substr($str, 0, $pos)); $parsed['password'] = rawurldecode(substr($str, $pos + 1)); } else { $parsed['username'] = rawurldecode($str); } } // Find protocol and hostspec // $dsn => proto(proto_opts)/database if (preg_match('|^([^(]+)\((.*?)\)/?(.*?)$|', $dsn, $match)) { $proto = $match[1]; $proto_opts = $match[2] ? $match[2] : false; $dsn = $match[3]; } // $dsn => protocol+hostspec/database (old format) else { if (strpos($dsn, '+') !== false) { list($proto, $dsn) = explode('+', $dsn, 2); } if (strpos($dsn, '/') !== false) { list($proto_opts, $dsn) = explode('/', $dsn, 2); } else { $proto_opts = $dsn; $dsn = null; } } // process the different protocol options $parsed['protocol'] = (!empty($proto)) ? $proto : 'tcp'; $proto_opts = rawurldecode($proto_opts); if (strpos($proto_opts, ':') !== false) { list($proto_opts, $parsed['port']) = explode(':', $proto_opts); } if ($parsed['protocol'] == 'tcp') { $parsed['hostspec'] = $proto_opts; } else if ($parsed['protocol'] == 'unix') { $parsed['socket'] = $proto_opts; } // Get dabase if any // $dsn => database if ($dsn) { // /database if (($pos = strpos($dsn, '?')) === false) { $parsed['database'] = rawurldecode($dsn); } // /database?param1=value1¶m2=value2 else { $parsed['database'] = rawurldecode(substr($dsn, 0, $pos)); $dsn = substr($dsn, $pos + 1); if (strpos($dsn, '&') !== false) { $opts = explode('&', $dsn); } else { // database?param1=value1 $opts = array($dsn); } foreach ($opts as $opt) { list($key, $value) = explode('=', $opt); if (!array_key_exists($key, $parsed) || false === $parsed[$key]) { // don't allow params overwrite $parsed[$key] = rawurldecode($value); } } } } return $parsed; } /** * Returns PDO DSN string from DSN array * * @param array $dsn DSN parameters * * @return string DSN string */ protected function dsn_string($dsn) { $params = array(); $result = $dsn['phptype'] . ':'; if ($dsn['hostspec']) { $params[] = 'host=' . $dsn['hostspec']; } if ($dsn['port']) { $params[] = 'port=' . $dsn['port']; } if ($dsn['database']) { $params[] = 'dbname=' . $dsn['database']; } if (!empty($params)) { $result .= implode(';', $params); } return $result; } /** * Returns driver-specific connection options * * @param array $dsn DSN parameters * * @return array Connection options */ protected function dsn_options($dsn) { $result = array(); return $result; } }