Bug: mysqli_sql_exception when using ->withIdentities()

[virdb]

PHP Version

7.4.32 | 8.1.12

CodeIgniter4 Version

4.2.10

Shield Version

dev-develop dd18be0

Which operating systems have you tested for this bug?

Linux

Which server did you use?

cli-server (PHP built-in webserver)

Database

MariaDB 10.9.3

Did you customize Shield?

No

What happened?

Consider this code in controller:

$userModel = new UserModel();
$users = $userModel->like('username','')
                            ->withIdentities()
                            ->findAll();

This works returning all not deleted usernames.
If I try to search a specific, existing user ("admin"), it also works finding the user:

$userModel = new UserModel();
$users = $userModel->like('username','adm')
                            ->withIdentities()
                            ->findAll();

Now, if I try the same search, but with a match that doesn't match any user, like:

$userModel = new UserModel();
$users = $userModel->like('username','foo')
                            ->withIdentities()
                            ->findAll();

It generate an error:

mysqli_sql_exception #1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')
ORDER BY `id`' at line 3
SYSTEMPATH/Database/MySQLi/Connection.php at line 292

If I remove ->withIdentities(), it return an empty array

Steps to Reproduce

see previous description

Expected Output

empty array

Anything else?

No response

[kenjis]

Cannot reproduce with MySQL.
This test passes without errors.

        $userModel = new UserModel();
        $users = $userModel->like('username','never-match')
            ->withIdentities()
            ->findAll();

        $this->assertSame([], $users);

[virdb]

I tried also with Mysql 8.0.31 but still have the same error.
Did you tried also in a controller, instead a unit test?

My controller just extend the BaseController and use just the Shield UserModel:

<?php

namespace App\Controllers;

use App\Controllers\BaseController;

use CodeIgniter\Shield\Models\UserModel;

class Test extends BaseController
{
    public function index()
    {
        $userModel = new UserModel();
        $users = $userModel->like('username','never-match')
            ->withIdentities()
            ->findAll();
    }
}

This generate the error for me, on MariaDB and Mysql (I also upgraded Shield to dev-develop 32eb7e7).
There is something I can share to better understand the issue?

Thanks a lot and best regards

[datamweb]

[MGatner]

Can someone try it against this branch of the framework which will supply the whole query in the error log? codeigniter4/CodeIgniter4#6195

[michalsn]

The problem is here: https://github.com/codeigniter4/shield/blob/develop/src/Models/UserModel.php#L70
We should also check if $data is empty. Because later we're producing an empty whereIn() call.

[kenjis]

Sorry, I mistakenly thought it was MySQL, but it was SQLite.

mysqli_sql_exception : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
ORDER BY id' at line 3

SELECT *
FROM `shield_auth_identities`
WHERE `user_id` IN ()
ORDER BY `id`

[datamweb]

Can someone try it against this branch of the framework which will supply the whole query in the error log? codeigniter4/CodeIgniter4#6195

