From 496972bf95e2ddbf01cb5e50a6a594615744d942 Mon Sep 17 00:00:00 2001
From: Aleksander Machniak <alec@alec.pl>
Date: Thu, 12 Mar 2015 04:44:31 -0400
Subject: [PATCH] Fix backtick character handling in sql queries (#1490312)
---
CHANGELOG | 1
program/lib/Roundcube/rcube_db_oracle.php | 9 ++
program/lib/Roundcube/rcube_db.php | 12 ++-
tests/Framework/DB.php | 108 ++++++++++++++++++++++++++++++++---
4 files changed, 112 insertions(+), 18 deletions(-)
diff --git a/CHANGELOG b/CHANGELOG
index f94e534..9ede28d 100644
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -35,6 +35,7 @@
- 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 2cacb30..a31b200 100644
--- a/program/lib/Roundcube/rcube_db.php
+++ b/program/lib/Roundcube/rcube_db.php
@@ -448,9 +448,14 @@
}
}
- // 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);
@@ -515,9 +520,6 @@
$pos++;
}
}
-
- // 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 34e4e69..bb03388 100644
--- a/program/lib/Roundcube/rcube_db_oracle.php
+++ b/program/lib/Roundcube/rcube_db_oracle.php
@@ -155,10 +155,15 @@
}
}
- // 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 42020f4..04897bb 100644
--- a/tests/Framework/DB.php
+++ b/tests/Framework/DB.php
@@ -25,6 +25,8 @@
{
$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,25 +40,87 @@
"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 @@
{
public $queries = array();
- protected function _query($query, $offset, $numrows, $params)
+ protected function query_execute($query)
{
- $this->queries[] = array(trim($query), $offset, $numrows, $params);
+ $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)
+ {
+ return "'$data'";
}
}
--
Gitblit v1.9.1