Merge pull request #31696 from nextcloud/fix/user_ldap-fix-migration-lengthcheck-oracle

Use getLengthExpression to measure field length instead of like

6 files changed, 105 insertions, 1 deletions

diff --git a/apps/user_ldap/lib/Migration/Version1120Date20210917155206.php b/apps/user_ldap/lib/Migration/Version1120Date20210917155206.php
index d2b440c4dfe..bca390441d7 100644
--- a/apps/user_ldap/lib/Migration/Version1120Date20210917155206.php
+++ b/apps/user_ldap/lib/Migration/Version1120Date20210917155206.php
@@ -129,7 +129,7 @@ class Version1120Date20210917155206 extends SimpleMigrationStep {
 		$qb = $this->dbc->getQueryBuilder();
 		$qb->select('owncloud_name', 'directory_uuid')
 			->from($table)
-			->where($qb->expr()->like('owncloud_name', $qb->createNamedParameter(str_repeat('_', 65) . '%'), Types::STRING));
+			->where($qb->expr()->gt($qb->func()->octetLength('owncloud_name'), '64', IQueryBuilder::PARAM_INT));
 		return $qb;
 	}
 
diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php
index 03630ea14a3..e0a7549a0ad 100644
--- a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php
+++ b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php
@@ -94,6 +94,18 @@ class FunctionBuilder implements IFunctionBuilder {
 		return new QueryFunction('COUNT(' . $quotedName . ')' . $alias);
 	}
 
+	public function octetLength($field, $alias = ''): IQueryFunction {
+		$alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : '';
+		$quotedName = $this->helper->quoteColumnName($field);
+		return new QueryFunction('OCTET_LENGTH(' . $quotedName . ')' . $alias);
+	}
+
+	public function charLength($field, $alias = ''): IQueryFunction {
+		$alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : '';
+		$quotedName = $this->helper->quoteColumnName($field);
+		return new QueryFunction('CHAR_LENGTH(' . $quotedName . ')' . $alias);
+	}
+
 	public function max($field): IQueryFunction {
 		return new QueryFunction('MAX(' . $this->helper->quoteColumnName($field) . ')');
 	}
diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php
index 43ecf599eba..a9844ec3373 100644
--- a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php
+++ b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php
@@ -91,4 +91,16 @@ class OCIFunctionBuilder extends FunctionBuilder {
 		$separator = $this->connection->quote($separator);
 		return new QueryFunction('LISTAGG(' . $this->helper->quoteColumnName($expr) . ', ' . $separator . ')' . $orderByClause);
 	}
+
+	public function octetLength($field, $alias = ''): IQueryFunction {
+		$alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : '';
+		$quotedName = $this->helper->quoteColumnName($field);
+		return new QueryFunction('LENGTHB(' . $quotedName . ')' . $alias);
+	}
+
+	public function charLength($field, $alias = ''): IQueryFunction {
+		$alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : '';
+		$quotedName = $this->helper->quoteColumnName($field);
+		return new QueryFunction('LENGTH(' . $quotedName . ')' . $alias);
+	}
 }
diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php
index fe700075a82..bb97d2e29f9 100644
--- a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php
+++ b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php
@@ -48,4 +48,16 @@ class SqliteFunctionBuilder extends FunctionBuilder {
 	public function least($x, $y): IQueryFunction {
 		return new QueryFunction('MIN(' . $this->helper->quoteColumnName($x) . ', ' . $this->helper->quoteColumnName($y) . ')');
 	}
+
+	public function octetLength($field, $alias = ''): IQueryFunction {
+		$alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : '';
+		$quotedName = $this->helper->quoteColumnName($field);
+		return new QueryFunction('LENGTH(CAST(' . $quotedName . ' as BLOB))' . $alias);
+	}
+
+	public function charLength($field, $alias = ''): IQueryFunction {
+		$alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : '';
+		$quotedName = $this->helper->quoteColumnName($field);
+		return new QueryFunction('LENGTH(' . $quotedName . ')' . $alias);
+	}
 }
diff --git a/lib/public/DB/QueryBuilder/IFunctionBuilder.php b/lib/public/DB/QueryBuilder/IFunctionBuilder.php
index 04c5cbd07bd..d4edc8ea9f8 100644
--- a/lib/public/DB/QueryBuilder/IFunctionBuilder.php
+++ b/lib/public/DB/QueryBuilder/IFunctionBuilder.php
@@ -124,6 +124,24 @@ interface IFunctionBuilder {
 	public function count($count = '', $alias = ''): IQueryFunction;
 
 	/**
+	 * @param string|ILiteral|IParameter|IQueryFunction $field The input to be measured
+	 * @param string $alias Alias for the length
+	 *
+	 * @return IQueryFunction
+	 * @since 24.0.0
+	 */
+	public function octetLength($field, $alias = ''): IQueryFunction;
+
+	/**
+	 * @param string|ILiteral|IParameter|IQueryFunction $field The input to be measured
+	 * @param string $alias Alias for the length
+	 *
+	 * @return IQueryFunction
+	 * @since 24.0.0
+	 */
+	public function charLength($field, $alias = ''): IQueryFunction;
+
+	/**
 	 * Takes the maximum of all rows in a column
 	 *
 	 * If you want to get the maximum value of multiple columns in the same row, use `greatest` instead
diff --git a/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php b/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php
index 3f4b8bb7524..08392b09d8d 100644
--- a/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php
+++ b/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php
@@ -336,6 +336,56 @@ class FunctionBuilderTest extends TestCase {
 		$this->assertGreaterThan(1, $column);
 	}
 
+	public function octetLengthProvider() {
+		return [
+			['', 0],
+			['foobar', 6],
+			['fé', 3],
+			['šđčćž', 10],
+		];
+	}
+
+	/**
+	 * @dataProvider octetLengthProvider
+	 */
+	public function testOctetLength(string $str, int $bytes) {
+		$query = $this->connection->getQueryBuilder();
+
+		$query->select($query->func()->octetLength($query->createNamedParameter($str, IQueryBuilder::PARAM_STR)));
+		$query->from('appconfig')
+			->setMaxResults(1);
+
+		$result = $query->execute();
+		$column = $result->fetchOne();
+		$result->closeCursor();
+		$this->assertEquals($bytes, $column);
+	}
+
+	public function charLengthProvider() {
+		return [
+			['', 0],
+			['foobar', 6],
+			['fé', 2],
+			['šđčćž', 5],
+		];
+	}
+
+	/**
+	 * @dataProvider charLengthProvider
+	 */
+	public function testCharLength(string $str, int $bytes) {
+		$query = $this->connection->getQueryBuilder();
+
+		$query->select($query->func()->charLength($query->createNamedParameter($str, IQueryBuilder::PARAM_STR)));
+		$query->from('appconfig')
+			->setMaxResults(1);
+
+		$result = $query->execute();
+		$column = $result->fetchOne();
+		$result->closeCursor();
+		$this->assertEquals($bytes, $column);
+	}
+
 	private function setUpMinMax($value) {
 		$query = $this->connection->getQueryBuilder();