Database error searching by all with PostgreSQL

[leonstr]

Steps to reproduce issue

1. Set up a Moodle site using PostgreSQL.
2. Install this plugin.
3. Go to Site administration > Users > Accounts > Merge user accounts > Merge user accounts.
4. In the Search for User box type "abc", select "All" in the drop-down list and click Search.

Expected results

The search results are displayed.

Actual results

Error reading from database

More information about this error
Debug info: ERROR: invalid input syntax for type bigint: "abc"
CONTEXT: unnamed portal parameter $1 = '...'
SELECT *
FROM mdl_user
WHERE
id = $1 OR
username LIKE $2 OR
firstname LIKE $3 OR
lastname LIKE $4 OR
email LIKE $5 OR
idnumber LIKE $6 ORDER BY lastname, firstname
[array (
0 => 'abc',
1 => '%abc%',
2 => '%abc%',
3 => '%abc%',
4 => '%abc%',
5 => '%abc%',
)]
Error code: dmlreadexception
Stack trace:

    line 494 of /lib/dml/moodle_database.php: dml_read_exception thrown
    line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
    line 341 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->read_slave_query_end()
    line 1027 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
    line 136 of /admin/tool/mergeusers/lib/mergeusersearch.php: call to pgsql_native_moodle_database->get_records_sql()
    line 165 of /admin/tool/mergeusers/index.php: call to MergeUserSearch->search_users()

Environment: Moodle 4.2.8+ (Build: 20240719) and tool_mergeusers 2024060300.

[jpahullo]

Thanks @leonstr for detecting this with the added test on PR #274 for the issue #273.

It will be very good if you could also provide a patch for this. Then, I would analyse both PR together to provide a full solution to both issues.

Thanks a lot in advance,

Jordi

[jpahullo]

Thanks a lot @camrionnvmff for your feedback.

Your idea is already included in the patch of the other PR, since @leonstr put together the solution for two issues.

Once we can test the patch in detail, we will merge the PR.

However, float numbers must be prevented to pass to be compared against an id column and https://www.php.net/manual/en/function.is-numeric.php tells true when any number is passed (int and floats are accepted).

But you got the idea, yes.

Thanks for your time and feedback.

Jordi