Is this a bug in Codeigniter active record?

Question

If I used codeigniter active record to fetch data from a db table like this:

$where = array("first_name" => "John", "age <" => 30, "status" => "active");
$this->db->where($where);
$query = $this->db->get("my_table");

die(var_dump($this->db->last_query())); // displays the query string

This code will produce the following query string:

SELECT * FROM (`my_table`) WHERE `first_name` = 'John' AND `age` < 30 AND `status` = 'active'

Now if I assigned a string to $where instead of the array, like this:

$where = "first_name = 'John' AND age < 30 AND status = 'active'";

Then it will produce the following query string:

SELECT * FROM (`my_table`) WHERE `first_name` = 'John' AND age < 30 AND status = 'active'

Notice that when assign a string to $where, codeigniter added backticks (``) only around the first field's name which is first_name in our case. While on the other hand, codeigniter added backticks around all fields names when we assigned an array to $where

My question: Is this a bug in codeigniter or this is normal? And if I used the following code to prevent codeigniter from adding backticks around fields names:

$this->db->where($where, null, false);

Which will produce the following query string:

SELECT * FROM (`my_table`) WHERE first_name = 'John' AND age < 30 AND status = 'active'

Is there any risks or cons of writing where portion this way?

Answer 1

Q: Is this a bug in codeigniter or this is normal?

• Yes its a bug, but not a serious one.

Q: Is there any risks or cons of writing $this->db->where($where, null, false)?

This code is not risky by itself. and if you are hard coding in your WHERE clause then there would likely never be an issue. The only time an issue would arise is when you would capturing user input, for example a form, or a URL, and this would be something you would have to sanitize manually

its going to be very important that you sanitize your where clause.

BAD Example: Vunerable to SQL Injection

$fname  = $_POST['fname']; 
$age    = $_POST['age'];
$status = $_POST['status'];

$where  = sprintf("first_name = '%s' AND age < %d AND status = '%s'",$fname,$age,$status);
$this->db->where($where, null, false);

BETTER Example: less vunerable

$fname  = (string) mysql_real_escape_string($_POST['fname']);
$age    = (int) $_POST['age'];
$status = (string) mysql_real_escape_string($_POST['status']);

$where  = sprintf("first_name = '%s' AND age < %d AND status = '%s'",$fname,$age,$status);
$this->db->where($where, null, false);

Nothing your doing so far is wrong, it will just put more of the burden to sanitize your where clauses on you, being that using $this->db->where($where, null, false) will remove built in sanitation.

In Short: using this method, makes you more vulnerable initially to SQL injection if you where clauses will have user input provided. But nothing you cant tighten up quickly with a few sanitation functions.

i know this answer inst perfect, but hope it helps.