CI 1.7 - Error on querys (quotes escaping problems)

[eluser]Nom4d3[/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 tried on my query and the error message is another.

Code:

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

Code:

A Database Error Occurred

Error Number:

ERROR: column "mylogin" does not exist 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

when i remove the third parameter from the first 2 'where' calls (both are strings and need the quotes), i back to the error on the my first post.

(sry for my bad english)

[eluser]Maxximus[/eluser]
Using FALSE in the select() part works, but here is another going wrong:

Code:

$this->db->where("LOWER(field) = 'value'");
works as expected, ie: WHERE LOWER(field) = 'value'

But using:
$this->db->where("LOWER(table.field) = 'value'");
gives the full string escaped:
where `LOWER(table`.`field)` = 'value'

You need to use:
$this->db->where("LOWER(table.field) = 'value'", null, false);

This might need improvement in a future version.

[eluser]Spockz[/eluser]
Simply turning escaping off isn't something that should be taken lightly. Some people don't check their variables first. So this could potentially lead to a lot of unsafe sites or parts in sites.
I hope you come up with a fix soon.

[eluser]Maxximus[/eluser]
Did not test it yet, but the problem seems to be fixed in SVN. The problem is only with the backticks, and it won't give you issues with your variables. So impact is virtually none.

Use of backticks is something to be argued anyway, since they do NOT belong in SQL statements. It's just a (My)SQL tweak to ensure keywords will be used as fieldname. So in my opinion it should be off by default, and use it only when you insist using keywords as fieldnames.

In the rare case you allow ';' in your URL, and use an URL segment directly as a SQL sorting field, you have a potential issue with SQL injection without the backticks. If you allow '`' too, you're toast anyway. But hey, anyone doing that deserves to be injected anyway

But since it breaks compatibility with previous versions of CI (like the port problem), a new version without this bug should be released soon.

[eluser]skattabrain[/eluser]
thanks for the heads up ... i'm going to wait for 1.7.1 ... i'm not interested in changing my queries unless i really need to or if i wrote them incorrectly. what stinks is it was such a show stopper for me, that i couldn't really test any of my application on 1.7.

[eluser]Crimp[/eluser]
It's reasonably fast to put a FALSE in there to turn off escaping, for now, in the queries that may need it. The db driver code to protect identifiers was rewritten in 1.7, but I gather from the above that they will tweak this some for another release. Meanwhile, I got all the new form_validation stuff sorted out for a form-heavy app. The show must go on, skattabrain.

[eluser]skattabrain[/eluser]
i'll set up a another repository with 1.7 and give it a shot later or tomorrow. but i have queries that don't even have select statements.

[eluser]thespy[/eluser]
[quote author="skattabrain" date="1225218620"]thanks for the heads up ... i'm going to wait for 1.7.1 ... i'm not interested in changing my queries unless i really need to or if i wrote them incorrectly. what stinks is it was such a show stopper for me, that i couldn't really test any of my application on 1.7.[/quote]

same thing for me.

[eluser]Maxximus[/eluser]
Depends on the amount of queries... Besides that, I was running on 1.7 SVN, and all worked just fine. But these sudden changes in 1.7 Release were a poorly written (yes, burn me on that) and clearly untested new 'feature'. And I'm quite shocked that this was not repaired with a quick new (re-)release, so people can upgrade without these problems.

[eluser]velti[/eluser]
Another example:

Code:

$this->db->select( $this->_table_data.'.ID,
                            '.$this->_table_data.'.title,
                            '.$this->_table_data.'.text,
                            DATE_FORMAT( '.$this->_table_data.".time, '%d-%m-%Y' ) AS time,
                            ".$this->_table_data.'.groupID,
                            '.$this->_table_groups.'.title as grouptitle
                        ');

results in:

Error Number: 1054

Unknown column ''%d-%m-%Y'' in 'field list'

SELECT `news_data`.`ID`, `news_data`.`title`, `news_data`.`text`, DATE_FORMAT( news_data.time, `'%d-%m-%Y'` ) AS time, `news_data`.`groupID`, `news_groups`.`title` as grouptitle FROM (`news_data`) INNER JOIN `news_groups` ON `news_groups`.`ID` = `news_data`.`groupID` ORDER BY `news_data`.`time` DESC LIMIT 8

Using FALSE as 2nd parameter it works as well as before updating. But i think it's not the meaning to use FALSE in every query now.