Use consistent column/table quoting in sql queries

Aleksander Machniak

2014-09-12 34a0902089a410d1f7dda78d1f8b0771333c09df

 program/lib/Roundcube/rcube_db.php

@@ -32,6 +32,8 @@
    protected $db_connected = false;  // Already connected ?
    protected $db_mode;               // Connection mode
    protected $dbh;                   // Connection handle
    protected $dbhs = array();
    protected $table_connections = array();

    protected $db_error     = false;
    protected $db_error_msg = '';
@@ -47,6 +49,8 @@
        'identifier_end'   => '"',
    );

    const DEBUG_LINE_LENGTH = 4096;
    const DEFAULT_QUOTE     = '`';

    /**
     * Factory, returns driver-specific instance of the class
@@ -65,6 +69,7 @@
            'sybase'  => 'mssql',
            'dblib'   => 'mssql',
            'mysqli'  => 'mysql',
            'oci'     => 'oracle',
        );

        $driver = isset($driver_map[$driver]) ? $driver_map[$driver] : $driver;
@@ -100,35 +105,36 @@
        $this->db_dsnw_array = self::parse_dsn($db_dsnw);
        $this->db_dsnr_array = self::parse_dsn($db_dsnr);

        // Initialize driver class
        $this->init();
    }
        $config = rcube::get_instance()->config;

    /**
     * Initialization of the object with driver specific code
     */
    protected function init()
    {
        // To be used by driver classes
        $this->options['table_prefix']  = $config->get('db_prefix');
        $this->options['dsnw_noread']   = $config->get('db_dsnw_noread', false);
        $this->options['table_dsn_map'] = array_map(array($this, 'table_name'), $config->get('db_table_dsn', array()));
    }

    /**
     * Connect to specific database
     *
     * @param array $dsn DSN for DB connections
     *
     * @return PDO database handle
     * @param array  $dsn  DSN for DB connections
     * @param string $mode Connection mode (r|w)
     */
    protected function dsn_connect($dsn)
    protected function dsn_connect($dsn, $mode)
    {
        $this->db_error     = false;
        $this->db_error_msg = null;

        // return existing handle
        if ($this->dbhs[$mode]) {
            $this->dbh = $this->dbhs[$mode];
            $this->db_mode = $mode;
            return $this->dbh;
        }

        // Get database specific connection options
        $dsn_string  = $this->dsn_string($dsn);
        $dsn_options = $this->dsn_options($dsn);

        if ($db_pconn) {
        if ($this->db_pconn) {
            $dsn_options[PDO::ATTR_PERSISTENT] = true;
        }

@@ -157,9 +163,11 @@
            return null;
        }

        $this->dbh          = $dbh;
        $this->dbhs[$mode]  = $dbh;
        $this->db_mode      = $mode;
        $this->db_connected = true;
        $this->conn_configure($dsn, $dbh);

        return $dbh;
    }

    /**
@@ -182,21 +190,12 @@
    }

    /**
     * Driver-specific database character set setting
     *
     * @param string $charset Character set name
     */
    protected function set_charset($charset)
    {
        $this->query("SET NAMES 'utf8'");
    }

    /**
     * Connect to appropriate database depending on the operation
     *
     * @param string $mode Connection mode (r|w)
     * @param boolean $force Enforce using the given mode
     */
    public function db_connect($mode)
    public function db_connect($mode, $force = false)
    {
        // previous connection failed, don't attempt to connect again
        if ($this->conn_failure) {
@@ -210,31 +209,65 @@

        // Already connected
        if ($this->db_connected) {
            // connected to db with the same or "higher" mode
            if ($this->db_mode == 'w' || $this->db_mode == $mode) {
            // connected to db with the same or "higher" mode (if allowed)
            if ($this->db_mode == $mode || $this->db_mode == 'w' && !$force && !$this->options['dsnw_noread']) {
                return;
            }
        }

        $dsn = ($mode == 'r') ? $this->db_dsnr_array : $this->db_dsnw_array;

        $this->dbh          = $this->dsn_connect($dsn);
        $this->db_connected = is_object($this->dbh);
        $this->dsn_connect($dsn, $mode);

        // use write-master when read-only fails
        if (!$this->db_connected && $mode == 'r' && $this->is_replicated()) {
            $mode = 'w';
            $this->dbh          = $this->dsn_connect($this->db_dsnw_array);
            $this->db_connected = is_object($this->dbh);
            $this->dsn_connect($this->db_dsnw_array, 'w');
        }

        if ($this->db_connected) {
            $this->db_mode = $mode;
            $this->set_charset('utf8');
        $this->conn_failure = !$this->db_connected;
    }

    /**
     * Analyze the given SQL statement and select the appropriate connection to use
     */
    protected function dsn_select($query)
    {
        // no replication
        if ($this->db_dsnw == $this->db_dsnr) {
            return 'w';
        }
        else {
            $this->conn_failure = true;

        // Read or write ?
        $mode = preg_match('/^(select|show|set)/i', $query) ? 'r' : 'w';

        $start = '[' . $this->options['identifier_start'] . self::DEFAULT_QUOTE . ']';
        $end   = '[' . $this->options['identifier_end']   . self::DEFAULT_QUOTE . ']';
        $regex = '/(?:^|\s)(from|update|into|join)\s+'.$start.'?([a-z0-9._]+)'.$end.'?\s+/i';

        // find tables involved in this query
        if (preg_match_all($regex, $query, $matches, PREG_SET_ORDER)) {
            foreach ($matches as $m) {
                $table = $m[2];

                // always use direct mapping
                if ($this->options['table_dsn_map'][$table]) {
                    $mode = $this->options['table_dsn_map'][$table];
                    break;  // primary table rules
                }
                else if ($mode == 'r') {
                    // connected to db with the same or "higher" mode for this table
                    $db_mode = $this->table_connections[$table];
                    if ($db_mode == 'w' && !$this->options['dsnw_noread']) {
                        $mode = $db_mode;
                    }
                }
            }

            // remember mode chosen (for primary table)
            $table = $matches[0][2];
            $this->table_connections[$table] = $mode;
        }

        return $mode;
    }

    /**
@@ -255,6 +288,11 @@
    protected function debug($query)
    {
        if ($this->options['debug_mode']) {
            if (($len = strlen($query)) > self::DEBUG_LINE_LENGTH) {
                $diff  = $len - self::DEBUG_LINE_LENGTH;
                $query = substr($query, 0, self::DEBUG_LINE_LENGTH)
                    . "... [truncated $diff bytes]";
            }
            rcube::write_log('sql', '[' . (++$this->db_index) . '] ' . $query . ';');
        }
    }
@@ -362,10 +400,9 @@
     */
    protected function _query($query, $offset, $numrows, $params)
    {
        // Read or write ?
        $mode = preg_match('/^(select|show)/i', ltrim($query)) ? 'r' : 'w';
        $query = ltrim($query);

        $this->db_connect($mode);
        $this->db_connect($this->dsn_select($query), true);

        // check connection before proceeding
        if (!$this->is_connected()) {
@@ -376,28 +413,33 @@
            $query = $this->set_limit($query, $numrows, $offset);
        }

        $params = (array) $params;
        // replace self::DEFAULT_QUOTE with driver-specific quoting
        $query = $this->query_parse($query);

        // Because in Roundcube we mostly use queries that are
        // executed only once, we will not use prepared queries
        $pos = 0;
        $idx = 0;

        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);
        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
        $query = rtrim(strtr($query, array('??' => '?')), ';');
        // replace escaped '?' back to normal, see self::quote()
        $query = str_replace('??', '?', $query);
        $query = rtrim($query, " \t\n\r\0\x0B;");

        // log query
        $this->debug($query);

        // destroy reference to previous result, required for SQLite driver (#1488874)
@@ -405,21 +447,84 @@
        $this->db_error_msg = null;

        // send query
        $query = $this->dbh->query($query);
        $result = $this->dbh->query($query);

        if ($query === false) {
            $error = $this->dbh->errorInfo();
        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->dbh->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]);

            rcube::raise_error(array('code' => 500, 'type' => 'db',
                'line' => __LINE__, 'file' => __FILE__,
                'message' => $this->db_error_msg), true, false);
                'message' => $this->db_error_msg . " (SQL Query: $query)"
                ), true, false);
        }

        $this->last_result = $query;

        return $query;
        return false;
    }

    /**
@@ -436,6 +541,32 @@
        }

        return 0;
    }

    /**
     * Get number of rows for a SQL query
     * If no query handle is specified, the last query will be taken as reference
     *
     * @param mixed $result Optional query handle
     * @return mixed   Number of rows or false on failure
     * @deprecated This method shows very poor performance and should be avoided.
     */
    public function num_rows($result = null)
    {
        if ($result || ($result === null && ($result = $this->last_result))) {
            // repeat query with SELECT COUNT(*) ...
            if (preg_match('/^SELECT\s+(?:ALL\s+|DISTINCT\s+)?(?:.*?)\s+FROM\s+(.*)$/ims', $result->queryString, $m)) {
                $query = $this->dbh->query('SELECT COUNT(*) FROM ' . $m[1], PDO::FETCH_NUM);
                return $query ? intval($query->fetchColumn(0)) : false;
            }
            else {
                $num = count($result->fetchAll());
                $result->execute();  // re-execute query because there's no seek(0)
                return $num;
            }
        }

        return false;
    }

    /**
@@ -571,7 +702,7 @@
     * Formats input so it can be safely used in a query
     *
     * @param mixed  $input Value to quote
     * @param string $type  Type of data
     * @param string $type  Type of data (integer, bool, ident)
     *
     * @return string Quoted/converted string for use in query
     */
