Description

While testing /api/v1/admin/{username}/badges I have come across the following issues:

1. RemoveUserBadges generates invalid sql on mysql, postgresql, sqlite (and possibly other databases):

DELETE FROM `user_badge` WHERE (`user_badge`.user_id=? AND `badge`.slug=?)

resulting in InternalServerError: Error 1054 (42S22): Unknown column 'badge.slug' in 'where clause'. To reproduce send a DELETE request with any badge_slugs payload and a valid username in the url.

2. There is no unique constraint on user_badge table, which makes is possible to accumulate duplicate badges for the same user. I can't see a valid use case for this, so I consider this a bug. This can be reproduced by repeated POST requests with the same badge_slugs payload.

I couldn't find a way to use admin API on demo.gitea.com, so it wasn't reproduced there.

These problems can be illustrated by a test I've created in my branch: https://github.com/komarov/gitea/blob/user-badges-api-fixes/models/user/badge_test.go

Gitea Version

cb6b33c

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?

I observe this behavior when building from source and running from command-line as ./gitea web

Database

PostgreSQL