mssql: Cannot call methods on nvarchar.

[ItsFullOfCode]

Gogs version

Gogs 0.13.0

Git version

git version 2.27.0.windows.1

Operating system

Microsoft Windows Server 2016 Standard

Database

mssql

Describe the bug

After upgrade to 0.13.0 Gogs is no longer operational.

gogs.io/gogs/internal/db/users.go:790 mssql: Cannot call methods on nvarchar.
[5.284ms] [rows:0] SELECT "user"."id","user"."lower_name","user"."name","user"."full_name","user"."email","user"."passwd","user"."login_source","user"."login_name","user"."type","user"."location","user"."website","user"."rands","user"."salt","user"."created_unix","user"."updated_unix","user"."last_repo_visibility","user"."max_repo_creation","user"."is_active","user"."is_admin","user"."allow_git_hook","user"."allow_import_local","user"."prohibit_login","user"."avatar","user"."avatar_email","user"."use_custom_avatar","user"."num_followers","user"."num_following","user"."num_stars","user"."num_repos","user"."description","user"."num_teams","user"."num_members" FROM "user" LEFT JOIN email_address ON email_address.uid = user.id WHERE user.type = '0' AND ((user.email = 'xxx.xxx@xxx.com' AND user.is_active = 1) OR (email_address.email = 'xxx.xxx@xxx.com' AND email_address.is_activated = 1)) ORDER BY "user"."id" OFFSET 0 ROW FETCH NEXT 1 ROWS ONLY

To reproduce

Upgrade to .13.0 from 0.11 on Windows Server with MS SQL DB.

Expected behavior

Gogs working as expeced.

Additional context

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[pkbutler]

The term user is a mssql reserved word so in the FROM and WHERE parts of the query the usage of user must be quoted i.e. user.type = '0' should be "user".type or [user].type

[ItsFullOfCode]

Thanks. Is there something I can do about this? The query is generated by Gogs.

[pkbutler]

No this requires a change in gogs to process this correctly for mssql.

I did a local recompile with the following changes, if that helps anyone fix this.
file: internal/db/actions.go

 func (db *actions) ListByUser(ctx context.Context, userID, actorID, afterID int64, isProfile bool) ([]*Action, error) {
diff --git a/internal/db/repo.go b/internal/db/repo.go
index 8f15ea2b..fff66768 100644
--- a/internal/db/repo.go
+++ b/internal/db/repo.go
@@ -2392,7 +2392,7 @@ func GetWatchers(repoID int64) ([]*Watch, error) {
 func (repo *Repository) GetWatchers(page int) ([]*User, error) {
        users := make([]*User, 0, ItemsPerPage)
        sess := x.Limit(ItemsPerPage, (page-1)*ItemsPerPage).Where("watch.repo_id=?", repo.ID)
-       if conf.UsePostgreSQL {
+       if conf.UsePostgreSQL || conf.UseMSSQL {
                sess = sess.Join("LEFT", "watch", `"user".id=watch.user_id`)
        } else {
                sess = sess.Join("LEFT", "watch", "user.id=watch.user_id")
@@ -2492,7 +2492,7 @@ func IsStaring(userID, repoID int64) bool {
 func (repo *Repository) GetStargazers(page int) ([]*User, error) {
        users := make([]*User, 0, ItemsPerPage)
        sess := x.Limit(ItemsPerPage, (page-1)*ItemsPerPage).Where("star.repo_id=?", repo.ID)
-       if conf.UsePostgreSQL {
+       if conf.UsePostgreSQL || conf.UseMSSQL {
                sess = sess.Join("LEFT", "star", `"user".id=star.uid`)
        } else {
                sess = sess.Join("LEFT", "star", "user.id=star.uid")
diff --git a/internal/dbutil/string.go b/internal/dbutil/string.go
index 4ee3f012..9afeb2d3 100644
--- a/internal/dbutil/string.go
+++ b/internal/dbutil/string.go
@@ -11,12 +11,16 @@ import (
 )

 // Quote adds surrounding double quotes for all given arguments before being
-// formatted if the current database is UsePostgreSQL.
+// formatted if the current database is UsePostgreSQL
+// Quote adds surrounding square brackets for all given arguments before being
+// formatted if the current database is UseMSSQL.
 func Quote(format string, args ...string) string {
        anys := make([]any, len(args))
        for i := range args {
                if conf.UsePostgreSQL {
                        anys[i] = `"` + args[i] + `"`
+               } else if conf.UseMSSQL {
+                       anys[i] = `[` + args[i] + `]`
                } else {
                        anys[i] = args[i]
                }