@@ -586,6 +717,10 @@
            return 'NULL';
        }

        if ($type == 'ident') {
            return $this->quote_identifier($input);
        }

        // create DB handle if not available
        if (!$this->dbh) {
            $this->db_connect('r');
@@ -596,11 +731,32 @@
                'bool'    => PDO::PARAM_BOOL,
                'integer' => PDO::PARAM_INT,
            );

            $type = isset($map[$type]) ? $map[$type] : PDO::PARAM_STR;
            return strtr($this->dbh->quote($input, $type), array('?' => '??'));  // escape ?

            return strtr($this->dbh->quote($input, $type),
                // escape ? and `
                array('?' => '??', self::DEFAULT_QUOTE => self::DEFAULT_QUOTE.self::DEFAULT_QUOTE)
            );
        }

        return 'NULL';
    }

    /**
     * Escapes a string so it can be safely used in a query
     *
     * @param string $str A string to escape
     *
     * @return string Escaped string for use in a query
     */
    public function escape($str)
    {
        if (is_null($str)) {
            return 'NULL';
        }

        return substr($this->quote($str), 1, -1);
    }

    /**
@@ -615,6 +771,20 @@
    public function quoteIdentifier($str)
    {
        return $this->quote_identifier($str);
    }

    /**
     * Escapes a string so it can be safely used in a query
     *
     * @param string $str A string to escape
     *
     * @return string Escaped string for use in a query
     * @deprecated    Replaced by rcube_db::escape
     * @see           rcube_db::escape
     */
    public function escapeSimple($str)
    {
        return $this->escape($str);
    }

    /**
@@ -635,24 +805,32 @@
            $name[] = $start . $elem . $end;
        }

        return  implode($name, '.');
        return implode($name, '.');
    }

    /**
     * Return SQL function for current time and date
     *
     * @param int $interval Optional interval (in seconds) to add/subtract
     *
     * @return string SQL function to use in query
     */
    public function now()
    public function now($interval = 0)
    {
        return "now()";
        if ($interval) {
            $add = ' ' . ($interval > 0 ? '+' : '-') . ' INTERVAL ';
            $add .= $interval > 0 ? intval($interval) : intval($interval) * -1;
            $add .= ' SECOND';
        }

        return "now()" . $add;
    }

    /**
     * Return list of elements for use with SQL's IN clause
     *
     * @param array  $arr  Input array
     * @param string $type Type of data
     * @param string $type Type of data (integer, bool, ident)
     *
     * @return string Comma-separated list of quoted values for use in query
     */
