CODEWITHSUNDEEP
mysqlprepared-statementprepare
DarkSide
DarkSide

Reputation: 3709

How to write "create user ?" with MySQL prepared statement

I tried:

SET @user = 'foo@localhost';
SET @pass = 'bar';
SET @sql = 'CREATE USER ? IDENTIFIED BY ?';
PREPARE stmt FROM $sql;

and I get error

ERROR 1064 (42000): 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 '? IDENTIFIED BY ?'

I also tried multiple variants how to define variables, but still without any luck.

SET @user = "'foo'@'localhost'";
SET @sql = 'CREATE USER ? IDENTIFIED BY ?';

and even

SET @user = 'foo';
SET @host = 'localhost';
SET @sql = 'CREATE USER ?@? IDENTIFIED BY ?';

Quite similar question was already asked more than 2 years ago, but there still is no useful answer :( Create User with MySQLi Prepared Statement

Upvotes: 4

Views: 2074

Answers (1)

b.b3rn4rd
b.b3rn4rd

Reputation: 8830

This is the only way how I managed to create a user, unfortunately it doesn't use placeholders for the user variables:

SET @user := 'foo';
SET @host := 'localhost';
SET @pass := 'bar';
SET @sql := CONCAT("CREATE USER ", QUOTE(@user), "@", QUOTE(@host), " IDENTIFIED BY ", QUOTE(@pass));
PREPARE stmt FROM @sql;
EXECUTE stmt;

P.S. {CREATE | RENAME | DROP} USER statements should be supported starting from MySQL 5.1.12

Upvotes: 5

Related Questions