OpeningDesign/gitea_customization

Fork 0

Spam fighting #3

New issue

Open

opened 2024-08-10 08:29:24 +00:00 by oliverpool · 4 comments

oliverpool commented

2024-08-10 08:29:24 +00:00

ENABLE_CAPTCHA was set to true, but since cache.ENABLED was set to false, it didn't work:

[W] Cache Service Disabled so that captcha disabled too

I enabled the cache and now new registration must solve the captcha.

`ENABLE_CAPTCHA` was set to true, but since `cache.ENABLED` was set to false, it didn't work: ``` [W] Cache Service Disabled so that captcha disabled too ``` I enabled the `cache` and now new registration must solve the captcha.

oliverpool commented

2024-08-10 08:51:14 +00:00

Author

Remove accounts who did not login 24h after creation:

select id,email,created_unix from user where prohibit_login = false and created_unix > 1719878400 and last_login_unix < created_unix +(24*3600) order by created_unix asc limit 10;

update user set prohibit_login = true where prohibit_login = false and created_unix > 1719878400 and last_login_unix < created_unix + (24*3600);

Query OK, 1556 rows affected (0.013 sec)

Remove accounts with seo in the domain name:

select id,email,created_unix from user where prohibit_login = false and created_unix > 1719878400 and email like '%@%seo%' order by created_unix asc limit 10;

update user set prohibit_login = true where prohibit_login = false and created_unix > 1719878400 and email like '%@%seo%';

Query OK, 124 rows affected (0.005 sec)

Remove accounts with 3 dots and gmail.com in the domain name:

select id,email,created_unix from user where prohibit_login = false and created_unix > 1719878400 and email like '%.%.%.%@gmail.com' order by created_unix asc limit 10;

update user set prohibit_login = true where prohibit_login = false and created_unix > 1719878400 and email like '%.%.%.%@gmail.com';

Query OK, 73 rows affected (0.005 sec)

Remove accounts who did not login 24h after creation: ```sql select id,email,created_unix from user where prohibit_login = false and created_unix > 1719878400 and last_login_unix < created_unix +(24*3600) order by created_unix asc limit 10; update user set prohibit_login = true where prohibit_login = false and created_unix > 1719878400 and last_login_unix < created_unix + (24*3600); Query OK, 1556 rows affected (0.013 sec) ``` Remove accounts with `seo` in the domain name: ```sql select id,email,created_unix from user where prohibit_login = false and created_unix > 1719878400 and email like '%@%seo%' order by created_unix asc limit 10; update user set prohibit_login = true where prohibit_login = false and created_unix > 1719878400 and email like '%@%seo%'; Query OK, 124 rows affected (0.005 sec) ``` Remove accounts with 3 dots and gmail.com in the domain name: ```sql select id,email,created_unix from user where prohibit_login = false and created_unix > 1719878400 and email like '%.%.%.%@gmail.com' order by created_unix asc limit 10; update user set prohibit_login = true where prohibit_login = false and created_unix > 1719878400 and email like '%.%.%.%@gmail.com'; Query OK, 73 rows affected (0.005 sec) ```

oliverpool commented

2024-08-10 08:56:33 +00:00

Author

Remove accounts with review in the domain name:

select id,email,created_unix from user where prohibit_login = false and created_unix > 1719878400 and email like '%@%review%' order by created_unix asc limit 10;

update user set prohibit_login = true where prohibit_login = false and created_unix > 1719878400 and email like '%@%review%';

Query OK, 42 rows affected (0.004 sec)

Check all remaining domains:

select substring_index(email, '@', -1) as `domain`, count(*) from user where prohibit_login = false and created_unix > 1719878400 group by substring_index(email, '@', -1) order by count(*) desc;

They all look suspicious (only one gmail, with only 2 dots - which is also a spam):

update user set prohibit_login = true where prohibit_login = false and created_unix > 1719878400;
Query OK, 228 rows affected (0.005 sec)

Remove accounts with `review` in the domain name: ```sql select id,email,created_unix from user where prohibit_login = false and created_unix > 1719878400 and email like '%@%review%' order by created_unix asc limit 10; update user set prohibit_login = true where prohibit_login = false and created_unix > 1719878400 and email like '%@%review%'; Query OK, 42 rows affected (0.004 sec) ``` Check all remaining domains: ```sql select substring_index(email, '@', -1) as `domain`, count(*) from user where prohibit_login = false and created_unix > 1719878400 group by substring_index(email, '@', -1) order by count(*) desc; ``` They all look suspicious (only one gmail, with only 2 dots - which is also a spam): ```sql update user set prohibit_login = true where prohibit_login = false and created_unix > 1719878400; Query OK, 228 rows affected (0.005 sec) ```

oliverpool commented

2024-08-10 09:15:16 +00:00

Author

User purge:

#!/bin/bash

if [ $# -ne 2 ]; then
  echo "Usage: $0 <start_number> <end_number>"
  exit 1
fi

for num in $(seq "$1" "$2"); do
  sudo -u git gitea admin user delete --config=/etc/gitea/app.ini --id $num --purge
done

./remove_users.sh 57 2079

User purge: ```sh #!/bin/bash if [ $# -ne 2 ]; then echo "Usage: $0 <start_number> <end_number>" exit 1 fi for num in $(seq "$1" "$2"); do sudo -u git gitea admin user delete --config=/etc/gitea/app.ini --id $num --purge done ``` ```shell ./remove_users.sh 57 2079 ```

theoryshaw commented

2024-08-13 13:27:53 +00:00

Owner

Thanks @oliverpool

Unfortunately there's been 200 users signed up in the last 3 days. Do you think the captcha is working?

Thanks @oliverpool Unfortunately there's been 200 users signed up in the last 3 days. Do you think the captcha is working?

No labels

No milestone

No project

No assignees

2 participants

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

OpeningDesign/gitea_customization#3

No description provided.

Rows
Columns