favorite plugin crash?

davidarnoult

Hi!

Since installation of 1.5.8b, when I activate favoritesHandler extension, I have this:


{2315498:Mon, 15 Feb 2021 23:24:24 GMT} Zenphoto v1.5.8b

AVERTISSEMENT : mysqli::query(): (HY000/3685): Illegal argument to a regular expression. dans /var/www/www.domain.com/htdocs/zp-core/functions-db-MySQLi.php à la ligne 84

mysqli->query called from query (functions-db-MySQLi.php [84])

from query_full_array (functions-db-MySQLi.php [130])

from favorites->__construct (favoritesClass.php [28])

from require_once (favoritesHandler.php [312])

from include (index.php [26])

from index.php [56]

{2315498:Mon, 15 Feb 2021 23:24:24 GMT}

ERREUR GÉNÉRÉE PAR L’UTILISATEUR : Erreur MySQLi : SELECT aux FROM [Préfixe]plugin_storage WHERE type="favorites" AND aux REGEXP '[[::]]' a échoué. MySQLi a renvoyé l’erreur Illegal argument to a regular expression. dans /var/www/www.domain.com/htdocs/zp-core/functions-db-MySQLi.php à la ligne 95

trigger_error called from query (functions-db-MySQLi.php [95])

from query_full_array (functions-db-MySQLi.php [130])

from favorites->__construct (favoritesClass.php [28])

from require_once (favoritesHandler.php [312])

from include (index.php [26])

from index.php [56]

It looks like there's an error in the SQL statement with [Préfixe] string. Is it my installation or is this a bug in the release?

Thanks for analysing

davidarnoult

Here is my setup, it could come from MySQli 8 or php 7.4:
Version Zenphoto 1.5.8b (Version officielle)
Paramètre régional présentement utilisé : fr_FR.UTF8
Chemin d’accès du serveur : /var/www/www.domain.com/htdocs
Chemin de la session PHP : /var/lib/php/sessions
Logiciel du serveur : Apache/2.4.41 (Ubuntu)
Version de PHP : 7.4.3
Bibliothèque graphique : PHP GD library 2.2.5
formats supportés : gif, jpg, jpeg, png, wbmp, webp
Version de MySQLi : 8.0.23

acrylian

I cannot reproduce any issue witht this. I don't have MySQL 8 but 7+ but this should IMHO not be the issue.

Do you have any prefix set? There was an issue that there was a wrong default set if an empty prefix was defined in earlier 1.5.8b versions, so please get the latest. Although the issue should have appeared in other places as well.

davidarnoult

Thanks. I do have a prefix defined in my cfg and it only happens with this extension... I am in latest 1.5.8b
$conf['mysql_prefix'] = "_";

Is there a way to debug this?

acrylian

Perhaps your "underscore" prefix is the problem? Try some actual chare as the first perhaps and see if it happen then as well. The db query in questions is correct as far as I see but maybe thse underscores trigger something in MySQL I am not aware of. This is a query using REGEX which I am not sure we have elsewhere.

davidarnoult

I have replaced my prefix by "zen", rename all my tables and error still throws:
ERREUR GÉNÉRÉE PAR L’UTILISATEUR : Erreur MySQLi : SELECT aux FROM [Préfixe]plugin_storage WHERE type="favorites" AND aux REGEXP '[[::]]' a échoué. MySQLi a renvoyé l’erreur Illegal argument to a regular expression. dans /var/www/www.arnoult.com/htdocs/zp-core/functions-db-MySQLi.php à la ligne 95

trigger_error called from query (functions-db-MySQLi.php [95])

from query_full_array (functions-db-MySQLi.php [130])

from favorites->__construct (favoritesClass.php [28])

from require_once (favoritesHandler.php [312])

from include (index.php [26])

from index.php [56]

It's like [Préfixe] strings comes from nowhere, from the locales that would crash the select command line ? Why it is not replaced by "zen" in the log?
You should test/check with MySQL 8, I am pretty sure there's a bug in this extension with this version, maybe with REGEXP conventions? All the rest is working like a charm...

davidarnoult

Line where it crashes, is that normal that $user is empty in the log too?

