OpenIDConnect Error handleOAuth2SignIn() [E] EnsureLinkExternalToUser: mssql nvarchar to datetime covnersion

[Binomimus]

Description

We tried setting up gitea with OAuth Authentication against Citrix NetScaler as IDP. Login seems to be fine and the user is asked to link that account or create a new one. Both is resulting in an 500 server error

The relevant logs shows a conversion error while writing something to the database (mssql message is in german, sorry for that):
"...web/auth/oauth.go:340:handleOAuth2SignIn() [E] EnsureLinkExternalToUser: mssql: Bei der Konvertierung eines nvarchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs."

The mssql error translates to "When converting an nvarchar data type to a datetime data type, the value is out of range."

gitea configuration for authentication source: only client id, secret and discovery url are configured
netscaler configuration for idp: client id, secret, redirect url, issuer name (iss), audience (aud) are configured

When the user authenticates the gitea server is checking the userinfo from /oauth/idp/userinfo and receives this response:
{"iss": "https://oauth.company.de", "issued_to": "https://git.company.de/user/oauth2/companyOAUTH/callback", "aud": "#clientID#", "sub": "my.name@company.de", "expires_in": 3559, "iat": 1739888790, "exp": 1739889090, "name": "my name", "given_name": "my", "family_name": "name", "initials": "myn", "preferred_username": "my name", "unique_name": "mname", "upn": "my.name@company.de"}

I assume this is not an issue with authentication but some user info which is not getting updated correctly. We have LDAP authentication in place so it must be something specific to OAuth. Maybe some value from the JWT should get updated to the database.

Any help is appreciated

Gitea Version

1.23.3

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

No response

Operating System

No response

How are you running Gitea?

gitea runs in docker, see https://docs.gitea.com/installation/install-with-docker

Database

None

[wxiaoguang]

The only "datetime" field for "external user" is ExpiresAt

gitea/models/user/external_login_user.go

Line 76 in c2e23d3

ExpiresAt time.Time

At web/auth/oauth.go:340:handleOAuth2SignIn(), the EnsureLinkExternalToUser sets ExpiresAt by toExternalLoginUser and try to update database.

gitea/services/externalaccount/user.go

Lines 75 to 82 in c2e23d3

	func EnsureLinkExternalToUser(ctx context.Context, user *user_model.User, gothUser goth.User) error {
	externalLoginUser, err := toExternalLoginUser(ctx, user, gothUser)
	if err != nil {
	return err
	}
	return user_model.EnsureLinkExternalToUser(ctx, externalLoginUser)
	}

toExternalLoginUser just uses the OAuth2 result gothUser directly.

---

So if you are able to add more logs and build your instance to debug, I think it need to figure out the content of "gothUser" first, I guess it's ExpiresAt is not valid (just a guess).

And you could enable the LOG_SQL config option to see all SQL statements to see which SQLs causes that error.

[Binomimus]

Hi wxiaoguang,

thanks for checking on this. I've some more logs here and plan to investigate more later today

2025/02/19 12:24:53 ...ernalaccount/user.go:53:LinkAccountToUser() [I] [SQL] SELECT [external_id], [user_id], [login_source_id], [raw_data], [provider], [email], [name], [first_name], [last_name], [nick_name], [description], [avatar_url], [location], [access_token], [access_token_secret], [refresh_token], [expires_at] FROM [external_login_user] WHERE external_id=? AND login_source_id=? ORDER BY 1 ASC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY [my.name@company.de 3] - 443.725µs
2025/02/19 12:24:53 ...ernalaccount/user.go:53:LinkAccountToUser() [I] [SQL] INSERT INTO [external_login_user] ([external_id],[user_id],[login_source_id],[raw_data],[provider],[email],[name],[first_name],[last_name],[nick_name],[description],[avatar_url],[location],[access_token],[access_token_secret],[refresh_token],[expires_at]) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) [my.name@company.de 36 3 {"aud":"BiD6A7KWd5dqb6YkIHa4","email":"my.name@company.de","exp":1739964580,"expires_in":3559,"family_name":"name","given_name":"my","iat":1739964280,"initials":"myn","iss":"https://oauth.company.de","issued_to":"https://git.company.de/user/oauth2/companyOAUTH/callback","name":"my name","nbf":1739963980,"preferred_username":"my name","sub":"my.name@company.de","unique_name":"mname","upn":"my.name@company.de"} companyOAUTH my.name@company.de my name my name my name 4538fa28df0189bd d8333325cc70cd7a 2025-02-19 12:29:40] - 5.962449ms
2025/02/19 12:24:53 .../auth/linkaccount.go:188:linkAccount() [E] UserLinkAccount: mssql: Bei der Konvertierung eines nvarchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.
2025/02/19 12:24:53 .../context_response.go:70:HTML() [D] Template: status/500
2025/02/19 12:24:53 ...eb/routing/logger.go:102:func1() [I] router: completed POST /user/link_account_signin for 10.200.21.53:53546, 500 Internal Server Error in 112.5ms @ auth/linkaccount.go:129(auth.LinkAccountPostSignIn)

[wxiaoguang]

It might also be a XORM bug due to nvarchar vs datetime type @lunny

Since the data in your log expires_at='2025-02-19 12:29:40' seems right.

[wxiaoguang]

I made a quick test:

CREATE TABLE t (a NVARCHAR(200), b DATETIME);
INSERT INTO t (a, b) VALUES (N'str', N'2025-02-19 12:29:40');
SELECT * FROM t;

It seems working as expected on https://dbfiddle.uk/LF2O4lu6 .

Does it work on your MSSQL instance? What's the table structure of your external_login_user table and what if you try to insert into external_login_user table by the logged INSERT manually? If it could reproduce the problem, it could be easier to figure out why.

[Binomimus]

Hi wxiaoguang,

your test generates the same error within MSSQL. However, when i use an ISO8601 date format (with "T" instead of space), it will work:

CREATE TABLE t (a NVARCHAR(200), b DATETIME);
INSERT INTO t (a, b) VALUES (N'str', N'2025-02-19T12:29:40');
SELECT * FROM t;

MS SQL Server Infos:

• SQL Server 15.0.4249.2
• Collation Latin1_General_CI_AS
• Language: German

[Binomimus]

Hi wxiaoguang,

I found the difference. When changing the language to english it will work with space as well (either by changing the default language of the user or setting it beforehand in the query).

this will always work:

SET LANGUAGE english
INSERT INTO t (a, b) VALUES (N'str5', N'2025-02-19 12:31:44');

this will always fail:

SET LANGUAGE german
INSERT INTO t (a, b) VALUES (N'str5', N'2025-02-19 12:31:44');

this will always work as well:

INSERT INTO t (a, b) VALUES (N'str5', N'2025-02-19T12:31:44');

[wxiaoguang]

Hmm, it's really tricky. MSSQL has too many strange behaviors ....... could you fine-tune your MSSQL server to make it work with N'2025-02-19 12:31:44'? Because IMO Gitea can't be a know-everything app to various database configurations.

---

And IIRC, using space is the ANSI SQL standard

[Binomimus]

Changing the default language of the gitea user within MSSQL fixed it.
Thank you very much for pointing me in the right direction.
I always thought ISO8601 was the most common format.

I can't tell whether it might be feasible to handle this in the code or just add language=english as a requirement in the documentation for mssql (at least I could not find any mention of it).