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.

One thought on “Custom string error in CodeIginter where clause

  1. HEY, very well written, this is very useful for me I want to ask some queries .
    Which is better, PHP or Python? Why? Should I learn PHP in 2019? Is it still worth it? Why is PHP hated by so many developers? How can current development worked?

Leave a Reply