$list = query_full_array('SELECT aux FROM ' . prefix('plugin_storage') . ' WHERE type="favorites" AND aux REGEXP ' . db_quote('[[:<:]]' . $user . '[[:>:]]'));


acrylian

What would the user / user name be? Does it use any special chars like accents or similar? However I tried a user tèster4 and that works as any other.

Please modify the code a little so we actuall see how the query looks like:

$query = 'SELECT `aux` FROM ' . prefix('plugin_storage') . ' WHERE `type`="favorites" AND `aux` REGEXP ' . db_quote('[[:<:]]' . $user . '[[:>:]]';
debuglog($query);
query_full_array($query);

davidarnoult

my username is d@vid (superadmin). I suspect the @ character crashes the query... I have tried to rename it in database directly but it does not work anymore...

davidarnoult

Result is:
{2450006:Wed, 17 Feb 2021 13:01:25 GMT} Zenphoto v1.5.8b

SELECT aux FROM zenplugin_storage WHERE type="favorites" AND aux REGEXP '[[::]]'
user is still empty.
So it seems that REGEXP does not work with @ character in username.

The query should be modified to support it. How can I rename the master account username?

acrylian

You cannot rename it and of course not in the database as the password hash is tied with the username.

You have to create a new one and delete the old one. If it is a full admin it would technically not make any difference. Although the old one may be in the owner or author field of items.

Probably it is just the REGEX query part as you otherwise would see this issue elsewhere as well. I first assumed it might have been a utt8 things otherwise. I have to admit I didn't look why REGEX is needed here as we to my knowlegde don't do that anywhere else. We just get the favorites by username primarily.

Try not to use usernames with special chars in it and instead use secure passwords ;-)

davidarnoult

I think I worked it out. It didnot come from the special character as after rename problem was persisting. Username never comes out in the debug.
When I replaced simple quote (') by twisted quote (`), it worked.
I do not know what the db_quote('[[:<:]]' . $user . '[[:>:]] supposed to generate in teh query but when replaced by this:

$list = query_full_array('SELECT aux FROM ' . prefix('plugin_storage') . ' WHERE type="favorites" AND aux REGEXP ' . db_quote([[:<:]] . $user . [[:>:]]));

I got that

SELECT aux FROM zenplugin_storage WHERE type="favorites" AND aux REGEXP 'David'

and no errors and it is functional ;-)

acrylian

The twisted code (or backticks) is normally for escaping column names that are reserved words like "show" currently. This is never used for the values actually.

As said I am not sure why the REGEX part is there at all, too. Since I cannot reproduce this issue with "normal" user names I need to take the time to closer look at what the favorites plugin does here as I didn't write it.

sbillard

What you are describing is indeed strange. For your information the [[:<:]] and [[:>:]] elements are the REGEX for match only whole words. It really should not match what kind of quote you place the strings in as MYSQL will never see the quotation marks--that is a PHP syntax. Could you try with double quotes (") and see if that works?

I would think the issue is really some character in the user name. The error claims that there is an illegal argument in the Regular Expression. That would occur if, for instance, there were a $ in the user name. There are other characters which would have the same effect.

acrylian

I did try this with a user name containing an @ sign as we suspected above but could not reproduce it.

chrisb

I'm running into this issue as well. I suspect it has to do with a change in MySQL 8 which is mentioned here: https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-compatibility

Prior to MySQL 8.0.4, MySQL used the Henry Spencer regular expression library to support regular expression operations, rather than International Components for Unicode (ICU). The following discussion describes differences between the Spencer and ICU libraries that may affect applications:

The Spencer library supports word-beginning and word-end boundary markers ([[:<:]] and [[:>:]] notation). ICU does not. For ICU, you can use \b to match word boundaries; double the backslash because MySQL interprets it as the escape character within strings.

I'm on version 8.0.25, so I'll try using the suggested \b

Update: Replacing '[[:<:]]' and '[[:>:]]' with '\b' seems to work, as far as I can tell.

acrylian

Sadly I cannot test this as I don't have access to MySQL 8 at all. Looking at the code I am not sure why there is a regex for querying the username at all since the user name probably should be a exact match?