SQL Select Query with CodeIgniter's active rec

问题:

I duplicated this problem on a completely clean installation of CodeIgniter 2.0.2

The following code was added to the default 'welcome' controller, and the I created a 'test_model' to demonstrate the error.

The following can be assumed/changes to config/autoload/database: - correct definition of base url - autoload database library - logging set to 4 (all messages) - database connection parameters

This is what I added to the controllers/welcome.php:

function test_me()
{
    $this->load->model('test_model');

    # data for sql query
    $where = array(
        'email'     => $this->input->post('email'),
        'password'  => sha1($this->input->post('password'), true);
    );

    log_message('info', '----email: '.$where['email']);
    log_message('info', '----password: '.$where['password']);

    # exec query
    $query = $this->test_model->get_user($where);

    log_message('info', '----query->num_rows() ='.$query->num_rows());

    if($query->num_rows() > 0) 
    {
        $row = $query->row();

        log_message('info', '----query->row()->email = '.$row->email);

        print $row->email;
    }
    else
        print "worked correctly.";  # returned ZERO rows

}

This is the models/test_model.php:

class Test_model extends CI_Model {

var $_user_table = 'users';     # user table name

// ------------------------------------------------------------------------

/**
 * Constructor
 *
 * @access  public
 */
public function __construct()
{
    log_message('INFO', 'User model initialized');

    parent::__construct();
}

/**
 * Get_user
 * 
 * Retrieves user data from the database
 *
 * @access  public
 * @param   array   sql select query data
 * @param   int     default return limits to 1 user
 * @param   int     offset  
 * @return  mixed   sql query result
 */
public function get_user($data, $limit = 1, $offset = 0)
{
    foreach($data as $where)
        log_message('INFO', 'Getting user data from db where: '.$where);


    # refer this as QUERY1

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

    # refer this as QUERY2

    #$query = $this->db->get_where($this->_user_table, $data, $limit, $offset);

    log_message('info', 'get last sql query: '.$this->db->last_query());

    return $query;
}
}

In QUERY1, this is just using the straight SQL with all the parameters that are passed to QUERY2. The difference is QUERY2 takes advantage of CodeIgniter's active records.

Now, this is the log produced from running the above:

INFO - 2011-07-19 23:34:01 --> ----email: INFO - 2011-07-19 23:34:01 --> ----password: Ú9£î^kK2U¿ï•`¯Ø INFO - 2011-07-19 23:34:01 --> Getting user data from db where:

INFO - 2011-07-19 23:34:01 --> Getting user data from db where: Ú9£î^kK2U¿ï•`¯Ø

INFO - 2011-07-19 23:34:01 --> get last sql query: SELECT * FROM users WHERE email = '' AND password = 'Ú9£î^kK2U¿ï•`¯Ø ' LIMIT 1 OFFSET 0

INFO - 2011-07-19 23:34:01 --> ----query->num_rows() =0

DEBUG - 2011-07-19 23:34:01 --> Final output sent to browser DEBUG - 2011-07-19 23:34:01 --> Total execution time: 0.0923

The output of the browser is: worked correctly.

And this is what happens when I comment out QUERY1 and run QUERY2 (CI's AR):

INFO - 2011-07-19 23:40:05 --> ----email:

INFO - 2011-07-19 23:40:05 --> ----password: ڹĮ^kK2U࠯֠Я؇

INFO - 2011-07-19 23:40:05 --> Getting user data from db where: INFO - 2011-07-19 23:40:05 --> Getting user data from db where: ڹĮ^kK2U࠯֠Я؇

INFO - 2011-07-19 23:40:05 --> get last sql query: SELECT * FROM (users) WHERE email = 0 AND password = 'ڹĮ^kK\r2U࠯֠Я؇ ' LIMIT 1

INFO - 2011-07-19 23:40:05 --> ----query->num_rows() =1

INFO - 2011-07-19 23:40:05 --> ----query->row()->email = [e-mail]

DEBUG - 2011-07-19 23:40:05 --> Final output sent to browser DEBUG - 2011-07-19 23:40:05 --> Total execution time: 0.0965

The output of the script is [e-mail] This is the FIRST row in the table and it always returns the first row of the table. If I don't use the LIMIT = 1 default, num_rows() actually equals the number of rows in the table.

Now, background, I'm using this as obviously a form script check, but the form is not necessarily always filled out. I use javascript to check, but this is a case where a browser could not have javascript enabled or if the fields are attempted to be submitted blank by the user. The database has no valid blank entries, and should not return any rows. Since i'm storing the sha1 hashes as raw binary(20) data, the sha1('', true) is being used. Oddly this is creating some sort of error I imagine with Active Records in CI and is instead of returning an error, or returning zero rows (as what would be expected) it returns EVER row in the table!

Am I doing something wrong or is this a bug or not the intended way of using active records? Because I would like to keep the method of accepting an array of where clauses in the query.

Thanks

• UPDATE -

Thanks to user commenting below for pushing me in this direction. It seems I was mistaken that the sha1 has anything to do with this actually... The $this->input->post() returns 'False' if there is no post data ( which is in this case ) and thus, the value of 'email' is 'false' in the array.

So now I am actually on a different question which i'll probably repost, and that is why does where email = 'false' return the entire table?

• UPDATE -

Actually, I've never seen this before because it NEVER happens... in QUERY1 (sql), it works correctly. In QUERY2 with ActiveRecords, it does not. SO there is still a difference.