globally disabling protect identifiers

[eluser]pocketmax[/eluser]
Is there any way to globally disable protect identifiers?

I have to explicitly turn them off at the end of my select / where active record commands so my sql queries will run...

Code:

$this->db->select('id,lname,fname',false);
$this->db->where('timeout',null,false);
$query = $this->db->get('mylog');

will produce...

SELECT id,lname,fname FROM mylog WHERE timeout IS NULL

vs me not turning them off and it produces...

SELECT 'id,lname,fname' FROM 'mylog' WHERE 'timeout IS NULL'

which doesn't execute and my db throws an error. I don't want to have to keep putting false at the end of my select/where clauses through out my code just to have sql statements that run. Is there something in the config I can use to just universally turn those off so I don't have to manually do it on each line?

[eluser]pocketmax[/eluser]
omg, it happened AGAIN in another piece of code but when I specify NOT to do it...it STILL does it!

Code:

$this->db->where('id',$log_id,false);
$data=$this->db->get('mylog',false)->result_array();

SELECT * FROM "mylog" WHERE id =8882c77d

I told it not to back tick the table name but it did it anyway. This is such an obnoxious feature. It would have been better to disable it by default then this. These are complex queries with sub queries, stored procedure calls or formulas, this is just grabbing 3 fields from a table, come on.

[eluser]danmontgomery[/eluser]
Have you modified the database class? When I use:

Code:

$res = $this->db->select("column_a,column_b")->where("column_c", NULL)->get('test_table')->result();

My query runs normally:

SELECT `column_a`, `column_b` FROM (`test_table`) WHERE `column_c` IS NULL

To answer your question, you can set:

Code:

$this->db->_protect_identifiers = false;

Although, by default all of the where() functions will protect fields, so you would either have to pass FALSE to all of your where() calls, or change the DB_active_rec library so that the where* functions pass $escape = NULL instead of $escape = TRUE.

[eluser]danmontgomery[/eluser]
[quote author="pocketmax" date="1263511235"]omg, it happened AGAIN in another piece of code but when I specify NOT to do it...it STILL does it!

Code:

$this->db->where('id',$log_id,false);
$data=$this->db->get('mylog',false)->result_array();

SELECT * FROM "mylog" WHERE id =8882c77d

I told it not to back tick the table name but it did it anyway. This is such an obnoxious feature. It would have been better to disable it by default then this. These are complex queries with sub queries, stored procedure calls or formulas, this is just grabbing 3 fields from a table, come on.[/quote]

get() doesn't take a true/false parameter for escaping the table name, it takes limit and offset parameters.

[eluser]pocketmax[/eluser]
It still does it. Heres my constructor for my controller...

Code:

function __construct(){
                parent::Controller();
                $this->load->database('mydb');
                $this->db->_protect_identifiers = false;
        }

Heres the code in my controller

Code:

$this->db->select('id,lname,fname');
$this->db->where('timeout',null);
$this->db->get('mylog')->result_array()

And my sql now reads...

SELECT log_id, lname, fname FROM mylog WHERE "timeout" IS NULL

So when I get rid of the falses to turn it off in the commands and turn it off globally in the constructor, it still back ticks the where field yet it leaves the table name and select fields alone. weird.

[eluser]danmontgomery[/eluser]
I explained that in my original post, you have to manually disable protection for all where() functions, or edit the DB_active_rec file so that it doesn't enable protection by default

[eluser]pocketmax[/eluser]
Then that's a bug in code igniter, or they need to update there documentation...

http://ellislab.com/codeigniter/user-gui...ecord.html

Towards the bottom of the page it gives a where example...

Code:

$this->db->where('name', $name);
// Produces: WHERE name = 'Joe'

I ran that exact same example and got

SELECT * WHERE "foo" = 'bar'

They didn't disable protection in there code and they got the result I couldn't get. I guess my conflict is it seems (to me anyway) that the db handling logic is flawed. In order to execute a simple query on my database, I have to explicitly disable things so it won't break. So for example...

Code:

$this->db->select('foo');
$this->db->where('foo','data');
$results = $this->db->get('bar');

will produce

Code:

SELECT "foo" FROM "bar" WHERE "foo" = 'data'

and break my query in three places. In order to prevent that from happening, I have to globally turn something off...

Code:

$this->db->_protect_identifiers = false;
//resulting in SELECT foo FROM bar WHERE "foo" = 'data'

and my code will still break. I also have to disable something in each where clause I use there by not breaking my query.

I would highly suggest to the ci team to come up with a patch in the config so if I put something like

$protect_identifiers=FALSE

it globally turns them off (including where clauses) and you can turn them back on by using true as the switch in each active record call that needs it. That way pre-existing apps don't have to be adjusted and future apps can take advantage of the patch.

[eluser]danmontgomery[/eluser]
Your active record library is surrounding fieldnames with double quotes...?

[eluser]pocketmax[/eluser]
For some weird reason yes and I didn't touch a line of lib code. My ver is 1.7.2

[eluser]danmontgomery[/eluser]
Sounds like you changed your DB driver from MySQL to something else... oci8 and postgresql both use " as field delimiters... Can you paste the contents of your config/database.php?

[edit]

Also, the value of var $_escape_char (system/database/drivers/mysql/mysql_driver.php, line 36)