QueryBuilder inside

Any time QueryBuilder where() and select() functions contain SQL functions (e.g., DATE(), ISNULL(), etc.) table prefix is not prepended and tables/columns are not escaped. What is the correct way to get $sale_total and $sale_subtotal to be

PHP Code:

$decimals = totals_decimals();
 $db_prefix = $this->db->getPrefix();

 if($this->config['tax_included'])
 {
 $sale_total = '(CASE WHEN ' . $db_prefix . 'sales_items.discount_type = ' . PERCENT
 . " THEN " . $db_prefix . "sales_items.quantity_purchased * " . $db_prefix . "sales_items.item_unit_price - ROUND(" . $db_prefix . "sales_items.quantity_purchased * " . $db_prefix . "sales_items.item_unit_price * " . $db_prefix . "sales_items.discount / 100, $decimals)"
 . ' ELSE ' . $db_prefix . 'sales_items.quantity_purchased * (' . $db_prefix . 'sales_items.item_unit_price - ' . $db_prefix . 'sales_items.discount) END)';

 $sale_subtotal = '(CASE WHEN ' . $db_prefix . 'sales_items.discount_type = ' . PERCENT
 . " THEN " . $db_prefix . "sales_items.quantity_purchased * " . $db_prefix . "sales_items.item_unit_price - ROUND(" . $db_prefix . "sales_items.quantity_purchased * " . $db_prefix . "sales_items.item_unit_price * " . $db_prefix . "sales_items.discount / 100, $decimals) "
 . 'ELSE ' . $db_prefix . 'sales_items.quantity_purchased * ' . $db_prefix . 'sales_items.item_unit_price - ' . $db_prefix . 'sales_items.discount END * (100 / (100 + ' . $db_prefix . 'sales_items_taxes.percent)))';
 }
 else
 {
 $sale_total = '(CASE WHEN ' . $db_prefix . 'sales_items.discount_type = ' . PERCENT
 . " THEN " . $db_prefix . "sales_items.quantity_purchased * " . $db_prefix . "sales_items.item_unit_price - ROUND(" . $db_prefix . "sales_items.quantity_purchased * " . $db_prefix . "sales_items.item_unit_price * " . $db_prefix . "sales_items.discount / 100, $decimals)"
 . ' ELSE ' . $db_prefix . 'sales_items.quantity_purchased * ' . $db_prefix . 'sales_items.item_unit_price - ' . $db_prefix . 'sales_items.discount END * (1 + (' . $db_prefix . 'sales_items_taxes.percent / 100)))';

 $sale_subtotal = '(CASE WHEN ' . $db_prefix . 'sales_items.discount_type = ' . PERCENT
 . " THEN " . $db_prefix . "sales_items.quantity_purchased * " . $db_prefix . "sales_items.item_unit_price - ROUND(" . $db_prefix . "sales_items.quantity_purchased * " . $db_prefix . "sales_items.item_unit_price * " . $db_prefix . "sales_items.discount / 100, $decimals)"
 . ' ELSE ' . $db_prefix . 'sales_items.quantity_purchased * (' . $db_prefix . 'sales_items.item_unit_price - ' . $db_prefix . 'sales_items.discount) END)';
 }

 $subquery_builder = $this->db->table('sales_items');
 $subquery_builder->select("name AS name, CONCAT(IFNULL(ROUND(percent, $decimals), 0), '%') AS percent, sales.sale_id AS sale_id, $sale_subtotal AS subtotal, IFNULL($db_prefix"."sales_items_taxes.item_tax_amount, 0) AS tax, IFNULL($sale_total, $sale_subtotal) AS total");

 $subquery_builder->join('sales', 'sales_items.sale_id = sales.sale_id', 'inner');
 $subquery_builder->join('sales_items_taxes', 'sales_items.sale_id = sales_items_taxes.sale_id AND sales_items.item_id = sales_items_taxes.item_id AND sales_items.line = sales_items_taxes.line', 'left outer');

 $subquery_builder->where('sale_status', COMPLETED);

 if(empty($this->config['date_or_time_format']))
 {
 $subquery_builder->where('DATE(' . $db_prefix . 'sales.sale_time) BETWEEN ' . $this->db->escape($inputs['start_date']) . ' AND ' . $this->db->escape($inputs['end_date']));
 }
 else
 {
 $subquery_builder->where('sales.sale_time BETWEEN ' . $this->db->escape(rawurldecode($inputs['start_date'])) . ' AND ' . $this->db->escape(rawurldecode($inputs['end_date'])));
 }

 $builder = $this->db->newQuery()->fromSubquery($subquery_builder, 'temp_taxes');
 $builder->select("name, percent, COUNT(DISTINCT sale_id) AS count, ROUND(SUM(subtotal), $decimals) AS subtotal, ROUND(SUM(tax), $decimals) AS tax, ROUND(SUM(total), $decimals) total");
 $builder->groupBy('percent, name');

 return $builder->get()->getResultArray(); 


There are three problems that I can see.

• I have to add $this->db->escape() around the $inputs['start_date'] and $inputs['end_date'] variables in order to get the single quotes around them
  
• I have to add $db_prefix programmatically to anything that appears inside a function or parenthesis (see results below).
  
