CI 1.7 - Error on querys (quotes escaping problems)

[eluser]Nom4d3[/eluser]
I always used my querys like this:

Code:

$this->db->where('O.dope_login', $login);
$this->db->where('O.dope_pass', $pass);
$this->db->where('O.dis_id = D.dis_id');
$query = $this->db->get('dis_operadores AS O, rot_distritos AS D');

I never had any problems. But with 1.7, i got this error:

Code:

A Database Error Occurred

Error Number:

ERROR: missing FROM-clause entry for table "O" LINE 3: WHERE "O"."dope_login" = 'mylogin' ^

SELECT * FROM "dis_operadores" AS O, rot_distritos AS D WHERE "O"."dope_login" = 'mylogin' AND "O"."dope_pass" = 'mypass' AND "O"."dis_id" = D.dis_id

im doing something wrong or this is a new bug? I have a lot of querys with the same problem now :/

[eluser]nico060475[/eluser]
Hi there,

I've just replaced 1.6.3 by 1.7.0 and I also have some queries that stopped working. In my case the quotes used to escape the field names are not set correctly.

for example i have

Code:

$this->db->select("IFNULL(foo,bar)")->get('table');

and the generated code is
SELECT IFNULL(`foo`,`bar)` FROM table;

[eluser]AgentPhoenix[/eluser]
I guess I'll tack on to this one. I'm having issues with the MySQL driver using the Database Forge. When I try to add a primary key, things go downhill. Here is what I'm doing in my controller:

Code:

$this->dbforge->add_field('role_id');
$this->dbforge->add_key('role_id', TRUE);
$this->dbforge->create_table('roles', TRUE);

Everything looks fine until you get to the end where it tries to do the primary key. CI outputs this when I try to run the code:

Code:

(...) PRIMARY KEY `role_id` ()

When I take the query, pule role_id inside the parenthesis, and run it in phpMyAdmin, it works fine. Any ideas?

Update - I did some digging and it turns out that I'm feeding the Forge an array of things and the 1.7 version of _protect_identifiers doesn't account for $item being an array whereas the 1.6.3 version did, hence why things are breaking and it's throwing errors about "Array to string conversion".

[eluser]thespy[/eluser]
I also have a problem after upgrading from 1.6.3 to 1.7 :
I'm using Mysqli.

In some models I have:

Code:

$this->db->select('onetable.*, othertable.name as other_name');

It was working perfectly, but now the query is not handled correctly.

Query error: Unknown column 'onetable.*' in 'field list'

[eluser]Derek Allard[/eluser]
Could those of you having issues with select() try adding the parameter to prevent escaping? IE:

Code:

$this->db->select('onetable.*, othertable.name as other_name', FALSE);

I'm not sure if this is the issue, in fact I don't think it is, but want to eliminate this quickly.

[eluser]skattabrain[/eluser]
you can add miner to list ... i have a deadline so i'm reverting to 1.6.3 for now.

http://ellislab.com/forums/viewthread/94824/

I'm cutting and pasting my issue here, sorry for duplication ...

So this used to work on 1.6.3, it should do the following ...

get from the table 'entries' that have 'active' = 1 that also have either 1 of the 2 items ...

entries.user_id = '.$this->session->userdata('user_id')

OR

entries.public = "1"

Code:

$this->db->from('entries');
$this->db->where('entries.active', '1');

$this->db->where('(entries.user_id = '.$this->session->userdata('user_id').' OR entries.public = "1")');
    
$this->db->join('projects', 'projects.project_id = entries.project_id');
$this->db->join('ci_users', 'ci_users.user_id = entries.user_id');
$this->db->order_by('entries.entry_id', 'desc');
$this->db->limit($limit);
$query = $this->db->get();

this is the code it creates ...

Code:

SELECT * FROM (`entries`)
JOIN `projects` ON `projects`.`project_id` = `entries`.`project_id`
JOIN `ci_users` ON `ci_users`.`user_id` = `entries`.`user_id`
WHERE `entries`.`active` = '1'
AND `(entries`.`user_id` = 3 OR entries.public = "1") ORDER BY `entries`.`entry_id` desc LIMIT 25

this is what it should create ...

Code:

SELECT * FROM (`entries`)
JOIN `projects` ON `projects`.`project_id` = `entries`.`project_id`
JOIN `ci_users` ON `ci_users`.`user_id` = `entries`.`user_id`
WHERE `entries`.`active` = '1'
AND (`entries`.`user_id` = 3 OR entries.public = "1")
ORDER BY `entries`.`entry_id` desc LIMIT 25

also this ... i use active record but probably do things it wasn't intended too ... this also no longer works ...

Code:

$this->db->select('ingredient_item.ingredient_id,
ingredient_to_product.ingredient_id as liveing_id,
ingredient_item.ingredient as name,
ingredient_type.ingredient_type,
ingredient_to_product.product_id');
$this->db->from('ingredient_item');
$this->db->join('ingredient_type', 'ingredient_type.ingredient_type_id = ingredient_item.ingredient_type_id');
$this->db->join('ingredient_to_product', 'ingredient_to_product.product_id =  '.$product_id.' AND ingredient_to_product.ingredient_id =  ingredient_item.ingredient_id');
$this->db->order_by('ingredient_type.ingredient_type');
$this->db->order_by('ingredient_item.ingredient');
$query = $this->db->get();                
return $query;

what it creates ...

Code:

SELECT `ingredient_item`.`ingredient_id`, `ingredient_to_product`.`ingredient_id` as liveing_id, `ingredient_item`.`ingredient` as name, `ingredient_type`.`ingredient_type`, `ingredient_to_product`.`product_id`
FROM (`ingredient_item`)
JOIN `ingredient_type` ON `ingredient_type`.`ingredient_type_id` = `ingredient_item`.`ingredient_type_id`
JOIN `ingredient_to_product` ON `ingredient_to_product`.`product_id` = `1001` AND ingredient_to_product.ingredient_id = ingredient_item.ingredient_id
ORDER BY `ingredient_type`.`ingredient_type`, `ingredient_item`.`ingredient`

[eluser]nico060475[/eluser]
Hi Derek,

The query works again if I set the 2nd parameter in select() and the 3rd paramter in where() to FALSE.

[eluser]thespy[/eluser]
An other related topic about this problem I think:
http://ellislab.com/forums/viewthread/94775/

[eluser]Rick Ellis[/eluser]
We'll try to get all these resolved today.

[eluser]hotmeteor[/eluser]
[quote author="Derek Allard" date="1224877579"]Could those of you having issues with select() try adding the parameter to prevent escaping? IE:

Code:

$this->db->select('onetable.*, othertable.name as other_name', FALSE);

I'm not sure if this is the issue, in fact I don't think it is, but want to eliminate this quickly.[/quote]

I too was having SELECT issues, and this fixed it.

[quote author="Rick Ellis" date="1224883077"]We'll try to get all these resolved today.[/quote]

Thanks Rick, Derek, and team. CI is super.