globally disabling protect identifiers

globally disabling protect identifiers - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: globally disabling protect identifiers (/showthread.php?tid=26468)

Pages: 1 2

globally disabling protect identifiers - El Forum - 01-14-2010

[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?

globally disabling protect identifiers - El Forum - 01-14-2010

[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.

globally disabling protect identifiers - El Forum - 01-14-2010

[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:

Quote: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.

globally disabling protect identifiers - El Forum - 01-14-2010

[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();

globally disabling protect identifiers - El Forum - 01-14-2010

[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.

globally disabling protect identifiers - El Forum - 01-14-2010

[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

globally disabling protect identifiers - El Forum - 01-14-2010

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

http://ellislab.com/codeigniter/user-guide/database/active_record.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.

globally disabling protect identifiers - El Forum - 01-14-2010

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

globally disabling protect identifiers - El Forum - 01-14-2010

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

globally disabling protect identifiers - El Forum - 01-14-2010

[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)