Why doesn't the database/sql package in Go support using placeholders "?" to replace the database name and username in SQL statements?

[John Zh]

Hi !

I am going to make some kind of manager app over MySQL clusters by using Golang. But I found that when I try to exec some SQL line includes user name or db name, the SQL line can't be correctly parameterized.

For example:

Using GORM based on database/sql or directly using database/sql

```

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

```

Got

```

[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 it does not replace "?" with a.Name, but rather passes the SQL command with "?" directly to MySQL.  What is more wired, it prints the SQL command with correctly replaced parameters in the log.

I don't know the the underlying reason behind this phenomenon, is it intentionally designed like that?

Thx!

[Brian Candler]

According to this SO answer, CREATE USER in mysql doesn't support placeholders:

https://stackoverflow.com/questions/20647115/how-to-write-create-user-with-mysql-prepared-statement

"+1 for a legitmiate use of QUOTE() which is really the sanest thing that can be done here, since CREATE USER doesn't support ? placeholders"

You can test this by trying a simple DML statement, e.g.

UPDATE users SET name=? where name=?

[Vladimir Varankin]

A thing, that it may be valuable to explain further, is that Go's "database/sql" doesn't come with a built-in query builder. 

The package implements the database connection pooling/management, but it passes the user's SQL input and its arguments to the "driver". Depending on the particular database kind, the driver may or may not try to interpret the query (and the args), before it passes them to the database server.

In the specific example of MySQL/MariaDB, the support for placeholder "?" is a part of this database's flavour of SQL. Thus, it's likely, the driver you're using, passes the query with a placeholder to the DB server, but the DB's support of placeholders is limited to only a subset of queries (or places inside a query) — as the link to SO's answer, shared previously, explained.

Hope this makes it a little bit more clear.

[Brian Candler]

Or else it's a prepared statement which gets invoked with parameters.

Mysql's own documentation is unclear on where placeholders can be used: https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

Note that they give this example:

mysql> SET @table = 't1'; mysql> SET @s = CONCAT('SELECT * FROM ', @table);

mysql> PREPARE stmt3 FROM @s; mysql> EXECUTE stmt3;

They don't do "SELECT * FROM ?" because you're not allowed to use a placeholder for the table name - it must be inlined into the SQL. (And in this example @table had better be from a trusted source, since they don't do any quoting or escaping)

[Inada Naoki]

Hi. I'm maintainer of go-mysql-driver.

There is an option to substitute placeholders in the driver, instead of in the MySQL server.

It can be used to:

* Reduce roundtrip to execute a query

* Avoid limitations of where placeholders can be used

[Roland Müller]

Even standard SQL does not support using bind variables for everything that uses SQL syntax and does not belong to DML (=data manipulation language)

> --
> You received this message because you are subscribed to the Google Groups "golang-nuts" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/1cc07940-48c3-4d53-b97e-d74faa4c76can%40googlegroups.com.
>