sql - How to instantiate user @variables? - Stack Overflow

admin管理员组

文章数量:1295912

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...)

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...)

• sql
• mariadb

Share Follow edited Feb 12 at 6:34 DarkBee 15.6k88 gold badges7272 silver badges116116 bronze badges asked Feb 12 at 1:11 jimavjimav 86088 silver badges1717 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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.

本文标签： sqlHow to instantiate user variablesStack Overflow