Merge pull request #7419 from Abijeet/feature-7175

Fixes #7175 - Allow to search for email address in user management 

8 files changed, 80 insertions, 48 deletions

diff --git a/lib/private/DB/Connection.php b/lib/private/DB/Connection.php
index b9ef447b74b..8a8a2f100ef 100644
--- a/lib/private/DB/Connection.php
+++ b/lib/private/DB/Connection.php
@@ -401,7 +401,7 @@ class Connection extends \Doctrine\DBAL\Connection implements IDBConnection {
 	}
 
 	/**
-	 * Espace a parameter to be used in a LIKE query
+	 * Escape a parameter to be used in a LIKE query
 	 *
 	 * @param string $param
 	 * @return string
diff --git a/lib/private/DB/QueryBuilder/ExpressionBuilder/ExpressionBuilder.php b/lib/private/DB/QueryBuilder/ExpressionBuilder/ExpressionBuilder.php
index a32ae4a1827..154f2447c77 100644
--- a/lib/private/DB/QueryBuilder/ExpressionBuilder/ExpressionBuilder.php
+++ b/lib/private/DB/QueryBuilder/ExpressionBuilder/ExpressionBuilder.php
@@ -26,6 +26,7 @@ namespace OC\DB\QueryBuilder\ExpressionBuilder;
 
 use Doctrine\DBAL\Query\Expression\ExpressionBuilder as DoctrineExpressionBuilder;
 use OC\DB\QueryBuilder\CompositeExpression;
+use OC\DB\QueryBuilder\FunctionBuilder\FunctionBuilder;
 use OC\DB\QueryBuilder\Literal;
 use OC\DB\QueryBuilder\QueryFunction;
 use OC\DB\QueryBuilder\QuoteHelper;
@@ -45,15 +46,20 @@ class ExpressionBuilder implements IExpressionBuilder {
 	/** @var IDBConnection */
 	protected $connection;
 
+	/** @var FunctionBuilder */
+	protected $functionBuilder;
+
 	/**
 	 * Initializes a new <tt>ExpressionBuilder</tt>.
 	 *
-	 * @param \OCP\IDBConnection $connection
+	 * @param IDBConnection $connection
+	 * @param IQueryBuilder $queryBuilder
 	 */
