MySQL directory parameters are limited and differ from the docs and other backends

Issues
1

Issue Description

At the very least, this is a documentation bug because the current description of query parameters in the SQL directory docs (stalw.art/docs/auth/backend/sql/#directory-queries) is incorrect for the MySQL variant. The query params described there are in the Postgres style $1 and don’t work for MariaDB or MySQL, producing the error:

Server error: `ERROR 42S22 (1054): Unknown column '$1' in 'where clause''

MySQL query params take the format ? instead and are purely positional, they cannot be used multiple times or out of order. I suggest the relevant section of the docs (and the query defaults) be rewritten as follows:

The SQL variant runs the following queries against the configured store; all of them use positional parameters and sensible defaults that can be overridden per deployment. Postgres users can use the positional parameter in the format $1 and the default queries as written. MySQL and MariaDB users must modify the default queries to use a ? as their positional parameter and may only use it once per query.

In addition I feel the MySQL/MariaDB query parameters as currently implemented are inadequate. The params are purely positional and must match the number of parameters passed to the query, which is one. That’s fine if you are preparing the schema to fit Stalwart exactly, however most people who need to resort to external directories are probably doing so because they already have the data in a format they can’t change and must adapt to, and giving them the tools to be able to adapt is vital.

In my case, here’s the login query I need to execute:

SELECT email, password, 'individual' as type, name FROM mail_user WHERE (login = ? OR email = ?) AND server_id = '1' AND NOT EXISTS (SELECT domain_id FROM mail_domain WHERE domain = SUBSTRING_INDEX(?, '@', -1) AND active = 'n' AND server_id = 1)

However because I use 3 parameters I get the error:

Driver error: `Statement takes 3 parameters but 1 was supplied.'

A workaround is to use a derived table like this:

SELECT mu.email, mu.password, 'individual' AS type, mu.name FROM (SELECT ? AS login_value) AS p JOIN mail_user AS mu WHERE (mu.login = p.login_value OR mu.email = p.login_value) AND mu.server_id = '1' AND NOT EXISTS (SELECT md.domain_id FROM mail_domain AS md WHERE md.domain = SUBSTRING_INDEX(p.login_value, '@', -1) AND md.active = 'n' AND md.server_id = 1)

But it’s not ideal and very limiting.

I think there’s enough repeated issues with this behaviour that it warrants addressing, see:

support.stalw.art/t/authentication-agains-external-sql-mariadb-directory-not-working/554/3

I wonder if you can use named parameters as described in the mysql_async docs (docs.rs/mysql_async/latest/mysql_async/#named-parameters) as there’s only a single parameter, is the library clever enough to work out that ? or $1 is the only named parameter or if that would break existing queries.

Expected Behavior

Repeated use of the login username parameter should be permitted in MySQL directory as it is in the Postgres directory.

Actual Behavior

Using ? more than once in a SQL query results in the error:

Driver error: `Statement takes 3 parameters but 1 was supplied.

Reproduction Steps

  1. Configure stalwart with a SQL authentication directory to MySQL or MariaDB.
  2. Configure the default queries and observe they don’t work on MySQL or MariaDB because they are written for PostgreSQL.
  3. Configure a slightly more complex login query that uses more than one parameter ?
  4. Observe that the logs show too many positional parameters were given.

Relevant Log Output

Server error: `ERROR 42S22 (1054): Unknown column '$1' in 'where clause''

Driver error: `Statement takes 3 parameters but 1 was supplied.'

Stalwart Version

v0.16.x

Installation Method

Binary (Linux)

Database Backend

MySQL

Blob Storage

Filesystem

Search Engine

Internal

Directory Backend

SQL

I have reviewed the documentation and FAQ and confirm that my issue is NOT addressed there.

on

I have searched this support forum (open and closed topics) and confirm this is not a duplicate.

on

I understand that topics in this category are triaged by a bot first but a human reply will follow up. If I’d prefer a human-only reply, I’ll add the no-ai tag to my topic.

on

2

Let me look into the SQL directory documentation and the relevant source code to give an informed response.

This is a well-documented and clearly-reproducible issue. Here is what the source code confirms, along with what your workaround correctly identifies:

The issue is real and confirmed in the source code. The MySQL backend uses Params::Positional(params.iter().map(Into::into).collect()) from mysql_async, and the directory lookup code always passes exactly one value (e.g. vec![username.into()]) to every query. Since MySQL’s ? placeholders must each be matched by a parameter, a query containing multiple ? will cause the “Statement takes N parameters but 1 was supplied” error.

Your diagnosis is correct on all points:

  1. Documentation bug: The docs show $1-style parameters which is PostgreSQL syntax. MySQL requires ? instead.
  2. Functional limitation: Because the directory layer only ever supplies one bound value per query, MySQL users cannot repeat the same parameter by using ? more than once, unlike PostgreSQL where $1 can appear multiple times freely.
  3. Your derived-table workaround (SELECT ? AS login_value) is the correct workaround given the current implementation, as it reduces the number of ? placeholders to one.

The named-parameter approach from mysql_async you linked to (:name syntax) would theoretically allow the same value to be referenced multiple times in a query while still only supplying one bound value. Whether it could be adopted without breaking existing queries is a question for the maintainers, but it is architecturally plausible.

This is a confirmed documentation bug plus a real functional limitation. It will need a human maintainer to address the fix on the source side and update the docs. Your derived-table workaround is the best available option until then.

This is an automated reply from the Stalwart Help Bot. Other community members may follow up if this answer is incomplete or wrong.