英文:
How do make safe query builder and prevent sql injection
问题
这是我的代码：
type mysqlRepository struct {
Conn *sql.DB
}
func (dbconn *mysqlRepository) GetAll(param map[string]string) (response []models.Subject, err error) {
var result models.Subject
c := 0
q := `
SELECT id, name, teacher, uuid
FROM subject
`
for i, x := range param {
if x != "" {
if c > 0 {
q += " AND " + i + " = " + x
} else {
q += " WHERE " + i + " = " + x
}
c++
}
}
query, err := dbconn.Conn.Query(q)
if err != nil {
utils.QueryErrorException(err)
return
}
defer query.Close()
for query.Next() {
errorScanningDataHistory := query.Scan(
&result.ID,
&result.Name,
&result.Teacher,
&result.UUID,
)
utils.QueryErrorException(errorScanningDataHistory)
response = append(response, result)
}
return
}
我尝试使用 Postman 运行以下命令，并且运行良好：http://localhost/public/api/v1/subject?name=robert。它只显示了以 Robert 为教师的科目。
但是，如果我注入 SQL 命令，它也可以工作：http://localhost/public/api/v1/subject?name=robert OR 1=1。但是，它会返回所有数据。
我该如何增加安全性？
英文:
This is my code:
type mysqlRepository struct {
Conn *sql.DB
}
func (dbconn *mysqlRepository) GetAll(param map[string]string) (response []models.Subject, err error) {
var result models.Subject
c := 0
q := `
SELECT id, name, teacher, uuid
FROM subject
`
for i, x := range param {
if x != "" {
if c > 0 {
q += ` AND ` + i + ` = ` + x
} else {
q += ` WHERE ` + i + ` = ` + x
}
c++
}
}
query, err := dbconn.Conn.Query(q)
if err != nil {
utils.QueryErrorException(err)
return
}
defer query.Close()
for query.Next() {
errorScanningDataHistory := query.Scan(
&result.ID,
&result.Name,
&result.Teacher,
&result.UUID,
)
utils.QueryErrorException(errorScanningDataHistory)
response = append(response, result)
}
return
}
I try using postman like this and run well: http://localhost/public/api/v1/subject?name=robert. It only show subject with robert as a teacher
But, If I inject sql command, it also works: http://localhost/public/api/v1/subject?name=robert OR 1=1. But, It return all data.
How do I make more secure?
答案1
得分: 3
实现@mkopriva的评论：
通过已知/允许的列的映射来过滤i。不要连接x，而是使用占位符?，并将x附加到参数切片中，然后将其传递给查询。
safeFields := map[string]bool{"name": true}
args := []interface{}{}
where := "WHERE 1"
for i, x := range param {
if _, ok := safeFields[i]; ok && x != "" {
where += fmt.Sprintf(" AND %s=?", i)
args = append(args, x)
}
}
query, err := dbconn.Conn.Query(q+where, args...)
英文:
implement of @mkopriva 's comment
> Filter i through a map of known/allowed columns. Instead of
> concatenating x use the placeholder ? and append x to a slice of
> arguments which you then pass to the query
safeFields := map[string]bool{"name": true}
args := []interface{}{}
where := "WHERE 1"
for i, x := range param {
if _, ok := safeFields[i]; ok && x != "" {
where += fmt.Sprintf(" AND %s=?", i)
args = append(args, x)
}
}
query, err := dbconn.Conn.Query(q+where, args...)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论