英文:
building a dynamic query in mysql and golang
问题
我可以帮你翻译这段代码。以下是翻译的结果：
如何根据获取的参数构建动态查询？
这个例子很简单，语法也有误，但你可以理解我想要表达的意思。
我猜我需要在查询的末尾添加一系列的变量。
我知道如何在PHP中实现，但不知道如何在golang中实现。
db := OpenDB()
defer db.Close()
var filter string
if name != "" {
filter = filter + " AND name = ?"
}
if surname != "" {
filter = filter + " AND surname = ?"
}
if address != "" {
filter = filter + " AND address = ?"
}
err = db.Query("SELECT id FROM users WHERE login = ?" +
filter, login)
希望对你有帮助！
英文:
How can I build a dynamic query depending on the parameters that I get?
This example is stupid and the syntax is wrong but you will get the idea of what I want.
I guess that I need to add a slice of variables to the end of the query.
I know how to do it in PHP, but not in golang.
db := OpenDB()
defer db.Close()
var filter string
if name != "" {
filter = filter " AND name = ?"
}
if surname != "" {
filter = filter + " AND surname = ?"
}
if address != "" {
filter = filter + " AND address = ?"
}
err = db.Query("SELECT id FROM users WHERE login = ?" +
filter, login)
答案1
得分: 3
回答你关于如何格式化字符串的问题，简单的答案是使用fmt.Sprintf来构建你的字符串。然而，关于在数据库查询中使用fmt.Sprintf，下面有一些需要注意的事项：
> Sprintf 根据格式说明符进行格式化，并返回结果字符串。
示例：
query := fmt.Sprintf("SELECT id FROM users WHERE login='%s'", login)
err = db.Query(query)
// 等同于：
rows, err := db.Query("SELECT id FROM users WHERE login=?", login)
在查询中使用这种方式，可以避免注入攻击。话虽如此，你可能会想要修改这个方式，并且在创建/更新/删除时使用db.Exec。作为一个一般的经验法则，如果你使用db.Exec和fmt.Sprintf，并且没有先对输入进行过滤，那么你就会面临SQL注入的风险。
在GoPlay中使用一个简单的例子来说明为什么使用fmt.Sprintf和db.Exec是不好的：
https://play.golang.org/p/-IWyymAg_Q
为了避免这种攻击向量，你应该适当地使用db.Query或db.Prepare。你可能需要修改上面的代码示例，以得到一个安全的代码片段，但希望我给你提供了足够的信息来开始。
英文:
To answer your question on how to format the string, the simple answer is to use fmt.Sprintf to structure your string. However see further down for a quick note on using fmt.Sprintf for db queries:
> Sprintf formats according to a format specifier and returns the resulting string.
Example:
query := fmt.Sprintf("SELECT id FROM users WHERE login='%s'", login)
err = db.Query(query)
// Equivalent to:
rows, err := db.Query("SELECT id FROM users WHERE login=?", login)
Using this for queries, you're safe from injections. That being said, you might be tempted to modify this and use db.Exec for creations/updates/deletions as well. As a general rule of thumb, if you use db.Exec with fmt.Sprintf and do not sanitize your inputs first, you open yourself up to sql injections.
GoPlay with simple example of why fmt.Sprintf with db.Exec is bad:
https://play.golang.org/p/-IWyymAg_Q
You should use db.Query or db.Prepare in an appropriate way to avoid these sorts of attack vectors. You might have to modify the code sample above to come up with a injection-safe snippet, but hopefully I gave you enough to get started.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论