CodeIgniter4: Bug: No DBPrefix or backticks inside SQL functions in QueryBuilder

PHP Version

8.2

CodeIgniter4 Version

4.5.1

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Linux

Which server did you use?

apache

Database

MySQL 8.2.0

What happened?

Backticks and DBPrefix are missing on SQL generated by QueryBuilder function calls when SQL functions are in the parameters.

Steps to Reproduce

This code

$builder = $this->db->table('attribute_links');
$builder->join('attribute_values', 'attribute_values.attribute_id = attribute_links.attribute_id', 'inner');
$builder->set('attribute_links.attribute_id', "IF((attribute_values.attribute_value IN('false','0','') OR (attribute_values.attribute_value IS NULL)), $checkbox_attribute_values[0], $checkbox_attribute_values[1])", false);
$builder->where('attribute_links.definition_id', $definition_id);
log_message('error', $builder->getCompiledUpdate(false));

Yields

UPDATE `ospos_attribute_links` SET `ospos_attribute_links`.`attribute_id` = IF((attribute_values.attribute_value IN('false','0','') OR (attribute_values.attribute_value IS NULL)), 45873, 25595)
WHERE `ospos_attribute_links`.`definition_id` = 6

Note the missing backticks and dbprefix prepended to the attribute_values table and attribute value column inside the IF()

Expected Output

The missing join is a completely different issue and I’ve already submitted a feature request for that, but this affects ISNULL(), IF(), DATE() and CONCAT() that I’ve seen.

UPDATE `ospos_attribute_links`
JOIN `ospos_attribute_values` ON `ospos_attribute_values`.`attribute_id` = `ospos_attribute_links`.`attribute_id` 
SET `ospos_attribute_links`.`attribute_id` = IF((`ospos_attribute_values`.`attribute_value` IN('false','0','') OR (`ospos_attribute_values`.`attribute_value` IS NULL)), 45873, 25595) 
WHERE `ospos_attribute_links`.`definition_id` = 6

Anything else?

See https://forum.codeigniter.com/showthread.php?tid=90798 for more examples of what I mean.

About this issue

  • Original URL
  • State: open
  • Created 3 years ago
  • Comments: 16 (16 by maintainers)

Most upvoted comments

I don’t think it can be fixed. To handle complex SQL, we would have to add support for more methods to generate the appropriate SQL instead of trying to analyze the string with regex.

Yes. Anything with inaccurate parameters can produce unpredictable results. For basic tasks, there are prepared methods $builder->selectAvg('age'); https://codeigniter4.github.io/userguide/database/query_builder.html#builder-selectavg

I don’t think this issue is easy to fix. If someone can fix the current behavior without breaking the existing apps and compromising performance, please send a PR to the 4.6 branch.