Badge SQL can no longer be edited by default

(Sam Saffron) #1

Starting from Discourse 1.6 badge sql can no longer be edited by admins unless explicitly enabled.

This change was made for a couple of reasons

  1. Security: allowing admins to enter SQL directly allows them raw access to the database, generally we are opting that raw access to the database from the web UI is a feature you opt-in for. Even though the queries only return user_ids, an admin attacker can discover any information in the database using badge queries. If column A of table Y has the letter A in it return user_id 1 else 2.

  2. Performance: getting badge SQL “just right” is an art, it is not something that is trivial for admins to do correctly. There is huge amount of risk that people who are not experts can create enormous load on a database by entering bad SQL

You can still get full authoring access by running:

./launcher enter app
rails c
> SiteSetting.enable_badge_sql = true

If you are a Discourse customer on the Enterprise plan, contact @team and we will enable it for you if needed.

SiteSetting.enable_badge_sql = false (the default) disables all new badge SQL authoring. Existing badges will continue to work just fine with the SQL you have created. You can safely disable badge SQL after authoring your required badges for extra safety.


How to add SQL Queries to badges?
Are automatic badge triggers still available?
Grant a badge to individual users manually
"Trigger" text field not showing up when creating custom badge
Triggered custom badge queries
SQL Free Badge Editing
Can't find badge criteria box
What cool badge queries have you come up with?
How can I add new badges?
How are user titles generated?
How do I make my own badge query?
Grant a badge to individual users manually
Change Moderator Ability
Indicate member is deactivated on their usercard and/or avatar
Give emblem from other emblems
What cool badge queries have you come up with?
[PAID] Allow badges to be granted in restricted categories
What cool badge queries have you come up with?
Configuring Patreon integration with Discourse
404 when clicking on badge link (which shouldn't exist)
Badge system in discourse
Get Discourse to read and issue OpenBadges?
As an admin, I'm unable to see the full list of grantable badges
Copy-Paste Locked Badge Query (SQL) For More Than Champion or Invitation
Questions on badges admin can create
Add Badge to Solve answer
A Simple Way to Grant Badges en-masse?
When editing badge updated parameters are not visible after saving
Group seems to have a limit of 201 members
Creating new badge
Data Explorer Plugin
A question re the Solved plugin and Badge SQL
(R3trosteve) #2

That’s probably wise, I always thought it was cool to have, but surprised it was so easy and accessible, especially after reading some of the Badge SQL Tips & Tricks threads… there’s some pretty complex SQL hacking going down.


(Kyle McAlpine) #3

Good change. Is there (or can there be) an easier method of creating rules for admins to set for badges? I know this is hard to do in a general, safe yet flexible way with a good UX.

There are a few operation that I’m sure are really common, one being assign a badge to anyone who’s added to a given group.


(Jeff Atwood) #4

Agree, common badge scenarios should be built in.


(Erlend Sogge Heggen) #5

Further down the line I’d love to have a basic rules editor similar to that of IFTTT or Zapier:

I even played around with the idea of a complete “visual query builder for SQL”, but that would probably make it even easier for unsavvy users such as myself to blow my leg off.


SQL Free Badge Editing
(Jeff Wong) #6

Rip erlend’s left leg, 2016.

Rules editor seems like a good next step to encourage people to stay out of the schema.

Are there any plans for easier alternative methods of awarding badges manually, like selecting or importing a list of users, rather than granting one-by-one?


(Daniel Gagnon) #7

Would there be a way to have badges threshold weighted by the number of active users? I want to introduce Discourse in the organisation I work for. It has about 80 employees. A popular link should require much less clicks.


(Rafael dos Santos Silva) #8

You can enable SQL, create a custom badges in place of the popular link that requires a % of the active users instead of a fixed number, and then deactivate SQL after.


(Tobias Eigen) #9

Suggestion: add a link to the badge admin pages to here, to explain to those who follow what happened to the badge sql options? I just spent a good hour trying to remember how to do this. :crying_cat_face:


(Jay Pfaffman) #10

Indeed. It would be good if some settings (or notes about them) could also be displayed on other admin pages. My latest example was spending a good while trying to figure out why the Restore buttons were all disabled on the Backup page.

Edit: Oh. Golly. And I’d forgotten that it was a SiteSetting available only through the console.


(Jeff Atwood) #11

Hmm, no, I see it in site settings just fine on my DO droplet. “allow restore”.


(Sam Saffron) #12

Enable badge sql is a hidden site setting, only available via console


(Jay Pfaffman) #13


Yes, the badge setting is hidden, so it’s really hard for someone to tell why they can’t make new badges.

Yes, “allow restore” is in site settings, but it took me a while looking at the backups page to figure out that I had to go to site settings to enable a restore (especially since it seems that on a Dev box you can restore regardless of the site setting.


(Christoph) #14

I agree. I don’t even want to start estimating, how many hours of wondering this has cost me. Could this be made more (admin-)user friendly? In my case, the confusion started when I was able to easily create new badges even though (as I can see now) the only way to actually use them is by manually granting them to individual users. In other words, the problem is that being able to generate new badges misleads the user to believe that these can be used just like any other badge So when I saw the option “Allow bdage to be used as a title” and thought, great, so I can create badges that people can use as titles (I know, for discourse veterans, this sounds stupid, but bear with me).

Long story short: I would suggest

  1. to add some help text in the “create new badge” dialogue informing the user that “unless badge SQL is enabled, the newly created badge can only be granted manually to individual users. Badge SQL can only be enabled via the command line.”
  2. to add a small help text under “Allow bdage to be used as a title” stating “Note that a user need to be granted the badge before they can select it”

How are user titles generated?
(Tobias Eigen) #15

A simpler suggestion methinks would be to just add a bullet about badge sql towards the bottom of the install instructions:


(Christoph) #16

That’s assuming that the person installing discourse is the same as the one getting confused about badges. To say the least, that’s not the case for all hosted discourse clients.


(Tobias Eigen) #17

Sure - that’s a good point. I had the same experience last summer and it was super frustrating. This issue was a big one back then because it affects people who already had discourse set up and upgraded at some point and the functionality changed/seemed to go away on us.

But the fix hasn’t happened yet and bikeshedding and all that. I honestly prefer to see discourse developers improving the frontend and like the approach they follow to try to keep the admin settings as simple as possible. Every change and every new text requires localization.

Badge sql queries are an advanced feature and should be implemented with care, which is presumably the reason why it has been hidden in the first place. I think the site admin should know about them.

If some commonly used queries could be exposed in a safe way via the admin UI, I’d totally welcome that. Perhaps this is already on the roadmap:


(Christoph) #18

That is indeed an important point that I had not thought of. And I see that it’s always a matter of prioritizing. Nonetheless, I feel it is my duty to report (admin-)user experiences that these folks will never have, given their familiarity with discourse. In the end, it is up to them to figure out what is feasible.

1 Like

(Steve Pavlina) #19

I agree that it would be nice to be able to add custom badges without having to use SQL. I just started configuring Discourse a few days ago and got to the badges section today. When I tried to create a new badge, it looked like something was missing – i.e. where am I supposed to set up the rules/triggers for each new badge? I had to search to find this thread about it. At least offer a few options like inputting triggering thresholds for posts, likes given, likes received, etc.

It would also be nice to edit the text for the default badges. I’d rather not provide a Lounge category for the Level 3 members (since I don’t think we’ll have many with a private forum), so I’d like to remove that from the description. I want to bring the text into alignment with the customizations that are permitted.

1 Like

(Harold Martin) #20

Can customers who have “fully hosted Discourse instances” get this configuration changed as well?