3

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

Community
  • 1
  • 1
DarkSide
  • 3,670
  • 1
  • 26
  • 34

1 Answers1

5

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

b.b3rn4rd
  • 8,494
  • 2
  • 45
  • 57
  • 3
    +1 for a legitmiate use of [`QUOTE()`](http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_quote) which is really the sanest thing that can be done here, since `CREATE USER` doesn't support `?` placeholders. As long as there's no injection opportunity when initially setting the variables (which, if there is, can be fixed by first preparing and executing `SET @user = ?` ... etc.), or passing args into a stored procedure where this code (possibly, I'm guessing) lives, this should be a winner. – Michael - sqlbot Dec 18 '13 at 01:25
  • @Michael-sqlbot, another thing, there will be no performance benefits of prepared statements, since placeholders are not used... – b.b3rn4rd Dec 18 '13 at 01:30
  • 1
    True, it has to be re-prepared for each run, but if someone is creating new MySQL server users at a rate that a they're concerned about how fast it can be done, then they have a whole new problem. I assume OP's motivation for using a prepared statement was safety rather than performance in this case. – Michael - sqlbot Dec 18 '13 at 01:38
  • Yeah this way it works, but then it's not big deal to use prepared statements. What I wanted to accomplish is to create pure MySQL script to generate 10-100 new users by changing just variables and use Execute and not rewrite "CREATE USER ..." every time. – DarkSide Dec 18 '13 at 08:27
  • you don't need to rewrite the `sql`, just assign new values to the variables – b.b3rn4rd Dec 18 '13 at 08:29
  • @b.b3rn4rd But I'll need to rewrite all these 4 SET rows (new values), then prepare again and then execute, right? So basically I'll need to rewrite all 6 rows for every user :) It's not big deal in fact, but ... I'm just trying to understand this. – DarkSide Dec 18 '13 at 08:35
  • the following solution differs from placeholders, only in speed and security (theoretically). The usage is exactly the same... Assign new values to the `@user`, `@hos`t, `@pass`... I would suggest you to create stored procedure based on this solution – b.b3rn4rd Dec 18 '13 at 08:41
  • @b.b3rn4rd yeah stored procedure will be the best solution I guess. I'll accept your answer in a few days if nothing better will come up here and I guess it will not. – DarkSide Dec 18 '13 at 08:44