RawSql not working in UpdateBatch

[pixobit]

PHP Version

8.0

CodeIgniter4 Version

4.2.6

CodeIgniter4 Installation Method

Manual (zip or tar.gz)

Which operating systems have you tested for this bug?

Windows

Which server did you use?

apache

Database

MySQL 5.6

What happened?

I'm trying to implement a decrement with UpdateBatch, and previously used the escape, but was recommended to use the RawSql class instead. However, when I checked the query, it seems to be escaped, and when I digged deeper, I didn't see RawSql being implemented in the UpdateBatch method.

Steps to Reproduce

$this->table('table')->updateBatch([
    'id' => 1,
    'int_column' => new RawSql('(CASE WHEN int_column > 1 THEN int_column - 1 ELSE 0  END)')
], 'id')

Expected Output

UPDATE `table` SET `int_column` = CASE WHEN `id` = '1' THEN (CASE WHEN int_column > 1 THEN int_column - 1 ELSE 0 END) ELSE `int_column` END WHERE `id` IN('1')

Anything else?

One way to solve this, is to update this line (in Database/BaseBuilder.php) $clean[$this->db->protectIdentifiers($k2, false)] = $escape ? $this->db->escape($v2) : $v2; to $clean[$this->db->protectIdentifiers($k2, false)] = $escape && !($v2 instanceof RawSql) ? $this->db->escape($v2) : (string) $v2;

[kenjis]

Your Steps to Reproduce does not use RawSql at all.

It seems v4.2.6 does not support RawSql in updateBatch(). https://codeigniter4.github.io/CodeIgniter4/changelogs/v4.2.0.html#database

• select(), where(), like(), join() accept the CodeIgniter\Database\RawSql instance.
• DBForge::addField() default value raw SQL string support. See Raw Sql Strings as Default Values.

That's all.

[pixobit]

Sorry about that. I edited the issue to include it. You were the one suggesting RawSql before (here: https://github.com/codeigniter4/CodeIgniter4/pull/6153#issuecomment-1159362412), and the use of RawSql should be consistent... This would feel like a bug to anybody

[sclubricants]

This should be resolved with 4.3.

RawSql was excluded from escape() https://github.com/codeigniter4/CodeIgniter4/pull/6332

This should have been fixed with the setData() method and refactor https://github.com/codeigniter4/CodeIgniter4/pull/6536

Also the updateBatch() method was updated. https://github.com/codeigniter4/CodeIgniter4/pull/6373

[sclubricants]

I have tested this in 4.3 without issue:

    public function testUpdateBatchRawSql()
    {
        $sql = $this->db->table('table')->testMode()->updateBatch([
            'id'         => 1,
            'int_column' => new RawSql('(CASE WHEN int_column > 1 THEN int_column - 1 ELSE 0  END)'),
        ], 'id');

        $expectedSql = <<<'SQL'
            UPDATE `db_table`
            SET
            `int_column` = _u.`int_column`
            FROM (
            SELECT 1 `id`, (CASE WHEN int_column > 1 THEN int_column - 1 ELSE 0  END) `int_column`
            ) _u
            WHERE `db_table`.`id` = _u.`id`
            SQL;
        $this->assertStringContainsString($expectedSql, $sql[0]);
    }