GenericDataException in extensions installer

[laoneo]

Due diligence

• I have searched all open and closed issues for this problem.
• I have searched all open and closed discussions for this problem.
• I have looked at the CHANGELOG to make sure this problem has not already been addressed.
• I agree to the Code of Conduct of this repository

Describe the bug

A GenericDataException is thrown when my Panopticon instance tries to call the API ExtensionsController on line 40. The only GET variable is page[limit]=10000.

The message is:
_Message: 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 ')) AND protected = ?' at line 4 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 ')) AND protected = ?' at line 4 _

Reproduction instructions

As this is an API request, I guess it comes from the instance crob job, so can't provide a step by step instruction.

Expected behaviour

No exception.

Actual behaviour

The message as described above.

Screenshots

No response

Panopticon version

1.3.1

PHP version

8.3.14

Database type

MySQL

Database version

8.3

Browser

None

Browser version

No response

Additional information

No response

[nikosdion]

Try using the PDO MySQL (mysql) driver instead of mysqli on your site.

If you trace the problem you will see that the code in my plugin is correct, and the SQL query is constructed in Joomla! core code.

There's a bug in Joomla's \Joomla\Database\Mysqli\MysqliStatement. Sometimes you get an off-by-one error when applying bound parameters, which means that one of the bound parameters is not assigned a value, leaving the literal string ? in the SQL query, hence the error.

I first spotted this problem around 3 years ago when I converted ATS to use Joomla's core API. I was getting this error getting the ATS categories on one specific site and server, but the query was generated by core code. I noticed that swapping out the DB driver fixed it, and I remembered that some older versions of the mysqli driver had some weird bugs, so I didn't pay much attention. Only two other clients reported this issue, and I shrugged it off.

About a year and a half ago I did run into the exact same problem you have with Panopticon –again, only on a specific server– and did more thorough troubleshooting. However, by that time I was already blocked from the Joomla repo, so I could not report anything back to the project and I didn't bother going beyond the discovery of an off-by-one error. Hopefully, now that you have come across this issue, you can use your maintainer status to fix it 😄

[laoneo]

If you can give me a hint how to fix it, I will be more than happy to fix it.

[nikosdion]

I only got as far as I told you above. The only other thing I know is that it doesn't seem to happen all the time. I've only seen it trying to list nested categories and when doing a complicated query like the one used to list extensions. The tenuous common thread I have found is that they have a lot of JOINs and more than five bound parameters.

Unfortunately, I am currently unable to spend time on this. I am already under quite a lot of time pressure from other things, both work-related and not.

My suspicion would be possibly the wrong handling of null values, but that's just a hunch, not something coming from troubleshooting.