英文:
Dynamic SQL Set Golang
问题
我对SQLite查询的结构有疑问。我正在尝试通过用户名引用行来更新表中用户选择的值。
表名为Data，包含以下列：USERNAME,PASSWORD,ADDRESS,NOTES。
我正在使用GO的SQL驱动程序（_ "github.com/mattn/go-sqlite3"），以下是我的查询语句：
...
stmt, err := db.Prepare("UPDATE Data SET ?=? WHERE USERNAME=?")
check(err)
res, err := stmt.Exec(splittedQuery[0], splittedQuery[1],splittedQuery[2])
...
从这个序列中，我只能得到一个语法错误：near "?": syntax error。
我应该如何处理这个问题？如果这是一个简单的问题，我很抱歉，我刚开始学习GO，想从中学到一些东西。
谢谢。
英文:
I have a doubt about the structure of a SQLite query. I'm trying to update a user-selected value in the table referencing the row by the username.
The table is called Data and has these columns: USERNAME,PASSWORD,ADDRESS,NOTES.
I'm using SQL drivers for GO (_ "github.com/mattn/go-sqlite3"), here's my query:
...
stmt, err := db.Prepare("UPDATE Data SET ?=? WHERE USERNAME=?")
check(err)
res, err := stmt.Exec(splittedQuery[0], splittedQuery[1],splittedQuery[2])
...
From this sequence I can only get a syntax error: near "?": syntax error.
How should I manage this? If it's a trivial question I'm sorry, I'm just new to GO and trying to learn something out of it.
Thanks
答案1
得分: 2
你不能在SQL中这样做。这也不仅限于sqlite。参数化占位符只适用于值，不能用它来改变查询的结构。以下是一些文档链接供你参考：
- https://jmoiron.github.io/sqlx/#bindvars
- https://use-the-index-luke.com/sql/where-clause/bind-parameters
你想要做的是构建一个动态查询。你可以通过自己构建查询字符串来实现：
query := "UPDATE Data SET " + col_name + "=? WHERE USERNAME=?"
但是，根据列名数据的来源，你需要注意SQL注入问题（这是一个完全不同的话题，你可以看看这个链接 https://imgs.xkcd.com/comics/exploits_of_a_mom.png）。
还有一些库可以帮助你。例如，你可以查看这个库 https://github.com/Masterminds/squirrel。
英文:
You cannot do that in SQL. It's not specific to sqlite either. Parameterized placeholder are only for value, you cannot change the structure of the query with that. Here are some documentation links for your reference:
- https://jmoiron.github.io/sqlx/#bindvars
- https://use-the-index-luke.com/sql/where-clause/bind-parameters
What you are trying to do is building a dynamic query. You can do that by building your query string yourself:
query := "UPDATE Data SET " + col_name + "=? WHERE USERNAME=?"
But depending from the source of your data for the column_name you need to be cautious of sql injection (this is a whole other topic, for fun you can look at that https://imgs.xkcd.com/comics/exploits_of_a_mom.png).
There are also a few library available to help you with that. For example to name one, you can check this one https://github.com/Masterminds/squirrel
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论