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

Issue

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?

Solution

? 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)

Answered By – Bill Karwin

Answer Checked By – Candace Johnson (GoLangFix Volunteer)

Leave a Reply

Your email address will not be published.