Merge pull request #16604 from owncloud/cache-escape-like

escape like parameter in cache operations

7 files changed, 149 insertions, 62 deletions

diff --git a/lib/private/appframework/db/db.php b/lib/private/appframework/db/db.php
index 8e3fa6e4197..9f912f9c0a4 100644
--- a/lib/private/appframework/db/db.php
+++ b/lib/private/appframework/db/db.php
@@ -258,4 +258,13 @@ class Db implements IDb {
 		return $this->connection->tableExists($table);
 	}
 
+	/**
+	 * Espace a parameter to be used in a LIKE query
+	 *
+	 * @param string $param
+	 * @return string
+	 */
+	public function escapeLikeParameter($param) {
+		return $this->connection->escapeLikeParameter($param);
+	}
 }
diff --git a/lib/private/db/adapteroci8.php b/lib/private/db/adapteroci8.php
index 15ec5a0677f..6e7857e6620 100644
--- a/lib/private/db/adapteroci8.php
+++ b/lib/private/db/adapteroci8.php
@@ -36,6 +36,7 @@ class AdapterOCI8 extends Adapter {
 	const UNIX_TIMESTAMP_REPLACEMENT = "(cast(sys_extract_utc(systimestamp) as date) - date'1970-01-01') * 86400";
 
 	public function fixupStatement($statement) {
+		$statement = preg_replace('( LIKE \?)', '$0 ESCAPE \'\\\'', $statement);
 		$statement = preg_replace('/`(\w+)` ILIKE \?/', 'REGEXP_LIKE(`$1`, \'^\' || REPLACE(?, \'%\', \'.*\') || \'$\', \'i\')', $statement);
 		$statement = str_replace('`', '"', $statement);
 		$statement = str_ireplace('NOW()', 'CURRENT_TIMESTAMP', $statement);
diff --git a/lib/private/db/adaptersqlite.php b/lib/private/db/adaptersqlite.php
index 5add0586da0..e133a20f543 100644
--- a/lib/private/db/adaptersqlite.php
+++ b/lib/private/db/adaptersqlite.php
@@ -28,6 +28,7 @@ namespace OC\DB;
 
 class AdapterSqlite extends Adapter {
 	public function fixupStatement($statement) {
+		$statement = preg_replace('( I?LIKE \?)', '$0 ESCAPE \'\\\'', $statement);
 		$statement = preg_replace('/`(\w+)` ILIKE \?/', 'LOWER($1) LIKE LOWER(?)', $statement);
 		$statement = str_replace( '`', '"', $statement );
 		$statement = str_ireplace( 'NOW()', 'datetime(\'now\')', $statement );
diff --git a/lib/private/db/connection.php b/lib/private/db/connection.php
index 4d33cd968af..1b86d3d383a 100644
--- a/lib/private/db/connection.php
+++ b/lib/private/db/connection.php
@@ -301,4 +301,14 @@ class Connection extends \Doctrine\DBAL\Connection implements IDBConnection {
 	public function inTransaction() {
 		return $this->getTransactionNestingLevel() > 0;
 	}
+
+	/**
+	 * Espace a parameter to be used in a LIKE query
+	 *
+	 * @param string $param
+	 * @return string
+	 */
+	public function escapeLikeParameter($param) {
+		return addcslashes($param, '\\_%');
+	}
 }
diff --git a/lib/private/files/cache/cache.php b/lib/private/files/cache/cache.php
index 71720ac58bf..40477243324 100644
--- a/lib/private/files/cache/cache.php
+++ b/lib/private/files/cache/cache.php
@@ -35,6 +35,7 @@
 namespace OC\Files\Cache;
 
 use \OCP\Files\IMimeTypeLoader;
+use OCP\IDBConnection;
 
 /**
  * Metadata cache for a storage
@@ -71,6 +72,11 @@ class Cache {
 	protected $mimetypeLoader;
 
 	/**
+	 * @var IDBConnection
+	 */
+	protected $connection;
+
+	/**
 	 * @param \OC\Files\Storage\Storage|string $storage
 	 */
 	public function __construct($storage) {
@@ -85,6 +91,7 @@ class Cache {
 
 		$this->storageCache = new Storage($storage);
 		$this->mimetypeLoader = \OC::$server->getMimeTypeLoader();
+		$this->connection = \OC::$server->getDatabaseConnection();
 	}
 
 	/**
@@ -101,22 +108,22 @@ class Cache {
 	 *
 	 * the returned cache entry contains at least the following values:
 	 * [
-	 * 		'fileid' => int, the numeric id of a file (see getId)
-	 * 		'storage' => int, the numeric id of the storage the file is stored on
-	 * 		'path' => string, the path of the file within the storage ('foo/bar.txt')
-	 * 		'name' => string, the basename of a file ('bar.txt)
-	 * 		'mimetype' => string, the full mimetype of the file ('text/plain')
-	 * 		'mimepart' => string, the first half of the mimetype ('text')
-	 * 		'size' => int, the size of the file or folder in bytes
-	 * 		'mtime' => int, the last modified date of the file as unix timestamp as shown in the ui
-	 * 		'storage_mtime' => int, the last modified date of the file as unix timestamp as stored on the storage
-	 * 			Note that when a file is updated we also update the mtime of all parent folders to make it visible to the user which folder has had updates most recently
-	 * 			This can differ from the mtime on the underlying storage which usually only changes when a direct child is added, removed or renamed
-	 * 		'etag' => string, the etag for the file
-	 * 			An etag is used for change detection of files and folders, an etag of a file changes whenever the content of the file changes
-	 * 			Etag for folders change whenever a file in the folder has changed
-	 * 		'permissions' int, the permissions for the file stored as bitwise combination of \OCP\PERMISSION_READ, \OCP\PERMISSION_CREATE
-	 * 			\OCP\PERMISSION_UPDATE, \OCP\PERMISSION_DELETE and \OCP\PERMISSION_SHARE
+	 *        'fileid' => int, the numeric id of a file (see getId)
+	 *        'storage' => int, the numeric id of the storage the file is stored on
+	 *        'path' => string, the path of the file within the storage ('foo/bar.txt')
+	 *        'name' => string, the basename of a file ('bar.txt)
+	 *        'mimetype' => string, the full mimetype of the file ('text/plain')
+	 *        'mimepart' => string, the first half of the mimetype ('text')
+	 *        'size' => int, the size of the file or folder in bytes
+	 *        'mtime' => int, the last modified date of the file as unix timestamp as shown in the ui
+	 *        'storage_mtime' => int, the last modified date of the file as unix timestamp as stored on the storage
+	 *            Note that when a file is updated we also update the mtime of all parent folders to make it visible to the user which folder has had updates most recently
+	 *            This can differ from the mtime on the underlying storage which usually only changes when a direct child is added, removed or renamed
+	 *        'etag' => string, the etag for the file
+	 *            An etag is used for change detection of files and folders, an etag of a file changes whenever the content of the file changes
+	 *            Etag for folders change whenever a file in the folder has changed
+	 *        'permissions' int, the permissions for the file stored as bitwise combination of \OCP\PERMISSION_READ, \OCP\PERMISSION_CREATE
+	 *            \OCP\PERMISSION_UPDATE, \OCP\PERMISSION_DELETE and \OCP\PERMISSION_SHARE
 	 * ]
 	 *
 	 * @param string | int $file either the path of a file or folder or the file id for a file or folder
@@ -136,8 +143,8 @@ class Cache {
 		$sql = 'SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`,
 					   `storage_mtime`, `encrypted`, `etag`, `permissions`
 				FROM `*PREFIX*filecache` ' . $where;
-		$result = \OC_DB::executeAudited($sql, $params);
-		$data = $result->fetchRow();
+		$result = $this->connection->executeQuery($sql, $params);
+		$data = $result->fetch();
 
 		//FIXME hide this HACK in the next database layer, or just use doctrine and get rid of MDB2 and PDO
 		//PDO returns false, MDB2 returns null, oracle always uses MDB2, so convert null to false
@@ -191,7 +198,7 @@ class Cache {
 			$sql = 'SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`,
 						   `storage_mtime`, `encrypted`, `etag`, `permissions`
 					FROM `*PREFIX*filecache` WHERE `parent` = ? ORDER BY `name` ASC';
-			$result = \OC_DB::executeAudited($sql, array($fileId));
+			$result = $this->connection->executeQuery($sql, [$fileId]);
 			$files = $result->fetchAll();
 			foreach ($files as &$file) {
 				$file['mimetype'] = $this->mimetypeLoader->getMimetypeById($file['mimetype']);
@@ -248,15 +255,16 @@ class Cache {
 			$queryParts[] = '`storage`';
 			$params[] = $this->getNumericStorageId();
 
-			$queryParts = array_map(function($item) {
+			$queryParts = array_map(function ($item) {
 				return trim($item, "`");
 			}, $queryParts);
 			$values = array_combine($queryParts, $params);
 			if (\OC::$server->getDatabaseConnection()->insertIfNotExist('*PREFIX*filecache', $values, [
 				'storage',
 				'path_hash',
-			])) {
-				return (int)\OC_DB::insertid('*PREFIX*filecache');
+			])
+			) {
+				return (int)$this->connection->lastInsertId('*PREFIX*filecache');
 			}
 
 			// The file was created in the mean time
@@ -297,7 +305,7 @@ class Cache {
 		// some databases (Postgres) don't like superfluous updates
 		$sql = 'UPDATE `*PREFIX*filecache` SET ' . implode(' = ?, ', $queryParts) . '=? ' .
 			'WHERE (' . implode(' <> ? OR ', $queryParts) . ' <> ? ) AND `fileid` = ? ';
-		\OC_DB::executeAudited($sql, $params);
+		$this->connection->executeQuery($sql, $params);
 
 	}
 
@@ -306,8 +314,8 @@ class Cache {
 	 *
 	 * @param array $data
 	 * @return array [$queryParts, $params]
-	 * 		$queryParts: string[], the (escaped) column names to be set in the query
-	 *		$params: mixed[], the new values for the columns, to be passed as params to the query
+	 *        $queryParts: string[], the (escaped) column names to be set in the query
+	 *        $params: mixed[], the new values for the columns, to be passed as params to the query
 	 */
 	protected function buildParts(array $data) {
 		$fields = array(
@@ -365,8 +373,8 @@ class Cache {
 		$pathHash = md5($file);
 
 		$sql = 'SELECT `fileid` FROM `*PREFIX*filecache` WHERE `storage` = ? AND `path_hash` = ?';
-		$result = \OC_DB::executeAudited($sql, array($this->getNumericStorageId(), $pathHash));
-		if ($row = $result->fetchRow()) {
+		$result = $this->connection->executeQuery($sql, array($this->getNumericStorageId(), $pathHash));
+		if ($row = $result->fetch()) {
 			return $row['fileid'];
 		} else {
 			return -1;
@@ -411,7 +419,7 @@ class Cache {
 	public function remove($file) {
 		$entry = $this->get($file);
 		$sql = 'DELETE FROM `*PREFIX*filecache` WHERE `fileid` = ?';
-		\OC_DB::executeAudited($sql, array($entry['fileid']));
+		$this->connection->executeQuery($sql, array($entry['fileid']));
 		if ($entry['mimetype'] === 'httpd/unix-directory') {
 			$this->removeChildren($entry);
 		}
@@ -442,7 +450,7 @@ class Cache {
 			$this->removeChildren($folder);
 		}
 		$sql = 'DELETE FROM `*PREFIX*filecache` WHERE `parent` = ?';
-		\OC_DB::executeAudited($sql, array($entry['fileid']));
+		$this->connection->executeQuery($sql, array($entry['fileid']));
 	}
 
 	/**
@@ -491,20 +499,20 @@ class Cache {
 		if ($sourceData['mimetype'] === 'httpd/unix-directory') {
 			//find all child entries
 			$sql = 'SELECT `path`, `fileid` FROM `*PREFIX*filecache` WHERE `storage` = ? AND `path` LIKE ?';
-			$result = \OC_DB::executeAudited($sql, [$sourceStorageId, $sourcePath . '/%']);
+			$result = $this->connection->executeQuery($sql, [$sourceStorageId, $this->connection->escapeLikeParameter($sourcePath) . '/%']);
 			$childEntries = $result->fetchAll();
 			$sourceLength = strlen($sourcePath);
-			\OC_DB::beginTransaction();
-			$query = \OC_DB::prepare('UPDATE `*PREFIX*filecache` SET `storage` = ?, `path` = ?, `path_hash` = ? WHERE `fileid` = ?');
+			$this->connection->beginTransaction();
+			$query = $this->connection->prepare('UPDATE `*PREFIX*filecache` SET `storage` = ?, `path` = ?, `path_hash` = ? WHERE `fileid` = ?');
 
 			foreach ($childEntries as $child) {
 				$newTargetPath = $targetPath . substr($child['path'], $sourceLength);
-				\OC_DB::executeAudited($query, [$targetStorageId, $newTargetPath, md5($newTargetPath), $child['fileid']]);
+				$query->execute([$targetStorageId, $newTargetPath, md5($newTargetPath), $child['fileid']]);
 			}
-			\OC_DB::executeAudited($moveSql, [$targetStorageId, $targetPath, md5($targetPath), basename($targetPath), $newParentId, $sourceId]);
-			\OC_DB::commit();
+			$this->connection->executeQuery($moveSql, [$targetStorageId, $targetPath, md5($targetPath), basename($targetPath), $newParentId, $sourceId]);
+			$this->connection->commit();
 		} else {
-			\OC_DB::executeAudited($moveSql, [$targetStorageId, $targetPath, md5($targetPath), basename($targetPath), $newParentId, $sourceId]);
+			$this->connection->executeQuery($moveSql, [$targetStorageId, $targetPath, md5($targetPath), basename($targetPath), $newParentId, $sourceId]);
 		}
 
 	}
@@ -514,10 +522,10 @@ class Cache {
 	 */
 	public function clear() {
 		$sql = 'DELETE FROM `*PREFIX*filecache` WHERE `storage` = ?';
-		\OC_DB::executeAudited($sql, array($this->getNumericStorageId()));
+		$this->connection->executeQuery($sql, array($this->getNumericStorageId()));
 
 		$sql = 'DELETE FROM `*PREFIX*storages` WHERE `id` = ?';
-		\OC_DB::executeAudited($sql, array($this->storageId));
+		$this->connection->executeQuery($sql, array($this->storageId));
 	}
 
 	/**
@@ -538,8 +546,8 @@ class Cache {
 
 		$pathHash = md5($file);
 		$sql = 'SELECT `size` FROM `*PREFIX*filecache` WHERE `storage` = ? AND `path_hash` = ?';
-		$result = \OC_DB::executeAudited($sql, array($this->getNumericStorageId(), $pathHash));
-		if ($row = $result->fetchRow()) {
+		$result = $this->connection->executeQuery($sql, array($this->getNumericStorageId(), $pathHash));
+		if ($row = $result->fetch()) {
 			if ((int)$row['size'] === -1) {
 				return self::SHALLOW;
 			} else {
@@ -571,12 +579,12 @@ class Cache {
 				`etag`, `permissions`
 			FROM `*PREFIX*filecache`
 			WHERE `storage` = ? AND `name` ILIKE ?';
-		$result = \OC_DB::executeAudited($sql,
-			array($this->getNumericStorageId(), $pattern)
+		$result = $this->connection->executeQuery($sql,
+			[$this->getNumericStorageId(), $pattern]
 		);
 
-		$files = array();
-		while ($row = $result->fetchRow()) {
+		$files = [];
+		while ($row = $result->fetch()) {
 			$row['mimetype'] = $this->mimetypeLoader->getMimetypeById($row['mimetype']);
 			$row['mimepart'] = $this->mimetypeLoader->getMimetypeById($row['mimepart']);
 			$files[] = $row;
@@ -588,7 +596,7 @@ class Cache {
 	 * search for files by mimetype
 	 *
 	 * @param string $mimetype either a full mimetype to search ('text/plain') or only the first part of a mimetype ('image')
-	 * 		where it will search for all mimetypes in the group ('image/*')
+	 *        where it will search for all mimetypes in the group ('image/*')
 	 * @return array  an array of cache entries where the mimetype matches the search
 	 */
 	public function searchByMime($mimetype) {
@@ -600,9 +608,9 @@ class Cache {
 		$sql = 'SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `encrypted`, `etag`, `permissions`
 				FROM `*PREFIX*filecache` WHERE ' . $where . ' AND `storage` = ?';
 		$mimetype = $this->mimetypeLoader->getId($mimetype);
-		$result = \OC_DB::executeAudited($sql, array($mimetype, $this->getNumericStorageId()));
+		$result = $this->connection->executeQuery($sql, array($mimetype, $this->getNumericStorageId()));
 		$files = array();
-		while ($row = $result->fetchRow()) {
+		while ($row = $result->fetch()) {
 			$row['mimetype'] = $this->mimetypeLoader->getMimetypeById($row['mimetype']);
 			$row['mimepart'] = $this->mimetypeLoader->getMimetypeById($row['mimepart']);
 			$files[] = $row;
@@ -640,16 +648,16 @@ class Cache {
 		} else {
 			$sql .= 'AND `tag`.`category` = ? ';
 		}
-		$result = \OC_DB::executeAudited(
+		$result = $this->connection->executeQuery(
 			$sql,
-			array(
+			[
 				$this->getNumericStorageId(),
 				$userId,
 				$tag
-			)
+			]
 		);
 		$files = array();
-		while ($row = $result->fetchRow()) {
+		while ($row = $result->fetch()) {
 			$files[] = $row;
 		}
 		return $files;
@@ -689,8 +697,8 @@ class Cache {
 			$sql = 'SELECT SUM(`size`) AS f1, MIN(`size`) AS f2 ' .
 				'FROM `*PREFIX*filecache` ' .
 				'WHERE `parent` = ? AND `storage` = ?';
-			$result = \OC_DB::executeAudited($sql, array($id, $this->getNumericStorageId()));
-			if ($row = $result->fetchRow()) {
+			$result = $this->connection->executeQuery($sql, array($id, $this->getNumericStorageId()));
+			if ($row = $result->fetch()) {
 				$result->closeCursor();
 				list($sum, $min) = array_values($row);
 				$sum = 0 + $sum;
@@ -721,9 +729,9 @@ class Cache {
 	 */
 	public function getAll() {
 		$sql = 'SELECT `fileid` FROM `*PREFIX*filecache` WHERE `storage` = ?';
-		$result = \OC_DB::executeAudited($sql, array($this->getNumericStorageId()));
+		$result = $this->connection->executeQuery($sql, array($this->getNumericStorageId()));
 		$ids = array();
-		while ($row = $result->fetchRow()) {
+		while ($row = $result->fetch()) {
 			$ids[] = $row['fileid'];
 		}
 		return $ids;
@@ -739,10 +747,10 @@ class Cache {
 	 * @return string|bool the path of the folder or false when no folder matched
 	 */
 	public function getIncomplete() {
-		$query = \OC_DB::prepare('SELECT `path` FROM `*PREFIX*filecache`'
+		$query = $this->connection->prepare('SELECT `path` FROM `*PREFIX*filecache`'
 			. ' WHERE `storage` = ? AND `size` = -1 ORDER BY `fileid` DESC', 1);
-		$result = \OC_DB::executeAudited($query, array($this->getNumericStorageId()));
-		if ($row = $result->fetchRow()) {
+		$query->execute([$this->getNumericStorageId()]);
+		if ($row = $query->fetch()) {
 			return $row['path'];
 		} else {
 			return false;
@@ -757,8 +765,8 @@ class Cache {
 	 */
 	public function getPathById($id) {
 		$sql = 'SELECT `path` FROM `*PREFIX*filecache` WHERE `fileid` = ? AND `storage` = ?';
-		$result = \OC_DB::executeAudited($sql, array($id, $this->getNumericStorageId()));
-		if ($row = $result->fetchRow()) {
+		$result = $this->connection->executeQuery($sql, array($id, $this->getNumericStorageId()));
+		if ($row = $result->fetch()) {
 			// Oracle stores empty strings as null...
 			if ($row['path'] === null) {
 				return '';
@@ -779,9 +787,10 @@ class Cache {
 	 * @return array first element holding the storage id, second the path
 	 */
 	static public function getById($id) {
+		$connection = \OC::$server->getDatabaseConnection();
 		$sql = 'SELECT `storage`, `path` FROM `*PREFIX*filecache` WHERE `fileid` = ?';
-		$result = \OC_DB::executeAudited($sql, array($id));
-		if ($row = $result->fetchRow()) {
+		$result = $connection->executeQuery($sql, array($id));
+		if ($row = $result->fetch()) {
 			$numericId = $row['storage'];
 			$path = $row['path'];
 		} else {
diff --git a/lib/public/idbconnection.php b/lib/public/idbconnection.php
index 6a4373583fa..945ca142163 100644
--- a/lib/public/idbconnection.php
+++ b/lib/public/idbconnection.php
@@ -206,4 +206,13 @@ interface IDBConnection {
 	 * @since 8.0.0
 	 */
 	public function tableExists($table);
+
+	/**
+	 * Escape a parameter to be used in a LIKE query
+	 *
+	 * @param string $param
+	 * @return string
+	 * @since 9.0.0
+	 */
+	public function escapeLikeParameter($param);
 }
diff --git a/tests/lib/files/cache/cache.php b/tests/lib/files/cache/cache.php
index 9a64375f4e3..c5395a97fd4 100644
--- a/tests/lib/files/cache/cache.php
+++ b/tests/lib/files/cache/cache.php
@@ -604,6 +604,54 @@ class Cache extends \Test\TestCase {
 		$this->assertNotEquals($fileId, $fileId2);
 	}
 
+	public function escapingProvider() {
+		return [
+				['foo'],
+				['o%'],
+				['oth_r'],
+		];
+	}
+
+	/**
+	 * @param string $name
+	 * @dataProvider escapingProvider
+	 */
+	public function testEscaping($name) {
+		$data = array('size' => 100, 'mtime' => 50, 'mimetype' => 'text/plain');
+		$this->cache->put($name, $data);
+		$this->assertTrue($this->cache->inCache($name));
+		$retrievedData = $this->cache->get($name);
+		foreach ($data as $key => $value) {
+			$this->assertEquals($value, $retrievedData[$key]);
+		}
+		$this->cache->move($name, $name . 'asd');
+		$this->assertFalse($this->cache->inCache($name));
+		$this->assertTrue($this->cache->inCache($name . 'asd'));
+		$this->cache->remove($name . 'asd');
+		$this->assertFalse($this->cache->inCache($name . 'asd'));
+		$folderData = array('size' => 100, 'mtime' => 50, 'mimetype' => 'httpd/unix-directory');
+		$this->cache->put($name, $folderData);
+		$this->cache->put('other', $folderData);
+		$childs = ['asd', 'bar', 'foo', 'sub/folder'];
+		$this->cache->put($name . '/sub/folder', $folderData);
+		$this->cache->put('other/sub/folder', $folderData);
+		foreach ($childs as $child) {
+			$this->cache->put($name . '/' . $child, $data);
+			$this->cache->put('other/' . $child, $data);
+			$this->assertTrue($this->cache->inCache($name . '/' . $child));
+		}
+		$this->cache->move($name, $name . 'asd');
+		foreach ($childs as $child) {
+			$this->assertTrue($this->cache->inCache($name . 'asd/' . $child));
+			$this->assertTrue($this->cache->inCache('other/' . $child));
+		}
+		foreach ($childs as $child) {
+			$this->cache->remove($name . 'asd/' . $child);
+			$this->assertFalse($this->cache->inCache($name . 'asd/' . $child));
+			$this->assertTrue($this->cache->inCache('other/' . $child));
+		}
+	}
+
 	protected function tearDown() {
 		if ($this->cache) {
 			$this->cache->clear();