Page MenuHomePhorge
Files F117888364

SQL.php
No OneTemporary
Actions

Authored By
Unknown
Size
16 KB
Referenced Files
None
Subscribers
None

SQL.php
View Options

<?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@kolabsys.com> |
| Author: Jeroen van Meeuwen <vanmeeuwen@kolabsys.com> |
+--------------------------------------------------------------------------+
*/
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');
$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&param2=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;
}
}

File Metadata

Mime Type
text/x-php
Expires
Mon, Apr 6, 3:18 AM (3 w, 3 d ago)
Storage Engine
local-disk
Storage Format
Raw Data
Storage Handle
55/28/d6d563c092481dfb688f81e8ad0b
Default Alt Text
SQL.php (16 KB)

Event Timeline