We have 600 instances where people don't need to request accounts and that have no spam protection enabled.

WITH t1 AS (
	SELECT w.id, s.name, s.value FROM wikis w
	LEFT JOIN wiki_settings s on w.id=s.wiki_id AND s.name='wwCaptchaQuestions'
	WHERE w.deleted_at IS NULL),

t2 AS (SELECT wiki_id FROM wiki_settings WHERE name='wwExtEnableConfirmAccount' AND value=1)

SELECT CASE
		WHEN t2.wiki_id IS NOT NULL THEN 'Accounts must be confirmed'
		WHEN t1.name IS NULL THEN 'No spam protection'
		WHEN t1.value='{"How many vowels are in this question?":["12","twelve"],"What is the chemical formula of water?":["H2O"],"2 + 4 = ?":["6","six"]}' THEN 'Default questions'
		ELSE 'Custom questions'
	END AS category,
	COUNT(*) AS count
FROM t1
LEFT JOIN t2 ON t1.id=t2.wiki_id
GROUP BY category

We've already had to fight spam bots in the past: T365443
There are two more examples of infested instances:
https://wikibase.world/wiki/Special:ListUsers?username=&group=&creationSort=1&desc=1&wpsubmit=&wpFormIdentifier=mw-listusers-form&limit=50
https://celltypes.wikibase.cloud/wiki/Special:ListUsers?username=&group=&creationSort=1&desc=1&wpsubmit=&wpFormIdentifier=mw-listusers-form&limit=50

Both managers have been notified.

SELECT d.domain, s.users, c.name, c2.value  from wikis d
INNER JOIN wiki_site_stats s on d.id=s.wiki_id
LEFT JOIN wiki_settings c ON d.id=c.wiki_id AND c.name='wwExtEnableConfirmAccount' AND c.value=1
LEFT JOIN wiki_settings c2 ON d.id=c2.wiki_id AND c2.name='wwCaptchaQuestions'
WHERE s.users > 10
ORDER BY users DESC

We should probably enforce spam protection for accounts that don't require account confirmation (and make sure they change their default questions too - see T374091).
We could also make 'Confirm Accounts' option enabled by default. After all, most of our instances are maintained by a single user who is also the manager.

Design solution

Figma file

We remove the default questions and instead guide users to create custom questions. The changes can be applied to all new users, users who don't have the feature enabled, users who have the feature enabled AND are using custom questions already. [TBD: how to proceed with users who have the feature enabled AND are using the default questions.]

I have marked all changes to the current design with pink dots 🩷.

AC's:

• no more default questions. instead there's only one Q&A field that has a placeholder question in it
• New design specified in figma has been implemented
• New design is applied to all new and existing users [TBD: how to proceed with users who have the feature enabled AND are using the default questions.]