-	public function __construct(IDBConnection $connection) {
+	public function __construct(IDBConnection $connection, IQueryBuilder $queryBuilder) {
 		$this->connection = $connection;
 		$this->helper = new QuoteHelper();
 		$this->expressionBuilder = new DoctrineExpressionBuilder($connection);
+		$this->functionBuilder = $queryBuilder->func();
 	}
 
 	/**
@@ -298,9 +304,7 @@ class ExpressionBuilder implements IExpressionBuilder {
 	 * @since 9.0.0
 	 */
 	public function iLike($x, $y, $type = null) {
-		$x = $this->helper->quoteColumnName($x);
-		$y = $this->helper->quoteColumnName($y);
-		return $this->expressionBuilder->comparison("LOWER($x)", 'LIKE', "LOWER($y)");
+		return $this->expressionBuilder->like($this->functionBuilder->lower($x), $this->functionBuilder->lower($y));
 	}
 
 	/**
diff --git a/lib/private/DB/QueryBuilder/ExpressionBuilder/MySqlExpressionBuilder.php b/lib/private/DB/QueryBuilder/ExpressionBuilder/MySqlExpressionBuilder.php
index 17f7fd5aa47..aa7ef8e70be 100644
--- a/lib/private/DB/QueryBuilder/ExpressionBuilder/MySqlExpressionBuilder.php
+++ b/lib/private/DB/QueryBuilder/ExpressionBuilder/MySqlExpressionBuilder.php
@@ -25,6 +25,7 @@ namespace OC\DB\QueryBuilder\ExpressionBuilder;
 
 
 use OC\DB\Connection;
+use OCP\DB\QueryBuilder\IQueryBuilder;
 use OCP\IDBConnection;
 
 class MySqlExpressionBuilder extends ExpressionBuilder {
@@ -34,9 +35,10 @@ class MySqlExpressionBuilder extends ExpressionBuilder {
 
 	/**
 	 * @param \OCP\IDBConnection|Connection $connection
+	 * @param IQueryBuilder $queryBuilder
 	 */
-	public function __construct(IDBConnection $connection) {
-		parent::__construct($connection);
+	public function __construct(IDBConnection $connection, IQueryBuilder $queryBuilder) {
+		parent::__construct($connection, $queryBuilder);
 
 		$params = $connection->getParams();
 		$this->charset = isset($params['charset']) ? $params['charset'] : 'utf8';
diff --git a/lib/private/DB/QueryBuilder/ExpressionBuilder/SqliteExpressionBuilder.php b/lib/private/DB/QueryBuilder/ExpressionBuilder/SqliteExpressionBuilder.php
index 14d8ad33e40..d9e8a1279cc 100644
--- a/lib/private/DB/QueryBuilder/ExpressionBuilder/SqliteExpressionBuilder.php
+++ b/lib/private/DB/QueryBuilder/ExpressionBuilder/SqliteExpressionBuilder.php
@@ -31,4 +31,8 @@ class SqliteExpressionBuilder extends ExpressionBuilder {
 	public function like($x, $y, $type = null) {
 		return parent::like($x, $y, $type) . " ESCAPE '\\'";
 	}
+
+	public function iLike($x, $y, $type = null) {
+		return $this->like($this->functionBuilder->lower($x), $this->functionBuilder->lower($y), $type);
+	}
 }
diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php
index 2789b8cb356..1d745306351 100644
--- a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php
+++ b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php
@@ -59,4 +59,8 @@ class FunctionBuilder implements IFunctionBuilder {
 	public function sum($field) {
 		return new QueryFunction('SUM(' . $this->helper->quoteColumnName($field) . ')');
 	}
+
+	public function lower($field) {
+		return new QueryFunction('LOWER(' . $this->helper->quoteColumnName($field) . ')');
+	}
 }
diff --git a/lib/private/DB/QueryBuilder/QueryBuilder.php b/lib/private/DB/QueryBuilder/QueryBuilder.php
index 58be4b43255..d6f8bb48acd 100644
--- a/lib/private/DB/QueryBuilder/QueryBuilder.php
+++ b/lib/private/DB/QueryBuilder/QueryBuilder.php
@@ -112,15 +112,15 @@ class QueryBuilder implements IQueryBuilder {
 	 */
 	public function expr() {
 		if ($this->connection instanceof OracleConnection) {
-			return new OCIExpressionBuilder($this->connection);
+			return new OCIExpressionBuilder($this->connection, $this);
 		} else if ($this->connection->getDatabasePlatform() instanceof PostgreSqlPlatform) {
-			return new PgSqlExpressionBuilder($this->connection);
+			return new PgSqlExpressionBuilder($this->connection, $this);
 		} else if ($this->connection->getDatabasePlatform() instanceof MySqlPlatform) {
-			return new MySqlExpressionBuilder($this->connection);
+			return new MySqlExpressionBuilder($this->connection, $this);
 		} else if ($this->connection->getDatabasePlatform() instanceof SqlitePlatform) {
-			return new SqliteExpressionBuilder($this->connection);
+			return new SqliteExpressionBuilder($this->connection, $this);
 		} else {
-			return new ExpressionBuilder($this->connection);
+			return new ExpressionBuilder($this->connection, $this);
 		}
 	}
 
diff --git a/lib/private/User/Database.php b/lib/private/User/Database.php
index 471ff1f45ae..6e44c902286 100644
--- a/lib/private/User/Database.php
+++ b/lib/private/User/Database.php
@@ -40,6 +40,7 @@
  * along with this program.  If not, see <http://www.gnu.org/licenses/>
  *
  */
+
 /*
  *
  * The following SQL statement is just a help for developers and will not be
@@ -56,6 +57,7 @@
 namespace OC\User;
 
 use OC\Cache\CappedMemoryCache;
+use OC\DB\QueryBuilder\Literal;
 use OCP\IUserBackend;
 use OCP\Util;
 use Symfony\Component\EventDispatcher\EventDispatcher;
@@ -83,6 +85,7 @@ class Database extends Backend implements IUserBackend {
 
 	/**
 	 * Create a new user
+	 *
 	 * @param string $uid The username of the user to create
 	 * @param string $password The password of the new user
 	 * @return bool
@@ -112,6 +115,7 @@ class Database extends Backend implements IUserBackend {
 
 	/**
 	 * delete a user
+	 *
 	 * @param string $uid The username of the user to delete
 	 * @return bool
 	 *
@@ -131,6 +135,7 @@ class Database extends Backend implements IUserBackend {
 
 	/**
 	 * Set password
+	 *
 	 * @param string $uid The username
 	 * @param string $password The new password
 	 * @return bool
@@ -152,6 +157,7 @@ class Database extends Backend implements IUserBackend {
 
 	/**
 	 * Set display name
+	 *
 	 * @param string $uid The username
 	 * @param string $displayName The new display name
 	 * @return bool
@@ -172,6 +178,7 @@ class Database extends Backend implements IUserBackend {
 
 	/**
 	 * get display name of the user
+	 *
 	 * @param string $uid user ID of the user
 	 * @return string display name
 	 */
@@ -189,20 +196,29 @@ class Database extends Backend implements IUserBackend {
 	 * @return array an array of all displayNames (value) and the corresponding uids (key)
 	 */
 	public function getDisplayNames($search = '', $limit = null, $offset = null) {
-		$parameters = [];
-		$searchLike = '';
-		if ($search !== '') {
-			$parameters[] = '%' . \OC::$server->getDatabaseConnection()->escapeLikeParameter($search) . '%';
-			$parameters[] = '%' . \OC::$server->getDatabaseConnection()->escapeLikeParameter($search) . '%';
-			$searchLike = ' WHERE LOWER(`displayname`) LIKE LOWER(?) OR '
-				. 'LOWER(`uid`) LIKE LOWER(?)';
-		}
+		$connection = \OC::$server->getDatabaseConnection();
+
+		$query = $connection->getQueryBuilder();
+
+		$query->select('uid', 'displayname')
+			->from('users', 'u')
+			->leftJoin('u', 'preferences', 'p', $query->expr()->andX(
+				$query->expr()->eq('userid', 'uid')),
+				$query->expr()->eq('appid', new Literal('settings')),
+				$query->expr()->eq('configkey', new Literal('email'))
+			)
+			// sqlite doesn't like re-using a single named parameter here
+			->where($query->expr()->iLike('uid', $query->createPositionalParameter('%' . $connection->escapeLikeParameter($search) . '%')))
+			->orWhere($query->expr()->iLike('displayname', $query->createPositionalParameter('%' . $connection->escapeLikeParameter($search) . '%')))
+			->orWhere($query->expr()->iLike('configvalue', $query->createPositionalParameter('%' . $connection->escapeLikeParameter($search) . '%')))
+			->orderBy($query->func()->lower('displayname'), 'ASC')
+			->orderBy($query->func()->lower('uid'), 'ASC')
+			->setMaxResults($limit)
+			->setFirstResult($offset);
 
-		$displayNames = array();
-		$query = \OC_DB::prepare('SELECT `uid`, `displayname` FROM `*PREFIX*users`'
-			. $searchLike .' ORDER BY LOWER(`displayname`), LOWER(`uid`) ASC', $limit, $offset);
-		$result = $query->execute($parameters);
-		while ($row = $result->fetchRow()) {
+		$result = $query->execute();
+		$displayNames = [];
+		while ($row = $result->fetch()) {
 			$displayNames[$row['uid']] = $row['displayname'];
 		}
 
@@ -211,6 +227,7 @@ class Database extends Backend implements IUserBackend {
 
 	/**
 	 * Check if the password is correct
+	 *
 	 * @param string $uid The username
 	 * @param string $password The password
 	 * @return string
@@ -226,8 +243,8 @@ class Database extends Backend implements IUserBackend {
 		if ($row) {
 			$storedHash = $row['password'];
 			$newHash = '';
-			if(\OC::$server->getHasher()->verify($password, $storedHash, $newHash)) {
-				if(!empty($newHash)) {
+			if (\OC::$server->getHasher()->verify($password, $storedHash, $newHash)) {
+				if (!empty($newHash)) {
 					$this->setPassword($uid, $password);
 				}
 				return $row['uid'];
@@ -240,15 +257,16 @@ class Database extends Backend implements IUserBackend {
 
 	/**
 	 * Load an user in the cache
+	 *
 	 * @param string $uid the username
 	 * @return boolean true if user was found, false otherwise
 	 */
 	private function loadUser($uid) {
-		$uid = (string) $uid;
+		$uid = (string)$uid;
 		if (!isset($this->cache[$uid])) {
 			//guests $uid could be NULL or ''
 			if ($uid === '') {
-				$this->cache[$uid]=false;
+				$this->cache[$uid] = false;
 				return true;
 			}
 
@@ -285,26 +303,15 @@ class Database extends Backend implements IUserBackend {
 	 * @return string[] an array of all uids
 	 */
 	public function getUsers($search = '', $limit = null, $offset = null) {
-		$parameters = [];
-		$searchLike = '';
-		if ($search !== '') {
-			$parameters[] = '%' . \OC::$server->getDatabaseConnection()->escapeLikeParameter($search) . '%';
-			$searchLike = ' WHERE LOWER(`uid`) LIKE LOWER(?)';
-			$parameters[] = '%' . \OC::$server->getDatabaseConnection()->escapeLikeParameter($search) . '%';
-			$searchLike .= ' OR LOWER(`displayname`) LIKE LOWER(?)';
-		}
-
-		$query = \OC_DB::prepare('SELECT `uid` FROM `*PREFIX*users`' . $searchLike . ' ORDER BY LOWER(`uid`) ASC', $limit, $offset);
-		$result = $query->execute($parameters);
-		$users = array();
-		while ($row = $result->fetchRow()) {
-			$users[] = $row['uid'];
-		}
-		return $users;
+		$users = $this->getDisplayNames($search, $limit, $offset);
+		$userIds = array_keys($users);
+		sort($userIds, SORT_STRING | SORT_FLAG_CASE);
+		return $userIds;
 	}
 
 	/**
 	 * check if a user exists
+	 *
 	 * @param string $uid the username
 	 * @return boolean
 	 */
@@ -315,6 +322,7 @@ class Database extends Backend implements IUserBackend {
 
 	/**
 	 * get the user's home directory
+	 *
 	 * @param string $uid the username
 	 * @return string|false
 	 */
@@ -364,14 +372,15 @@ class Database extends Backend implements IUserBackend {
 
 	/**
 	 * Backend name to be shown in user management
+	 *
 	 * @return string the name of the backend to be shown
 	 */
-	public function getBackendName(){
+	public function getBackendName() {
 		return 'Database';
 	}
 
 	public static function preLoginNameUsedAsUserName($param) {
-		if(!isset($param['uid'])) {
+		if (!isset($param['uid'])) {
 			throw new \Exception('key uid is expected to be set in $param');
 		}
 
diff --git a/lib/public/DB/QueryBuilder/IFunctionBuilder.php b/lib/public/DB/QueryBuilder/IFunctionBuilder.php
index 478fd879404..b82749d5425 100644
--- a/lib/public/DB/QueryBuilder/IFunctionBuilder.php
+++ b/lib/public/DB/QueryBuilder/IFunctionBuilder.php
@@ -71,4 +71,13 @@ interface IFunctionBuilder {
 	 * @since 12.0.0
 	 */
 	public function sum($field);
+
+	/**
+	 * Transforms a string field or value to lower case
+	 *
+	 * @param mixed $field
+	 * @return IQueryFunction
+	 * @since 13.0.0
+	 */
+	public function lower($field);
 }