PostgreSQL: Contacts list broken due to case-sensitive postgres behavior

[kainhofer]

Describe the bug
When using postgres as the database, the contacts list returns an error ("Warning: Undefined array key "FIRST_NAME" in /var/www/html/adm_program/modules/contacts/contacts_data.php on line333") and does not dipslay the contacts list

To Reproduce
Steps to reproduce the behavior:

1. use Postgresql as database
2. Go to the contacts list
3. See error

Expected behavior
The contacts list should be loaded as json.

System (please complete the following information):

• Admidio-Version git master
• PHP PHP 8.2.27
• Database PostgreSQL

Additional context

After some more digging, the problem is as follows:

• Postgres uses case-insensitive column names, unless they were wrapped in double quotes on table creation. Or rather, Postgres is case-sensitive, but by default it converts all unquoted names to lowercase: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#:~:text=Quoting%20an%20identifier,never%20quote%20it.%29

• Mysql uses case-sensitive column names

• The query generated by ListConfiguration::getSql is (a little simplified):

SELECT DISTINCT  1  AS member_this_orga,
        0 AS member_other_orga, usr_login_name as loginname,
                (SELECT email.usd_value FROM adm_user_data email
                  WHERE  email.usd_usr_id = usr_id
                    AND email.usd_usf_id = 12
                 ) AS member_email,   usr_uuid,  row1id1.usd_value AS LAST_NAME, row2id2.usd_value AS FIRST_NAME, usr_login_name AS usr_login_name, row4id11.usd_value AS GENDER, row5id10.usd_value AS BIRTHDAY, row6id5.usd_value AS CITY, usr_timestamp_change AS usr_timestamp_change
                      FROM adm_members mem
                INNER JOIN adm_roles
                        ON rol_id = mem_rol_id
                INNER JOIN adm_categories
                        ON cat_id = rol_cat_id
                           INNER JOIN adm_users
                                ON usr_id = mem_usr_id
                            LEFT JOIN adm_user_data row1id1
                                     ON row1id1.usd_usr_id = usr_id
                                    AND row1id1.usd_usf_id = 1 LEFT JOIN adm_user_data row2id2
                                     ON row2id2.usd_usr_id = usr_id
                                    AND row2id2.usd_usf_id = 2 LEFT JOIN adm_user_data row4id11
                                     ON row4id11.usd_usr_id = usr_id
                                    AND row4id11.usd_usf_id = 11 LEFT JOIN adm_user_data row5id10
                                     ON row5id10.usd_usr_id = usr_id
                                    AND row5id10.usd_usf_id = 10 LEFT JOIN adm_user_data row6id5
                                     ON row6id5.usd_usr_id = usr_id
                                    AND row6id5.usd_usf_id = 5
                     WHERE usr_valid = true
                       AND rol_valid = true
                       AND rol_uuid IN (SELECT rol_uuid
                              FROM adm_categories
                             INNER JOIN adm_roles ON rol_cat_id = cat_id
                             WHERE (  cat_org_id = 1
                                   OR cat_org_id IS NULL )
                               AND cat_name_intern <> 'EVENTS'
                            )
                           
                       AND (  cat_org_id = 1
                           OR cat_org_id IS NULL )
                           AND mem_begin <= '2025-02-14' AND mem_end >= '2025-02-14' ORDER BY row1id1.usd_value ASC, row2id2.usd_value ASC LIMIT 25 OFFSET 0

Notice the unqoted LAST_NAME and FIRST_NAME, which means postgres will use them case-insensitive... When you run this sql in mysql, though, the column will be upper-case:

But when you run the same sql query in postgres, the column names will be lowercase:

So this means that the contacts list is currently broken for all postgres users, while mysql works just fine...

A possible solution would be to quote the LAST_NAME and FIRST_NAME in the sql query to force postgres to case-sensitivity, too.