Massive problems in DB::_protect_identifiers

[eluser]Cristiano Bianchi[/eluser]
Hi all,

basically, we have some 20 project working fine in CI 1.6.1 to 1.6.3.

None of them works with 1.7.0.

Which is a massive headache, if this is a sign of the direction to come...

The big issue is the _protect_identifiers, which there is no way to skip if you use Active Record queries (MySQL). It seems that the function seems to try to be too good for its own right. I have a perfectly legitimate query, such as:

Code:

$this->db->select("
        c.id,
        c.name,
        c.rank,
        c.parentId,
        n.controller,
        ifnull(t.name,'page') as template
    ")
    ->from("{$this->content} c")
    ->join("content c1", "c1.id = c.id and c1.online = {$this->online}")
    ->join("node n","n.id = c.nodeId")
    ->join("category t","t.id = c.custom9", "left")
    ->where("c.typeId", 100);
$this->db->where("c.rank {$rankCondition}"); // Rank over 100 are for footer pages
$this->db->order_by("c.rank");
$query = $this->db->get();
$result = $query->result();

Code:

SELECT
`c`.`id`,
`c`.`name`,
`c`.`rank`,
`c`.`parentId`,
`n`.`controller`,
`ifnull(t`.`name`, `'page')` as template
FROM (`content_live` c)
JOIN `content` c1 ON `c1`.`id` = `c`.`id` and c1.online = 1
JOIN `node` n ON `n`.`id` = `c`.`nodeId`
LEFT JOIN `category` t ON `t`.`id` = `c`.`custom9`
WHERE `c`.`typeId` = 100 AND `c`.`rank` < 100
ORDER BY `c`.`rank`

Look at what a mess CI does!
Is it not possible to have a DB param in database.php config to actually SKIP this? While I do appreciate that the deep automatism could be beneficial to some users, can we not assume that some of us actually know what we are doing and don't need to be 'protected'?

Probably adding some spaces here and there would help, but why should I do, considering this is legitimate MySQL code? It's going to require a massive re-work for the hundreds of queries we have...

I have noticed that a lot of people have issue with this as well.

This looks like a massive show stopper for us :-(

I am willing to add the extra param myself :-)

CI is great, because it leaves you a lot of freedom, but why do we need to extra safety net?

Best, Cristiano

[eluser]m4rw3r[/eluser]
Use select(something, false) to prevent escape/protect identifiers in the SELECT part

[eluser]Cristiano Bianchi[/eluser]
Thanks for your reply, but there are two problems.

1. I would need to go through tens of models and hundred of queries to add the 'FALSE' condition.
2. And worse, that would only fix the issue for the SELECT statements, but the problems are also in every other statement (WHERE, JOIN, etc)

And by the way, there are problems with the fix you propose, which people in other threads claim it does not fix the issue.

Any other idea?

Thanks, Cristiano

[eluser]m4rw3r[/eluser]
I don't really know if there is some kind of elegant solution.

The problem is that AR is a very integral part of the db lib, so it is hard to replace.
(but I've heard that some people have replaced the 1.7 db lib with the 1.6.3)

[eluser]Cristiano Bianchi[/eluser]
I think the only real 'elegant' solution is to have an extra parameter in the DB config, that can be set as false/true.
You are right, the _protect_identifiers is used thoroughly in the AR library, but I guess the only way forward is to do some extra work in removing it or make it parametric.

Odd, given the number of people that have mentioned the problem, that no 'official' line has been stated.

Things started to get bad in 1.6.3, so that I had to place some of the queries in arrays, but now they got much worse...

Best regards, Cristiano

[eluser]James Gifford[/eluser]
I agree.

In dbforge, _protect_identifiers is passed an array even though it doesn't know what to do with an array. This causes the code to break whenever using more than one key field. Seems like a big oversight.

[eluser]Cristiano Bianchi[/eluser]
I did some more investigation. The problem is mostly in DB_driver::_escape_identifiers. I also tried to set the $escape_character to an empty string, de-facto avoiding the escapism... But _protect_identifiers still breaks the query. Ellislab, any comment?

Thanks, Cristiano

[eluser]Jacob156[/eluser]
You can edit the file below to set _protect_identifiers to false.
CI->system->database->DB_driver.php line 1193:

function _protect_identifiers($item, $prefix_single = FALSE, $protect_identifiers = NULL, $field_exists = TRUE)


to

function _protect_identifiers($item, $prefix_single = FALSE, $protect_identifiers = FALSE, $field_exists = TRUE)



This allowed me to to use all the MYSQL code in my projects without having to add false to each statement.

[eluser]AgentPhoenix[/eluser]
[quote author="Cristiano Bianchi" date="1226101254"]I did some more investigation. The problem is mostly in DB_driver::_escape_identifiers. I also tried to set the $escape_character to an empty string, de-facto avoiding the escapism... But _protect_identifiers still breaks the query. Ellislab, any comment?

Thanks, Cristiano[/quote]

I have a fix for this issue in this thread. Not sure if it's the way EL will fix it, but it works for me.

[eluser]Cristiano Bianchi[/eluser]
That sound like a (very good) interim solution :-)
But I guess someone will have to address the issue, sooner or later...

With best regards,

Cristiano