[Solved] NC- Need help reverting "login by email" misconfiguration

Nextcloud version:
Operating system and version / Apache or nginx version: : Not sure. Using shared hosting at bitpalast.net, very limited CLI accessibility.
PHP version: 7.4.25

The issue you are facing:

Nextcloud service (including configuration pages) completely unavailable. Login not working. Entering anything results in an internal server error.

I suspect the reason for this is that I changed a configuration option. I activated “login by email”, but also use login credentials via SQL Backend. Did not configure the email backend before activating “login by email”.

Moreover I updated nextcloud today and then upped the PHP Version from 7.3.32 to 8.0.12, which made the service temporarily unavailable until I downgraded PHP to 7.4.25.

During the Nextcloud-update the web updater showed that it created a backup. Is this backup deleted after the update? I was unable to find it…

nextcloud.log shows this error which is an SQL Syntax Exception:

"Exception":"Doctrine\\DBAL\\Exception\\SyntaxErrorException","Message":"An exception occurred while executing 'SELECT u.ID AS uid, u.user_login AS username, u.user_login AS name, null AS email, null AS quota, null AS home, true AS active, false AS avatar, null AS salt, u.user_pass AS password FROM uCiCndcu_users u WHERE u.user_login = :username OR u. = :email ' with params [\"John Doe\", \"John Doe\"]:\n\nSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= 'John Doe'' at line 1","Code":0,"Trace"

The error in particular is caused by the bit OR u. = :email as far as I can tell.


$CONFIG = array (
  'instanceid' => '<instanceid>',
  'passwordsalt' => '<pw_salt>',
  'secret' => '<secret>',
  'trusted_domains' =>
  array (
    0 => '<URL>',
  'datadirectory' => '/var/www/vhosts/<URL>/data',
  'dbtype' => 'mysql',
  'version' => '',
  'overwrite.cli.url' => 'https://<URL>',
  'dbname' => 'db_cloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'cu_cloudadmin',
  'dbpassword' => '<db_pw>',
  'installed' => true,
  'app_install_overwrite' =>
  array (
    0 => 'user_sql',
    1 => 'emlviewer',
  'maintenance' => false,
  'theme' => '',
  'loglevel' => 2,
  'updater.secret' => '<up_secret>',

The output of your Apache/nginx/system log in /var/log/____:
unavailable due to above mentioned limitations

I was unable to find out exactly how to change the setting for the “login by mail”. Alternatively a change to the db, which I have full access to, can solve the syntax error also? Or editing the SQL Backend option via CLI to include the email field, which I can also easily include via the external DB, which I have access to.

I expect that the CLI limitations disallow me from performing the fix myself, but I want to provide my webspace provider with the exact command I wish to be executed.

Thankful for any help!



I went browsing through the database and found the sheet ‘oc_appconfig’ [appid,configkey,configvalue] where the entry
“user_sql,db.table.user.column.email,” was present, which I replaced by “user_sql,db.table.user.column.email,user_email”, where “user_email” is the corresponding column name of the sheet in the external database I take the credentials from.

Now I can access Nextcloud in the web again and change any settings normally.