• Anything appearing inside an SQL function does not get escaped with backticks.
  

Code:

SELECT
  `name`,
  `percent`,
  COUNT(DISTINCT sale_id) AS count,
  ROUND(
    SUM(subtotal),
    2
  ) AS subtotal,
  ROUND(
    SUM(tax),
    2
  ) AS tax,
  ROUND(
    SUM(total),
    2
  ) total
FROM
  (
    SELECT
      `name` AS `name`,
      CONCAT(
        IFNULL(
          ROUND(percent, 2),
          0
        ),
        '%'
      ) AS percent,
      `ospos_sales`.`sale_id` AS `sale_id`,
      (
        CASE WHEN ospos_sales_items.discount_type = 0 THEN ospos_sales_items.quantity_purchased * ospos_sales_items.item_unit_price - ROUND(
          ospos_sales_items.quantity_purchased * ospos_sales_items.item_unit_price * ospos_sales_items.discount / 100,
          2
        ) ELSE ospos_sales_items.quantity_purchased * ospos_sales_items.item_unit_price - ospos_sales_items.discount END * (
          100 / (
            100 + ospos_sales_items_taxes.percent
          )
        )
      ) AS subtotal,
      IFNULL(
        ospos_sales_items_taxes.item_tax_amount,
        0
      ) AS tax,
      IFNULL(
        (
          CASE WHEN ospos_sales_items.discount_type = 0 THEN ospos_sales_items.quantity_purchased * ospos_sales_items.item_unit_price - ROUND(
            ospos_sales_items.quantity_purchased * ospos_sales_items.item_unit_price * ospos_sales_items.discount / 100,
            2
          ) ELSE ospos_sales_items.quantity_purchased * (
            ospos_sales_items.item_unit_price - ospos_sales_items.discount
          ) END
        ),
        (
          CASE WHEN ospos_sales_items.discount_type = 0 THEN ospos_sales_items.quantity_purchased * ospos_sales_items.item_unit_price - ROUND(
            ospos_sales_items.quantity_purchased * ospos_sales_items.item_unit_price * ospos_sales_items.discount / 100,
            2
          ) ELSE ospos_sales_items.quantity_purchased * ospos_sales_items.item_unit_price - ospos_sales_items.discount END * (
            100 / (
              100 + ospos_sales_items_taxes.percent
            )
          )
        )
      ) AS total
    FROM
      `ospos_sales_items`
      INNER JOIN `ospos_sales` ON `ospos_sales_items`.`sale_id` = `ospos_sales`.`sale_id`
      LEFT OUTER JOIN `ospos_sales_items_taxes` ON `ospos_sales_items`.`sale_id` = `ospos_sales_items_taxes`.`sale_id`
      AND `ospos_sales_items`.`item_id` = `ospos_sales_items_taxes`.`item_id`
      AND `ospos_sales_items`.`line` = `ospos_sales_items_taxes`.`line`
    WHERE
      `sale_status` = 0
      AND DATE(ospos_sales.sale_time) BETWEEN '2010-01-01'
      AND '2023-12-31'
  ) `temp_taxes`
GROUP BY
  `percent`,
  `name`

I don't know what is the correct way.
1. Do not use Query Builder. Use $db->query() instead.
2. Use RawSql. See https://codeigniter4.github.io/CodeIgnit...tml#rawsql

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

I don't know what is the correct way.
1. Do not use Query Builder. Use $db->query() instead.
2. Use RawSql. See https://codeigniter4.github.io/CodeIgnit...tml#rawsql

If I understand correctly, both of those options lose protections provided by query builder functions. This seems like a bug to me... like the parser is stopping at anything inside parenthesis.

If I understand correctly, both of those options lose protections provided by query builder functions.

Yes. Don't believe Query Builder's protection for identifiers.
It does not protect many of cases.

Perhaps this is not a bug but a specification.
Query Builder does not support complicated SQL statements.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

So this wouldn't work either? just an example. Cause I have this code from active record codeigniter 2. so if I rewrote this. in query builder?

Code:

$uri_web = $this->uri->segment(3);
$sites = $this->db
->select("
cs_sites.site_id,
cs_sites.site_name,
cs_sites.site_code,
(select cs_web_display.site_id from cs_web_display where cs_web_display.site_id = cs_sites.site_id AND cs_web_display.web_id = '$uri_web') as site_selected,
")
->from("cs_sites")
->get();
return $sites;

In query builder would it work like this below?

Code:

$uri_web = $this->uri->segment(3);
$sites = $this->db->table('cs_sites')
->select("
cs_sites.site_id,
cs_sites.site_name,
cs_sites.site_code,
(select cs_web_display.site_id from cs_web_display where cs_web_display.site_id = cs_sites.site_id AND cs_web_display.web_id = '$uri_web') as site_selected,
")
->get();
return $sites;

Or would this break in query builder too? I have not tested, im curious as the OP, if this kind of subquery would work or not.

I'm not sure if this is the best way but I've been using queryBuilder to make the sub query then a different instance for the outer query.

PHP Code:

$subqueryBuilder = $this->db->table('cs_web_display');
$subqueryBuilder->select('site_id');
...
$subquery = $subqueryBuilder->getCompiledSelect(); 


and then plug $subquery into your outer $builder