英文:
Does gorm interpret the content of a struct with a logical OR?
问题
新手学习SQL,我正在编写一个API中间件作为练习,该中间件检查一些头部信息是否与数据库条目匹配("基于令牌的身份验证")。数据库访问基于GORM
。
为此,我定义了我的ORM如下:
type User struct {
ID uint
UserName string
Token string
}
在我的中间件中,我获取相关头部的内容,并得到变量userHeader
和tokenHeader
。它们应该与数据库中的条目匹配以进行身份验证。
user
表只有一条记录:
select * from users
// 1,admin,admintoken
身份验证代码如下:
var auth User
res := db.Where(&User{UserName: userHeader, Token: tokenHeader}).Find(&auth)
if res.RowsAffected == 1 {
// 身份验证成功
}
在测试时,我得到了以下两个错误结果(其他组合是正确的):
- 只有一个头部设置为正确值(另一个头部不存在),身份验证成功(添加另一个头部并设置为错误值是可以的(=身份验证失败))
- 没有设置头部 → 身份验证通过
针对上述错误结果,我期望我的查询意味着:
select * from users where users.user_name = 'admin' and users.token = ''
select * from users where users.user_name = '' and users.token = ''
在控制台上,这个查询是正确的,即产生零结果(针对数据库运行)。
然而,ORM似乎会丢弃不存在的头部并假设它们是正确的(至少这是我的理解)。
我还尝试通过以下方式链接Where
子句:
db.Where(&User{UserName: userHeader}).Where(&User{Token: tokenHeader}).Find(&auth)
但结果是一样的。
应该使用什么样的正确查询?
英文:
New to SQL, I am writing as an exercise an API middleware that checks if the information contained in some headers match a database entry ("token-based authentication"). Database access is based on GORM
.
To this, I have defined my ORM as follows:
type User struct {
ID uint
UserName string
Token string
}
In my middleware I retrieve the content of relevant headers and end up with the variables userHeader
and tokenHeader
. They are supposed to be matched to the database in order to do the authentication.
The user
table has one single entry:
select * from users
// 1,admin,admintoken
The authentication code is
var auth User
res := db.Where(&User{UserName: userHeader, Token: tokenHeader}).Find(&auth)
if res.RowsAffected == 1 {
// authentication succeeded
}
When testing this, I end up with the following two incorrect results (other combinations are correct):
- with only one header set to a correct value (and the other one not present) the authentication is successful (adding the other header with an incorrect value is OK (=auth fails))
- no headers set → authentication goes though
I expected my query to mean (in the context of the incorrect results above)
select * from users where users.user_name = 'admin' and users.token = ''
select * from users where users.user_name = '' and users.token = ''
and this query is correct on the console, i.e. produces zero results (ran against the database).
The ORM one, however, seems to discard non-existing headers and assume they are fine (this is at least my understanding)
I also tried to chain the Where
clauses via
db.Where(&User{UserName: userHeader}).Where(&User{Token: tokenHeader}).Find(&auth)
but the result is the same.
What should be the correct query?
答案1
得分: 2
gorm.io文档中关于在Where
条件中使用结构体的说明如下:
使用结构体进行查询时,GORM 只会查询非零字段,这意味着如果字段的值为
0
、''
、false
或其他零值,它将不会用于构建查询条件...
对此的建议解决方案是:
要在查询条件中包含零值,可以使用一个map,它将包含所有键值作为查询条件...
因此,当令牌头或两个头都为空,但您仍然希望将它们包含在生成的查询的WHERE
子句中时,您需要将map
作为参数而不是结构体传递给Where
方法。
db.Where(map[string]interface{}{"user_name": userHeader, "token": tokenHeader}).Find(&auth)
您可以使用Debug()
来检查生成的SQL(它会打印到stderr中);如果您不确定代码生成了什么SQL语句,可以使用它。
英文:
The gorm.io documentation says the following on the use of structs in Where
conditionals:
> When querying with struct, GORM will only query with non-zero fields,
> that means if your field’s value is 0
, ''
, false
or other zero
> values, it won’t be used to build query conditions ...
The suggested solution to this is:
> To include zero values in the query conditions, you can use a map,
> which will include all key-values as query conditions ...
So, when the token header or both headers are empty, but you still want to include them in the WHERE
clause of the generated query, you need to use a map
instead of the struct as the argument to the Where
method.
db.Where(map[string]interface{}{"user_name": userHeader, "token": tokenHeader}).Find(&auth)
You can use Debug()
to check for the generated SQL (it gets printed into stderr); use it if you are unsure what SQL your code generates
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论