Bug: [QueryBuilder] join() with BETWEEN causes TypeError preg_quote(): Argument #1 ($str) must be of type string, false given

[oakhurstmgmt]

PHP Version

8.3

CodeIgniter4 Version

4.5.1

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Windows, Linux

Which server did you use?

apache

Database

MariaDB 11.2.3

What happened?

After upgrading to 4.5.0 and then 4.5.1, I am now getting TypeError preg_quote(): Argument #1 ($str) must be of type string, false given at SYSTEMPATH/Database/BaseBuilder.php at line 680 with some of my SQL queries. These queries all use the BETWEEN Operator to compare dates.

These queries worked perfectly fine going all the way back to CI 4.0.x up through 4.4.8.

Below is a screenshot of the error:

Steps to Reproduce

Execute an SQL query created using the Query Builder Class utilizing the BETWEEN operator.

Expected Output

A view with the results of the SQL query.

Anything else?

No response

[kenjis]

How do we reproduce?
It seems Query Builder does not support BETWEEN.
https://codeigniter4.github.io/CodeIgniter4/database/query_builder.html

[oakhurstmgmt]

I probably did not phrase the bug report correctly. I have two SQL queries that have worked perfectly from CI version 4.0.x all the way through 4.4.8. Both of these queries are constructed using the Query Builder Class. Here are the lines from each Query Builder that appear to be throwing the type error:

->join('leases', 'units.unit_id = leases.unit_id AND CURDATE() BETWEEN lease_start_date AND lease_exp_date', 'left')

->join('leases', 'units.unit_id = leases.unit_id AND workorder_start BETWEEN lease_start_date AND lease_exp_date', 'left')

The only common thread seems to be the use of the BETWEEN SQL operator. I'm not sure that has anything to do with it.

It does seem to be an issue in Query Builder because it no longer puts together the query without throwing an error.

It could simply be a strict typing issue like some of the other 4.5.0 bugs, but I will do some further troubleshooting.

[kenjis]

Thank you for the feedback.

There is no sample code with BETWEEN in join().
https://codeigniter4.github.io/CodeIgniter4/database/query_builder.html#join
and there is no test case:
https://github.com/codeigniter4/CodeIgniter4/blob/develop/tests/system/Database/Builder/JoinTest.php

We need further investigation.

[kenjis]

It seems join() does not support BETWEEN, but it worked by accident in previous versions.

When there is a conditon 'units.unit_id = leases.unit_id AND CURDATE() BETWEEN lease_start_date AND lease_exp_date', it is parsed in $conditions as

array (
  0 => 'units.unit_id = leases.unit_id',
  1 => 'CURDATE() BETWEEN lease_start_date',
  2 => 'lease_exp_date',
)

but it is of course wrong.

Fixing this behavior is difficult, so I sent ad hoc fix PR #8792

[oakhurstmgmt]

Ahh, I understand now.

I replaced the BETWEEN operators in my JOIN clauses with a combination of >= and <= operators to workaround this limitation with Query Builder.

This project also has many WHERE clauses constructed by Query Builder utilizing the BETWEEN operator that are still working as expected in 4.5.1. Does where() officially support BETWEEN or is is this also working by accident?

[kenjis]

There is one test case.

CodeIgniter4/tests/system/Database/Builder/WhereTest.php

Lines 198 to 210 in d425bdf

	public function testWhereCustomStringWithBetweenEscapeFalse(): void
	{
	$builder = $this->db->table('jobs');
	$where = "created_on BETWEEN '2022-07-01 00:00:00' AND '2022-12-31 23:59:59'";
	$builder->where($where, null, false);
	$expectedSQL = "SELECT * FROM \"jobs\" WHERE created_on BETWEEN '2022-07-01 00:00:00' AND '2022-12-31 23:59:59'";
	$this->assertSame($expectedSQL, str_replace("\n", ' ', $builder->getCompiledSelect()));
	$expectedBinds = [];
	$this->assertSame($expectedBinds, $builder->getBinds());
	}

But note that uses false in the third parameter $escape:

CodeIgniter4/tests/system/Database/Builder/WhereTest.php

Line 203 in d425bdf

$builder->where($where, null, false);

If $escape is set to false, the value as is becomes a part of the SQL statement, so you can freely create SQL statements, but QueryBuilder does not provide any protection/escaping.

[kenjis]

@oakhurstmgmt If you agree to merge #8792, approve the PR.