Search code examples
sqlmariadb

How to instantiate user @variables?

In the mariadb command shell, how can user variables like @var be interpolated into subsequent commands? So far I have not found how to use a string @variable for anything except in SELECT @var; to just print it as a single-value table.

For example, how could a user variable @var be used to supply the pre-hashed password to CREATE USER (using the auth_ed25519 MariaDB plug-in)?

INSTALL SONAME 'auth_ed25519';  # one time
...

set @hash = 'ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY';  # OK

CREATE OR REPLACE USER username@hostname IDENTIFIED VIA ed25519 USING @hash;
-->You have an error in your SQL syntax

I get the same error using (@hash) instead of just @hash. Using '@hash' I got "Password hash should be 43 characters long" so that probably provides the literal string '@hash' without interpolation. Ditto with "@hash".

How do @variables work?

Thanks in advance for any advice.

Addendum: I tried using PREPARE and EXECUTE to instantiate the string value but couldn't get to first base:

PREPARE stmt FROM 'CREATE OR REPLACE USER username@hostname IDENTIFIED VIA ed25519 USING ?';
-->You have an error in your SQL syntax

(The docs explicitly say that the ? marker should not be in quotes even if a string value is expected, so I'm confused here too...)

Solution

  • User variables can only be used in some places in SQL as expressions and aren't a general substitution.

    To use them in other places you can concatenate them into the string and execute them as a prepared statement. As you'll need to do this only once, EXECUTE IMMEDIATE is the easy way.

    set @hash = 'ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY';
EXECUTE IMMEDIATE
  CONCAT('CREATE OR REPLACE USER username@hostname IDENTIFIED VIA ed25519 USING ''',
   @hash,
   '''');

    Note the double quotes is an escaped single quote.