英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论