Passing Go var to MySQL - Error when trying to do db.Exec in Go lang program (MySQL db)

Question

I'm trying to pass a Go variable inside db.Exec:

    pinakas := "dokimi03"
    crTable := `CREATE TABLE ? (id bigint(20) NOT NULL AUTO_INCREMENT, username varchar(100) NOT NULL, password varchar(100) NOT NULL, email varchar(100) NOT NULL, PRIMARY KEY (id));`

    _, errCreate := db.Exec(crTable, pinakas)
    if errCreate != nil {
        log.Println(errCreate)
    }

The error I get from MySQL when I run the code is:

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 '? (id bigint(20) NOT NULL AUTO_INCREMENT, username varchar(100) NOT NULL, passwo' at line 1

The code runs if I substitute ? with dokimi03, the table name (and remove pinakas var of course).

I've searched the docs, ? seems to be the proper character to represent variables on Go-MySQL. What am I missing?

? is for values. Table names, column names, etc. are NOT values, they are identifiers. You can't use ? for table names. — mkopriva
– mkopriva, Commented Nov 9, 2022 at 15:04

Bill Karwin · Accepted Answer · 2022-11-09 15:17:27Z

2

? is the proper character for a parameter placeholder in MySQL, but you can't use a parameter for a table identifier.

Parameters can be used only as a substitute for a scalar value, not an identifier, or an SQL keyword, or an expression, etc.

This is not only relevant to MySQL and not only relevant to the Go connector. It's standard in every implementation of the SQL language (there are some connectors that fake parameters by doing string-substitution, but the Go connector for MySQL does not do that).

The table name must be fixed in the SQL string before passing it to db.Exec().

For example, I would write it this way:

pinakas := "dokimi03"
crTable := fmt.Sprintf("CREATE TABLE `%s` (id bigint(20) NOT NULL AUTO_INCREMENT, username varchar(100) NOT NULL, password varchar(100) NOT NULL, email varchar(100) NOT NULL, PRIMARY KEY (id))", pinakas)

edited Nov 9, 2022 at 15:17

answered Nov 9, 2022 at 15:05

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

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

2 Comments

NotX Over a year ago

Sidenote: You shouldn't just plainly insert a table name if it doesn't come directly from the code, because it would be prone to an injection attack. What I did was removing all ";" characters from the tablename. Not sure whether that covers all cases, but probably most.

Bill Karwin Over a year ago

@NotX You should also remove or escape any back-tick characters.

Collectives™ on Stack Overflow

Passing Go var to MySQL - Error when trying to do db.Exec in Go lang program (MySQL db)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related