I know most of these issues are resolved by disabling protected identifiers or using $this->db->query(), but that really limits the usefulness of Query Builder in the area where it should be the most helpful. My most recent run-in with this comes down to using the result of $this->db->get_compiled_select() in a call to from(), which, unlike select(), has no parameter to disable the use of protect/escape_identifiers().
Basically, my query builder call is similar to this:
PHP Code:
$this->db->select(
array(
'count(result_alias.table1_key) as table1_count',
'result_alias.col1'
)
)
->from('(
SELECT DISTINCT
`table1`.`key` as `table1_key`,
`table2`.`col1` as `col1`
FROM `table1`
INNER JOIN `table3` ON `table3`.`fkey1` = `table1`.`key`
INNER JOIN `table4` ON `table4`.`fkey` = `table3`.`fkey4`
JOIN `table2` ON `table2`.`key` = `table4`.`fkey2`
WHERE `table1`.`deleted` = 0
) as result_alias'
)
->group_by('result_alias.col1');
The output ends up being something like this:
Code:
SELECT
count(result_alias.table1_key) as table1_count,
`result_alias`.`col1`
FROM (
SELECT DISTINCT
`table1`.`key` as `table1_key`,
`table2`.`col1`` as ``col``
FROM ``table1``
INNER JOIN ``table3`` ON ``table3`.`fkey1`` = ``table1`.`key``
INNER JOIN ``table4`` ON ``table4`.`fkey`` = ``table3`.`fkey4``
JOIN ``table2`` ON ``table2`.`key`` = ``table4`.`fkey2``
WHERE ``table1`.`deleted`` =0
)` as `result_alias`
GROUP BY `result_alias`.`col1`
Notes:
- No issues until after the first comma in the from clause. This is because the from() method explodes the input on the comma (splitting this into 2 parts), then passes each portion of the clause into protect_identifiers, which finds the opening parenthesis and decides not to escape the first portion.
- The second line is ok until you get to the end of the first column name, then the double escape characters begin. I haven't narrowed down why it starts here rather than at the beginning of the line.
- From this point on, everything within the parentheses gets a double escape character unless the escape characters surround the . between the table and column names
- The closing parenthesis gets an escape character.
