尝试使用Prepare在Golang中进行包含搜索查询。

huangapple go评论144阅读模式
英文:

Trying to query contains search in golang with Prepare

问题

我在Go语言中有一个准备和查询函数,当我在搜索中使用?时,它可以正常工作。但是,当我尝试使用%进行包含搜索时,我得到了"sql: statement expects 0 inputs; got 1"的错误。

如何在不直接将变量放入SQL语句中的情况下执行此类查询?我想要防止SQL注入。谢谢你的帮助。

我正在使用go版本go1.8.1 darwin/amd64,并且使用MySQL。

我的Go代码:

stmt, err := dBusers.Prepare("SELECT id, name, email, username FROM users WHERE name LIKE ?;")
if err != nil {
    logging.LogError("Could Not Query All Users: "+err.Error())
    return nil, errors.New("Could Not Query Users")
}
rows, err := stmt.Query("%" + name + "%")

请注意,我将%放在了代码中的查询参数中,而不是在SQL语句中。这样可以确保在执行查询时,查询参数会被正确地转义和处理,从而防止SQL注入攻击。

英文:

I have a prepare and a query function in go and when I place the ? in the search it works fine. When I try and do a contains search with the % I get sql: statement expects 0 inputs; got 1

How can I do this type of query without having the variables directly in the SQL? I want to prevent SQL injection . Thank You for your help.

I am using go version go1.8.1 darwin/amd64 and using MySQL

My Go Code:

stmt, err := dBusers.Prepare("SELECT id, name, email, username FROM users WHERE name LIKE '%?%';")
	if err != nil {
		logging.LogError("Could Not Query All Users: "+err.Error())
		return nil, errors.New("Could Not Query Users")
	}
	rows, err := stmt.Query(name)

答案1

得分: 3

将我的评论重新发布为答案,以便这个问题实际上有一个答案:

这个答案建议在参数参数(在这种情况下是name)中包含通配符,而不是在查询中。所以使用stmt.Query("%" + name + "%")。这仍然可以防止注入,因为它仍然作为参数发送,而不是与查询内联。

英文:

Reposting my comment as an answer so this question actually has one:

This answer suggests making your parameter argument (in this case, name) include the wildcards instead of the query. So stmt.Query("%" + name + "%"). This still prevents injection, because it's still being sent as a parameter instead of inline with the query.

答案2

得分: 0

你可以将整个%searchValue%作为参数值本身,或者在查询语句内部使用字符串拼接。

方法1(推荐):

stmt, err := dBusers.Prepare("SELECT id, name, email, username FROM users WHERE name LIKE ?;")
if err != nil {
    logging.LogError("Could Not Query All Users: "+err.Error())
    return nil, errors.New("Could Not Query Users")
}
rows, err := stmt.Query("%" + name + "%")

方法2:

stmt, err := dBusers.Prepare("SELECT id, name, email, username FROM users WHERE name LIKE '%' + ? + '%';")
if err != nil {
    logging.LogError("Could Not Query All Users: "+err.Error())
    return nil, errors.New("Could Not Query Users")
}
rows, err := stmt.Query(name)

我建议使用第一种方法,因为它不会强制你始终使用"包含"搜索。你可以选择性地使用相同的语句进行精确搜索(仅限名称)、以...开始的搜索(名称+"%")、以...结束的搜索("%"+名称)或包含搜索("%"+名称+"%")。

PS:想一想,第二种方法可能会导致注入问题,不确定,需要进一步思考。

英文:

You could either make the whole %searchValue% a parameter value itself, or use string concatenation for it inside the query itself.

Approach 1 (recommended):

stmt, err := dBusers.Prepare("SELECT id, name, email, username FROM users WHERE name LIKE ?;")
    if err != nil {
        logging.LogError("Could Not Query All Users: "+err.Error())
        return nil, errors.New("Could Not Query Users")
    }
    rows, err := stmt.Query("%" + name + "%")

Approach 2:

stmt, err := dBusers.Prepare("SELECT id, name, email, username FROM users WHERE name LIKE '%' + ? + '%';")
    if err != nil {
        logging.LogError("Could Not Query All Users: "+err.Error())
        return nil, errors.New("Could Not Query Users")
    }
    rows, err := stmt.Query(name) 

I would recommend the first approach because it wouldn't force you to always use a "contains' search. You could selectively use the same statement to do exact (just name), begins with (name+"%"), ends with ("%"+name) or contains ("%"+name+"%") searches.

PS: Thinking about it, the second approach might lead to injection, not sure, need to think about it more.

huangapple
  • 本文由 发表于 2017年5月10日 03:50:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/43878740.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定