gorm是否解释具有逻辑或的结构体的内容?

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

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
}

在我的中间件中,我获取相关头部的内容,并得到变量userHeadertokenHeader。它们应该与数据库中的条目匹配以进行身份验证。

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

huangapple
  • 本文由 发表于 2022年2月18日 18:20:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/71171845.html
匿名

发表评论

匿名网友

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

确定