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`