E-Mail Server Ubuntu SQL Error

Question

I've been trying to set up a server but it doesn't work. I got this error:

23:59:43 mail dovecot: auth-worker(14604): Error: sql([email protected], ip): Password query failed; 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 '\ ROM users WHERE userid = '[email protected]' and mysql_pass = password('psw' at line 1.

Here my config:

driver = mysql connect = "host=127.0.0.1 dbname=dbname user=userdb password=psw" default_pass_scheme = SHA512-CRYPT
password_query = SELECT NULL AS password, 'Y' as nopassword, userid AS user \ FROM users $
user_query = SELECT username AS user, domain, password FROM accounts WHERE username = '%n$
iterate_query = SELECT username, domain FROM accounts where sendonly = false;

My setup: I have installed mariadb, dovecot, postfix, postfixadmin.

When I create an email account with postfixadmin and use the same database with dovecot, I got the error. What am I doing wrong?

I'm not very experienced. If you need more informations, please ask.

Answer 1

The error you have is as follows; bold emphasis is mine:

23:59:43 mail dovecot: auth-worker(14604): Error: sql([email protected], ip): Password query failed; 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 '\ ROM users WHERE userid = '[email protected]' and mysql_pass = password('psw' at line 1.

The query value you have set is as follows; again bold emphasis is mine:

SELECT NULL AS password, 'Y' as nopassword, userid AS user \ FROM users $

Seems like that \ is a problem. But also some of the queries oddly have a $ at the end of them like these:

SELECT NULL AS password, 'Y' as nopassword, userid AS user \ FROM users $
SELECT username AS user, domain, password FROM accounts WHERE username = '%n$ 

It looks like you copied and pasted these queries from some command line output that truncated the queries. The solution? Get the actual full queries and place them in the config.

Also, what is this about?

driver = mysql connect = "host=127.0.0.1 dbname=dbname user=userdb password=psw"

Why are those two driver and connect values on the same line?

In general, the overall format quirks you are seeing makes the config seem like a mess. No easy way for us to solve it, but basically I would recommend rewriting the config properly with full queries, quotes around values (if the config works that way) and properly setting each value on each line.