github-starred/gitea
2
0
Fork 0
mirror of https://github.com/go-gitea/gitea.git synced 2026-03-12 02:24:21 -05:00
Code Issues 2.6k Packages Projects Releases 100 Wiki Activity

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

New Issue
Closed
opened 2025-11-02 11:04:39 -06:00 by GiteaMirror · 9 comments
GiteaMirror commented 2025-11-02 11:04:39 -06:00
Owner
Copy Link

Originally created by @Binomimus on GitHub (Feb 18, 2025).

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

Originally created by @Binomimus on GitHub (Feb 18, 2025). ### 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
GiteaMirror added the issue/workaroundissue/not-a-bug labels 2025-11-02 11:04:40 -06:00
GiteaMirror commented 2025-11-02 11:04:41 -06:00
Author
Owner
Copy Link

@wxiaoguang commented on GitHub (Feb 19, 2025):

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

c2e23d3301/models/user/external_login_user.go (L76)

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

c2e23d3301/services/externalaccount/user.go (L75-L82)

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.

@wxiaoguang commented on GitHub (Feb 19, 2025): The only "datetime" field for "external user" is `ExpiresAt` https://github.com/go-gitea/gitea/blob/c2e23d3301b1be2b2ad667184030087f92ad2470/models/user/external_login_user.go#L76 At `web/auth/oauth.go:340:handleOAuth2SignIn()`, the `EnsureLinkExternalToUser` sets `ExpiresAt` by `toExternalLoginUser` and try to update database. https://github.com/go-gitea/gitea/blob/c2e23d3301b1be2b2ad667184030087f92ad2470/services/externalaccount/user.go#L75-L82 `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.
GiteaMirror commented 2025-11-02 11:04:41 -06:00
Author
Owner
Copy Link

@Binomimus commented on GitHub (Feb 19, 2025):

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)

@Binomimus commented on GitHub (Feb 19, 2025): 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)
GiteaMirror commented 2025-11-02 11:04:41 -06:00
Author
Owner
Copy Link

@wxiaoguang commented on GitHub (Feb 19, 2025):

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 commented on GitHub (Feb 19, 2025): 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.
GiteaMirror commented 2025-11-02 11:04:41 -06:00
Author
Owner
Copy Link

@wxiaoguang commented on GitHub (Feb 19, 2025):

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.

@wxiaoguang commented on GitHub (Feb 19, 2025): I made a quick test: ```sql 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.
GiteaMirror commented 2025-11-02 11:04:42 -06:00
Author
Owner
Copy Link

@Binomimus commented on GitHub (Feb 19, 2025):

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 commented on GitHub (Feb 19, 2025): 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: ```sql 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
GiteaMirror commented 2025-11-02 11:04:42 -06:00
Author
Owner
Copy Link

@Binomimus commented on GitHub (Feb 19, 2025):

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');
@Binomimus commented on GitHub (Feb 19, 2025): 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: ```sql SET LANGUAGE english INSERT INTO t (a, b) VALUES (N'str5', N'2025-02-19 12:31:44'); ``` this will always fail: ```sql SET LANGUAGE german INSERT INTO t (a, b) VALUES (N'str5', N'2025-02-19 12:31:44'); ``` this will always work as well: ```sql INSERT INTO t (a, b) VALUES (N'str5', N'2025-02-19T12:31:44'); ```
GiteaMirror commented 2025-11-02 11:04:42 -06:00
Author
Owner
Copy Link

@wxiaoguang commented on GitHub (Feb 19, 2025):

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

@wxiaoguang commented on GitHub (Feb 19, 2025): 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
GiteaMirror commented 2025-11-02 11:04:42 -06:00
Author
Owner
Copy Link

@Binomimus commented on GitHub (Feb 19, 2025):

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).

@Binomimus commented on GitHub (Feb 19, 2025): 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).
GiteaMirror commented 2025-11-02 11:04:42 -06:00
Author
Owner
Copy Link

@Binomimus commented on GitHub (Feb 24, 2025):

Solution:
MSSQL user for gitea database should have set language to english to avoid conversion issues with the datetime string

@Binomimus commented on GitHub (Feb 24, 2025): Solution: MSSQL user for gitea database should have set language to english to avoid conversion issues with the datetime string
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
$20
$250
$50
$500
backport/done
💎 Bounty
docs-update-needed
good first issue
hacktoberfest
issue/bounty
issue/confirmed
issue/critical
issue/duplicate
issue/needs-feedback
issue/not-a-bug
issue/regression
issue/stale
issue/workaround
lgtm/need 2
modifies/api
modifies/translation
outdated/backport/v1.18
outdated/theme/markdown
outdated/theme/timetracker
performance/bigrepo
performance/cpu
performance/memory
performance/speed
pr/breaking
proposal/accepted
proposal/rejected
pr/wip
pull-request
Mirrored from GitHub Pull Request
 reviewed/wontfix
💰 Rewarded
skip-changelog
status/blocked
topic/accessibility
topic/api
topic/authentication
topic/build
topic/code-linting
topic/commit-signing
topic/content-rendering
topic/deployment
topic/distribution
topic/federation
topic/gitea-actions
topic/issues
topic/lfs
topic/mobile
topic/moderation
topic/packages
topic/pr
topic/projects
topic/repo
topic/repo-migration
topic/security
topic/theme
topic/ui
topic/ui-interaction
topic/ux
topic/webhooks
topic/wiki
type/bug
type/deprecation
type/docs
type/enhancement
type/feature
type/miscellaneous
type/proposal
type/question
type/refactoring
type/summary
type/testing
type/upstream
No Label issue/not-a-bug issue/workaround
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
GiteaMirror ninjasurge
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/gitea#14154
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?