It is impossible to add x509 certificates for rollover for IDPs using SAML

[chrhoh]

Detailed description of the problem

Trying to add a certificate for rollover for an existing SAML-based IDP currently seems impossible from the website. Adding a certificate in the dialogue and saving leads to the error message:

SQLSTATE[42000]: Syntax error or access violation: 1064 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 '= 'C:\\fakepath\\idp-cert-2024.pem' WHERE id = 1' at line 1

The new certificate does not save.

This error does not occur if changing the main certificate.

Expected Behavior

The certificate should save.

Steps to reproduce the behavior

1. Have an SAML-based IDP configured
2. Navigate to it in the sysadmin panel SAML → IDP
3. Click edit
4. Upload the new certificate for rollover

Do you have any idea what may have caused this?

This looks a lot like broken SQL. Also C:\\fakepath seems a bit unlikely on a linux server.

Do you have an idea how to solve the issue?

No response

What is your docker-compose configuration?

version: '3'
services:
  web:
    image: elabftw/elabimg:5.1.8
    restart: always
    container_name: elabftw
    depends_on:
      mysql:
        condition: service_healthy
    security_opt:
      - no-new-privileges:true
    cap_drop:
        - ALL
    cap_add:
        - CHOWN
        - SETGID
        - SETUID
        - FOWNER
        - DAC_OVERRIDE
    environment:
        - DB_HOST=mysql
        - DB_PORT=3306
        - DB_NAME=elabftw
        - DB_USER=elabftw
        - PHP_TIMEZONE=Europe/Berlin
        - TZ=Europe/Berlin
        - PHP_MAX_CHILDREN=50
        - PHP_MAX_EXECUTION_TIME=120
        - MAX_PHP_MEMORY=2G
        - SITE_URL=redacted
        - MAX_UPLOAD_SIZE=100M
        - SERVER_NAME=redacted
        - DISABLE_HTTPS=true
        - ENABLE_LETSENCRYPT=false
        - ENABLE_IPV6=false
        - ELABFTW_USER=nginx
        - ELABFTW_GROUP=nginx
        - ELABFTW_USERID=501
        - ELABFTW_GROUPID=501
        - SET_REAL_IP=true
        - SET_REAL_IP_FROM=127.0.0.1
        - NGINX_WORK_PROC=auto
        - KEEPALIVE_TIMEOUT=100s
    ports:
        - "127.0.0.1:5000:443"
    volumes:
        - /var/elabftw/web:/elabftw/uploads
    networks:
      - elabftw-net
  mysql:
    image: mysql:8.0
    restart: always
    command: --default-authentication-plugin=mysql_native_password
    container_name: mysql
    healthcheck:
      interval: 30s
      timeout: 5s
      retries: 5
    logging:
      driver: "journald"
    cap_drop:
        - AUDIT_WRITE
        - MKNOD
        - SYS_CHROOT
        - SETFCAP
        - NET_RAW
    cap_add:
        - SYS_NICE
    environment:
        - MYSQL_DATABASE=elabftw
        - MYSQL_USER=elabftw
        - TZ=Europe/Berlin
    volumes:
        - /var/elabftw/mysql:/var/lib/mysql
    expose:
      - '3306'
    networks:
      - elabftw-net
networks:
  elabftw-net:

Output of uname -a

Linux elabftw-doku 6.1.0-23-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.99-1 (2024-07-15) x86_64 GNU/Linux

Output of cat /etc/os-release

PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
NAME="Debian GNU/Linux"
VERSION_ID="12"
VERSION="12 (bookworm)"
VERSION_CODENAME=bookworm
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"

Output of docker info

Client: Docker Engine - Community
 Version:    27.3.1
 Context:    default
 Debug Mode: false
 Plugins:
  buildx: Docker Buildx (Docker Inc.)
    Version:  v0.17.1
    Path:     /usr/libexec/docker/cli-plugins/docker-buildx
  compose: Docker Compose (Docker Inc.)
    Version:  v2.29.7
    Path:     /usr/libexec/docker/cli-plugins/docker-compose

Server:
 Containers: 2
  Running: 2
  Paused: 0
  Stopped: 0
 Images: 11
 Server Version: 27.3.1
 Storage Driver: overlay2
  Backing Filesystem: extfs
  Supports d_type: true
  Using metacopy: false
  Native Overlay Diff: true
  userxattr: false
 Logging Driver: json-file
 Cgroup Driver: systemd
 Cgroup Version: 2
 Plugins:
  Volume: local
  Network: bridge host ipvlan macvlan null overlay
  Log: awslogs fluentd gcplogs gelf journald json-file local splunk syslog
 Swarm: inactive
 Runtimes: io.containerd.runc.v2 runc
 Default Runtime: runc
 Init Binary: docker-init
 containerd version: 7f7fdf5fed64eb6a7caf99b3e12efcf9d60e311c
 runc version: v1.1.14-0-g2c9f560
 init version: de40ad0
 Security Options:
  apparmor
  seccomp
   Profile: builtin
  cgroupns
 Kernel Version: 6.1.0-23-amd64
 Operating System: Debian GNU/Linux 12 (bookworm)
 OSType: linux
 Architecture: x86_64
 CPUs: 2
 Total Memory: 3.823GiB
 Name: elabftw-doku
 ID: 6dd611a7-eb4b-4ada-925f-cbf5004a2f46
 Docker Root Dir: /var/lib/docker
 Debug Mode: false
 Experimental: false
 Insecure Registries:
  127.0.0.0/8

Relevant php error log entry

No response

Additional information

No response

[NicolasCARPi]

Hello,

Thank you for reporting this issue. It is reproducible, I will look into it. In the mean time, you can copy/paste the content of the cert in the text area, and save that. The "Browse" option to upload a file is a convenience, not a requirement.

Also C:\\fakepath seems a bit unlikely on a linux server.

That's your browser doing that. And even on GNU/Linux (client side), it does that. It is a security feature of browsers to hide the real full path of uploaded files.