ERROR - 2022-11-08 06:52:14 --> mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')
ORDER BY `id`' at line 3 in P:\WorkApp\cv472\vendor\codeigniter4\framework\system\Database\MySQLi\Connection.php:292
Stack trace:
#0 P:\WorkApp\cv472\vendor\codeigniter4\framework\system\Database\MySQLi\Connection.php(292): mysqli->query('SELECT *\nFROM `...', 0)
#1 P:\WorkApp\cv472\vendor\codeigniter4\framework\system\Database\BaseConnection.php(666): CodeIgniter\Database\MySQLi\Connection->execute('SELECT *\nFROM `...')
#2 P:\WorkApp\cv472\vendor\codeigniter4\framework\system\Database\BaseConnection.php(593): CodeIgniter\Database\BaseConnection->simpleQuery('SELECT *\nFROM `...')
#3 P:\WorkApp\cv472\vendor\codeigniter4\framework\system\Database\BaseBuilder.php(1585): CodeIgniter\Database\BaseConnection->query('SELECT *\nFROM `...', Array, false)
#4 P:\WorkApp\cv472\vendor\codeigniter4\framework\system\Model.php(223): CodeIgniter\Database\BaseBuilder->get()
#5 P:\WorkApp\cv472\vendor\codeigniter4\framework\system\BaseModel.php(585): CodeIgniter\Model->doFindAll(0, 0)
#6 P:\WorkApp\cv472\vendor\codeigniter4\shield\src\Models\UserIdentityModel.php(243): CodeIgniter\BaseModel->findAll()
#7 P:\WorkApp\cv472\vendor\codeigniter4\shield\src\Models\UserModel.php(82): CodeIgniter\Shield\Models\UserIdentityModel->getIdentitiesByUserIds(Array)
#8 P:\WorkApp\cv472\vendor\codeigniter4\framework\system\BaseModel.php(1480): CodeIgniter\Shield\Models\UserModel->fetchIdentities(Array)
#9 P:\WorkApp\cv472\vendor\codeigniter4\framework\system\BaseModel.php(593): CodeIgniter\BaseModel->trigger('afterFind', Array)
#10 P:\WorkApp\cv472\app\Controllers\Home.php(14): CodeIgniter\BaseModel->findAll()
#11 P:\WorkApp\cv472\vendor\codeigniter4\framework\system\CodeIgniter.php(927): App\Controllers\Home->index()
#12 P:\WorkApp\cv472\vendor\codeigniter4\framework\system\CodeIgniter.php(482): CodeIgniter\CodeIgniter->runController(Object(App\Controllers\Home))
#13 P:\WorkApp\cv472\vendor\codeigniter4\framework\system\CodeIgniter.php(351): CodeIgniter\CodeIgniter->handleRequest(NULL, Object(Config\Cache), false)
#14 P:\WorkApp\cv472\public\index.php(67): CodeIgniter\CodeIgniter->run()
#15 P:\WorkApp\cv472\vendor\codeigniter4\framework\system\Commands\Server\rewrite.php(46): require_once('P:\\WorkApp\\cv47...')
#16 {main}
CRITICAL - 2022-11-08 06:52:14 --> You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')
ORDER BY `id`' at line 3
in SYSTEMPATH\Database\MySQLi\Connection.php on line 292.
 1 SYSTEMPATH\Database\MySQLi\Connection.php(292): mysqli->query('SELECT *
FROM `auth_identities`
WHERE `user_id` IN ()
ORDER BY `id`', 0)
 2 SYSTEMPATH\Database\BaseConnection.php(666): CodeIgniter\Database\MySQLi\Connection->execute('SELECT *
FROM `auth_identities`
WHERE `user_id` IN ()
ORDER BY `id`')
 3 SYSTEMPATH\Database\BaseConnection.php(593): CodeIgniter\Database\BaseConnection->simpleQuery('SELECT *
FROM `auth_identities`
WHERE `user_id` IN ()
ORDER BY `id`')
 4 SYSTEMPATH\Database\BaseBuilder.php(1585): CodeIgniter\Database\BaseConnection->query('SELECT *
FROM `auth_identities`
WHERE `user_id` IN :user_id:
ORDER BY `id`', [...], false)
 5 SYSTEMPATH\Model.php(223): CodeIgniter\Database\BaseBuilder->get()
 6 SYSTEMPATH\BaseModel.php(585): CodeIgniter\Model->doFindAll(0, 0)
 7 VENDORPATH\codeigniter4\shield\src\Models\UserIdentityModel.php(243): CodeIgniter\BaseModel->findAll()
 8 VENDORPATH\codeigniter4\shield\src\Models\UserModel.php(82): CodeIgniter\Shield\Models\UserIdentityModel->getIdentitiesByUserIds([])
 9 SYSTEMPATH\BaseModel.php(1480): CodeIgniter\Shield\Models\UserModel->fetchIdentities([...])
10 SYSTEMPATH\BaseModel.php(593): CodeIgniter\BaseModel->trigger('afterFind', [...])
11 APPPATH\Controllers\Home.php(14): CodeIgniter\BaseModel->findAll()
12 SYSTEMPATH\CodeIgniter.php(927): App\Controllers\Home->index()
13 SYSTEMPATH\CodeIgniter.php(482): CodeIgniter\CodeIgniter->runController(Object(App\Controllers\Home))
14 SYSTEMPATH\CodeIgniter.php(351): CodeIgniter\CodeIgniter->handleRequest(null, Object(Config\Cache), false)
15 FCPATH\index.php(67): CodeIgniter\CodeIgniter->run()
16 SYSTEMPATH\Commands\Server\rewrite.php(46): require_once('FCPATH\\index.php')

[datamweb]

@virdb thanks for your report, the problem is now fixed, use the develop branch.

[virdb]

@datamweb you are welcome! Thanks to all for debugging and fixing