Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
sneakyimp opened this issue Feb 25, 2021 · 4 comments
Labels
bug Verified issues on the current code behavior or pull requests that will fix them database Issues or pull requests that affect the database layer

Comments

@sneakyimp
Copy link
Contributor

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
@sneakyimp sneakyimp added the bug Verified issues on the current code behavior or pull requests that will fix them label Feb 25, 2021
@MGatner MGatner added the database Issues or pull requests that affect the database layer label May 18, 2021
@kenjis
Copy link
Member

kenjis commented Oct 28, 2021

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
Copy link
Member

kenjis commented Oct 29, 2021

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 kenjis changed the title Bug: BaseBuilder::getOperator doesn't recognize LIKE operator in array expression Bug: [QueryBuilder] BaseBuilder::getOperator doesn't recognize LIKE operator in array expression Apr 2, 2022
@kenjis
Copy link
Member

kenjis commented Dec 17, 2022

I sent PR #6986

@kenjis
Copy link
Member

kenjis commented Dec 23, 2022

Closed by #6986

@kenjis kenjis closed this as completed Dec 23, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Verified issues on the current code behavior or pull requests that will fix them database Issues or pull requests that affect the database layer
Projects
None yet
Development

No branches or pull requests

3 participants