Custom string error in CodeIginter where clause

What is the trouble?

Base on CodeIgniter Database class user guide, you can pass a custom string directly to the where clause by the $this->db->where() method. Let see the flowing example, suppose that you have successfully initiated your database.

...
$val = 1;
$this->db->where("id='".$val."'");
$this->db->get('mytable');
...

You are sure that your table has a field named id and expects that query returns a record whose id equals to 1. But, ironically, your get an ugly error message!

A Database Error Occurred Error Number: 1054 Unknown column `id=1` in `where clause` SELECT * FROM (`mytable`) WHERE `id=1`

Understand the error

Notes: This section explain how this error arises. If you just want to have a solution, skip to next section.The evil thing happens when the function $this->_protect_identifiers() is call from _where method of CI_DB_active_record class in ./system/database/DB_active_rec.php file. Actually, the goal of the _protect_identifiers() function is to escape the table field name with ‘`’ (backticks) character, which is very helpful when executing some queries with particular field name. Read the source of this function carefully with the notice that our parameters now is $item = ‘id=1’, $prefix_single = FALSE, $protect_identifiers = NULL, $field_exists = TRUE you can find that the hidden culprit is these line:

$alias = '';
if (strpos($item, ' ') !== FALSE)
    {
        $alias = strstr($item, " ");            
                $item = substr($item, 0, - strlen($alias));
    }

The snippet find the first space in $item and then split $item into two sub string. For example, if $item = ‘my_name AS f1′, after executing, $item=’myname’ and $alias = ‘AS f1’. Next, CI “wrap” the $item by a pair of backticks, as a result, the whole of our custom string, which does not have any space, will be “wrapped” and be considered as a field name instead of a valid where clause.

The correct solution

Till the time I wrote this post, only two things you can do to overcome this obstacle: modify your custom string or use the optional parameters of $this->db->where() method.

  • Modify your custom string: remember to leave a space character (” ” – ACSII code is 32) between the field name and the operators. To find out why I suggest this solution, read the explanation section above.
  • Use the optional parameters: instead of $this->db->where($custom_string) , you can use $this->db->where($custom_string, NULL, FALSE) to avoid this error. But, do queries in this way can cause critical errors because it cancel the escaping process, which ensure your input queries is safe to be executed.

Leave a Reply

Your email address will not be published. Required fields are marked *