Escape reference to user table in models.SearchEmails #16313

zeripath · 2021-06-30T16:10:22Z

adyanth commented

2021-06-30 16:10:22 +00:00

(Migrated from github.com)

This PR prevents an internal server error in models.SearchEmails on postgres and likely other dbs due to an unescaped reference to user table.

Fixes regression introduced by #15956

Fix #16312
Signed-off-by: Adyanth H adyanthh@gmail.com

Details

Testing done on a gitea/gitea:latest and a fresh build with postgres:alpine as the DB:

Before:

gitea_1  | 2021/06/30 15:44:37 Started GET /admin/emails for 10.10.10.10:0
gitea_1  | 2021/06/30 15:44:37 models/user.go:1370:getUserByID() [I] [SQL] SELECT "id", "lower_name", "name", "full_name", "email", "keep_email_private", "email_notifications_preference", "passwd", "passwd_hash_algo", "must_change_password", "login_type", "login_source", "login_name", "type", "location", "website", "rands", "salt", "language", "description", "created_unix", "updated_unix", "last_login_unix", "last_repo_visibility", "max_repo_creation", "is_active", "is_admin", "is_restricted", "allow_git_hook", "allow_import_local", "allow_create_organization", "prohibit_login", "avatar", "avatar_email", "use_custom_avatar", "num_followers", "num_following", "num_stars", "num_repos", "num_teams", "num_members", "visibility", "repo_admin_change_team_access", "diff_view_style", "theme", "keep_activity_private" FROM "user" WHERE "id"=$1 LIMIT 1 [1] - 721.426µs
gitea_1  | 2021/06/30 15:44:37 ...s/issue_stopwatch.go:67:HasUserStopwatch() [I] [SQL] SELECT "id", "issue_id", "user_id", "created_unix" FROM "stopwatch" WHERE (user_id = $1) LIMIT 1 [1] - 613.259µs
gitea_1  | 2021/06/30 15:44:37 .../web/admin/emails.go:79:Emails() [I] [SQL] SELECT count(*) FROM "email_address" INNER JOIN "user" ON "user".ID = email_address.uid WHERE user."type"=$1 [0] - 268.689µs
gitea_1  | 2021/06/30 15:44:37 .../web/admin/emails.go:81:Emails() [E] SearchEmails: Count: pq: syntax error at or near "."
gitea_1  | 2021/06/30 15:44:37 ...dels/notification.go:716:GetNotificationCount() [I] [SQL] SELECT count(*) FROM "notification" WHERE (user_id = $1) AND (status = $2) [1 1] - 2.18059ms
gitea_1  | 2021/06/30 15:44:37 models/avatar.go:135:SizedAvatarLink() [I] [SQL] BEGIN TRANSACTION [] - 213.794µs
gitea_1  | 2021/06/30 15:44:37 models/avatar.go:99:func1() [I] [SQL] SELECT "hash", "email" FROM "email_hash" WHERE (email = $1 AND hash = $2) LIMIT 1 [adyanthh@gmail.com 56c1a30ce27236aaa99c65c60f73cde0] - 2.48431ms
gitea_1  | 2021/06/30 15:44:37 models/avatar.go:87:HashedAvatarLink() [I] [SQL] ROLLBACK [] - 127.941µs
gitea_1  | 2021/06/30 15:44:37 Completed GET /admin/emails 500 Internal Server Error in 14.211549ms

After:

gitea_1  | 2021/06/30 15:57:36 Started GET /admin/emails for 10.10.10.10:0
gitea_1  | 2021/06/30 15:57:36 models/user.go:1370:getUserByID() [I] [SQL] SELECT "id", "lower_name", "name", "full_name", "email", "keep_email_private", "email_notifications_preference", "passwd", "passwd_hash_algo", "must_change_password", "login_type", "login_source", "login_name", "type", "location", "website", "rands", "salt", "language", "description", "created_unix", "updated_unix", "last_login_unix", "last_repo_visibility", "max_repo_creation", "is_active", "is_admin", "is_restricted", "allow_git_hook", "allow_import_local", "allow_create_organization", "prohibit_login", "avatar", "avatar_email", "use_custom_avatar", "num_followers", "num_following", "num_stars", "num_repos", "num_teams", "num_members", "visibility", "repo_admin_change_team_access", "diff_view_style", "theme", "keep_activity_private" FROM "user" WHERE "id"=$1 LIMIT 1 [1] - 1.182398ms
gitea_1  | 2021/06/30 15:57:36 ...s/issue_stopwatch.go:67:HasUserStopwatch() [I] [SQL] SELECT "id", "issue_id", "user_id", "created_unix" FROM "stopwatch" WHERE (user_id = $1) LIMIT 1 [1] - 976µs
gitea_1  | 2021/06/30 15:57:36 .../web/admin/emails.go:79:Emails() [I] [SQL] SELECT count(*) FROM "email_address" INNER JOIN "user" ON "user".ID = email_address.uid WHERE "user"."type"=$1 [0] - 1.176922ms
gitea_1  | 2021/06/30 15:57:36 models/user_mail.go:363:SearchEmails() [I] [SQL] SELECT email_address.*, "user".name, "user".full_name FROM "email_address" INNER JOIN "user" ON "user".ID = email_address.uid WHERE "user"."type"=$1 ORDER BY email_address.lower_email ASC, email_address.is_primary DESC, email_address.id ASC LIMIT 50 [0] - 1.217405ms
gitea_1  | 2021/06/30 15:57:36 ...dels/notification.go:716:GetNotificationCount() [I] [SQL] SELECT count(*) FROM "notification" WHERE (user_id = $1) AND (status = $2) [1 1] - 726.65µs
gitea_1  | 2021/06/30 15:57:36 Completed GET /admin/emails 200 OK in 9.095327ms

