SQL Select Query with AR and 'where sha1' returns entire table

[eluser]CopyJosh[/eluser]
Hi, I'm sorry I can't be more descriptive, I just spent most of my time putting this to stackoverflow and i'm about to lose internet in a couple minutes from where I am so I am just going to link to it and hope no one yells at me for that before I can get back online in the morning... because i don't know, is this a bug?

Thanks!

http://stackoverflow.com/questions/67542...urns-first

update-------

An updated version of this question is here now.. since it looks like it only happens in the condition of email = < 1 and a raw output of the sha1 hash.. If the integer value passed to email when email = false (0) was put in ''s then query would work correctly. But if email = true (1) it still works correctly without quotes... so confused.

http://stackoverflow.com/questions/67591...-active-re

[eluser]CopyJosh[/eluser]
Okay, so it looks like the problem is not SHA1 at all. This is the code:

Code:

$data = array(
        'email'    => false,
);

Code:

$queryA = $this->db->query('SELECT *
                         FROM `users`
                 WHERE `email` = \''.$data['email'].'\'
                 LIMIT 1
                 OFFSET 0
                ');

Code:

$queryB = $this->db->get_where('users', $data);

The value 'false' is returned because I am actually using the $this->input->post('email') there for some instances of the function that it is contained in. And if there is no email, then that field is potentially left blank. Blank fields are, as I found out, returned as 'false' by the function `post()`.

Now, when I use $queryA, which uses the SQL, the result is that no rows are selected from the db. That is exactly what should be expected since all table rows have an email in them.

When I use $queryB, which uses the Active Records, the result is that the ENTIRE table is selected! This is definitely a problem right?


-- UPDATE

further experimenting gives me the following conditions:

- when 'email' = false
the unescaped SQL (removing quotes from value in queryA) ('email' = false) returns an error

- when 'email' = false
the escaped SQL (as shown in queryA) ('email' = '') returns zero rows

- when 'email' = false
the active record SQL ('email' = 0) returns all rows

- when 'email' = 0
the active record SQL ('email' = 0) returns all rows

- when 'email' = '0'
the active record SQL ('email' = '0') returns zero rows

It looks like the 'where' doesn't automatically '' values.

--- UPDATE again

Err.. it actually looks like this combination of error only happens when sha1 returns raw output data on an unescaped value!! For instance:

Code:

$where = array(
'email'     => 0,
'password'  => sha1(false, true), # sha1($this->input>post('password'), true);
);

if

Code:

sha1(false, true)

is changed to

Code:

sha1('whatever')

there is no error. ('whatever' includes strings, booleans, etc.)

if array is unchanged and,

Code:

'email' => 0

is changed to

Code:

'email' => '0'

which happens in the SQL QueryA, then there is no error either. If I remove the ''s from the value in QueryA, then I get the same error as in Active Records... but the quotes are there for a reason right?

The combination of 'email' = > 0 and sha1('any value', true) results in returning EVERY row in the table in the Active Records because there's no quotes added to the value 0. Quotes are however added around the 'password' = 'value'. If there are no quotes around the raw hash, then SQL returns an Error and prevents the script from running.. (which is better than returning ever row...)

I think this is more an SQL error..