MySQL error while using gorm exec

Hi guys, I’m trying to use gorm to create a new user for my local mysql,it fails when I try to use "?"in gormdb.exec:

err := db.Exec("CREATE USER ? IDENTIFIED BY ?", a.Name, a.Pwd).Error

And it returns error:

[1.824ms] [rows:0] CREATE USER 'Reiis' IDENTIFIED BY '12345'
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 ?' at line 1

Seems like gorm didn’t swap the “?” with a.Name? but it also prints the correct SQL line on the screen.

When I try to run the code below it works:

cmd := fmt.Sprintf("CREATE USER '%s' IDENTIFIED BY '%s'", a.Name, a.Pwd)
err := db.Exec(cmd).Error

It’s just so weird. :cry: Does anyone know what the problem is? Thx!

Not everything can be parameterized, and CREATE USER is one such example.

Why are you creating database users from your application?

So it’s not a bug but a feature? Actually I want to make some kind of control manager over MySQL clusters and using GORM to avoid sql injection. Is there any better ways to realize those features ? Thanks!

I don’t believe it’s a feature; just a “limitation.” Maybe that limitation could be considered a bug. I’m not familiar with MySQL, but you might be able to do it indirectly with something like this:

    SET @username = ?;
    SET @password = ?;
    SET @sql = CONCAT("CREATE USER ", QUOTE(@username), " IDENTIFIED BY ", QUOTE(@password), ";"); 
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
`, a.Name, a.Pwd)

Wow that seems to work. I will try it tomorrow, thanks so much :smile:

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.