英文:
how can I avoid sql inject when using "order by case when" in Golang?
问题
有一个参数表示用户名；
我需要获取一些排序后的记录，如果“starred_by”列包含'userPrefix'，它应该在前面，其他记录应该在后面。
var userPrefix string; //userPrefix是一个请求参数。
usePrefix = `%` + userPrefix + `%`
if userPrefix != "" {
order := fmt.Sprintf("CASE \nWHEN starred_by LIKE %q THEN 1\nELSE 2\nEND", userPrefix)
db = db.Order(order)
}
db = db.Order(otherParam1).Order(otherParam2)
db = db.Model(***).Scan(***)
原始的SQL语句如下：
SELECT * FROM `***` ORDER BY
CASE WHEN starred_by LIKE "%prefix1%" THEN 1 ELSE 2 END,otherParam1,otherParam2,otherParam3
但显然这会导致SQL注入问题，我该如何解决？
解决SQL注入问题的方法。
英文:
there is a param representing userName;
I need to get some sorted records,if column "starred_by" contains 'userPrefix',it should be at the front,others should be at the end.
var userPrefix string; //userPrefix is a Request Param.
usePrefix = `%` + userPrefix + `%`
if userPrefix != "" {
order := fmt.Sprintf("CASE \nWHEN starred_by LIKE %q THEN 1\nELSE 2\nEND", userPrefix)
db = db.Order(order)
}
db = db.Order(otherParam1).Order(otherParam2)
db = db.Model(***).Scan(***)
the raw sql likes below:
SELECT * FROM `***` ORDER BY
CASE WHEN starred_by LIKE "%prefix1%" THEN 1 ELSE 2 END,otherParam1,otherParam2,otherParam3
but apparently this causes sql inject problem , how can i fix this?
the way to solve sql inject.
答案1
得分: 2
这些元素无法绑定到JDBC，并且gorm不支持它们作为参数化查询或转义它们 - 在使用不受信任的输入时它们是危险的。
有两种安全的方法来处理这个问题 - 理想情况下，你应该同时使用这两种方法：
- 通过正向验证/白名单验证来验证这些列。每个列名都应该检查是否存在于相关的表中。
- 你应该对列名加上引号 - 在列名周围添加单引号。如果这样做，你需要小心验证名称中是否有引号，并在有引号时报错或转义。你还需要注意，在大多数数据库中，添加引号会使名称区分大小写。
英文:
These elements cannot be bound into JDBC and gorm doesn't support them as parameterized queries or escape them - they are dangerous to use with untrusted input.
There are two options to do this safely - ideally you should use both:
- Validate the columns in these via positive / whitelist validation. Each column name should be checked for existence in the associated tables.
- You should enquote the column name - adding single quotes around the columns. If you do this, you need to be careful to validate there are no quotes in the name, and error out or escape any quotes. You also need to be aware that (in most databases) adding quotes will make the name case sensitive.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论