lib/api lib/Auth lib/functions.php lib/SQL lib/SQL.php
Aleksander Machniak
machniak at kolabsys.com
Mon Feb 24 14:19:17 CET 2014
lib/Auth/LDAP.php | 5
lib/SQL.php | 501 +++++++++++++++++++++++++++----
lib/SQL/mysql.php | 133 ++++++++
lib/api/kolab_api_service_form_value.php | 3
lib/api/kolab_api_service_type.php | 8
lib/functions.php | 2
6 files changed, 584 insertions(+), 68 deletions(-)
New commits:
commit 21b6f160fdb3afec0906082767de317af43d4737
Author: Aleksander Machniak <machniak at kolabsys.com>
Date: Mon Feb 24 14:18:59 2014 +0100
Implemented DB layer based on PDO (Bug #1041)
diff --git a/lib/Auth/LDAP.php b/lib/Auth/LDAP.php
index a4991be..243c4ab 100644
--- a/lib/Auth/LDAP.php
+++ b/lib/Auth/LDAP.php
@@ -1065,8 +1065,9 @@ class LDAP extends Net_LDAP3 {
protected function entry_base_dn($type, $typeid = null)
{
if ($typeid) {
- $db = SQL::get_instance();
- $sql = $db->fetch_assoc($db->query("SELECT `key` FROM {$type}_types WHERE id = ?", $typeid));
+ $db = SQL::get_instance();
+ $query = $db->query("SELECT " . $db->quote_identifier('key') . " FROM {$type}_types WHERE id = ?", array($typeid));
+ $sql = $db->fetch_assoc($query);
// Check if the type has a specific base DN specified.
$base_dn = $this->_subject_base_dn($sql['key'] . '_' . $type, true);
diff --git a/lib/SQL.php b/lib/SQL.php
index 3c41d8e..d4fc59c 100644
--- a/lib/SQL.php
+++ b/lib/SQL.php
@@ -3,7 +3,7 @@
+--------------------------------------------------------------------------+
| This file is part of the Kolab Web Admin Panel |
| |
- | Copyright (C) 2011-2012, Kolab Systems AG |
+ | Copyright (C) 2011-2014, Kolab Systems AG |
| |
| This program is free software: you can redistribute it and/or modify |
| it under the terms of the GNU Affero General Public License as published |
@@ -27,128 +27,509 @@ class SQL
{
static private $instance = array();
- private $sql_uri = "mysql://username:password@hostname/database";
+ private $name;
+ private $conn = false;
+ private $conn_tried = false;
- /* Placeholder for the existing MySQL connection */
- private $conn = false;
+ protected $sql_uri;
+ protected $last_result;
+ protected $db_error;
+ protected $db_error_msg;
- private $conn_tried = false;
- private $sql_stats = array(
- 'queries' => 0,
- 'query_time' => 0,
- 'connections' => 0
+ protected $options = array(
+ // column/table quotes
+ 'identifier_start' => '"',
+ 'identifier_end' => '"',
);
+
/**
* This implements the 'singleton' design pattern
*
* @return SQL The one and only instance associated with $_conn
*/
- static function get_instance($_conn = 'kolab_wap')
+ static function get_instance($conn_name = 'kolab_wap')
{
- if (!isset(self::$instance[$_conn])) {
- self::$instance[$_conn] = new SQL($_conn);
+ if (!array_key_exists($conn_name, self::$instance)) {
+ self::$instance[$conn_name] = SQL::factory($conn_name);
}
- return self::$instance[$_conn];
+ return self::$instance[$conn_name];
}
- public function __construct($_conn = 'kolab_wap')
+ /**
+ * 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');
- $this->name = $_conn;
- $this->sql_uri = $conf->get($_conn, 'sql_uri');
+ $driver = strtolower(substr($dsn, 0, strpos($dsn, ':')));
+ $driver_map = array(
+ 'sqlite2' => 'sqlite',
+ 'sybase' => 'mssql',
+ 'dblib' => 'mssql',
+ 'mysqli' => 'mysql',
+ 'oracle' => 'oci',
+ );
+
+ $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);
}
- public function query()
+ /**
+ * Connects to database
+ */
+ protected function connect()
{
- if (!$this->conn) {
- $this->_connect();
+ if (!$this->conn && !$this->conn_tried) {
+ Log::debug("SQL: Connecting to " . $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());
+ }
}
- $i = 0;
- $start = 0;
- $query = func_get_arg(0);
+ return $this->conn;
+ }
+
+ /**
+ * Driver-specific preparation of database connection
+ */
+ protected function conn_prepare()
+ {
+ }
- while (strlen($query) > $start && ($pos = strpos($query, '?', $start)) !== false) {
- $i++;
- $param = $this->escape(func_get_arg($i));
+ /**
+ * Driver-specific configuration of database connection
+ */
+ protected function conn_configure()
+ {
+ }
- $query = substr_replace($query, $param, $pos, 1);
- $start = $pos + strlen($param) + 1;
+ /**
+ * 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);
}
- Log::trace("SQL: $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;
- $result = mysql_query($query);
+ // send query
+ $result = $this->conn->query($query);
- if (!$result) {
- Log::error('SQL Error: ' . mysql_error($this->conn));
+ if ($result === false) {
+ $result = $this->handle_error($query);
}
+ $this->last_result = $result;
+
return $result;
}
- public function fetch_assoc($result)
+ /**
+ * 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 (!$result) {
- return array();
+ if ($limit) {
+ $query .= ' LIMIT ' . intval($limit);
}
- return mysql_fetch_assoc($result);
+ if ($offset) {
+ $query .= ' OFFSET ' . intval($offset);
+ }
+
+ return $query;
}
- public function affected_rows()
+ /**
+ * Returns a record from query result
+ */
+ public function fetch_assoc($result = null)
{
- return mysql_affected_rows($this->conn);
+ if (!$this->connect()) {
+ return null;
+ }
+
+ return $this->fetch_row($result, PDO::FETCH_ASSOC);
}
- public function last_insert_id()
+ /**
+ * Returns a record from query result
+ */
+ public function fetch_array($result = null)
{
- return mysql_insert_id($this->conn);
+ return $this->fetch_row($result, PDO::FETCH_NUM);
}
- public function escape($str)
+ /**
+ * 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 ($str === null || is_array($str)) {
+ if ($result || ($result === null && ($result = $this->last_result))) {
+ return $result->fetch($mode);
+ }
+
+ return false;
+ }
+
+ public function affected_rows($result)
+ {
+ 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 (!$this->conn) {
- $this->_connect();
+ if ($type == 'ident') {
+ return $this->quote_identifier($input);
}
- return "'" . mysql_real_escape_string($str, $this->conn) . "'";
+ // 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('?' => '??')); // escape ?
}
- public function escape_identifier($str)
+ /**
+ * 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)
{
- $name = array();
+ $start = $this->options['identifier_start'];
+ $end = $this->options['identifier_end'];
+ $name = array();
foreach (explode('.', $str) as $elem) {
- $elem = str_replace('`', '', $elem);
- $name[] = '`' . $elem . '`';
+ $elem = str_replace(array($start, $end), '', $elem);
+ $name[] = $start . $elem . $end;
}
- return implode($name, '.');
+ return implode($name, '.');
}
- private function _connect()
+ /**
+ * MDB2 DSN string parser
+ *
+ * @param string $sequence Secuence name
+ *
+ * @return array DSN parameters
+ */
+ public static function parse_dsn($dsn)
{
- if (!$this->conn && !$this->conn_tried) {
- Log::debug("SQL: Connecting to " . $this->sql_uri);
+ if (empty($dsn)) {
+ return null;
+ }
- $_uri = parse_url($this->sql_uri);
- $this->_username = $_uri['user'];
- $this->_password = $_uri['pass'];
- $this->_hostname = $_uri['host'];
- $this->_database = str_replace('/','',$_uri['path']);
- $this->conn_tried = true;
+ // 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 at 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'];
+ }
- $this->conn = mysql_connect($this->_hostname, $this->_username, $this->_password);
- mysql_select_db($this->_database, $this->conn);
+ 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;
+ }
}
diff --git a/lib/SQL/mysql.php b/lib/SQL/mysql.php
new file mode 100644
index 0000000..9eb7543
--- /dev/null
+++ b/lib/SQL/mysql.php
@@ -0,0 +1,133 @@
+<?php
+
+/*
+ +--------------------------------------------------------------------------+
+ | This file is part of the Kolab Web Admin Panel |
+ | |
+ | Copyright (C) 2011-2014, Kolab Systems AG |
+ | |
+ | This program is free software: you can redistribute it and/or modify |
+ | it under the terms of the GNU Affero General Public License as published |
+ | by the Free Software Foundation, either version 3 of the License, or |
+ | (at your option) any later version. |
+ | |
+ | This program is distributed in the hope that it will be useful, |
+ | but WITHOUT ANY WARRANTY; without even the implied warranty of |
+ | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
+ | GNU Affero General Public License for more details. |
+ | |
+ | You should have received a copy of the GNU Affero General Public License |
+ | along with this program. If not, see <http://www.gnu.org/licenses/> |
+ +--------------------------------------------------------------------------+
+ | Author: Aleksander Machniak <machniak at kolabsys.com> |
+ +--------------------------------------------------------------------------+
+*/
+
+/**
+ * Database independent query interface
+ *
+ * This is a wrapper for the PHP PDO
+ */
+class SQL_mysql extends SQL
+{
+ public $db_provider = 'mysql';
+
+ /**
+ * Object constructor
+ */
+ public function __construct($conn_name, $conn_dsn)
+ {
+ parent::__construct($conn_name, $conn_dsn);
+
+ // SQL identifiers quoting
+ $this->options['identifier_start'] = '`';
+ $this->options['identifier_end'] = '`';
+ }
+
+ /**
+ * Driver-specific configuration of database connection
+ */
+ protected function conn_configure()
+ {
+ if ($this->conn) {
+ $this->conn->query("SET NAMES 'utf8'");
+ }
+ }
+
+ /**
+ * Returns PDO DSN string from DSN array
+ *
+ * @param array $dsn DSN parameters
+ *
+ * @return string Connection string
+ */
+ protected function dsn_string($dsn)
+ {
+ $params = array();
+ $result = 'mysql:';
+
+ if ($dsn['database']) {
+ $params[] = 'dbname=' . $dsn['database'];
+ }
+
+ if ($dsn['hostspec']) {
+ $params[] = 'host=' . $dsn['hostspec'];
+ }
+
+ if ($dsn['port']) {
+ $params[] = 'port=' . $dsn['port'];
+ }
+
+ if ($dsn['socket']) {
+ $params[] = 'unix_socket=' . $dsn['socket'];
+ }
+
+ $params[] = 'charset=utf8';
+
+ 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();
+
+ if (!empty($dsn['key'])) {
+ $result[PDO::MYSQL_ATTR_SSL_KEY] = $dsn['key'];
+ }
+
+ if (!empty($dsn['cipher'])) {
+ $result[PDO::MYSQL_ATTR_SSL_CIPHER] = $dsn['cipher'];
+ }
+
+ if (!empty($dsn['cert'])) {
+ $result[PDO::MYSQL_ATTR_SSL_CERT] = $dsn['cert'];
+ }
+
+ if (!empty($dsn['capath'])) {
+ $result[PDO::MYSQL_ATTR_SSL_CAPATH] = $dsn['capath'];
+ }
+
+ if (!empty($dsn['ca'])) {
+ $result[PDO::MYSQL_ATTR_SSL_CA] = $dsn['ca'];
+ }
+
+ // Always return matching (not affected only) rows count
+ $result[PDO::MYSQL_ATTR_FOUND_ROWS] = true;
+
+ // Enable AUTOCOMMIT mode
+ $result[PDO::ATTR_AUTOCOMMIT] = true;
+
+ return $result;
+ }
+}
diff --git a/lib/api/kolab_api_service_form_value.php b/lib/api/kolab_api_service_form_value.php
index 2e82342..c93e75e 100644
--- a/lib/api/kolab_api_service_form_value.php
+++ b/lib/api/kolab_api_service_form_value.php
@@ -1365,7 +1365,8 @@ class kolab_api_service_form_value extends kolab_api_service
}
$db = SQL::get_instance();
- $result = $db->fetch_assoc($db->query("SELECT option_values FROM options WHERE attribute = ?", $attribute));
+ $query = $db->query("SELECT option_values FROM options WHERE attribute = ?", array($attribute));
+ $result = $db->fetch_assoc($query);
$result = json_decode($result['option_values']);
return array('list' => $result);
diff --git a/lib/api/kolab_api_service_type.php b/lib/api/kolab_api_service_type.php
index 9faeff1..162b29d 100644
--- a/lib/api/kolab_api_service_type.php
+++ b/lib/api/kolab_api_service_type.php
@@ -100,13 +100,13 @@ class kolab_api_service_type extends kolab_api_service
$query['used_for'] = $postdata['used_for'] == 'hosted' ? 'hosted' : null;
}
- $query = array_map(array($this->db, 'escape'), $query);
- $columns = array_map(array($this->db, 'escape_identifier'), array_keys($query));
+ $query = array_map(array($this->db, 'quote'), $query);
+ $columns = array_map(array($this->db, 'quote_identifier'), array_keys($query));
$this->db->query("INSERT INTO {$type}_types"
. " (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $query) . ")");
- if (!($id = $this->db->last_insert_id())) {
+ if (!($id = $this->db->insert_id())) {
return false;
}
@@ -189,7 +189,7 @@ class kolab_api_service_type extends kolab_api_service
}
foreach ($query as $idx => $value) {
- $query[$idx] = $this->db->escape_identifier($idx) . " = " . $this->db->escape($value);
+ $query[$idx] = $this->db->quote_identifier($idx) . " = " . $this->db->quote($value);
}
$result = $this->db->query("UPDATE {$type}_types SET "
diff --git a/lib/functions.php b/lib/functions.php
index 016f1f3..62e368e 100644
--- a/lib/functions.php
+++ b/lib/functions.php
@@ -63,7 +63,7 @@ mb_internal_encoding(KADM_CHARSET);
// register autoloader
function class_autoloader($classname)
{
- $classname = preg_replace('/(Net|HTTP)_(.+)/', "\\1/\\2", $classname);
+ $classname = preg_replace('/(Net|HTTP|SQL)_(.+)/', "\\1/\\2", $classname);
if ($fp = @fopen("$classname.php", 'r', true)) {
include_once "$classname.php";
More information about the commits
mailing list