Codeigniter issue with escaping values when passing array to query with "in" in the where clause

Question

I have the function below in my model for a codeigniter project, and the variable $id is an array and for example, contains (1,2,3). Now that i'm revisiting it, I think that i'm not actually escaping my array $id. I think I would have to change the line $this->db->escape($id) to $id = $this->db->escape($id)

If I do that, then it puts single quotes around every element in the array and treats it as one long string like this: '(1,2,3)'.

Can someone confirm that I am not actually escaping my variable and either suggest a solution or let me know if this is a bug within the codeigniter framework?

function get_ratings($id)
    {

        $this->db->escape($id); // had to manually escape the variable since it's being used in an "in" in the where clause. 

        $sql = "select * from toys t
                 where t.toy_id in ($id)";

        $query = $this->db->query($sql, $id);

        if($query->num_rows() > 0)
        {
            return $query->result_array();
        }
        else
        {
            return false;
        }
    }

Answer 1

You may be interested in using the CI Active Record class:

Beyond simplicity, a major benefit to using the Active Record features is that it allows you to create database independent applications, since the query syntax is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system.

Your rewritten query would look like this (assuming $id is an array):

$this->db->where_in('toy_id', $id)->get('toys');

Aside: I will admit I am a bit confused, as it looks like $ids would be a more appropriate variable name, and the way you are using it in the query, I would assume it is a string...

If active record is not your thing, you may also find Query Bindings to be useful:

The secondary benefit of using binds is that the values are automatically escaped, producing safer queries. You don't have to remember to manually escape data; the engine does it automatically for you.

---

EDIT: Looking back on this later, it looks like this is what you're trying to do. In that case, try replacing:

$sql = "select * from toys t where t.toy_id in ($id)";

With:

$sql = "select * from toys t where t.toy_id in (?)";

And pass $id as the second argument to query(), but as a comma separated string (implode(',', $id) if $id is indeed an array).

---

Otherwise you may want to use $this->db->escape_str().

$this->db->escape_str() This function escapes the data passed to it, regardless of type.

Here is an excerpt from the source code of the mysql driver to maybe put your mind at ease.

function escape_str($str, $like = FALSE)
{
    if (is_array($str))
    {
        foreach ($str as $key => $val)
        {
            $str[$key] = $this->escape_str($val, $like);
        }

        return $str;
    }
   // continued...

It loops through arrays and escapes their values.

It does indeed seem that $this->db->escape is not going to work for arrays.

$this->db->escape() This function determines the data type so that it can escape only string data.

Here is the source:

function escape($str)
{
    if (is_string($str))
    {
        $str = "'".$this->escape_str($str)."'";
    }
    elseif (is_bool($str))
    {
        $str = ($str === FALSE) ? 0 : 1;
    }
    elseif (is_null($str))
    {
        $str = 'NULL';
    }

    return $str;
}

Looks like it ignores arrays.

Anyways, hope you find a solution that works for you. My vote is for Active Record.

Answer 2

What you want to do is escape the individual values in the array. So you can use array_map on the array first.

$id = array_map('some_escape_function', $id);

See: http://php.net/manual/en/function.array-map.php

Then you can do:

$in = join(",",$id);

Your SQL would then be:

WHERE t.toy_id in ($in)

Which gives you:

WHERE t.toy_id in ('1','2','3')

Answer 3

You could try something like this:

$sql = 'select * from toys t where t.toy_id in ('.
  join(',',array_map(function($i) {
    return $this->db->escape($i);
  }, $id)).');';

*Disclaimer: I'm not where I can access my PHP/MySQL server right now, so I haven't validated this. Some modification and/or tweakage may be necessary.