syntax error from CREATE USER with variables giving username and password

Question

Stored procedure code:

CREATE DEFINER = `root` @`localhost` PROCEDURE `P_CreateUser3` (
  IN _Username NVARCHAR(30), IN _Password NVARCHAR(32), IN _DBName VARCHAR(20))
BEGIN
  CREATE USER _Username @'localhost' IDENTIFIED BY _Password ;
  GRANT SELECT, UPDATE, DELETE, INSERT 
  ON    _DBName.* 
  TO    _Username @'localhost' 
  WITH GRANT OPTION ;
END $$

Error Code: 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 '_Password ; GRANT SELECT, UPDATE, DELETE, INSERT ON `pr' at line 6

help me please.

The unsupported use of stored procedure input parameters as passwords in a `CREATE USER` or `GRANT` statement is [documented in this bug](http://bugs.mysql.com/bug.php?id=28406). — Michael Berkowski, Jul 06 '12 at 20:19
Hmm I wouldn't have even tried that. Just reached straight for exec('SomeQuery') — Tony Hopkinson, Jul 06 '12 at 20:28
@eggyal Looks like I had it as an answer originally. I restored and undeleted it. — Michael Berkowski, Sep 04 '12 at 16:20
possible duplicate of [MySQL CREATE USER with a variable?](http://stackoverflow.com/questions/11951570/mysql-create-user-with-a-variable) — Lucio, Apr 22 '14 at 05:21

Michael Berkowski · Accepted Answer · 2012-09-04T16:18:17.440

Unfortunately, the use of stored procedure input parameters as passwords in a CREATE USER or GRANT statement is documented in this bug as unsupported. So you cannot actually do what you attempted.

It would be possible to PREPARE and EXECUTE a statement which is built by CONCAT() to concatenate in the new password, but this is not a secure method and is therefore not recommended. You lose all the security benefits of the stored procedure if you were to do it that way.

score 0 · Answer 2 · answered Jul 17 '18 at 23:28

You must use prepared statements because the variables doesn't evaluate directly in queries.

DELIMITER $$

DROP PROCEDURE IF EXISTS `create_user`$$

CREATE PROCEDURE `create_user`(
    `user_name` VARCHAR(50), 
    `passwd` VARCHAR(255),
    `ip` VARCHAR(50)
)
BEGIN
   set @sql = concat("CREATE USER '",`user_name`,"'@'",`ip`,"' IDENTIFIED BY '",`passwd`,"'");
   PREPARE stmt1 FROM @sql;
   EXECUTE stmt1;
   DEALLOCATE PREPARE stmt1;

   set @sql = concat("GRANT ALL PRIVILEGES ON * . * TO '",`user_name`,"'@'",`ip`,"' ");
   PREPARE stmt2 FROM @sql;
   EXECUTE stmt2;
   DEALLOCATE PREPARE stmt2;

   FLUSH PRIVILEGES;
END$$

DELIMITER ;

syntax error from CREATE USER with variables giving username and password

2 Answers2

Linked

Related