Bug: [QueryBuilder] BaseBuilder::getOperator doesn't recognize LIKE operator in array expression

[sneakyimp]

Describe the bug I am migrating some CI3 code to CI4 and apparently the Builder::where functionality doesn't properly detect a LIKE operator unless it also includes the pattern to be matched. BaseBuilder::getOperator('col1 LIKE', true) returns false.

CodeIgniter 4 version 4.1.1 (latest develop branch as of this writing)

Affected module(s) BaseBuilder class and anything that extends it.

Expected behavior, and steps to reproduce if appropriate I tried running this code:

$builder = $db->table('user');
$query = $builder->getWhere([
	'id <' => 100,
	'col1 LIKE' => '%gmail%'
]);

I would expect the getWhere functionality to recognize the LIKE operator in the second array key to form this SQL:

SELECT * FROM `user` WHERE `id` < 100 AND `col1` LIKE '%gmail%'

however, it fails to see the LIKE operator, inserts an equal sign, and this invalid SQL is what I get:

SELECT * FROM `user` WHERE `id` < 100 AND `col1` LIKE = '%gmail%'

I suggest a look at the regexes in the BaseBuilder::getOperator and BaseBuilder::hasOperator functions, but we must be very wary about breaking existing functionality or introducing security issues. Should we be concerned about false positives for operators? E.g., what happens if someone creates a table planets with a column named earth like?

Context

• OS: Ubuntu 20
• Web server: Apache 2.4.41
• PHP version 7.4.3

[kenjis]

I confirmed this bug in develop after 4.1.4.

--- a/tests/system/Database/Builder/WhereTest.php
+++ b/tests/system/Database/Builder/WhereTest.php
@@ -111,6 +111,20 @@ final class WhereTest extends CIUnitTestCase
         $this->assertSame($expectedBinds, $builder->getBinds());
     }
 
+    public function testWhereLikeInAssociateArray()
+    {
+        $builder = $this->db->table('user');
+
+        $where = [
+            'id <'      => 100,
+            'col1 LIKE' => '%gmail%',
+        ];
+        $builder->where($where);
+
+        $expectedSQL   = 'SELECT * FROM "user" WHERE "id" < 100 AND "col1" LIKE \'%gmail%\'';
+        $this->assertSame($expectedSQL, str_replace("\n", ' ', $builder->getCompiledSelect()));
+    }
+
     public function testWhereCustomString()
     {
         $builder = $this->db->table('jobs');

Failed asserting that two strings are identical.
Expected :'SELECT * FROM "user" WHERE "id" < 100 AND "col1" LIKE '%gmail%''
Actual   :'SELECT * FROM "user" WHERE "id" < 100 AND "col1" LIKE = '%gmail%''

[kenjis]

On CI3 the following test passes:

	public function test_where_like_in_associative_array()
	{
		$where = array('id <' => 100, 'col1 LIKE' => '%gmail%');
		$sql = $this->db->where($where)->get_compiled_select('user');

		$expectedSQL = 'SELECT * FROM "user" WHERE "id" < 100 AND col1 LIKE \'%gmail%\'';
		$this->assertSame($expectedSQL, str_replace("\n", ' ', $sql));
	}

[kenjis]

I sent PR #6986

[kenjis]

Closed by #6986