From 8f485469c7955fbf5b420ee0b6f043282965715b Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Tue, 24 Feb 2015 12:23:11 +0100 Subject: Add possibility to configure max_allowed_packet value for all database engines (#1490283) --- program/lib/Roundcube/rcube_db.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'program/lib/Roundcube/rcube_db.php') diff --git a/program/lib/Roundcube/rcube_db.php b/program/lib/Roundcube/rcube_db.php index ab7058f2f..2cacb3013 100644 --- a/program/lib/Roundcube/rcube_db.php +++ b/program/lib/Roundcube/rcube_db.php @@ -357,7 +357,7 @@ class rcube_db public function get_variable($varname, $default = null) { // to be implemented by driver class - return $default; + return rcube::get_instance()->config->get('db_' . $varname, $default); } /** -- cgit v1.2.3 From 496972bf95e2ddbf01cb5e50a6a594615744d942 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Thu, 12 Mar 2015 09:44:31 +0100 Subject: Fix backtick character handling in sql queries (#1490312) --- CHANGELOG | 1 + program/lib/Roundcube/rcube_db.php | 12 ++-- program/lib/Roundcube/rcube_db_oracle.php | 9 ++- tests/Framework/DB.php | 108 +++++++++++++++++++++++++++--- 4 files changed, 112 insertions(+), 18 deletions(-) (limited to 'program/lib/Roundcube/rcube_db.php') diff --git a/CHANGELOG b/CHANGELOG index f94e5343d..9ede28d7f 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -35,6 +35,7 @@ CHANGELOG Roundcube Webmail - Fix rows count when messages search fails (#1490266) - Fix bug where spellchecking in HTML editor do not work after switching editor type more than once (#1490311) - Fix bug where TinyMCE area height was too small on slow network connection (#1490310) +- Fix backtick character handling in sql queries (#1490312) RELEASE 1.1.0 ------------- diff --git a/program/lib/Roundcube/rcube_db.php b/program/lib/Roundcube/rcube_db.php index 2cacb3013..a31b2005d 100644 --- a/program/lib/Roundcube/rcube_db.php +++ b/program/lib/Roundcube/rcube_db.php @@ -448,10 +448,15 @@ class rcube_db } } - // replace escaped '?' back to normal, see self::quote() - $query = str_replace('??', '?', $query); $query = rtrim($query, " \t\n\r\0\x0B;"); + // replace escaped '?' and quotes back to normal, see self::quote() + $query = str_replace( + array('??', self::DEFAULT_QUOTE.self::DEFAULT_QUOTE), + array('?', self::DEFAULT_QUOTE), + $query + ); + // log query $this->debug($query); @@ -516,9 +521,6 @@ class rcube_db } } - // replace escaped quote back to normal, see self::quote() - $query = str_replace($quote.$quote, $quote, $query); - return $query; } diff --git a/program/lib/Roundcube/rcube_db_oracle.php b/program/lib/Roundcube/rcube_db_oracle.php index 34e4e69f8..bb033884c 100644 --- a/program/lib/Roundcube/rcube_db_oracle.php +++ b/program/lib/Roundcube/rcube_db_oracle.php @@ -155,10 +155,15 @@ class rcube_db_oracle extends rcube_db } } - // replace escaped '?' back to normal, see self::quote() - $query = str_replace('??', '?', $query); $query = rtrim($query, " \t\n\r\0\x0B;"); + // replace escaped '?' and quotes back to normal, see self::quote() + $query = str_replace( + array('??', self::DEFAULT_QUOTE.self::DEFAULT_QUOTE), + array('?', self::DEFAULT_QUOTE), + $query + ); + // log query $this->debug($query); diff --git a/tests/Framework/DB.php b/tests/Framework/DB.php index 42020f47a..04897bb90 100644 --- a/tests/Framework/DB.php +++ b/tests/Framework/DB.php @@ -25,6 +25,8 @@ class Framework_DB extends PHPUnit_Framework_TestCase { $db = new rcube_db_test_wrapper('test'); $db->set_option('table_prefix', 'prefix_'); + $db->set_option('identifier_start', '`'); + $db->set_option('identifier_end', '`'); $script = implode("\n", array( "CREATE TABLE `xxx` (test int, INDEX xxx (test));", @@ -38,26 +40,88 @@ class Framework_DB extends PHPUnit_Framework_TestCase "SELECT test FROM xxx;", )); $output = implode("\n", array( - "CREATE TABLE `prefix_xxx` (test int, INDEX prefix_xxx (test));", - "ALTER TABLE `prefix_xxx` CHANGE test test int;", - "TRUNCATE prefix_xxx;", - "DROP TABLE `prefix_vvv`;", + "CREATE TABLE `prefix_xxx` (test int, INDEX prefix_xxx (test))", + "ALTER TABLE `prefix_xxx` CHANGE test test int", + "TRUNCATE prefix_xxx", + "DROP TABLE `prefix_vvv`", "CREATE TABLE `prefix_i` (test int CONSTRAINT `prefix_iii` - FOREIGN KEY (`test`) REFERENCES `prefix_xxx`(`test`) ON DELETE CASCADE ON UPDATE CASCADE);", - "INSERT INTO prefix_xxx test = 1;", - "SELECT test FROM prefix_xxx;", + FOREIGN KEY (`test`) REFERENCES `prefix_xxx`(`test`) ON DELETE CASCADE ON UPDATE CASCADE)", + "INSERT INTO prefix_xxx test = 1", + "SELECT test FROM prefix_xxx", )); $result = $db->exec_script($script); - $out = ''; + $out = array(); foreach ($db->queries as $q) { - $out[] = $q[0]; + $out[] = $q; } $this->assertTrue($result, "Execute SQL script (result)"); $this->assertSame(implode("\n", $out), $output, "Execute SQL script (content)"); } + + /** + * Test query parsing and arguments quoting + */ + function test_query_parsing() + { + $db = new rcube_db_test_wrapper('test'); + $db->set_option('identifier_start', '`'); + $db->set_option('identifier_end', '`'); + + $db->query("SELECT ?", "test`test"); + $db->query("SELECT ?", "test?test"); + $db->query("SELECT ?", "test``test"); + $db->query("SELECT ?", "test??test"); + $db->query("SELECT `test` WHERE 'test``test'"); + $db->query("SELECT `test` WHERE 'test??test'"); + $db->query("SELECT `test` WHERE `test` = ?", "`te``st`"); + $db->query("SELECT `test` WHERE `test` = ?", "?test?"); + $db->query("SELECT `test` WHERE `test` = ?", "????"); + + $expected = implode("\n", array( + "SELECT 'test`test'", + "SELECT 'test?test'", + "SELECT 'test``test'", + "SELECT 'test??test'", + "SELECT `test` WHERE 'test`test'", + "SELECT `test` WHERE 'test?test'", + "SELECT `test` WHERE `test` = '`te``st`'", + "SELECT `test` WHERE `test` = '?test?'", + "SELECT `test` WHERE `test` = '????'", + )); + + $this->assertSame($expected, implode("\n", $db->queries), "Query parsing [1]"); + + $db->set_option('identifier_start', '"'); + $db->set_option('identifier_end', '"'); + $db->queries = array(); + + $db->query("SELECT ?", "test`test"); + $db->query("SELECT ?", "test?test"); + $db->query("SELECT ?", "test``test"); + $db->query("SELECT ?", "test??test"); + $db->query("SELECT `test` WHERE 'test``test'"); + $db->query("SELECT `test` WHERE 'test??test'"); + $db->query("SELECT `test` WHERE `test` = ?", "`te``st`"); + $db->query("SELECT `test` WHERE `test` = ?", "?test?"); + $db->query("SELECT `test` WHERE `test` = ?", "????"); + + $expected = implode("\n", array( + "SELECT 'test`test'", + "SELECT 'test?test'", + "SELECT 'test``test'", + "SELECT 'test??test'", + "SELECT \"test\" WHERE 'test`test'", + "SELECT \"test\" WHERE 'test?test'", + "SELECT \"test\" WHERE \"test\" = '`te``st`'", + "SELECT \"test\" WHERE \"test\" = '?test?'", + "SELECT \"test\" WHERE \"test\" = '????'", + )); + + $this->assertSame($expected, implode("\n", $db->queries), "Query parsing [2]"); + } } /** @@ -67,8 +131,30 @@ class rcube_db_test_wrapper extends rcube_db { public $queries = array(); - protected function _query($query, $offset, $numrows, $params) + protected function query_execute($query) + { + $this->queries[] = $query; + } + + public function db_connect($mode, $force = false) + { + $this->dbh = new rcube_db_test_dbh(); + } + + public function is_connected() + { + return true; + } + + protected function debug($data) + { + } +} + +class rcube_db_test_dbh +{ + public function quote($data, $type) { - $this->queries[] = array(trim($query), $offset, $numrows, $params); + return "'$data'"; } } -- cgit v1.2.3 From 48d01837a0a5725d2779f30d20478e77572e9ac5 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Mon, 6 Apr 2015 12:00:09 +0200 Subject: Fix tables listing routine on mysql and postgres so it skips system or other database tables and views (#1490337) --- CHANGELOG | 1 + program/lib/Roundcube/rcube_db.php | 11 ++++------- program/lib/Roundcube/rcube_db_mysql.php | 24 ++++++++++++++++++++++++ program/lib/Roundcube/rcube_db_pgsql.php | 19 +++++++++++++++++++ 4 files changed, 48 insertions(+), 7 deletions(-) (limited to 'program/lib/Roundcube/rcube_db.php') diff --git a/CHANGELOG b/CHANGELOG index 428238269..d79610b2d 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -10,6 +10,7 @@ CHANGELOG Roundcube Webmail - Fix zipped messages downloads after selecting all messages in a folder (#1490339) - Fix vpopmaild driver of password plugin - Fix PHP warning: Non-static method PEAR::setErrorHandling() should not be called statically (#1490343) +- Fix tables listing routine on mysql and postgres so it skips system or other database tables and views (#1490337) RELEASE 1.1.1 ------------- diff --git a/program/lib/Roundcube/rcube_db.php b/program/lib/Roundcube/rcube_db.php index a31b2005d..4ccc59ba3 100644 --- a/program/lib/Roundcube/rcube_db.php +++ b/program/lib/Roundcube/rcube_db.php @@ -691,14 +691,11 @@ class rcube_db { // get tables if not cached if ($this->tables === null) { - $q = $this->query('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME'); + $q = $this->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES" + . " WHERE TABLE_TYPE = 'BASE TABLE'" + . " ORDER BY TABLE_NAME"); - if ($q) { - $this->tables = $q->fetchAll(PDO::FETCH_COLUMN, 0); - } - else { - $this->tables = array(); - } + $this->tables = $q ? $q->fetchAll(PDO::FETCH_COLUMN, 0) : array(); } return $this->tables; diff --git a/program/lib/Roundcube/rcube_db_mysql.php b/program/lib/Roundcube/rcube_db_mysql.php index dd28c25c8..616d1752b 100644 --- a/program/lib/Roundcube/rcube_db_mysql.php +++ b/program/lib/Roundcube/rcube_db_mysql.php @@ -149,6 +149,30 @@ class rcube_db_mysql extends rcube_db return $result; } + /** + * Returns list of tables in a database + * + * @return array List of all tables of the current database + */ + public function list_tables() + { + // get tables if not cached + if ($this->tables === null) { + // first fetch current database name + $d = $this->query("SELECT database()"); + $d = $this->fetch_array($d); + + // get list of tables in current database + $q = $this->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES" + . " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'" + . " ORDER BY TABLE_NAME", $d ? $d[0] : ''); + + $this->tables = $q ? $q->fetchAll(PDO::FETCH_COLUMN, 0) : array(); + } + + return $this->tables; + } + /** * Get database runtime variables * diff --git a/program/lib/Roundcube/rcube_db_pgsql.php b/program/lib/Roundcube/rcube_db_pgsql.php index ff41df224..b4255513b 100644 --- a/program/lib/Roundcube/rcube_db_pgsql.php +++ b/program/lib/Roundcube/rcube_db_pgsql.php @@ -157,6 +157,25 @@ class rcube_db_pgsql extends rcube_db return isset($this->variables[$varname]) ? $this->variables[$varname] : $default; } + /** + * Returns list of tables in a database + * + * @return array List of all tables of the current database + */ + public function list_tables() + { + // get tables if not cached + if ($this->tables === null) { + $q = $this->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES" + . " WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA NOT IN ('pg_catalog', 'information_schema')" + . " ORDER BY TABLE_NAME"); + + $this->tables = $q ? $q->fetchAll(PDO::FETCH_COLUMN, 0) : array(); + } + + return $this->tables; + } + /** * Returns PDO DSN string from DSN array * -- cgit v1.2.3