@@ -728,12 +906,19 @@
    /**
     * Encodes non-UTF-8 characters in string/array/object (recursive)
     *
     * @param mixed $input Data to fix
     * @param mixed $input      Data to fix
     * @param bool  $serialized Enable serialization
     *
     * @return mixed Properly UTF-8 encoded data
     */
    public static function encode($input)
    public static function encode($input, $serialized = false)
    {
        // use Base64 encoding to workaround issues with invalid
        // or null characters in serialized string (#1489142)
        if ($serialized) {
            return base64_encode(serialize($input));
        }

        if (is_object($input)) {
            foreach (get_object_vars($input) as $idx => $value) {
                $input->$idx = self::encode($value);
@@ -744,6 +929,7 @@
            foreach ($input as $idx => $value) {
                $input[$idx] = self::encode($value);
            }

            return $input;
        }

@@ -753,12 +939,24 @@
    /**
     * Decodes encoded UTF-8 string/object/array (recursive)
     *
     * @param mixed $input Input data
     * @param mixed $input      Input data
     * @param bool  $serialized Enable serialization
     *
     * @return mixed Decoded data
     */
    public static function decode($input)
    public static function decode($input, $serialized = false)
    {
        // use Base64 encoding to workaround issues with invalid
        // or null characters in serialized string (#1489142)
        if ($serialized) {
            // Keep backward compatybility where base64 wasn't used
            if (strpos(substr($input, 0, 16), ':') !== false) {
                return self::decode(@unserialize($input));
            }

            return @unserialize(base64_decode($input));
        }

        if (is_object($input)) {
            foreach (get_object_vars($input) as $idx => $value) {
                $input->$idx = self::decode($value);
@@ -778,22 +976,45 @@
    /**
     * Return correct name for a specific database table
     *
     * @param string $table Table name
     * @param string $table  Table name
     * @param bool   $quoted Quote table identifier
     *
     * @return string Translated table name
     */
    public function table_name($table)
    public function table_name($table, $quoted = false)
    {
        $rcube = rcube::get_instance();
        // add prefix to the table name if configured
        if (($prefix = $this->options['table_prefix']) && strpos($table, $prefix) !== 0) {
            $table = $prefix . $table;
        }

        // return table name if configured
        $config_key = 'db_table_'.$table;

        if ($name = $rcube->config->get($config_key)) {
            return $name;
        if ($quoted) {
            $table = $this->quote_identifier($table);
        }

        return $table;
    }

    /**
     * Set class option value
     *
     * @param string $name  Option name
     * @param mixed  $value Option value
     */
    public function set_option($name, $value)
    {
        $this->options[$name] = $value;
    }

    /**
     * Set DSN connection to be used for the given table
     *
     * @param string Table name
     * @param string DSN connection ('r' or 'w') to be used
     */
    public function set_table_dsn($table, $mode)
    {
        $this->options['table_dsn_map'][$this->table_name($table)] = $mode;
    }

    /**
@@ -969,4 +1190,61 @@

        return $result;
    }

    /**
     * Execute the given SQL script
     *
     * @param string SQL queries to execute
     *
     * @return boolen True on success, False on error
     */
    public function exec_script($sql)
    {
        $sql  = $this->fix_table_names($sql);
        $buff = '';

        foreach (explode("\n", $sql) as $line) {
            if (preg_match('/^--/', $line) || trim($line) == '')
                continue;

            $buff .= $line . "\n";
            if (preg_match('/(;|^GO)$/', trim($line))) {
                $this->query($buff);
                $buff = '';
                if ($this->db_error) {
                    break;
                }
            }
        }

        return !$this->db_error;
    }

    /**
     * Parse SQL file and fix table names according to table prefix
     */
    protected function fix_table_names($sql)
    {
        if (!$this->options['table_prefix']) {
            return $sql;
        }

        $sql = preg_replace_callback(
            '/((TABLE|TRUNCATE|(?<!ON )UPDATE|INSERT INTO|FROM'
            . '| ON(?! (DELETE|UPDATE))|REFERENCES|CONSTRAINT|FOREIGN KEY|INDEX)'
            . '\s+(IF (NOT )?EXISTS )?[`"]*)([^`"\( \r\n]+)/',
            array($this, 'fix_table_names_callback'),
            $sql
        );

        return $sql;
    }

    /**
     * Preg_replace callback for fix_table_names()
     */
    protected function fix_table_names_callback($matches)
    {
        return $matches[1] . $this->options['table_prefix'] . $matches[count($matches)-1];
    }
}