How to format a MySql CREATE USER statement for Golang

Question

I am trying to create a MySql user from a Golang program but I am unable to find the correct formatting of the SQL string:

    _, err := db.Query("CREATE USER ?@`%` IDENTIFIED BY ?",username)

I have tried many variations: enclosing the ? in backticks, single quotes, parenthesis, but nothing works.

I either get sql: expected 0 arguments, got 1 (or 2 if I add one or two parameters), or I get

Error 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 '?@`%` IDENTIFIED BY ?' at line 1

I have found similar questions but none is using the CREATE USER, and none of the solutions found there worked for me. Thanks

See stackoverflow.com/questions/11368966/… and stackoverflow.com/a/11951620/965900 — mkopriva
– mkopriva, Commented Jun 7, 2019 at 12:38
Thank you @mkopriva but neither of those is a Go problem. I was having a problem with using the placeholders in Go. But thanks anyway. — bennythejudge
– bennythejudge, Commented Jun 7, 2019 at 22:04
If I'm not mistaken both of them point to a bug in mysql which causes the placeholders not work for the CREATE USER command. Go or not go, the problem is in mysql, at least that's how I understood it. And if you think Go does the placeholder replacement and not mysql I would think twice. — mkopriva
– mkopriva, Commented Jun 7, 2019 at 22:08

bennythejudge · Accepted Answer · 2019-06-07 10:27:31Z

0

I have found a workaround:

    password := "aaargh!"
    s := "CREATE USER '" + username + "'@`%` IDENTIFIED BY '" + password + "'"

This is working in my test.

answered Jun 7, 2019 at 10:27

bennythejudge

6,7212 gold badges15 silver badges13 bronze badges

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

4 Comments

bennythejudge Over a year ago

What is wrong @Volker? Could you pls explain? It does work in my program.

mkopriva Over a year ago

@Benedetto the problem with building sql statements with string concatenation is that you're simply exposing yourself to sql injection.

bennythejudge Over a year ago

@mkopriva thanks a lot. I am aware of this issue. I am only building an internal automation that will not be exposed externally and will not receive uncontrolled input. Besides I am a beginner with Go so I am just doing my best, but I would not use unsanitized SQL if this was coming from an external source such as a form or similar.

Sergey Ponomarev May 14 at 11:19

Please note that you should sanitize username valUsername = regexp.MustCompile("[^a-zA-Z0-9_]+").ReplaceAllString(username, "") and escape single quote: valPassword = strings.ReplaceAll(password, "'", "''")

Collectives™ on Stack Overflow

How to format a MySql CREATE USER statement for Golang

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related