Skip to content

mysql error at customer->edit->address and customer->edit->limits because groups is reserved keyword in mysql 8+ (not in mariadb i think)

Summary

After Update to 3.2.12p1 (from 3.2.11) i get

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 'groups FROM `sys_group` WHERE client_id IN (SELECT client_id FROM `client` WHERE' at line 1'

at customer->edit->address in the interface

Steps to reproduce

go to customer->edit->address

Environment

Ubuntu Focal
Ispconfig 3.2.12p1
mysql-server-8.0                       8.0.37-0ubuntu0.20.04.3

Proposed fix

Backtick the groups word (added in mysql 8.0.2 as reserved keyword)

https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-G

+++ interface/lib/classes/functions.inc.php     2024-08-19 14:40:37.557361985 +0200
@@ -684,7 +684,7 @@

       if ($client_id != null) {
         // Get the clients groupid, and incase it's a reseller the groupid's of it's clients.
-        $group = $app->db->queryOneRecord("SELECT GROUP_CONCAT(groupid) AS groups FROM `sys_group` WHERE client_id IN (SELECT client_id FROM `client` WHERE client_id=? OR parent_client_id=?)", $client_id, $client_id);
+        $group = $app->db->queryOneRecord("SELECT GROUP_CONCAT(groupid) AS `groups` FROM `sys_group` WHERE client_id IN (SELECT client_id FROM `client` WHERE client_id=? OR parent_client_id=?)", $client_id, $client_id);
         return $group['groups'];
       }
       return null;

Screenshot 2024-08-19 144747.png