Errors due to "groups" after MySQL 8.0 upgrade

[jradwan]

After upgrading to MySQL 8.0 I started getting SQL errors in my Piwigo installation from the plugin. I tracked it down to needing to add backticks around "groups" (which is reserved word now) on line 532:

    $sql="SELECT COUNT(id) AS nbGroup
          FROM `".GROUPS_TABLE."`;";

and line 556:

    $sql="SELECT id, `order`, users, `groups`
          FROM ".$this->tables['blocks']."

[VolkerBoedker]

I found a quick and dirty fix, working in following environment:

Piwigo 2.10.1 Check for upgrade
Operating system: Linux
PHP: 7.3.11-1+0~20191026.48+debian9~1.gbpf71ca0 (Show info) [2019-11-07 22:56:59]
MySQL: 8.0.17 [2019-11-07 22:56:59]
Graphics Library: ImageMagick 6.9.7-4

The file piwigo/include/dblayer/functions_mysqli.inc.php contains already a fix for the new MySQL 8.x keyword 'rank'. I duplicated the few lines and replaced 'rank' by 'groups' in function pwg_query

// starting with MySQL 8, rank becomes a reserved keyword, we need to escape it
if (preg_match('/\brank\b/', $query))
{
// first we unescape what's already escaped (to avoid double escaping)
$query = preg_replace('/rank/', 'rank', $query);
// then we escape the keyword
$query = preg_replace('/\brank\b/', 'rank', $query);
}

// starting with MySQL 8, groups becomes a reserved keyword, we need to escape it
if (preg_match('/\bgroups\b/', $query))
{
// first we unescape what's already escaped (to avoid double escaping)
$query = preg_replace('/groups/', 'groups', $query);
// then we escape the keyword
$query = preg_replace('/\groups\b/', 'groups', $query);
}

Would be great if some one could integrate my fix in the functions_mysqli.inc.php, because I'm not really good in PHP to implement a smart solution. Maybe an additional 'reserved_keyword_qouting" function could solve also future issues. see also Piwigo/Piwigo#1095

[erAck]

Note that
$query = preg_replace('/\groups\b/', 'groups', $query);
is wrong, it should be
$query = preg_replace('/\bgroups\b/', 'groups', $query);
instead; note \bg instead of \g

[erAck]

Also note that the simple GitHub backtick code embedding munges the backticks used in the code example so it's completely useless.. it should be (block indented with 4 spaces does a real code block in GitHub):

// starting with MySQL 8, groups becomes a reserved keyword, we need to escape it
if (preg_match('/\bgroups\b/', $query))
{
  // first we unescape what's already escaped (to avoid double escaping)
  $query = preg_replace('/`groups`/', 'groups', $query);
  // then we escape the keyword
  $query = preg_replace('/\bgroups\b/', '`groups`', $query);
}

[jradwan]

This problem is still present in Revision 3.2.13.