0

I am working on an application that needs an ability to create MySQL users and schemas. But I am having issues with running the CREATE USER statement

I would expect this statement to work but it does not:

DB::statement('CREATE USER :user@`%` IDENTIFIED WITH mysql_native_password BY :password', [
    'user' => $this->username,
    'password' => $this->password,
]);

Under the hood Laravel sends the entire statement to PDO without doing any processing,
so this is pretty much the same as if I would call:

$statement = $pdo->prepare('CREATE USER :user@`%` IDENTIFIED WITH mysql_native_password BY :password');
$statement->bindValue('user', $this->username, PDO::PARAM_STR);
$statement->bindValue('password', $this->password, PDO::PARAM_STR);
$statement->execute();

I did a bunch of googling and all solutions I found used string concatenation, but I would prefer to use parameter binding.
I also tried many different ways of running this statement but none worked:

CREATE USER :user@% IDENTIFIED WITH mysql_native_password BY :password
CREATE USER :user@'%' IDENTIFIED WITH mysql_native_password BY :password
CREATE USER ':user'@'%' IDENTIFIED WITH mysql_native_password BY ':password'
CREATE USER `:user`@`%` IDENTIFIED WITH mysql_native_password BY `:password`
CREATE USER :user@'%'
CREATE USER ':user'@'%'
CREATE USER `:user`@'%'
CREATE USER :user
CREATE USER ':user'
CREATE USER `:user`
CREATE USER :user  -- with ['user' => $this->username . '@`%`'

What am I missing? Is it not possible to run CREATE USER with bindValues?

edit: To add context they are all failing at prepare() except for the ones where the parameter is in a comment, since those dont consider it a parameter, just a literal.

HubertNNN
  • 1,727
  • 1
  • 14
  • 29
  • In what way does the last one (CREATE USER :user -- with ['user' => $this->username . '@`%`') fail? That is still a parameter, in the same way as when you want to use `LIKE` with a wildcard parameter you have to use `LIKE :param` and `param => '%$param%'` – Nick Mar 28 '22 at 10:56
  • @Nick `SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1 ` during `prepare()` – HubertNNN Mar 28 '22 at 11:15
  • It seems the username is not a parameter which can be bound. Here's a workaround: https://stackoverflow.com/questions/35749340/create-user-fails-with-mysqli-prepared-statements – Nick Mar 28 '22 at 11:26

0 Answers0