Thanks for your reply. I understand now that the issue does not lie with the database/sql interface. It appears to be a limitation on the usage of placeholders in MySQL. 在2023年9月18日星期一 UTC+8 20:27:11<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) > > On Monday, 18 September 2023 at 13:01:52 UTC+1 Vladimir Varankin wrote: > >> 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. >> >> On Sunday, September 17, 2023 at 10:45:27 AM UTC+2 Brian Candler wrote: >> >>> 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=? >>> >>> On Sunday, 17 September 2023 at 01:02:08 UTC+1 John Zh wrote: >>> >>>> 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! >>>> >>> -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/0fdfe954-8297-40e1-a906-17a9d59b56a3n%40googlegroups.com.