1054 error on Join using active record for multiple sites using single code base (erkanaauth modification)

[eluser]oppenheimer[/eluser]
Hello

I am getting the following error:

Code:

Error Number: 1054

Unknown column '12' in 'on clause'

SELECT `roles`.`name` FROM (`12-users`) JOIN `roles` ON `12`-`users`.`role_id` = roles.id WHERE `12-users`.`id` = '1' LIMIT 1

I have modified the Erkanaauth library to use a different table depending on the URL (to handle multiple sites on the same code base). The code is:

Code:

function getRole($schoolid) {
        $this->CI->db->select('roles.name');
        $this->CI->db->JOIN('roles', $schoolid . '-users.role_id = roles.id');
        $query = $this->CI->db->getwhere($schoolid . '-users', array($schoolid . '-users.id'=>$this->CI->session->userdata('user_id')), 1, 0);
        if ($query->num_rows() == 1) {
            $row = $query->row();
            return $row->name;
        }
    }

For this example, $schoolid=12
The tables are:

Code:

TABLE: 12-users
Field    Type
id     int(11)
email     varchar(40)
password     varchar(128)
role_id     int(11)
attempts     tinyint(3)

Code:

TABLE: roles
Field    Type
id     smallint(5)
name     varchar(10)

It seems to me that the trying to make the table name variable is causing a syntax error but I can't seem to figure out a way to fix. Other queries work great but I'm hung up on the join.

Thank you.

[eluser]oppenheimer[/eluser]
D'oh!

It turns out that in Version 1.6.0, getwhere was deprecated and was replaced by get_where. The Erkanaauth code was still using the getwhere. Once I changed to get_where, everything worked!

[eluser]oppenheimer[/eluser]
Double D'oh.

That wasn't the problem! I was using an old version of the code. I think it may be a bug in the active record $this->db->join where it interprets the the beginning of the table named "12-users" as a number. However, I don't think it should because the table name does contain text. The following should be to demonstrate.

Code:

$this->db->select('roles.name');
$this->db->JOIN('roles', '12-users.role_id = roles.id');
$query = $this->db->get('12-users');

[eluser]oppenheimer[/eluser]
Looks like a bug report was already filed on incorrect escaping with active record join:

http://codeigniter.com/bug_tracker/bug/7692/

[eluser]oppenheimer[/eluser]
The workaround was to build the join without the active record class. I used manual query instead: $query = $this->db->query

It was very tricky to get exactly the right syntax with correct escapes.

Code:

$query = $this->CI->db->query('SELECT roles.name FROM `' .$schoolid . '-users` JOIN roles ON `' . $schoolid . '-users`.`role_id` = roles.id where `' . $schoolid . '-users`.`id` = 1 LIMIT 1');