[aur-dev] Removing spammer accounts
I analyzed recent additions to the AUR user database in the hope of finding a way to delete all spammer accounts. Unfortunately, the account creation bot seems to be a bit more clever than I thought. Various different user names (both random user names, user names extracted from IRC channels etc.) and various different email addresses (different hosts, some local parts match the user name and some don't) were used. The only obvious pattern is that following fields are always empty: * RealName * IRCNick * PGPKey However, using that filter and also filtering by ID (51617 is the first account obviously created by a bot) and last login (1363737600 is 2013-03-20 00:00:00 which is after the login form has been disabled for Tor users) still leaves 18378 accounts: mysql> SELECT COUNT(*) FROM Users WHERE RealName = '' AND -> IRCNick = '' AND PGPKey = '' AND ID >= 51617 AND -> ID <= 70072 AND LastLogin < 1363737600; +----------+ | COUNT(*) | +----------+ | 18378 | +----------+ 1 row in set (0.01 sec) My suggestion is: 1. Set the password field of these accounts to "", so that they will no longer be able to login. They can still use the password reset form. 2. Delete all accounts that still have an empty password field in ~2 weeks. If there are any objections or alternative suggestions, please let me know. Otherwise, I will unset password fields before I re-enable the registration form (on Tuesday, 2013-03-26). Regards, Lukas
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 Lukas Fleischer <archlinux@cryptocrack.de> wrote:
I analyzed recent additions to the AUR user database in the hope of finding a way to delete all spammer accounts.
Unfortunately, the account creation bot seems to be a bit more clever than I thought. Various different user names (both random user names, user names extracted from IRC channels etc.) and various different email addresses (different hosts, some local parts match the user name and some don't) were used.
The only obvious pattern is that following fields are always empty:
* RealName * IRCNick * PGPKey
However, using that filter and also filtering by ID (51617 is the first account obviously created by a bot) and last login (1363737600 is 2013-03-20 00:00:00 which is after the login form has been disabled for Tor users) still leaves 18378 accounts:
mysql> SELECT COUNT(*) FROM Users WHERE RealName = '' AND -> IRCNick = '' AND PGPKey = '' AND ID >= 51617 AND -> ID <= 70072 AND LastLogin < 1363737600; +----------+ | COUNT(*) | +----------+ | 18378 | +----------+ 1 row in set (0.01 sec)
My suggestion is:
1. Set the password field of these accounts to "", so that they will no longer be able to login. They can still use the password reset form.
2. Delete all accounts that still have an empty password field in ~2 weeks.
If there are any objections or alternative suggestions, please let me know. Otherwise, I will unset password fields before I re-enable the registration form (on Tuesday, 2013-03-26).
Regards, Lukas
The only suggestion I have to include a message about passwords being cleared on the login screen, more specifically the failed login screen. - -- Sent from my Android Phone. Daniel Wallace Arch Linux Trusted User GTManfred -----BEGIN PGP SIGNATURE----- Version: APG v1.0.8 iQFUBAEBCAA+BQJRT4JzNxxEYW5pZWwgV2FsbGFjZSAoZ3RtYW5mcmVkKSA8ZGFu aWVsLndhbGxhY2VAZ2F0ZWNoLmVkdT4ACgkQX6XlVE8BDUjk4gf/fCqpAr8OUnIk oy/1L0iZzIg0McX9yysXd1L49tWlrFOMD+NLl+14j5ZdOmsbUx/GrLDngQVvqO9Z BM8NWd6V9gPCk6R3dxZybtojsvR/TbivilmH36arkdNyLBjNOMaIyxTAAnpGZEOJ vETGYDmQgb5fw1sqfSrrLqND/s4KXfcSLngQW6eeqOSvHUJRvNGLZDE3UTT2qS1w GF2Z87B5d9SMTauvcTicFvN0pleE22upB9NA4ldtO0eIhxFfdLuSf8Qi+RUX6nl0 uZXMDa+nKHZ9A0Xcma7lKKpzOy1haj9ZBAOs8xN+Sl5JNmEhXT0fKmPO3AvNYHgh nrrn6LYIiw== =CH2W -----END PGP SIGNATURE-----
On Sun, Mar 24, 2013 at 11:37:53PM +0100, Lukas Fleischer wrote:
I analyzed recent additions to the AUR user database in the hope of finding a way to delete all spammer accounts.
[...]
My suggestion is:
1. Set the password field of these accounts to "", so that they will no longer be able to login. They can still use the password reset form.
2. Delete all accounts that still have an empty password field in ~2 weeks.
Purged. I will make up (and execute) a SQL query to sync the number of votes, which are stored separately, later.
If there are any objections or alternative suggestions, please let me know. Otherwise, I will unset password fields before I re-enable the registration form (on Tuesday, 2013-03-26).
Regards, Lukas
On Tue, Apr 23, 2013 at 09:12:49PM +0200, Lukas Fleischer wrote:
On Sun, Mar 24, 2013 at 11:37:53PM +0100, Lukas Fleischer wrote:
I analyzed recent additions to the AUR user database in the hope of finding a way to delete all spammer accounts.
[...]
My suggestion is:
1. Set the password field of these accounts to "", so that they will no longer be able to login. They can still use the password reset form.
2. Delete all accounts that still have an empty password field in ~2 weeks.
Purged. I will make up (and execute) a SQL query to sync the number of votes, which are stored separately, later.
Done. Note that this fixed a couple of other inconsistencies as well (only +/-1 vote in most cases -- apart from the spam votes).
If there are any objections or alternative suggestions, please let me know. Otherwise, I will unset password fields before I re-enable the registration form (on Tuesday, 2013-03-26).
Regards, Lukas
participants (2)
-
Daniel Wallace
-
Lukas Fleischer