Merge pull request #5816 from nextcloud/fix-oracle

Fix oracle

12 files changed, 121 insertions, 26 deletions

diff --git a/lib/private/Authentication/Token/DefaultTokenMapper.php b/lib/private/Authentication/Token/DefaultTokenMapper.php
index a23be624542..35bb83b85f4 100644
--- a/lib/private/Authentication/Token/DefaultTokenMapper.php
+++ b/lib/private/Authentication/Token/DefaultTokenMapper.php
@@ -156,7 +156,7 @@ class DefaultTokenMapper extends Mapper {
 	public function deleteByName($name) {
 		$qb = $this->db->getQueryBuilder();
 		$qb->delete('authtoken')
-			->where($qb->expr()->eq('name', $qb->createNamedParameter($name)));
+			->where($qb->expr()->eq('name', $qb->createNamedParameter($name), IQueryBuilder::PARAM_STR));
 		$qb->execute();
 	}
 
diff --git a/lib/private/Comments/Manager.php b/lib/private/Comments/Manager.php
index 4645adc6c5b..078e1eef4d3 100644
--- a/lib/private/Comments/Manager.php
+++ b/lib/private/Comments/Manager.php
@@ -411,9 +411,12 @@ class Manager implements ICommentsManager {
 	 */
 	public function getNumberOfUnreadCommentsForFolder($folderId, IUser $user) {
 		$qb = $this->dbConn->getQueryBuilder();
-		$query = $qb->select('fileid', $qb->createFunction(
-			'COUNT(' . $qb->getColumnName('c.id') . ')')
-		)->from('comments', 'c')
+		$query = $qb->select('f.fileid')
+			->selectAlias(
+				$qb->createFunction('COUNT(' . $qb->getColumnName('c.id') . ')'),
+				'num_ids'
+			)
+			->from('comments', 'c')
 			->innerJoin('c', 'filecache', 'f', $qb->expr()->andX(
 				$qb->expr()->eq('c.object_type', $qb->createNamedParameter('files')),
 				$qb->expr()->eq('f.fileid', $qb->expr()->castColumn('c.object_id', IQueryBuilder::PARAM_INT))
@@ -431,9 +434,13 @@ class Manager implements ICommentsManager {
 			->groupBy('f.fileid');
 
 		$resultStatement = $query->execute();
-		return array_map(function ($count) {
-			return (int)$count;
-		}, $resultStatement->fetchAll(\PDO::FETCH_KEY_PAIR));
+
+		$results = [];
+		while ($row = $resultStatement->fetch()) {
+			$results[$row['fileid']] = (int) $row['num_ids'];
+		}
+		$resultStatement->closeCursor();
+		return $results;
 	}
 
 	/**
diff --git a/lib/private/DB/QueryBuilder/ExpressionBuilder/ExpressionBuilder.php b/lib/private/DB/QueryBuilder/ExpressionBuilder/ExpressionBuilder.php
index f172260df79..a32ae4a1827 100644
--- a/lib/private/DB/QueryBuilder/ExpressionBuilder/ExpressionBuilder.php
+++ b/lib/private/DB/QueryBuilder/ExpressionBuilder/ExpressionBuilder.php
@@ -31,6 +31,7 @@ use OC\DB\QueryBuilder\QueryFunction;
 use OC\DB\QueryBuilder\QuoteHelper;
 use OCP\DB\QueryBuilder\IExpressionBuilder;
 use OCP\DB\QueryBuilder\ILiteral;
+use OCP\DB\QueryBuilder\IQueryBuilder;
 use OCP\DB\QueryBuilder\IQueryFunction;
 use OCP\IDBConnection;
 
@@ -351,6 +352,28 @@ class ExpressionBuilder implements IExpressionBuilder {
 	}
 
 	/**
+	 * Creates a $x = '' statement, because Oracle needs a different check
+	 *
+	 * @param string $x The field in string format to be inspected by the comparison.
+	 * @return string
+	 * @since 13.0.0
+	 */
+	public function emptyString($x) {
+		return $this->eq($x, $this->literal('', IQueryBuilder::PARAM_STR));
+	}
+
+	/**
+	 * Creates a `$x <> ''` statement, because Oracle needs a different check
+	 *
+	 * @param string $x The field in string format to be inspected by the comparison.
+	 * @return string
+	 * @since 13.0.0
+	 */
+	public function nonEmptyString($x) {
+		return $this->neq($x, $this->literal('', IQueryBuilder::PARAM_STR));
+	}
+
+	/**
 	 * Binary AND Operator copies a bit to the result if it exists in both operands.
 	 *
 	 * @param string|ILiteral $x The field or value to check
diff --git a/lib/private/DB/QueryBuilder/ExpressionBuilder/OCIExpressionBuilder.php b/lib/private/DB/QueryBuilder/ExpressionBuilder/OCIExpressionBuilder.php
index 179ce72e8e1..693d2faecff 100644
--- a/lib/private/DB/QueryBuilder/ExpressionBuilder/OCIExpressionBuilder.php
+++ b/lib/private/DB/QueryBuilder/ExpressionBuilder/OCIExpressionBuilder.php
@@ -138,6 +138,28 @@ class OCIExpressionBuilder extends ExpressionBuilder {
 	}
 
 	/**
+	 * Creates a $x = '' statement, because Oracle needs a different check
+	 *
+	 * @param string $x The field in string format to be inspected by the comparison.
+	 * @return string
+	 * @since 13.0.0
+	 */
+	public function emptyString($x) {
+		return $this->isNull($x);
+	}
+
+	/**
+	 * Creates a `$x <> ''` statement, because Oracle needs a different check
+	 *
+	 * @param string $x The field in string format to be inspected by the comparison.
+	 * @return string
+	 * @since 13.0.0
+	 */
+	public function nonEmptyString($x) {
+		return $this->isNotNull($x);
+	}
+
+	/**
 	 * Returns a IQueryFunction that casts the column to the given type
 	 *
 	 * @param string $column
@@ -166,6 +188,6 @@ class OCIExpressionBuilder extends ExpressionBuilder {
 	public function iLike($x, $y, $type = null) {
 		$x = $this->helper->quoteColumnName($x);
 		$y = $this->helper->quoteColumnName($y);
-		return new QueryFunction('REGEXP_LIKE('.$x.', \'^\' || REPLACE('.$y.', \'%\', \'.*\') || \'$\', \'i\')');
+		return new QueryFunction('REGEXP_LIKE(' . $x . ', \'^\' || REPLACE(REPLACE(' . $y . ', \'%\', \'.*\'), \'_\', \'.\') || \'$\', \'i\')');
 	}
 }
diff --git a/lib/private/DB/QueryBuilder/QueryBuilder.php b/lib/private/DB/QueryBuilder/QueryBuilder.php
index 217789ce728..3fc827c9680 100644
--- a/lib/private/DB/QueryBuilder/QueryBuilder.php
+++ b/lib/private/DB/QueryBuilder/QueryBuilder.php
@@ -560,7 +560,7 @@ class QueryBuilder implements IQueryBuilder {
 	public function from($from, $alias = null) {
 		$this->queryBuilder->from(
 			$this->getTableName($from),
-			$alias
+			$this->quoteAlias($alias)
 		);
 
 		return $this;
@@ -585,9 +585,9 @@ class QueryBuilder implements IQueryBuilder {
 	 */
 	public function join($fromAlias, $join, $alias, $condition = null) {
 		$this->queryBuilder->join(
-			$fromAlias,
+			$this->quoteAlias($fromAlias),
 			$this->getTableName($join),
-			$alias,
+			$this->quoteAlias($alias),
 			$condition
 		);
 
@@ -613,9 +613,9 @@ class QueryBuilder implements IQueryBuilder {
 	 */
 	public function innerJoin($fromAlias, $join, $alias, $condition = null) {
 		$this->queryBuilder->innerJoin(
-			$fromAlias,
+			$this->quoteAlias($fromAlias),
 			$this->getTableName($join),
-			$alias,
+			$this->quoteAlias($alias),
 			$condition
 		);
 
@@ -641,9 +641,9 @@ class QueryBuilder implements IQueryBuilder {
 	 */
 	public function leftJoin($fromAlias, $join, $alias, $condition = null) {
 		$this->queryBuilder->leftJoin(
-			$fromAlias,
+			$this->quoteAlias($fromAlias),
 			$this->getTableName($join),
-			$alias,
+			$this->quoteAlias($alias),
 			$condition
 		);
 
@@ -669,9 +669,9 @@ class QueryBuilder implements IQueryBuilder {
 	 */
 	public function rightJoin($fromAlias, $join, $alias, $condition = null) {
 		$this->queryBuilder->rightJoin(
-			$fromAlias,
+			$this->quoteAlias($fromAlias),
 			$this->getTableName($join),
-			$alias,
+			$this->quoteAlias($alias),
 			$condition
 		);
 
@@ -1193,4 +1193,18 @@ class QueryBuilder implements IQueryBuilder {
 
 		return $this->helper->quoteColumnName($tableAlias . $column);
 	}
+
+	/**
+	 * Returns the column name quoted and with table alias prefix as needed by the implementation
+	 *
+	 * @param string $alias
+	 * @return string
+	 */
+	public function quoteAlias($alias) {
+		if ($alias === '' || $alias === null) {
+			return $alias;
+		}
+
+		return $this->helper->quoteColumnName($alias);
+	}
 }
diff --git a/lib/private/DB/QueryBuilder/QuoteHelper.php b/lib/private/DB/QueryBuilder/QuoteHelper.php
index 705c3a89712..8821863ed07 100644
--- a/lib/private/DB/QueryBuilder/QuoteHelper.php
+++ b/lib/private/DB/QueryBuilder/QuoteHelper.php
@@ -70,7 +70,7 @@ class QuoteHelper {
 			list($alias, $columnName) = explode('.', $string, 2);
 
 			if ($columnName === '*') {
-				return $string;
+				return '`' . $alias . '`.*';
 			}
 
 			return '`' . $alias . '`.`' . $columnName . '`';
diff --git a/lib/private/Files/Config/UserMountCache.php b/lib/private/Files/Config/UserMountCache.php
index 7ecad6e8cfb..9466aaf6c89 100644
--- a/lib/private/Files/Config/UserMountCache.php
+++ b/lib/private/Files/Config/UserMountCache.php
@@ -365,6 +365,11 @@ class UserMountCache implements IUserMountCache {
 
 		$result = $query->execute();
 
-		return $result->fetchAll(\PDO::FETCH_KEY_PAIR);
+		$results = [];
+		while ($row = $result->fetch()) {
+			$results[$row['user_id']] = $row['size'];
+		}
+		$result->closeCursor();
+		return $results;
 	}
 }
diff --git a/lib/private/Repair/NC12/UpdateLanguageCodes.php b/lib/private/Repair/NC12/UpdateLanguageCodes.php
index 891473f51a7..f4a1204543b 100644
--- a/lib/private/Repair/NC12/UpdateLanguageCodes.php
+++ b/lib/private/Repair/NC12/UpdateLanguageCodes.php
@@ -23,6 +23,7 @@
 
 namespace OC\Repair\NC12;
 
+use OCP\DB\QueryBuilder\IQueryBuilder;
 use OCP\IConfig;
 use OCP\IDBConnection;
 use OCP\Migration\IOutput;
@@ -80,7 +81,7 @@ class UpdateLanguageCodes implements IRepairStep {
 				->set('configvalue', $qb->createNamedParameter($newCode))
 				->where($qb->expr()->eq('appid', $qb->createNamedParameter('core')))
 				->andWhere($qb->expr()->eq('configkey', $qb->createNamedParameter('lang')))
-				->andWhere($qb->expr()->eq('configvalue', $qb->createNamedParameter($oldCode)))
+				->andWhere($qb->expr()->eq('configvalue', $qb->createNamedParameter($oldCode), IQueryBuilder::PARAM_STR))
 				->execute();
 
 			$output->info('Changed ' . $affectedRows . ' setting(s) from "' . $oldCode . '" to "' . $newCode . '" in preferences table.');
diff --git a/lib/private/Repair/NC13/RepairInvalidPaths.php b/lib/private/Repair/NC13/RepairInvalidPaths.php
index 5a4187949a7..29a0e1ed157 100644
--- a/lib/private/Repair/NC13/RepairInvalidPaths.php
+++ b/lib/private/Repair/NC13/RepairInvalidPaths.php
@@ -64,17 +64,19 @@ class RepairInvalidPaths implements IRepairStep {
 		);
 
 		//select f.path, f.parent,p.path from oc_filecache f inner join oc_filecache p on f.parent=p.fileid and p.path!='' where f.path != p.path || '/' || f.name;
-		$query = $builder->select('f.fileid', 'f.path', 'p.path AS parent_path', 'f.name', 'f.parent', 'f.storage', 'p.storage as parent_storage')
+		$builder->select('f.fileid', 'f.path', 'f.name', 'f.parent', 'f.storage')
+			->selectAlias('p.path', 'parent_path')
+			->selectAlias('p.storage', 'parent_storage')
 			->from('filecache', 'f')
 			->innerJoin('f', 'filecache', 'p', $builder->expr()->andX(
 				$builder->expr()->eq('f.parent', 'p.fileid'),
-				$builder->expr()->neq('p.name', $builder->createNamedParameter(''))
+				$builder->expr()->nonEmptyString('p.name')
 			))
 			->where($builder->expr()->neq('f.path', $computedPath))
 			->setMaxResults(self::MAX_ROWS);
 
 		do {
-			$result = $query->execute();
+			$result = $builder->execute();
 			$rows = $result->fetchAll();
 			foreach ($rows as $row) {
 				yield $row;
diff --git a/lib/private/Share/Share.php b/lib/private/Share/Share.php
index 8938c6f3066..93117bf3c7e 100644
--- a/lib/private/Share/Share.php
+++ b/lib/private/Share/Share.php
@@ -338,6 +338,7 @@ class Share extends Constants {
 							}
 						}
 					}
+					$result->closeCursor();
 				}
 			}
 
@@ -1460,8 +1461,9 @@ class Share extends Constants {
 			->from('share')
 			->where($qb->expr()->eq('id', $qb->createParameter('shareId')))
 			->setParameter(':shareId', $shareId);
-		$result = $qb->execute();
-		$result = $result->fetch();
+		$dbResult = $qb->execute();
+		$result = $dbResult->fetch();
+		$dbResult->closeCursor();
 
 		if (empty($result)) {
 			throw new \Exception('Share not found');
diff --git a/lib/private/User/Manager.php b/lib/private/User/Manager.php
index ded807a223b..8f3c98d4b5e 100644
--- a/lib/private/User/Manager.php
+++ b/lib/private/User/Manager.php
@@ -34,6 +34,7 @@
 namespace OC\User;
 
 use OC\Hooks\PublicEmitter;
+use OCP\DB\QueryBuilder\IQueryBuilder;
 use OCP\IUser;
 use OCP\IUserBackend;
 use OCP\IUserManager;
@@ -436,7 +437,7 @@ class Manager extends PublicEmitter implements IUserManager {
 			->from('preferences')
 			->where($queryBuilder->expr()->eq('appid', $queryBuilder->createNamedParameter('core')))
 			->andWhere($queryBuilder->expr()->eq('configkey', $queryBuilder->createNamedParameter('enabled')))
-			->andWhere($queryBuilder->expr()->eq('configvalue', $queryBuilder->createNamedParameter('false')));
+			->andWhere($queryBuilder->expr()->eq('configvalue', $queryBuilder->createNamedParameter('false'), IQueryBuilder::PARAM_STR));
 
 		$query = $queryBuilder->execute();
 
diff --git a/lib/public/DB/QueryBuilder/IExpressionBuilder.php b/lib/public/DB/QueryBuilder/IExpressionBuilder.php
index c123875b803..eab93b52f8a 100644
--- a/lib/public/DB/QueryBuilder/IExpressionBuilder.php
+++ b/lib/public/DB/QueryBuilder/IExpressionBuilder.php
@@ -305,6 +305,24 @@ interface IExpressionBuilder {
 	 */
 	public function notIn($x, $y, $type = null);
 
+	/**
+	 * Creates a $x = '' statement, because Oracle needs a different check
+	 *
+	 * @param string $x The field in string format to be inspected by the comparison.
+	 * @return string
+	 * @since 13.0.0
+	 */
+	public function emptyString($x);
+
+	/**
+	 * Creates a `$x <> ''` statement, because Oracle needs a different check
+	 *
+	 * @param string $x The field in string format to be inspected by the comparison.
+	 * @return string
+	 * @since 13.0.0
+	 */
+	public function nonEmptyString($x);
+
 
 	/**
 	 * Creates a bitwise AND comparison