Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

Selecting rows where a column is not null in the CodeIgniter query builder

Errorwhere('col', 'IS NOT NULL') returns zero rows although the column has values

Short answer

The query builder escapes values, so a value such as 'IS NOT NULL' becomes a quoted string, not SQL. Pass the whole condition as one custom string instead: where('col IS NOT NULL'). Because custom strings are not escaped for you, keep user input out of them or escape it by hand. Print the last query to confirm.

  • Applies to CodeIgniter 3.1, CodeIgniter 4.5, PHP 8.3
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print the SQL the builder produced. The WHERE part shows whether IS NOT NULL reached the database as SQL or as a quoted string.

    // CodeIgniter 3
    echo $this->db->last_query();
    // CodeIgniter 4
    echo $db->getLastQuery();
  2. 2Match what you see, then jump to its fix

    1. 1The compiled SQL shows IS NOT NULL inside quotesIS NOT NULL was passed as a valueFix
    2. 2You build the string with concatenation, for example where("col IS NOT NULL AND owner = $id")A custom string mixes in user inputFix
    3. 3The compiled SQL changes your expression, for example around a function callCodeIgniter 3 rewrites the field name in a complex conditionFix

What does the error look like?

  • where('col', 'IS NOT NULL') returns zero rows although the column has values
  • IS NOT NULL appears inside quotes in the compiled SQL

Cause 1 of 3

IS NOT NULL was passed as a value

1Check

In both versions every value given to where() is escaped, which turns a keyword passed as a value into a string literal. Look at how the call is written.

// does not do what you want
$this->db->where('col', 'IS NOT NULL');

2Fix

Give where() the whole condition as one custom string. The CodeIgniter 4 guide itself uses this form for IS NULL in a query builder example.

// CodeIgniter 3
$this->db->where('col IS NOT NULL');
$query = $this->db->get('my_table');

// CodeIgniter 4
$builder = $db->table('my_table');
$builder->where('col IS NOT NULL');
$rows = $builder->get()->getResult();

3Verify

Print the last query again. The WHERE part should read col IS NOT NULL without quotes around IS NOT NULL.

echo $this->db->last_query();

Confirmed by codeigniter.com

Did this fix it?

Cause 2 of 3

A custom string mixes in user input

1Check

Values placed inside a custom string are not escaped by the builder. The guide says you must escape values and protect identifiers yourself, or the query is open to SQL injection.

grep -rn "IS NOT NULL" application/ app/

2Fix

Keep the null test as a custom string, and pass the other conditions as separate where() calls so they stay escaped.

$this->db->where('col IS NOT NULL');
$this->db->where('owner', $id);

3Verify

Print the last query. The owner value should appear escaped and quoted.

echo $this->db->last_query();

Confirmed by codeigniter.com

Did this fix it?

Cause 3 of 3

CodeIgniter 3 rewrites the field name in a complex condition

1Check

The CodeIgniter 3 guide says identifiers are protected unless the third argument of where() is FALSE. This matters when the condition contains a function call or table alias that the builder cannot split.

$this->db->where('LOWER(email) IS NOT NULL');
echo $this->db->get_compiled_select();

2Fix

Pass NULL as the value and FALSE as the third argument, so the builder leaves the string alone. You then own the quoting.

$this->db->where('LOWER(email) IS NOT NULL', NULL, FALSE);

3Verify

Run the query and read the compiled SQL again.

echo $this->db->last_query();

Confirmed by codeigniter.com

Did this fix it?

How do I stop it coming back?

  • Print the compiled query whenever a where() returns nothing, before looking anywhere else.
  • Use a custom string only for the null test and keep real values in separate where() calls.
  • Read the escape rules once for the major version you use, because CI3 and CI4 differ in details.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Back end

Apache returns 403 when a URL contains %3F

a request like /page%3Fname=test is refused while /page?name=test works

Recent Apache httpd blocks a rewrite when the request contains an encoded question mark (%3F) and the rewritten target has a literal ?.

2 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.