This PR prevents an internal server error in `models.SearchEmails` on postgres and likely other dbs due to an unescaped reference to `user` table. Fixes regression introduced by #15956 Fix #16312 Signed-off-by: Adyanth H <adyanthh@gmail.com> ## Details Testing done on a `gitea/gitea:latest` and a fresh build with `postgres:alpine` as the DB: Before: ``` gitea_1 | 2021/06/30 15:44:37 Started GET /admin/emails for 10.10.10.10:0 gitea_1 | 2021/06/30 15:44:37 models/user.go:1370:getUserByID() [I] [SQL] SELECT "id", "lower_name", "name", "full_name", "email", "keep_email_private", "email_notifications_preference", "passwd", "passwd_hash_algo", "must_change_password", "login_type", "login_source", "login_name", "type", "location", "website", "rands", "salt", "language", "description", "created_unix", "updated_unix", "last_login_unix", "last_repo_visibility", "max_repo_creation", "is_active", "is_admin", "is_restricted", "allow_git_hook", "allow_import_local", "allow_create_organization", "prohibit_login", "avatar", "avatar_email", "use_custom_avatar", "num_followers", "num_following", "num_stars", "num_repos", "num_teams", "num_members", "visibility", "repo_admin_change_team_access", "diff_view_style", "theme", "keep_activity_private" FROM "user" WHERE "id"=$1 LIMIT 1 [1] - 721.426µs gitea_1 | 2021/06/30 15:44:37 ...s/issue_stopwatch.go:67:HasUserStopwatch() [I] [SQL] SELECT "id", "issue_id", "user_id", "created_unix" FROM "stopwatch" WHERE (user_id = $1) LIMIT 1 [1] - 613.259µs gitea_1 | 2021/06/30 15:44:37 .../web/admin/emails.go:79:Emails() [I] [SQL] SELECT count(*) FROM "email_address" INNER JOIN "user" ON "user".ID = email_address.uid WHERE user."type"=$1 [0] - 268.689µs gitea_1 | 2021/06/30 15:44:37 .../web/admin/emails.go:81:Emails() [E] SearchEmails: Count: pq: syntax error at or near "." gitea_1 | 2021/06/30 15:44:37 ...dels/notification.go:716:GetNotificationCount() [I] [SQL] SELECT count(*) FROM "notification" WHERE (user_id = $1) AND (status = $2) [1 1] - 2.18059ms gitea_1 | 2021/06/30 15:44:37 models/avatar.go:135:SizedAvatarLink() [I] [SQL] BEGIN TRANSACTION [] - 213.794µs gitea_1 | 2021/06/30 15:44:37 models/avatar.go:99:func1() [I] [SQL] SELECT "hash", "email" FROM "email_hash" WHERE (email = $1 AND hash = $2) LIMIT 1 [adyanthh@gmail.com 56c1a30ce27236aaa99c65c60f73cde0] - 2.48431ms gitea_1 | 2021/06/30 15:44:37 models/avatar.go:87:HashedAvatarLink() [I] [SQL] ROLLBACK [] - 127.941µs gitea_1 | 2021/06/30 15:44:37 Completed GET /admin/emails 500 Internal Server Error in 14.211549ms ``` After: ``` gitea_1 | 2021/06/30 15:57:36 Started GET /admin/emails for 10.10.10.10:0 gitea_1 | 2021/06/30 15:57:36 models/user.go:1370:getUserByID() [I] [SQL] SELECT "id", "lower_name", "name", "full_name", "email", "keep_email_private", "email_notifications_preference", "passwd", "passwd_hash_algo", "must_change_password", "login_type", "login_source", "login_name", "type", "location", "website", "rands", "salt", "language", "description", "created_unix", "updated_unix", "last_login_unix", "last_repo_visibility", "max_repo_creation", "is_active", "is_admin", "is_restricted", "allow_git_hook", "allow_import_local", "allow_create_organization", "prohibit_login", "avatar", "avatar_email", "use_custom_avatar", "num_followers", "num_following", "num_stars", "num_repos", "num_teams", "num_members", "visibility", "repo_admin_change_team_access", "diff_view_style", "theme", "keep_activity_private" FROM "user" WHERE "id"=$1 LIMIT 1 [1] - 1.182398ms gitea_1 | 2021/06/30 15:57:36 ...s/issue_stopwatch.go:67:HasUserStopwatch() [I] [SQL] SELECT "id", "issue_id", "user_id", "created_unix" FROM "stopwatch" WHERE (user_id = $1) LIMIT 1 [1] - 976µs gitea_1 | 2021/06/30 15:57:36 .../web/admin/emails.go:79:Emails() [I] [SQL] SELECT count(*) FROM "email_address" INNER JOIN "user" ON "user".ID = email_address.uid WHERE "user"."type"=$1 [0] - 1.176922ms gitea_1 | 2021/06/30 15:57:36 models/user_mail.go:363:SearchEmails() [I] [SQL] SELECT email_address.*, "user".name, "user".full_name FROM "email_address" INNER JOIN "user" ON "user".ID = email_address.uid WHERE "user"."type"=$1 ORDER BY email_address.lower_email ASC, email_address.is_primary DESC, email_address.id ASC LIMIT 50 [0] - 1.217405ms gitea_1 | 2021/06/30 15:57:36 ...dels/notification.go:716:GetNotificationCount() [I] [SQL] SELECT count(*) FROM "notification" WHERE (user_id = $1) AND (status = $2) [1 1] - 726.65µs gitea_1 | 2021/06/30 15:57:36 Completed GET /admin/emails 200 OK in 9.095327ms ```

Escape reference to user table in models.SearchEmails #16313

Details

Escape reference to `user` table in models.SearchEmails #16313