在mysql和golang中建立动态查询 [英] building a dynamic query in mysql and golang

问题描述

如何根据获取的参数构建动态查询?

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.

我知道如何在PHP中做到这一点，但在golang中却没有.

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)

推荐答案

要回答有关如何设置字符串格式的问题，简单的答案是使用

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根据格式说明符进行格式化，并返回结果字符串.

Sprintf formats according to a format specifier and returns the resulting string.

示例:

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)

使用它进行查询，可以避免注入.话虽这么说，您可能很想修改它，并使用db.Exec进行创建/更新/删除.根据一般经验，如果您将db.Exec与fmt.Sprintf一起使用并且不先清理输入内容，则可以进行sql注入.

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带有一个简单示例，说明带有db.Exec的fmt.Sprintf不好的原因:
https://play.golang.org/p/-IWyymAg_Q

GoPlay with simple example of why fmt.Sprintf with db.Exec is bad:
https://play.golang.org/p/-IWyymAg_Q

您应使用 db.Query 或

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.

这篇关于在mysql和golang中建立动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！