mysql forbidden field name in WHERE clause ?

[eluser]symbol.software[/eluser]
Hello CI community,

I just moved to CI and now I'm working to my own user_model class.

I'm trying to query the users table but here is what happens:

When the column name is 'username' the this field is erased from my query!
When I rename the column name to 'userid' or 'uname' or anything else it works fine.

Is this a security protection, how do I avoid this ?

Here is my code:

Code:

$query = $this->db->get_where($this->user_table, array('username'=>$username, 'password'=>$password));

The result of this query is:

Code:

SELECT * FROM (`users`) WHERE ` = 'jhon365' AND `password` = 'mypassword'

Here is the code that works

Code:

$query = $this->db->get_where($this->user_table, array('uname'=>$username, 'password'=>$password));

The result of this query is:

Code:

SELECT * FROM (`users`) WHERE `uname` = 'jhon365' AND `password` = 'mypassword'

It is obvious that active record class is simply erasing the word 'username' from my code.
How do I solve this ? I really like to have my field named 'username'.


Thank you so much!

Michael

[eluser]symbol.software[/eluser]
Am i the only one who's experiencing this error ?

[eluser]überfuzz[/eluser]
Are you sure you don't mess it up somewhere else in the code. Search for usernamn in your code and see if you overwrite it... or something.

[eluser]symbol.software[/eluser]
[quote author="überfuzz" date="1253301979"]Are you sure you don't mess it up somewhere else in the code. Search for usernamn in your code and see if you overwrite it... or something.[/quote]

I am 100% sure I am not messing up the code somewhere else. It is the same line of code. First is not working second is working. It works if I use any string excepting 'username' string. This is really pissing me off because I am sure there is a list of forbidden works CI is filtering before running the queries.

[eluser]überfuzz[/eluser]
Try to echo the variable before and after the query.

[eluser]symbol.software[/eluser]
[quote author="überfuzz" date="1253304517"]Try to echo the variable before and after the query.[/quote]

I have printed everything and works perfect. I am 7 years in PHP development & I have more than just basic understanding of PHP code.

This works:

Code:

$query = $this->db->get_where('users', array('asdfasdf'=>'jon', 'password'=>'password'));

But this is generating the sql wrong:

Code:

$query = $this->db->get_where('users', array('username'=>'jon', 'password'=>'password'));

It works with any string excepting 'username' string.

I am digging into the database library to see what the hell is happening.

[eluser]BrianDHall[/eluser]
Try executing your SQL string 'manually', so as to see if CI is somehow eating the query or if MySQL is the one complaining.

[eluser]symbol.software[/eluser]
[quote author="BrianDHall" date="1253311830"]Try executing your SQL string 'manually', so as to see if CI is somehow eating the query or if MySQL is the one complaining.[/quote]

Brian,

After i tested in so many ways here is the result:

if i place this line of code inside my controller the query is running without problems.

Code:

$query = $this->db->get_where('users', array("username"=>"jon", "password"=>"mypassword"));

But if I place the same line of code inside a model class it gives me the following error:

Code:

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password` = 'mypassword'' at line 3

SELECT * FROM (`users`) WHERE ` = 'jon' AND `password` = 'mypassword'

So it is a matter of how CI is interpreting models and controls. If mysql was the one eating my query then why it works without a problem when I run the query from my controller code?

[eluser]BrianDHall[/eluser]
Hrm...for fun, try this in your model:

Code:

$CI =& get_instance();
$query = $CI->db->get_where('users', array("username"=>"jon", "password"=>"mypassword"));

If that doesn't work I'm rather stumped. Do you have a debugger setup so you can step through the code to see where 'username' is getting erased? I've done things just like what you've done and never seen this problem, so I don't think CI is doing anything by default to get rid of the word 'username'.

You might search all files in your project for instances of username to make sure there isn't something odd somewhere erasing them.

[eluser]symbol.software[/eluser]
[quote author="BrianDHall" date="1253315599"]Hrm...for fun, try this in your model:

Code:

$CI =& get_instance();
$query = $CI->db->get_where('users', array("username"=>"jon", "password"=>"mypassword"));

If that doesn't work I'm rather stumped. Do you have a debugger setup so you can step through the code to see where 'username' is getting erased? I've done things just like what you've done and never seen this problem, so I don't think CI is doing anything by default to get rid of the word 'username'.

You might search all files in your project for instances of username to make sure there isn't something odd somewhere erasing them.[/quote]

Yes this one works!
So you assign the CodeIgniter object to a variable and call the database functions via this new object. I thought this approach would only work for libraries - I am working in a model so I didn't consider that solution. Thanks for your kindness to lighten me up! I'm new to CI so I prefer to not just solve problems without a logical explanation.


Funny thing is that I solved this problem changing the way the query is build:

Code:

$query = $this->db->where('username','Jon');
        $query = $this->db->where('password','password');
        $query = $this->db->limit(1);
        $query = $this->db->get('users');

The above code did the trick perfectly. I assume get_where() method have some particular way of building the query or I don't know what to say..

However your solution is great.
Thank You!!