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. — Michael Berkowski
– Michael Berkowski, Commented Jul 6, 2012 at 20:19
Hmm I wouldn't have even tried that. Just reached straight for exec('SomeQuery') — Tony Hopkinson
– Tony Hopkinson, Commented Jul 6, 2012 at 20:28
@eggyal Looks like I had it as an answer originally. I restored and undeleted it. — Michael Berkowski
– Michael Berkowski, Commented Sep 4, 2012 at 16:20

Michael Berkowski · Accepted Answer · 2012-09-04 16:18:17Z

4

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.

edited Sep 4, 2012 at 16:18

answered Jul 6, 2012 at 20:06

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ivan Cachicatari · Accepted Answer · 2018-07-17 23:28:02Z

0

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 ;

answered Jul 17, 2018 at 23:28

Ivan Cachicatari

4,3143 gold badges24 silver badges41 bronze badges

Collectives™ on Stack Overflow

syntax error from CREATE USER with variables giving username and password

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related