Adding group in Davical causes error

Question

Hi I'm trying to install Davical next to Nextcloud using Postgres, nginx and php8.1, it has been partially working (adding users or resources works fine) but when I want to create groups I get the following error message :

I can create groups in the admin panel, but when I call the groups page (https://calendar.example.de/admin.php?action=edit&t=principal&id=1019) I get this error message under "Group Membership":

Exception [42601] SQLSTATE[42601]: Syntax error: 7 FEHLER: Müll folgt auf numerische Konstante bei »1019O«
ZEILE 1: ...member WHERE group_id = 1019) AND principal_id != 1019ORDER ...
                                                              ^
At line 95 of /usr/share/awl/inc/AwlDatabase.php
================= Stack Trace ===================
/usr/share/davical/htdocs/admin.php[50] Browser->Render()
/usr/share/awl/inc/classBrowser.php[852] edit_group_row()
/usr/share/davical/inc/ui/principal-edit.php[591] Editor->Render()
/usr/share/awl/inc/classEditor.php[669] preg_replace_callback()
[0] Editor->ReplaceEditorPart()
/usr/share/awl/inc/classEditor.php[597] EntryField::BuildOptionList()
/usr/share/awl/inc/DataEntry.php[306] AwlQuery->Exec()
/usr/share/awl/inc/AwlQuery.php[585] AwlQuery->Execute()
/usr/share/awl/inc/AwlQuery.php[396] AwlDatabase->query()
/usr/share/awl/inc/AwlDatabase.php[95] PDO->query()

And I can't add users to groups.

The following error message also exists in the Davical error log

2023/04/26 11:38:59 [error] 330989#330989: *17 FastCGI sent in stderr: "PHP message: PHP Early Exception: [42601] SQLSTATE[42601]: Syntax error: 7 FEHLER: Müll folgt auf numerische Konstante bei »1019O«
ZEILE 1: ...member WHERE group_id = 1019) AND principal_id != 1019ORDER ...
 ^ at /usr/share/awl/inc/AwlDatabase.php:95" while reading upstream, client: 192.168.100.2, server: calendar.example.de, request: "GET /admin.php?action=edit&t =principal&id=1019 HTTP/2.0", upstream: "fastcgi://unix:/run/php/php8.1-fpm.sock:", host: "calendar.example.de"< /pre>
<p>postgresql error log shows: </p>
<pre class="brush:php;toolbar:false;">2023-04-26 11:59:29.228 CEST [333501] davical_app@davical ANWEISUNG: SELECT principal_id, coalesce(displayname,fullname,username) FROM dav_principal WHERE principal_id NOT IN (SELECT member_id FROM group_member WHERE group_id = 1019) AND principal_id != 1019ORDER BY 2

Using ubuntu 22.04, php8.1, postgresql 15, Davical 1.1.11-1, libawl-php 0.63-1 All

I checked php8.1-fpm.sock and the link to the socket is correct in both www.conf and the corresponding nginx configuration. php8.1.-fpm.sock works, user permissions and owner appear to be set correctly. Maybe I'm missing something, or there's something wrong with the postgresql database? Can anyone help me further?

Thank you!

Answer 1

This code is wrong:

$sql = 'SELECT principal_id, coalesce(displayname,fullname,username) FROM dav_principal ';
  $sql .= 'WHERE principal_id NOT IN (SELECT member_id FROM group_member WHERE group_id = '.$id.') ';
  $sql .= 'AND principal_id != '.$id;
  $sql .= 'ORDER BY 2';

ORDER BY gets stuck after $id, causing you to see the error message.

Fix: There are extra spaces before ORDER BY:

$sql .= ' ORDER BY 2';

The file can be foundhere, principal-edit.php