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)
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-selectavgI 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.6branch.