Welcome Guest, Not a member yet? Register   Sign In
Query Builder Protect Identifiers Disable
#1

I am migrating from CI2 to CI3 and previously had this option in my db config:

$db['default']['_protect_identifiers']= FALSE;

When leaving this in, I have a problem with using Session with a database setup. It stops putting quotes around string values apparently.

Error Number: 1054

Unknown column 'f2eced3d7db870757ab1304bf44ceef4c19597c4' in 'where clause'

SELECT data FROM ci_sessions WHERE id = f2eced3d7db870757ab1304bf44ceef4c19597c4

Filename: libraries/Session/drivers/Session_database_driver.php

Line Number: 160



It works fine if I remove that old setting. I have lots of areas that I have used query builder and it is now breaking with identifiers back on. I know that I can kill them off at the method level, but is there a way to turn it off global for the query builder?

Thanks,

Paul
Reply
#2

This should solve it: https://github.com/bcit-ci/CodeIgniter/c...7d333c5e04

However, as explained in the commit message - this is NOT a valid use case and setting _protect_identifiers to FALSE is a hack that you should never use.
Reply
#3

(07-20-2015, 02:36 AM)Narf Wrote: This should solve it: https://github.com/bcit-ci/CodeIgniter/c...7d333c5e04

However, as explained in the commit message - this is NOT a valid use case and setting _protect_identifiers to FALSE is a hack that you should never use.

Thanks Narf!  It would be nice if there could be a way to turn it off that was not considered a hack.  I know this came over from the previous version of CI, but I struggle to see why it is forcing to protect everything with back ticks out of the box.  It would be nice if it was something that could be flipped on globally or per query build for db's that used reserved words, but I would hope that would be more of an outlier situation to have to use it.  It looks like there is a lot of logic built up under there now to try and figure out what is actually referencing a column ore table, and it is being run through there multiple times per query.  I am not sure what the cost of this is, but it would have to seem like it may be an unnecessary performance hit most of the time.

On another note, I think I ran across another bug in the query builder.  I can file a bug report for it, assuming I am not using the query builder wrong...

In my "From" clause, I am specifying a table alias that is get identifiers put around the alias.  I was trying to go down the road of passing FALSE to each method, but it does not look like the from() has that option.


Code:
from('mytable m');


ends up producing


Code:
FROM `mytable` `m`
Reply
#4

(07-20-2015, 06:10 AM)zepernick Wrote: On another note, I think I ran across another bug in the query builder.  I can file a bug report for it, assuming I am not using the query builder wrong...

In my "From" clause, I am specifying a table alias that is get identifiers put around the alias.  I was trying to go down the road of passing FALSE to each method, but it does not look like the from() has that option.



Code:
from('mytable m');


ends up producing



Code:
FROM `mytable` `m`

I don't see how that's a bug ... it's the intended behavior.
Reply
#5

I think the point was that he was trying to prevent it from protecting the identifiers in the first place, but the from() method doesn't give you an option to disable this feature, so you can only disable it by setting _protect_identifiers to FALSE, which you've already mentioned should not be done...
Reply
#6

You are correct, my apologies.  I was getting a mysql error and assuming it was the protection around the alias that was causing the problem.  I was not aware that MySQL allowed that.  It must be something else...I will have to look at the query closer.  It was not happening when _protect_identifiers was off and I had passed FALSE to everything except FROM so just made the assumption that is where the problem was.
Reply
#7

(07-20-2015, 06:43 AM)mwhitney Wrote: I think the point was that he was trying to prevent it from protecting the identifiers in the first place, but the from() method doesn't give you an option to disable this feature, so you can only disable it by setting _protect_identifiers to FALSE, which you've already mentioned should not be done...

I thought it was a bug and I was wrong.  I did not know the alias would allow the ``.  A bad assumption on my end when trying to look for the cause of why a SQL error was happening when _protect_identifers was enabled.  However, I do think that it should be an option to shut it off as a supported feature in the config, OR, disable it on a per query instead of having to pass it on every method call.  Something like


Code:
$this->db->protect_idenifiers(FALSE)->select(...)->where(...)
Reply




Theme © iAndrew 2016 - Forum software by © MyBB